Using custom SQL for Hibernate CRUD Operations

Hibernate lets you override every single SQL statement generated with CRUD Operations. We have already covered native SQL query usage in this tutorial Using native Queries with Hibernate and JPA but you can also override the SQL statement used to load or change the state of entities. Let’s see how.

Let’s see how to override CRUD Operations with a basic Hibernate example:

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import org.hibernate.annotations.ResultCheckStyle;
import org.hibernate.annotations.SQLDelete;
import org.hibernate.annotations.SQLInsert;
import org.hibernate.annotations.SQLUpdate;

@Entity
@SQLInsert(sql = "INSERT INTO {h-schema}Person (name, id) VALUES (?, ?)")
@SQLDelete(sql = "DELETE FROM {h-schema}Person WHERE id = ?", check = ResultCheckStyle.COUNT)
@SQLUpdate(sql = "UPDATE {h-schema}Person SET name = ? WHERE id = ? ")
public class Person {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	Long id;
	String name;


	public Person(String name) {
		super();
		this.name = name;
	}

	public Person() {
		super();
	}

	public Long getId() {
		return id;
	}

	private void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}




}

In this example Entity class, @SQLInsert, @SQLUpdate, @SQLDelete override the INSERT statement, UPDATE statement and DELETE statement.
To check that the execution happens correctly, Hibernate allows you to define one of those three strategies:

NONE: no check is performed: the store procedure is expected to fail upon issues
COUNT: use of rowcount to check that the update is successful
PARAM: like COUNT but using an output parameter rather that the standard mechanism 

When it’s convenient to use a custom SQL Statements? there are some use cases where, for example, a non nullable column prevents to complete an insert or an update. This is often the case if you are testing an application and you don’t have all the parameters available.

By using a custom @SQLInsert you could inject a dummy value for your SQL Query:

@Entity
@Table(name=”Customer”)
@SQLInsert(sql=”Insert into Customer (name, surname, address) value (?, ?, ”dummy”), check=ResultCheckStyle.COUNT)

You can also override the SQL load statement by a native SQL query or a HQL query. You just have to refer to a named query with the @Loader annotation.

@Loader(namedQuery = "find_foo_by_id")
@NamedNativeQuery(
name = "find_foo_by_id",
query = "SELECT id, name FROM {h-schema}FOO WHERE id = ?",
resultClass = CustomEntity.class
)

Besides, instead of a custom SQL statement, you can call a stored procedure to perform the operation:

@org.hibernate.annotations.SQLInsert(
    sql = "{call INSERT_CUSTOMER(?, ?, ?)}",
    callable = true
)
@org.hibernate.annotations.SQLUpdate(
    sql = "{call UPDATE_CUSTOMER(?, ?, ?)}",
    callable = true,
    check = ResultCheckStyle.NONE
)
@org.hibernate.annotations.SQLDelete(
    sql = "{call DELETE_CUSTOMER(?)}",
    callable = true
)
@Entity
@Table(name = "CUSTOMER")
public class Customer {
    // ...
}

The source code for this tutorial is available here: https://github.com/fmarchioni/masterspringboot/tree/master/jpa/advancedwebapp

Found the article helpful? if so please follow us on Socials