Make delicious recipes!

Custom write-SQL for Columns

Annotation org.hibernate.annotations.ColumnTransformer can be used to specify custom SQL during a write/update operation.

Note: The SQL has to be native SQL (and not HQL).
Due to this, the database-independent nature of Hibernate is lost for that Entity.

Requirement: Suppose there is a requirement to have two columns - created_date and updated_date in some table.
created_date should be filled automatically whenever a new record is inserted into table.
updated_date should be updated automatically whenever the corresponding row is modified.

This can be achieved by adding the fields createdDate and updatedDate to the previous code:

Note: Repetitive code from previous example is marked with ...
Only interesting thing here are the annotations used above createdDate and updatedDate.

import java.util.Date;
import javax.persistence.*;
import static javax.persistence.GenerationType.*;
import org.hibernate.annotations.ColumnTransformer;

@Entity @Table(...)
public class Person 
   @Id @Column(...)
   private int id;

   @Column(name = "UPDATED_DATE")
   @ColumnTransformer(write="Coalesce(Current_Timestamp, ?)")
   private Date updatedDate;  
   @Column(name = "CREATED_DATE", updatable = false) // This prevents column setting during update operation.
   @ColumnTransformer(write="Coalesce(Current_Timestamp, ?)")
   private Date createdDate;



The above example prints the below SQL when run.

/* SQL for insert operation */
/* Note both created_date and updated_date are getting set to the database-date */
    insert into Person
        (Coalesce(Current_Timestamp, ?), Coalesce(Current_Timestamp, ?), ?)

/* SQL for update operation */
/* Note that only updated_date is being set in update operation */
    update Person 
        UPDATED_DATE=Coalesce(Current_Timestamp, ?) 

  1. Coalesce (...) is similar to Oracle's nvl() function and supported by all major DBs.
    It returns the first non-null argument.

  2. Current_Timestamp is similar to Oracle's sysdate() function and supported by all major DBs.

Like us on Facebook to remain in touch
with the latest in technology and tutorials!

Got a thought to share or found a
bug in the code?
We'd love to hear from you:

Email: (Your email is not shared with anybody)

Facebook comments:

Site Owner: Sachin Goyal