Setting a JPA timestamp column to be generated by the database

Question

In my SQL Server 2000 database  I have a timestamp  in function not in data type  column of type DATETIME named lastTouched set to getdate   as its default value binding   I am using the Netbeans 6 5 generated JPA entity classes  and have this in my code   Basic optional   false   Column name    LastTouched    Temporal TemporalType TIMESTAMP  private Date lastTouched    However when I try to put the object into the database I get    javax persistence PersistenceException  org hibernate PropertyValueException  not-null property references a null or transient value  com generic Stuff lastTouched   I ve tried setting the  Basic to  optional   true   but that throws an exception saying the database doesn t allow null values for the TIMESTAMP column  which it doesn t by design   ERROR JDBCExceptionReporter - Cannot insert the value NULL into column  LastTouched   table  DatabaseName dbo Stuff   column does not allow nulls  INSERT fails    I previously got this to work in pure Hibernate  but I have since switched over to JPA and have no idea how to tell it that this column is supposed to be generated on the database side  Note that I am still using Hibernate as my JPA persistence layer

User · Answer

I do not think that every database has auto-update timestamps (e.g. Postgres). So I've decided to update this field manually everywhere in my code. This will work with every database:

thingy.setLastTouched(new Date());
HibernateUtil.save(thingy);

There are reasons to use triggers, but for most projects, this is not one of them. Triggers dig you even deeper into a specific database implementation.

MySQL 5.6.28 (Ubuntu 15.10, OpenJDK 64-Bit 1.8.0_66) seems to be very forgiving, not requiring anything beyond

@Column(name="LastTouched")

MySQL 5.7.9 (CentOS 6, OpenJDK 64-Bit 1.8.0_72) only works with

@Column(name="LastTouched", insertable=false, updatable=false)

not:

FAILED: removing @Temporal
FAILED: @Column(name="LastTouched", nullable=true)
FAILED: @Column(name="LastTouched", columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")

My other system info (identical in both environments)

hibernate-entitymanager 5.0.2
hibernate-validator 5.2.2
mysql-connector-java 5.1.38

User · Answer

If you are doing development in Java 8 and Hibernate 5 Or Spring Boot JPA then use following annotation directly in your Entity class  Hibernate gets the current timestamp from the VM and will insert date and time in database  public class YourEntity          Id      GeneratedValue     private Long id        private String name         CreationTimestamp     private LocalDateTime createdDateTime         UpdateTimestamp     private LocalDateTime updatedDateTime

User · Answer

I fixed the issue by changing the code to   Basic optional   false   Column name    LastTouched   insertable   false  updatable   false   Temporal TemporalType TIMESTAMP  private Date lastTouched    So the timestamp column is ignored when generating SQL inserts  Not sure if this is the best way to go about this  Feedback is welcome

User · Answer

I m posting this for people searching for an answer when using MySQL and Java Spring Boot JPA  like  immanuelRocha says  only have too  CreationTimeStamp to the  Column in Spring  and in MySQL set the default value to  CURRENT TIMESTAMP    In Spring add just the line     x000D   x000D   Column name    insert date   x000D   CreationTimestamp x000D  private Timestamp insert date  x000D   x000D   x000D

User · Answer

Column nullable   false  updatable   false   CreationTimestamp private Date created at    this worked for me  more info

User · Answer

This also works for me -   Temporal TemporalType TIMESTAMP      Column name    CREATE DATE TIME   nullable   false  updatable   false  insertable   false  columnDefinition    TIMESTAMP DEFAULT CURRENT TIMESTAMP   public Date getCreateDateTime         return createDateTime     public void setCreateDateTime Date createDateTime        this createDateTime   createDateTime

User · Answer

If you mark your entity with  DynamicInsert e g    Entity  DynamicInsert  Table name    TABLE NAME   public class ClassName implements Serializable      Hibernate will generate SQL without null values     Then the database will insert its own default value  This does have performance implications See  Dynamic Insert  1

User · Answer

Column name    LastTouched   insertable   false  updatable   false  columnDefinition    TIMESTAMP default getdate      Temporal TemporalType TIMESTAMP  private Date LastTouched  enter code here

User · Answer

I have this working well using JPA2 0 and MySQL 5 5 10  for cases where I only care about the last time the row was modified  MySQL will create a timestamp on first insertion  and every time UPDATE is called on the row   NOTE  this will be problematic if I cared whether or not the UPDATE actually made a change    The  timestamp  column in this example is like a  last-touched  column x   The code below uses a separate column  version  for optimistic locking    private long version  private Date timeStamp   Version public long getVersion         return version     public void setVersion long version        this version   version        columnDefinition could simply be    TIMESTAMP   as the other settings are the MySQL default  Column name  timeStamp   columnDefinition  TIMESTAMP DEFAULT CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP    Temporal TemporalType TIMESTAMP  public Date getTimeStamp         return timeStamp     public void setTimeStamp Date timeStamp        this timeStamp   timeStamp       NOTE   Version doesn t work on a MySQL  DATETIME  column  where the attribute type is  Date  in the Entity class  This was because Date was generating a value down to the millisecond  however MySQL was not storing the millisecond  so when it did a comparison between what was in the database  and the  attached  entity  it thought they had different version numbers   From the MySQL manual regarding TIMESTAMP    With neither DEFAULT nor ON UPDATE clauses  it is the same as DEFAULT CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP

User · Answer

Add the  CreationTimestamp annotation    CreationTimestamp  Column name  timestamp   nullable   false  updatable   false  insertable   false  private Timestamp timestamp

User · Answer

I realize this is a bit late  but I ve had success with annotating a timestamp column with   Column name  timestamp   columnDefinition  TIMESTAMP DEFAULT CURRENT TIMESTAMP     This should also work with CURRENT DATE and CURRENT TIME  I m using JPA Hibernate with Oracle  so YMMV

User · Answer

This worked for me    Column name    transactionCreatedDate   nullable   false  updatable   false  insertable   false  columnDefinition    TIMESTAMP DEFAULT CURRENT TIMESTAMP

[java] Setting a JPA timestamp column to be generated by the database?

Examples related to java

Examples related to jpa

Examples related to persistence

Examples related to annotations

Examples related to timestamp