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.

Answer 1

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.

Answer 2

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.

Answer 3

@Column(nullable = false, updatable = false)
@CreationTimestamp
private Date created_at;

this worked for me. more info

Answer 4

Add the @CreationTimestamp annotation:

@CreationTimestamp
@Column(name="timestamp", nullable = false, updatable = false, insertable = false)
private Timestamp timestamp;

Answer 5

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;
 
    …
}

Answer 6

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

Answer 7

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.

Answer 8

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].

Answer 9

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 :

@Column(name = "insert_date")
@CreationTimestamp
private Timestamp insert_date;

Answer 10

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;
}

Answer 11

@Column(name = "LastTouched", insertable = false, updatable = false, columnDefinition = "TIMESTAMP default getdate()")
@Temporal(TemporalType.TIMESTAMP)
private Date LastTouched;`enter code here`

Answer 12

This worked for me:

@Column(name = "transactionCreatedDate", nullable = false, updatable = false, insertable = false, columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")