Creation timestamp and last update timestamp with Hibernate and MySQL

Question

For a certain Hibernate entity we have a requirement to store its creation time and the last time it was updated   How would you design this    What data types would you use in the database  assuming MySQL  possibly in a different timezone that the JVM    Will the data types be timezone-aware  What data types would you use in Java  Date  Calendar  long        Whom would you make responsible for setting the timestamps mdash the database  the ORM framework  Hibernate   or the application programmer  What annotations would you use for the mapping  e g   Temporal     I m not only looking for a working solution  but for a safe and well-designed solution

User · Answer

You might consider storing the time as a DateTime  and in UTC  I typically use DateTime instead of Timestamp because of the fact that MySql converts dates to UTC and back to local time when storing and retrieving the data  I d rather keep any of that kind of logic in one place  Business layer   I m sure there are other situations where using Timestamp is preferable though

User · Answer

Thanks everyone who helped   After doing some research myself  I m the guy who asked the question   here is what I found to make sense most    Database column type  the timezone-agnostic number of milliseconds since 1970 represented as decimal 20  because 2 64 has 20 digits and disk space is cheap  let s be straightforward   Also  I will use neither DEFAULT CURRENT TIMESTAMP  nor triggers   I want no magic in the DB  Java field type  long   The Unix timestamp is well supported across various libs  long has no Y2038 problems  timestamp arithmetic is fast and easy  mainly operator  lt  and operator    assuming no days months years are involved in the calculations    And  most importantly  both primitive longs and java lang Longs are immutable mdash effectively passed by value mdash unlike java util Dates  I d be really pissed off to find something like foo getLastUpdate   setTime System currentTimeMillis    when debugging somebody else s code  The ORM framework should be responsible for filling in the data automatically  I haven t tested this yet  but only looking at the docs I assume that  Temporal will do the job  not sure about whether I might use  Version for this purpose    PrePersist and  PreUpdate are good alternatives to control that manually   Adding that to the layer supertype  common base class  for all entities  is a cute idea provided that you really want timestamping for all of your entities

User · Answer

In case you are using the Session API the PrePersist and PreUpdate callbacks won t work according to this answer    I am using Hibernate Session s persist   method in my code so the only way I could make this work was with the code below and following this blog post  also posted in the answer     MappedSuperclass public abstract class AbstractTimestampEntity         Temporal TemporalType TIMESTAMP       Column name    created       private Date created new Date          Temporal TemporalType TIMESTAMP       Column name    updated        Version     private Date updated       public Date getCreated             return created             public void setCreated Date created            this created   created             public Date getUpdated             return updated             public void setUpdated Date updated            this updated   updated

User · Answer

Thanks everyone who helped   After doing some research myself  I m the guy who asked the question   here is what I found to make sense most    Database column type  the timezone-agnostic number of milliseconds since 1970 represented as decimal 20  because 2 64 has 20 digits and disk space is cheap  let s be straightforward   Also  I will use neither DEFAULT CURRENT TIMESTAMP  nor triggers   I want no magic in the DB  Java field type  long   The Unix timestamp is well supported across various libs  long has no Y2038 problems  timestamp arithmetic is fast and easy  mainly operator  lt  and operator    assuming no days months years are involved in the calculations    And  most importantly  both primitive longs and java lang Longs are immutable mdash effectively passed by value mdash unlike java util Dates  I d be really pissed off to find something like foo getLastUpdate   setTime System currentTimeMillis    when debugging somebody else s code  The ORM framework should be responsible for filling in the data automatically  I haven t tested this yet  but only looking at the docs I assume that  Temporal will do the job  not sure about whether I might use  Version for this purpose    PrePersist and  PreUpdate are good alternatives to control that manually   Adding that to the layer supertype  common base class  for all entities  is a cute idea provided that you really want timestamping for all of your entities

User · Answer

With Olivier s solution  during update statements you may run into      com mysql jdbc exceptions jdbc4 MySQLIntegrityConstraintViolationException  Column  created  cannot be null   To solve this  add updatable false to the  Column annotation of  created  attribute    Temporal TemporalType TIMESTAMP   Column name    created   nullable   false  updatable false  private Date created

User · Answer

Now there is also  CreatedDate and  LastModifiedDate annotations      https   programmingmitra blogspot fr 2017 02 automatic-spring-data-jpa-auditing-saving-CreatedBy-createddate-lastmodifiedby-lastmodifieddate-automatically html   Spring framework

