Hibernate JPA @SQLDelete Example

@SQLDelete

The @SQLDelete annotation is used to specify a custom SQL DELETE statement for the currently annotated entity or collection.

SQLDelete used for custom SQL statement for delete of an entity/collection.

Custom CRUD

@Entity(name = "Person")
@SQLInsert(
sql = "INSERT INTO person (name, id, valid) VALUES (?, ?, true) ",
check = ResultCheckStyle.COUNT
)
@SQLUpdate(
sql = "UPDATE person SET name = ? where id = ? "
)
@SQLDelete(
sql = "UPDATE person SET valid = false WHERE id = ? "
)
@Loader(namedQuery = "find_valid_person")
@NamedNativeQueries({
@NamedNativeQuery(
name = "find_valid_person",
query = "SELECT id, name " +
"FROM person " +
"WHERE id = ? and valid = true",
resultClass = Person.class
)
})
public static class Person {

@Id
@GeneratedValue
private Long id;

private String name;

@ElementCollection
@SQLInsert(
sql = "INSERT INTO person_phones (person_id, phones, valid) VALUES (?, ?, true) ")
@SQLDeleteAll(
sql = "UPDATE person_phones SET valid = false WHERE person_id = ?")
@Where( clause = "valid = true" )
private List<String> phones = new ArrayList<>();

//Getters and setters are omitted for brevity

}
In the example above, the entity is mapped so that entries are soft-deleted (the records are not removed from the database, but instead, a flag marks the row validity). The Person entity benefits from custom INSERT, UPDATE, and DELETE statements which update the valid column accordingly. The custom @Loader is used to retrieve only Person rows that are valid.

The same is done for the phones collection. The @SQLDeleteAll and the SQLInsert queries are used whenever the collection is modified.

You can also call a store procedure using the custom CRUD statements. The only requirement is to set the callable attribute to true.

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 constraint violations.

count: use of row-count returned by the executeUpdate() method call to check that the update was successful.

param: like count but using a CallableStatement output parameter.

To define the result check style, use the check parameter.

The parameter order is important and is defined by the order Hibernate handles properties. You can see the expected order by enabling debug logging, so Hibernate can print out the static SQL that is used to create, update, delete entities.

To see the expected sequence, remember to not include your custom SQL through annotations or mapping files as that will override the Hibernate generated static SQL.

Overriding SQL statements for secondary tables is also possible using @org.hibernate.annotations.Table and the sqlInsert, sqlUpdate, sqlDelete attributes.

@SQLDeleteAll
The @SQLDeleteAll annotation is used to specify a custom SQL DELETE statement when removing all elements of the currently annotated collection.