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;  
      
   //@Generated(GenerationTime.INSERT)  
   @Column(name = "CREATED_DATE", updatable = false) // This prevents column setting during update operation.
   @ColumnTransformer(write="Coalesce(Current_Timestamp, ?)")
   private Date createdDate;

   .
   .
   .   
}



Output


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
        (CREATED_DATE, UPDATED_DATE, ID) 
    values
        (Coalesce(Current_Timestamp, ?), Coalesce(Current_Timestamp, ?), ?)


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


Notes:
  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:

Name:
Email: (Your email is not shared with anybody)
Comment:

Facebook comments:

Site Owner: Sachin Goyal