User · Answer

A good approach is to have a common base class for all your entities  In this base class  you can have your id property if it is commonly named in all your entities  a common design   your creation and last update date properties    For the creation date  you simply keep a java util Date property  Be sure  to always initialize it with new Date     For the last update field  you can use a Timestamp property  you need to map it with  Version  With this Annotation the property will get updated automatically by Hibernate  Beware that Hibernate will also apply optimistic locking  it s a good thing

User · Answer

Just to reinforce  java util Calender is not for Timestamps   java util Date is for a moment in time  agnostic of regional things like timezones   Most database store things in this fashion  even if they appear not to  this is usually a timezone setting in the client software  the data is good

User · Answer

If you are using the JPA annotations  you can use  PrePersist and  PreUpdate event hooks do this    Entity  Table name    entities       public class Entity            private Date created    private Date updated      PrePersist   protected void onCreate         created   new Date            PreUpdate   protected void onUpdate         updated   new Date            or you can use the  EntityListener annotation on the class and place the event code in an external class

User · Answer

Following code worked for me   package com my backend models   import java util Date   import javax persistence GeneratedValue  import javax persistence GenerationType  import javax persistence Id  import javax persistence MappedSuperclass   import com fasterxml jackson annotation JsonIgnore   import org hibernate annotations ColumnDefault  import org hibernate annotations CreationTimestamp  import org hibernate annotations UpdateTimestamp   import lombok Getter  import lombok Setter    MappedSuperclass  Getter  Setter public class BaseEntity         Id      GeneratedValue strategy   GenerationType IDENTITY      protected Integer id        CreationTimestamp      ColumnDefault  CURRENT TIMESTAMP       protected Date createdAt        UpdateTimestamp      ColumnDefault  CURRENT TIMESTAMP       protected Date updatedAt

User · Answer

You might consider storing the time as a DateTime  and in UTC  I typically use DateTime instead of Timestamp because of the fact that MySql converts dates to UTC and back to local time when storing and retrieving the data  I d rather keep any of that kind of logic in one place  Business layer   I m sure there are other situations where using Timestamp is preferable though

User · Answer

If you are using the JPA annotations  you can use  PrePersist and  PreUpdate event hooks do this    Entity  Table name    entities       public class Entity            private Date created    private Date updated      PrePersist   protected void onCreate         created   new Date            PreUpdate   protected void onUpdate         updated   new Date            or you can use the  EntityListener annotation on the class and place the event code in an external class

User · Answer

For those whose want created or modified user detail along with the time using JPA and Spring Data can follow this  You can add   CreatedDate  LastModifiedDate  CreatedBy and  LastModifiedBy in the base domain  Mark the base domain with  MappedSuperclass and  EntityListeners AuditingEntityListener class  like shown below   MappedSuperclass  EntityListeners AuditingEntityListener class  public class BaseDomain implements Serializable         CreatedDate     private Date createdOn        LastModifiedDate     private Date modifiedOn        CreatedBy     private String createdBy        LastModifiedBy     private String modifiedBy      Since we marked the base domain with AuditingEntityListener we can tell JPA about currently logged in user  So we need to provide an implementation of AuditorAware and override getCurrentAuditor   method  And inside getCurrentAuditor   we need to return the currently authorized user Id  public class AuditorAwareImpl implements AuditorAware lt String gt         Override     public Optional lt String gt  getCurrentAuditor             Authentication authentication   SecurityContextHolder getContext   getAuthentication            return authentication    null   Optional empty     Optional ofNullable authentication getName              In the above code if Optional is not working you may using older spring data  In that case try changing Optional with String  Now for enabling the above Audtior implementation use the code below  Configuration  EnableJpaAuditing auditorAwareRef    quot auditorAware quot   public class JpaConfig        Bean     public AuditorAware lt String gt  auditorAware             return new AuditorAwareImpl             Now you can extend the BaseDomain class to all of your entity class where you want the created and modified date  amp  time along with user Id

User · Answer

