CODEWITHSUNDEEP
javajpah2
DominikM
DominikM

Reputation: 1052

H2 database default value of TIMESTAMP column

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?

Upvotes: 11

Views: 26676

Answers (3)

Brad Parks
Brad Parks

Reputation: 71991

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;

Upvotes: 0

Jeremy D
Jeremy D

Reputation: 974

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.

Upvotes: 0

Thomas Mueller
Thomas Mueller

Reputation: 50097

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

Upvotes: 9

Related Questions