2.4.11. Custom SQL for CRUD operations
Hibernate gives you the ability to override every single SQL statement generated. We have seen native SQL query usage already, but you can also override the SQL statement used to load or change the state of entities.
@Entity @Table(name="CHAOS") @SQLInsert( sql="INSERT INTO CHAOS(size, name, nickname, id) VALUES(?,upper(?),?,?)") @SQLUpdate( sql="UPDATE CHAOS SET size = ?, name = upper(?), nickname = ? WHERE id = ?") @SQLDelete( sql="DELETE CHAOS WHERE id = ?") @SQLDeleteAll( sql="DELETE CHAOS") @Loader(namedQuery = "chaos") @NamedNativeQuery(name="chaos", query="select id, size, name, lower( nickname ) as nickname from CHAOS where id= ?", resultClass = Chaos.class) public class Chaos { @Id private Long id; private Long size; private String name; private String nickname;
@SQLInsert, @SQLUpdate, @SQLDelete, @SQLDeleteAll respectively override the INSERT statement, UPDATE statement, DELETE statement, DELETE statement to remove all entities.
If you expect to call a store procedure, be sure to set the
callable attribute to true (@SQLInsert(callable=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 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
To define the result check style, use the
check parameter (@SQLUpdate(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
You can use the exact same set of annotations to override the collection related statements.
@OneToMany
@JoinColumn(name="chaos_fk")
@SQLInsert( sql="UPDATE CASIMIR_PARTICULE SET chaos_fk = ? where id = ?") @SQLDelete( sql="UPDATE CASIMIR_PARTICULE SET chaos_fk = null where id = ?")
private Set<CasimirParticle> particles = new HashSet<CasimirParticle>();
The parameters order is important and is defined by the order Hibernate handle properties. You can see the expected order by enabling debug logging for the
org.hibernate.persister.entity level. With this level enabled Hibernate will print out the static SQL that is used to create, update, delete etc. entities. (To see the expected sequence, remember to not include your custom SQL through annotations as that will override the Hibernate generated static sql.)
Overriding SQL statements for secondary tables is also possible using
@org.hibernate.annotations.Table and either (or all) attributes sqlInsert, sqlUpdate, sqlDelete:
@Entity
@SecondaryTables({
@SecondaryTable(name = "`Cat nbr1`"),
@SecondaryTable(name = "Cat2"})
@org.hibernate.annotations.Tables( {
@Table(appliesTo = "Cat", comment = "My cat table" ),
@Table(appliesTo = "Cat2", foreignKey = @ForeignKey(name="FK_CAT2_CAT"), fetch = FetchMode.SELECT,
sqlInsert=@SQLInsert(sql="insert into Cat2(storyPart2, id) values(upper(?), ?)") )
} )
public class Cat implements Serializable {
The previous example also show that you can give a comment to a given table (primary or secondary): This comment will be used for DDL generation.