For those whose want created or modified user detail along with the time using JPA and Spring Data can follow this  You can add   CreatedDate  LastModifiedDate  CreatedBy and  LastModifiedBy in the base domain  Mark the base domain with  MappedSuperclass and  EntityListeners AuditingEntityListener class  like shown below   MappedSuperclass  EntityListeners AuditingEntityListener class  public class BaseDomain implements Serializable         CreatedDate     private Date createdOn        LastModifiedDate     private Date modifiedOn        CreatedBy     private String createdBy        LastModifiedBy     private String modifiedBy      Since we marked the base domain with AuditingEntityListener we can tell JPA about currently logged in user  So we need to provide an implementation of AuditorAware and override getCurrentAuditor   method  And inside getCurrentAuditor   we need to return the currently authorized user Id  public class AuditorAwareImpl implements AuditorAware lt String gt         Override     public Optional lt String gt  getCurrentAuditor             Authentication authentication   SecurityContextHolder getContext   getAuthentication            return authentication    null   Optional empty     Optional ofNullable authentication getName              In the above code if Optional is not working you may using older spring data  In that case try changing Optional with String  Now for enabling the above Audtior implementation use the code below  Configuration  EnableJpaAuditing auditorAwareRef    quot auditorAware quot   public class JpaConfig        Bean     public AuditorAware lt String gt  auditorAware             return new AuditorAwareImpl             Now you can extend the BaseDomain class to all of your entity class where you want the created and modified date  amp  time along with user Id

User · Answer

Now there is also  CreatedDate and  LastModifiedDate annotations      https   programmingmitra blogspot fr 2017 02 automatic-spring-data-jpa-auditing-saving-CreatedBy-createddate-lastmodifiedby-lastmodifieddate-automatically html   Spring framework

User · Answer

Just to reinforce  java util Calender is not for Timestamps   java util Date is for a moment in time  agnostic of regional things like timezones   Most database store things in this fashion  even if they appear not to  this is usually a timezone setting in the client software  the data is good

User · Answer

I think it is neater not doing this in Java code  you can simply set column default value in MySql table definition

User · Answer

As data type in JAVA I strongly recommend to use java util Date  I ran into pretty nasty timezone problems when using Calendar  See this Thread   For setting the timestamps I would recommend using either an AOP approach or you could simply use Triggers on the table  actually this is the only thing that I ever find the use of triggers acceptable

User · Answer

As data type in JAVA I strongly recommend to use java util Date  I ran into pretty nasty timezone problems when using Calendar  See this Thread   For setting the timestamps I would recommend using either an AOP approach or you could simply use Triggers on the table  actually this is the only thing that I ever find the use of triggers acceptable

User · Answer

Just to reinforce  java util Calender is not for Timestamps   java util Date is for a moment in time  agnostic of regional things like timezones   Most database store things in this fashion  even if they appear not to  this is usually a timezone setting in the client software  the data is good

User · Answer

Following code worked for me   package com my backend models   import java util Date   import javax persistence GeneratedValue  import javax persistence GenerationType  import javax persistence Id  import javax persistence MappedSuperclass   import com fasterxml jackson annotation JsonIgnore   import org hibernate annotations ColumnDefault  import org hibernate annotations CreationTimestamp  import org hibernate annotations UpdateTimestamp   import lombok Getter  import lombok Setter    MappedSuperclass  Getter  Setter public class BaseEntity         Id      GeneratedValue strategy   GenerationType IDENTITY      protected Integer id        CreationTimestamp      ColumnDefault  CURRENT TIMESTAMP       protected Date createdAt        UpdateTimestamp      ColumnDefault  CURRENT TIMESTAMP       protected Date updatedAt

User · Answer

Taking the resources in this post along with information taken left and right from different sources  I came with this elegant solution  create the following abstract class  import java util Date   import javax persistence Column  import javax persistence MappedSuperclass  import javax persistence PrePersist  import javax persistence PreUpdate  import javax persistence Temporal  import javax persistence TemporalType    MappedSuperclass public abstract class AbstractTimestampEntity         Temporal TemporalType TIMESTAMP       Column name    created   nullable   false      private Date created        Temporal TemporalType TIMESTAMP       Column name    updated   nullable   false      private Date updated        PrePersist     protected void onCreate         updated   created   new Date                PreUpdate     protected void onUpdate         updated   new Date              and have all your entities extend it  for instance    Entity  Table name    campaign   public class Campaign extends AbstractTimestampEntity implements Serializable

User · Answer

