On Update Current_Timestamp and JPA

ON UPDATE CURRENT_TIMESTAMP and JPA

You need to change the column annotation to include updatable = false. This will cause the edit_timestamp column to not show up in the update SQL, so the JPA provider won't include the current value of the field which is what is causing it to override the default.

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "edit_timestamp", 
        updatable = false,
        columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
private Date editTimestamp;

JPA entity update via getById & save didn't updated it's updatedtimestamp column

ON UPDATE CURRENT_TIMESTAMP is a construct of MySql's SQL dialect. It is described as follows in the documentation:

An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

Since all JPA implementation update all columns no matter, if they changed or not they will also provide a value for your CURRENT_TIMESTAMP column thus preventing any automatic update as described in the documentation.

You have various ways to get an update timestamp with this technology stack:

1. You can use dynamic inserts to only update fields that changed. Note that in most cases this is bad for performance. See Hibernate: Dirty Checking and Only Update of Dirty Attributes?

2. You can make the column read only by using @Column(updatable=false). See JPA readonly mapping

3. Instead of using the MySql feature you may use the Hibernates UpdateTimestamp for this purpose

4. Or you use JPAs EntityListener.

5. And since you seem to use Spring Data JPA you may also use Spring Datas @LastModifiedDate.

Update Timestamp field Spring JPA within Query annotation

You can use CURRENT_DATE like this :

UPDATE UserEntry o SET o.frozen = 1, o.frozen = CURRENT_DATE WHERE o.username = :username

How to retrieve the newest(current) time value specificd by DEFAULT CURRENT_TIMESTAMP on jpa entity's property?

You should use @Generated annotation in this case.

2.3.18. Generated properties

Generated properties are properties that have their values generated by the database. Typically, Hibernate applications needed to refresh objects that contain any properties for which the database was generating values. Marking properties as generated, however, lets the application delegate this responsibility to Hibernate. When Hibernate issues an SQL INSERT or UPDATE for an entity that has defined generated properties, it immediately issues a select to retrieve the generated values.

So, you should correct your mapping something like this:

@Temporal(TemporalType.TIMESTAMP)
@Generated(value = GenerationTime.INSERT)
@Column(name = "created_time", updatable = false, insertable = false, nullable = false,
        columnDefinition = "datetime default CURRENT_TIMESTAMP")
private Date createdTime;

@Temporal(TemporalType.TIMESTAMP)
@Generated(value = GenerationTime.ALWAYS)
@Column(name = "modified_time", updatable = false, insertable = false, nullable = false,
        columnDefinition = "datetime default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
private Date modifiedTime;

Last update timestamp with JPA

Use @PrePersist and @PreUpdate annotations and write your own event listener.

Take a look at this answer for details. It's tagged as Hibernate but is applicable to any JPA provider.

Mysql 5.6 column on update current timestamp not working in hibernate

As the @Shadow mentioned, your current schema design implies that you want MySQL to automatically set the lastModified field for you (on update CURRENT_TIMESTAMP). So if you pass null from your Java code, then it should work as expected. Try using the updatable = false option in your column definition to tell JPA not to send a value to MySQL:

@Column(name = "lastModified",
        updatable = false)
private Date lastModified;

My guess as to what is currently happening is that Hibernate is populating the lastModified field with the value from the database, and then your JPA code is then persisting this same value back to MySQL. So it appears that the value is not being updated. But what is really happening is that your JPA code is doing an update with the same old value.

Using Hibernate doesn't trigger MySQL's ON UPDATE CURRENT_TIMESTAMP

You need JPARepository.saveAndFlush() (or with raw JPA, EntityManager.refresh()) to get the updated value from the database in the same transaction. This affects all database generated values.

JPA doesn't know that values are changing inside the database, and it would be bad for performance to always re-read the saved value. That's why in a new transaction you get the correct values, as the data is actually fetched from the database. In the same transaction the value is still in memory and assumed to be unchanged.

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

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.

---

Related Topics