H2 database default column TIMESTAMP

Question

I am writing integration tests with an H2 database. My database initialization (generated) includes this script (since this column is not in the generated connection table):

ALTER TABLE INT_USR ADD IU_INSDTTM TIMESTAMP DEFAULT NOW(); 

This is how I create the entries:

 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 logRecentIntegrations (.., ..) method simply calls DAO, and 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 entries (correct), but they have the same timestamp. Even when I inserted a breakpoint in the line labeled // 1 and waited a while, so the time gap between the inserts will be significant. (Thread.sleep - same result)

I tried changing the SQL script to

 ALTER TABLE INT_USR ADD IU_INSDTTM TIMESTAMP DEFAULT CURRENT_TIMESTAMP; 

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

Answer 1

As indicated, the CURRENT_TIMESTAMP function always returns the same value in a transaction. This behavior is consistent with other databases, such as PostgreSQL.

Answer 2

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

@Transaction(propagation = Propagation.NEVER)

Note. This annotation consists of Spring and may be something else for the environment in which you work.