With Olivier s solution  during update statements you may run into      com mysql jdbc exceptions jdbc4 MySQLIntegrityConstraintViolationException  Column  created  cannot be null   To solve this  add updatable false to the  Column annotation of  created  attribute    Temporal TemporalType TIMESTAMP   Column name    created   nullable   false  updatable false  private Date created

User · Answer

You might consider storing the time as a DateTime  and in UTC  I typically use DateTime instead of Timestamp because of the fact that MySql converts dates to UTC and back to local time when storing and retrieving the data  I d rather keep any of that kind of logic in one place  Business layer   I m sure there are other situations where using Timestamp is preferable though

User · Answer

Thanks everyone who helped   After doing some research myself  I m the guy who asked the question   here is what I found to make sense most    Database column type  the timezone-agnostic number of milliseconds since 1970 represented as decimal 20  because 2 64 has 20 digits and disk space is cheap  let s be straightforward   Also  I will use neither DEFAULT CURRENT TIMESTAMP  nor triggers   I want no magic in the DB  Java field type  long   The Unix timestamp is well supported across various libs  long has no Y2038 problems  timestamp arithmetic is fast and easy  mainly operator  lt  and operator    assuming no days months years are involved in the calculations    And  most importantly  both primitive longs and java lang Longs are immutable mdash effectively passed by value mdash unlike java util Dates  I d be really pissed off to find something like foo getLastUpdate   setTime System currentTimeMillis    when debugging somebody else s code  The ORM framework should be responsible for filling in the data automatically  I haven t tested this yet  but only looking at the docs I assume that  Temporal will do the job  not sure about whether I might use  Version for this purpose    PrePersist and  PreUpdate are good alternatives to control that manually   Adding that to the layer supertype  common base class  for all entities  is a cute idea provided that you really want timestamping for all of your entities

User · Answer

We had a similar situation  We were using Mysql 5 7   CREATE TABLE my table                     updated time TIMESTAMP DEFAULT CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP          This worked for us

User · Answer

A good approach is to have a common base class for all your entities  In this base class  you can have your id property if it is commonly named in all your entities  a common design   your creation and last update date properties    For the creation date  you simply keep a java util Date property  Be sure  to always initialize it with new Date     For the last update field  you can use a Timestamp property  you need to map it with  Version  With this Annotation the property will get updated automatically by Hibernate  Beware that Hibernate will also apply optimistic locking  it s a good thing

User · Answer

You can also use an interceptor to set the values  Create an interface called TimeStamped which your entities implement  public interface TimeStamped       public Date getCreatedDate        public void setCreatedDate Date createdDate       public Date getLastUpdated        public void setLastUpdated Date lastUpdatedDate       Define the interceptor  public class TimeStampInterceptor extends EmptyInterceptor        public boolean onFlushDirty Object entity  Serializable id  Object   currentState               Object   previousState  String   propertyNames  Type   types            if  entity instanceof TimeStamped                int indexOf   ArrayUtils indexOf propertyNames   lastUpdated                currentState indexOf    new Date                return true                    return false             public boolean onSave Object entity  Serializable id  Object   state               String   propertyNames  Type   types                if  entity instanceof TimeStamped                    int indexOf   ArrayUtils indexOf propertyNames   createdDate                    state indexOf    new Date                    return true                            return false            And register it with the session factory

User · Answer

You might consider storing the time as a DateTime  and in UTC  I typically use DateTime instead of Timestamp because of the fact that MySql converts dates to UTC and back to local time when storing and retrieving the data  I d rather keep any of that kind of logic in one place  Business layer   I m sure there are other situations where using Timestamp is preferable though

User · Answer

A good approach is to have a common base class for all your entities  In this base class  you can have your id property if it is commonly named in all your entities  a common design   your creation and last update date properties    For the creation date  you simply keep a java util Date property  Be sure  to always initialize it with new Date     For the last update field  you can use a Timestamp property  you need to map it with  Version  With this Annotation the property will get updated automatically by Hibernate  Beware that Hibernate will also apply optimistic locking  it s a good thing

User · Answer

In case you are using the Session API the PrePersist and PreUpdate callbacks won t work according to this answer    I am using Hibernate Session s persist   method in my code so the only way I could make this work was with the code below and following this blog post  also posted in the answer     MappedSuperclass public abstract class AbstractTimestampEntity         Temporal TemporalType TIMESTAMP       Column name    created       private Date created new Date          Temporal TemporalType TIMESTAMP       Column name    updated        Version     private Date updated       public Date getCreated             return created             public void setCreated Date created            this created   created             public Date getUpdated             return updated             public void setUpdated Date updated            this updated   updated

