How to use "on update CURRENT_TIMESTAMP" in H2 database with JUnit?

Question

I want my entity to have a modification timestamp whenever it is updated. mysql supports this using the following definition:

@Entity
public class MyTable {
    @Column(columnDefinition = "TIMESTAMP default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP")
    private LocalDateTime thetime;
}

Problem: in my JUnit tests I want to use an embedded inmemory H2 database. And H2 does not support on update CURRENT_TIMESTAMP.

Question: how can I keep the column definition (as I will be running mysql in all cases except in automated tests)? And how can I workaround that in my h2 testing?

Answer 1

The official statement from the H2 is that it is not supported and the workaround is to create a trigger. You can read this here https://github.com/commandos59/h2database/issues/491

Whatever you put in the "columnDefinition" it is provider specific. And since you have already mapped your entity with this specific column definition you are not leaving yourself much space to manouver.

There are several things you can do. Some of the them are hacks.

1. Mix XML configuration for the tests. The XML configurationn of the Entities has higher priority than the annotations so you can actualy override with H2 specific column definition.

   @Column(columnDefinition = "TIMESTAMP default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP") private LocalDateTime thetime

2. Agnostic of the Database alternative is to leave your time generation to the application server layer and hook it to the @PrePersist @PreUpdate listeners on the entity

3. If the timestamp must be generated by the database you can do something similar to how the IDs are generated. Have some sort of dedicated object that reads the CURRENT_TIMESTAMP from the database and puts it the entity right before you persist, update.