H2 database default value of TIMESTAMP column

Question

I am writing integration tests with H2 database. My database (generated) initialization include this script (because generated join table does not have this column):

ALTER TABLE INT_USR ADD IU_INSDTTM TIMESTAMP DEFAULT NOW();

This is how I create records:

Integration integrationOne = createIntegration(firstId, "FIRST");
Integration integrationTwo = createIntegration(secondId, "SECOND");
flushAndClear();
userService.logRecentIntegration(integrationOne.getId(), user.getId());
flushAndClear();
userService.logRecentIntegration(integrationTwo.getId(), user.getId()); //1

The method logRecentIntegrations(.., ..) just calls the DAO and the dao does this:

Query query = entityManager.createNativeQuery(
    "INSERT INTO INT_USR (USR_ID, INT_ID) VALUES (?, ?)");
query.setParameter(1, userId)
    .setParameter(2, integrationId);
query.executeUpdate();

Later in my test:

Query query = entityManager.createNativeQuery(
    "SELECT * FROM INT_USR ORDER BY IU_INSDTTM");
List resultList = query.getResultList();

When I debug this test in resultList there are two records (correct) but they have same timestamp. Even when I inserted a breakpoint on line marked //1 and waited a while - so the time gap between inserts would be significant. (Thread.sleep - same result)

I tried to modify the SQL script to

ALTER TABLE INT_USR ADD IU_INSDTTM TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

But with same result. Why both results have same timestamp?

Thinker

Answer 1

As documented, the function CURRENT_TIMESTAMP always returns the same value within a transaction. This behavior matches other databases, for example PostgreSQL.

Rhodes

@Service @Transactional public class UserServiceImpl implements UserService { ... public void logRecentIntegration(Long integrationId, Long userId) {} So each call to the method is in different transaction, isn't it? – Thinker

This I don't know, I'm not very familiar with JPA. But I'm familiar with H2 :-) – Rhodes

@Thinker No, it is not. Default propagation level of '@Transactional' is REQUIRED, which means: If there is no transaction, a new one is started. If exists, use that one. I know, you called it from test, which is a single transaction. – Vibratory

Answer 2

You may add the following annotation to your test to disable transactions.

@Transaction(propagation = Propagation.NEVER)

Note: That annotation comes from Spring and there may be something else for the environment that you are running within.

Karissakarita

Answer 3

Note that if you're generating hibernate pojo's you can also use CreationTimestamp. I just tried it and it seemed to work!

  @CreationTimestamp
  protected LocalDateTime createdDate;

Banzai