User · Answer

If we are using  Transactional in our methods   CreationTimestamp and  UpdateTimestamp will save the value in DB but will return null after using save        In this situation  using saveAndFlush      did the trick

User · Answer

Taking the resources in this post along with information taken left and right from different sources  I came with this elegant solution  create the following abstract class  import java util Date   import javax persistence Column  import javax persistence MappedSuperclass  import javax persistence PrePersist  import javax persistence PreUpdate  import javax persistence Temporal  import javax persistence TemporalType    MappedSuperclass public abstract class AbstractTimestampEntity         Temporal TemporalType TIMESTAMP       Column name    created   nullable   false      private Date created        Temporal TemporalType TIMESTAMP       Column name    updated   nullable   false      private Date updated        PrePersist     protected void onCreate         updated   created   new Date                PreUpdate     protected void onUpdate         updated   new Date              and have all your entities extend it  for instance    Entity  Table name    campaign   public class Campaign extends AbstractTimestampEntity implements Serializable

User · Answer

As data type in JAVA I strongly recommend to use java util Date  I ran into pretty nasty timezone problems when using Calendar  See this Thread   For setting the timestamps I would recommend using either an AOP approach or you could simply use Triggers on the table  actually this is the only thing that I ever find the use of triggers acceptable

User · Answer

You can also use an interceptor to set the values  Create an interface called TimeStamped which your entities implement  public interface TimeStamped       public Date getCreatedDate        public void setCreatedDate Date createdDate       public Date getLastUpdated        public void setLastUpdated Date lastUpdatedDate       Define the interceptor  public class TimeStampInterceptor extends EmptyInterceptor        public boolean onFlushDirty Object entity  Serializable id  Object   currentState               Object   previousState  String   propertyNames  Type   types            if  entity instanceof TimeStamped                int indexOf   ArrayUtils indexOf propertyNames   lastUpdated                currentState indexOf    new Date                return true                    return false             public boolean onSave Object entity  Serializable id  Object   state               String   propertyNames  Type   types                if  entity instanceof TimeStamped                    int indexOf   ArrayUtils indexOf propertyNames   createdDate                    state indexOf    new Date                    return true                            return false            And register it with the session factory

User · Answer

A good approach is to have a common base class for all your entities  In this base class  you can have your id property if it is commonly named in all your entities  a common design   your creation and last update date properties    For the creation date  you simply keep a java util Date property  Be sure  to always initialize it with new Date     For the last update field  you can use a Timestamp property  you need to map it with  Version  With this Annotation the property will get updated automatically by Hibernate  Beware that Hibernate will also apply optimistic locking  it s a good thing

User · Answer

