Persisting and updating date-time or timestamp of the database server via JPA

Question

When the associated database server and the application server are running on different time zones, retrieving, persisting and updating the current instant of time of the application server is not a good way to go. The database server should always be asked for the current time in place of the application server.

Thus, doing like the following on the middle tier JPA itself would be a wrong way to go.

Entity entity = new Entity();
entity.setTimestamp(new Timestamp(new java.util.Date().getTime()));
// Persist or merge the entity.

Or

Entity entity = new Entity();
entity.setLocalDateTime(java.time.LocalDateTime.now(ZoneOffset.UTC));
// Persist or merge the entity.

Or

Entity entity = new Entity();
entity.setZonedDateTime(java.time.ZonedDateTime.now(ZoneOffset.UTC));
// Persist or merge the entity.

Or

Entity entity = new Entity();
entity.setDateTime(org.joda.time.DateTime.now(org.joda.time.DateTime.DateTimeZone.UTC));
// Persist or merge the entity.

etc along with insertable = false, updatable = false for the respective field in the entity.

JPA allows the retrieval of date-time / timestamp from the database server.

• javax.​persistence.​criteria.​CriteriaBuilder#Expression<Date> currentDate()
• javax.​persistence.​criteria.​CriteriaBuilder#Expression<Time> currentTime()
• javax.​persistence.​criteria.​CriteriaBuilder#Expression<Timestamp> currentTimestamp()

_{Naturally, the same is true for JPQL as well.}

But there seems no way which JPA can exhibit to persist and merge the current time of the database server itself by delegating the task of persisting and merging the current time to the database server itself.

---

I am not interested in decorating date-time related fields with @Version as it has a completely different purpose of acquiring a row level optimistic lock in the middle tier JPA itself (optimistic locking using timestamp itself in turn also suffers from certain special drawbacks).

I do not much consider the jungle of RDBMS while using a persistence provider in the middle tier.

So, except for the functionality being adhered to RDBMS like CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, does JPA or a particular persistence provider itself has a way to deal with the current time of the database server for two idempotent operations namely "persist" and "merge"?

_{I am currently concerned with Hibernate and EclipseLink.}

Answer 1

I had the same problem and solved it in two places.

1) In the Oracle table I've added the default value to SYSTIMESTAMP

"CREATION_DATE" TIMESTAMP (6) DEFAULT SYSTIMESTAMP 

It is important to use SYSTIMESTAMP and not CURRENT_TIMESTAMP or LOCALTIMESTAMP because they both would use the caller session's locale!

2) In the Entity class, for columns that should never be updated I've used:

@Column(name = "CREATION_DATE", insertable=false, updatable = false)
public Timestamp getCreationDate() 
{
    return creationDate;
}

By doing so, the resulting INSERT query won't include the Timestamp column so the database uses the default SYSTIMESTAMP value.

For columns that could be updated, say a LAST_UPDATE column, I wanted to avoid the use of triggers, so I had to write a custom query in Spring Data:

DB

"LAST_UPDATE" TIMESTAMP (6) DEFAULT SYSTIMESTAMP 

Entity

@Column(name = "LAST_UPDATE", insertable=false, updatable = true)
public Timestamp getLastUpdate() 
{
    return lastUpdate;
}
...

Spring Data Repository

@Modifying
@Query(value="update User u set u.lastUpdate=SYSTIMESTAMP where u.id=:userId")
void setLastUpdateTime(@Param("userId") int userId);

This solution is not db-independent but avoids to use triggers and to synchronize the web servers time.