What database column types you should use   Your first question was   What data types would you use in the database  assuming MySQL  possibly in a different timezone that the JVM   Will the data types be timezone-aware   In MySQL  the TIMESTAMP column type does a shifting from the JDBC driver local time zone to the database timezone  but it can only store timestamps up to 2038-01-19 03 14 07 999999  so it s not the best choice for the future  So  better to use DATETIME instead  which doesn t have this upper boundary limitation  However  DATETIME is not timezone aware  So  for this reason  it s best to use UTC on the database side and use the hibernate jdbc time zone Hibernate property   What entity property type you should use   Your second question was   What data types would you use in Java  Date  Calendar  long         On the Java side  you can use the Java 8 LocalDateTime  You can also use the legacy Date  but the Java 8 Date Time types are better since they are immutable  and don t do a timezone shifting to local timezone when logging them  Now  we can also answer this question   What annotations would you use for the mapping  e g   Temporal    If you are using the LocalDateTime or java sql Timestamp to map a timestamp entity property  then you don t need to use  Temporal since HIbernate already knows that this property is to be saved as a JDBC Timestamp  Only if you are using java util Date  you need to specify the  Temporal annotation  like this   Temporal TemporalType TIMESTAMP   Column name    quot created on quot   private Date createdOn   But  it s much better if you map it like this   Column name    quot created on quot   private LocalDateTime createdOn   How to generate the audit column values Your third question was   Whom would you make responsible for setting the timestamps   the database  the ORM framework  Hibernate   or the application programmer  What annotations would you use for the mapping  e g   Temporal    There are many ways you can achieve this goal  You can allow the database to do that   For the create on column  you could use a DEFAULT DDL constraint  like   ALTER TABLE post  ADD CONSTRAINT created on default  DEFAULT CURRENT TIMESTAMP   FOR created on   For the updated on column  you could use a DB trigger to set the column value with CURRENT TIMESTAMP   every time a given row is modified  Or  use JPA or Hibernate to set those  Let s assume you have the following database tables   And  each table has columns like   created by created on updated by updated on  Using Hibernate  CreationTimestamp and  UpdateTimestamp annotations Hibernate offers the  CreationTimestamp and  UpdateTimestamp annotations that can be used to map the created on and updated on columns  You can use  MappedSuperclass to define a base class that will be extended by all entities   MappedSuperclass public class BaseEntity          Id      GeneratedValue     private Long id         Column name    quot created on quot        CreationTimestamp     private LocalDateTime createdOn         Column name    quot created by quot       private String createdBy         Column name    quot updated on quot        UpdateTimestamp     private LocalDateTime updatedOn         Column name    quot updated by quot       private String updatedBy          Getters and setters omitted for brevity    And  all entities will extend the BaseEntity  like this   Entity name    quot Post quot    Table name    quot post quot   public class Post extend BaseEntity         private String title         OneToMany          mappedBy    quot post quot           cascade   CascadeType ALL          orphanRemoval   true           private List lt PostComment gt  comments   new ArrayList lt  gt            OneToOne          mappedBy    quot post quot           cascade   CascadeType ALL          orphanRemoval   true          fetch   FetchType LAZY           private PostDetails details         ManyToMany      JoinTable          name    quot post tag quot           joinColumns    JoinColumn              name    quot post id quot                     inverseJoinColumns    JoinColumn              name    quot tag id quot                      private List lt Tag gt  tags   new ArrayList lt  gt             Getters and setters omitted for brevity    However  even if the createdOn and updateOn properties are set by the Hibernate-specific  CreationTimestamp and  UpdateTimestamp annotations  the createdBy and updatedBy require registering an application callback  as illustrated by the following JPA solution  Using JPA  EntityListeners You can encapsulate the audit properties in an Embeddable   Embeddable public class Audit          Column name    quot created on quot       private LocalDateTime createdOn         Column name    quot created by quot       private String createdBy         Column name    quot updated on quot       private LocalDateTime updatedOn         Column name    quot updated by quot       private String updatedBy          Getters and setters omitted for brevity    And  create an AuditListener to set the audit properties  public class AuditListener          PrePersist     public void setCreatedOn Auditable auditable            Audit audit   auditable getAudit              if audit    null                audit   new Audit                auditable setAudit audit                       audit setCreatedOn LocalDateTime now             audit setCreatedBy LoggedUser get                  PreUpdate     public void setUpdatedOn Auditable auditable            Audit audit   auditable getAudit              audit setUpdatedOn LocalDateTime now             audit setUpdatedBy LoggedUser get              To register the AuditListener  you can use the  EntityListeners JPA annotation   Entity name    quot Post quot    Table name    quot post quot    EntityListeners AuditListener class  public class Post implements Auditable          Id     private Long id         Embedded     private Audit audit        private String title         OneToMany          mappedBy    quot post quot           cascade   CascadeType ALL          orphanRemoval   true           private List lt PostComment gt  comments   new ArrayList lt  gt            OneToOne          mappedBy    quot post quot           cascade   CascadeType ALL          orphanRemoval   true          fetch   FetchType LAZY           private PostDetails details         ManyToMany      JoinTable          name    quot post tag quot           joinColumns    JoinColumn              name    quot post id quot                     inverseJoinColumns    JoinColumn              name    quot tag id quot                      private List lt Tag gt  tags   new ArrayList lt  gt             Getters and setters omitted for brevity

User · Answer

Just to reinforce  java util Calender is not for Timestamps   java util Date is for a moment in time  agnostic of regional things like timezones   Most database store things in this fashion  even if they appear not to  this is usually a timezone setting in the client software  the data is good

User · Answer

Thanks everyone who helped   After doing some research myself  I m the guy who asked the question   here is what I found to make sense most    Database column type  the timezone-agnostic number of milliseconds since 1970 represented as decimal 20  because 2 64 has 20 digits and disk space is cheap  let s be straightforward   Also  I will use neither DEFAULT CURRENT TIMESTAMP  nor triggers   I want no magic in the DB  Java field type  long   The Unix timestamp is well supported across various libs  long has no Y2038 problems  timestamp arithmetic is fast and easy  mainly operator  lt  and operator    assuming no days months years are involved in the calculations    And  most importantly  both primitive longs and java lang Longs are immutable mdash effectively passed by value mdash unlike java util Dates  I d be really pissed off to find something like foo getLastUpdate   setTime System currentTimeMillis    when debugging somebody else s code  The ORM framework should be responsible for filling in the data automatically  I haven t tested this yet  but only looking at the docs I assume that  Temporal will do the job  not sure about whether I might use  Version for this purpose    PrePersist and  PreUpdate are good alternatives to control that manually   Adding that to the layer supertype  common base class  for all entities  is a cute idea provided that you really want timestamping for all of your entities

User · Answer

If you are using the JPA annotations  you can use  PrePersist and  PreUpdate event hooks do this    Entity  Table name    entities       public class Entity            private Date created    private Date updated      PrePersist   protected void onCreate         created   new Date            PreUpdate   protected void onUpdate         updated   new Date            or you can use the  EntityListener annotation on the class and place the event code in an external class

User · Answer

As data type in JAVA I strongly recommend to use java util Date  I ran into pretty nasty timezone problems when using Calendar  See this Thread   For setting the timestamps I would recommend using either an AOP approach or you could simply use Triggers on the table  actually this is the only thing that I ever find the use of triggers acceptable

User · Answer

You can just use  CreationTimestamp and  UpdateTimestamp    CreationTimestamp  Temporal TemporalType TIMESTAMP   Column name    create date   private Date createDate    UpdateTimestamp  Temporal TemporalType TIMESTAMP   Column name    modify date   private Date modifyDate

User · Answer

What database column types you should use   Your first question was   What data types would you use in the database  assuming MySQL  possibly in a different timezone that the JVM   Will the data types be timezone-aware   In MySQL  the TIMESTAMP column type does a shifting from the JDBC driver local time zone to the database timezone  but it can only store timestamps up to 2038-01-19 03 14 07 999999  so it s not the best choice for the future  So  better to use DATETIME instead  which doesn t have this upper boundary limitation  However  DATETIME is not timezone aware  So  for this reason  it s best to use UTC on the database side and use the hibernate jdbc time zone Hibernate property   What entity property type you should use   Your second question was   What data types would you use in Java  Date  Calendar  long         On the Java side  you can use the Java 8 LocalDateTime  You can also use the legacy Date  but the Java 8 Date Time types are better since they are immutable  and don t do a timezone shifting to local timezone when logging them  Now  we can also answer this question   What annotations would you use for the mapping  e g   Temporal    If you are using the LocalDateTime or java sql Timestamp to map a timestamp entity property  then you don t need to use  Temporal since HIbernate already knows that this property is to be saved as a JDBC Timestamp  Only if you are using java util Date  you need to specify the  Temporal annotation  like this   Temporal TemporalType TIMESTAMP   Column name    quot created on quot   private Date createdOn   But  it s much better if you map it like this   Column name    quot created on quot   private LocalDateTime createdOn   How to generate the audit column values Your third question was   Whom would you make responsible for setting the timestamps   the database  the ORM framework  Hibernate   or the application programmer  What annotations would you use for the mapping  e g   Temporal    There are many ways you can achieve this goal  You can allow the database to do that   For the create on column  you could use a DEFAULT DDL constraint  like   ALTER TABLE post  ADD CONSTRAINT created on default  DEFAULT CURRENT TIMESTAMP   FOR created on   For the updated on column  you could use a DB trigger to set the column value with CURRENT TIMESTAMP   every time a given row is modified  Or  use JPA or Hibernate to set those  Let s assume you have the following database tables   And  each table has columns like   created by created on updated by updated on  Using Hibernate  CreationTimestamp and  UpdateTimestamp annotations Hibernate offers the  CreationTimestamp and  UpdateTimestamp annotations that can be used to map the created on and updated on columns  You can use  MappedSuperclass to define a base class that will be extended by all entities   MappedSuperclass public class BaseEntity          Id      GeneratedValue     private Long id         Column name    quot created on quot        CreationTimestamp     private LocalDateTime createdOn         Column name    quot created by quot       private String createdBy         Column name    quot updated on quot        UpdateTimestamp     private LocalDateTime updatedOn         Column name    quot updated by quot       private String updatedBy          Getters and setters omitted for brevity    And  all entities will extend the BaseEntity  like this   Entity name    quot Post quot    Table name    quot post quot   public class Post extend BaseEntity         private String title         OneToMany          mappedBy    quot post quot           cascade   CascadeType ALL          orphanRemoval   true           private List lt PostComment gt  comments   new ArrayList lt  gt            OneToOne          mappedBy    quot post quot           cascade   CascadeType ALL          orphanRemoval   true          fetch   FetchType LAZY           private PostDetails details         ManyToMany      JoinTable          name    quot post tag quot           joinColumns    JoinColumn              name    quot post id quot                     inverseJoinColumns    JoinColumn              name    quot tag id quot                      private List lt Tag gt  tags   new ArrayList lt  gt             Getters and setters omitted for brevity    However  even if the createdOn and updateOn properties are set by the Hibernate-specific  CreationTimestamp and  UpdateTimestamp annotations  the createdBy and updatedBy require registering an application callback  as illustrated by the following JPA solution  Using JPA  EntityListeners You can encapsulate the audit properties in an Embeddable   Embeddable public class Audit          Column name    quot created on quot       private LocalDateTime createdOn         Column name    quot created by quot       private String createdBy         Column name    quot updated on quot       private LocalDateTime updatedOn         Column name    quot updated by quot       private String updatedBy          Getters and setters omitted for brevity    And  create an AuditListener to set the audit properties  public class AuditListener          PrePersist     public void setCreatedOn Auditable auditable            Audit audit   auditable getAudit              if audit    null                audit   new Audit                auditable setAudit audit                       audit setCreatedOn LocalDateTime now             audit setCreatedBy LoggedUser get                  PreUpdate     public void setUpdatedOn Auditable auditable            Audit audit   auditable getAudit              audit setUpdatedOn LocalDateTime now             audit setUpdatedBy LoggedUser get              To register the AuditListener  you can use the  EntityListeners JPA annotation   Entity name    quot Post quot    Table name    quot post quot    EntityListeners AuditListener class  public class Post implements Auditable          Id     private Long id         Embedded     private Audit audit        private String title         OneToMany          mappedBy    quot post quot           cascade   CascadeType ALL          orphanRemoval   true           private List lt PostComment gt  comments   new ArrayList lt  gt            OneToOne          mappedBy    quot post quot           cascade   CascadeType ALL          orphanRemoval   true          fetch   FetchType LAZY           private PostDetails details         ManyToMany      JoinTable          name    quot post tag quot           joinColumns    JoinColumn              name    quot post id quot                     inverseJoinColumns    JoinColumn              name    quot tag id quot                      private List lt Tag gt  tags   new ArrayList lt  gt             Getters and setters omitted for brevity

User · Answer

If you are using the JPA annotations  you can use  PrePersist and  PreUpdate event hooks do this    Entity  Table name    entities       public class Entity            private Date created    private Date updated      PrePersist   protected void onCreate         created   new Date            PreUpdate   protected void onUpdate         updated   new Date            or you can use the  EntityListener annotation on the class and place the event code in an external class

User · Answer

If we are using  Transactional in our methods   CreationTimestamp and  UpdateTimestamp will save the value in DB but will return null after using save        In this situation  using saveAndFlush      did the trick

User · Answer

You can just use  CreationTimestamp and  UpdateTimestamp    CreationTimestamp  Temporal TemporalType TIMESTAMP   Column name    create date   private Date createDate    UpdateTimestamp  Temporal TemporalType TIMESTAMP   Column name    modify date   private Date modifyDate

User · Answer

We had a similar situation  We were using Mysql 5 7   CREATE TABLE my table                     updated time TIMESTAMP DEFAULT CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP          This worked for us

User · Answer

I think it is neater not doing this in Java code  you can simply set column default value in MySql table definition

[java] Creation timestamp and last update timestamp with Hibernate and MySQL

Examples related to java

Examples related to hibernate

Examples related to timestamp