Force commit JDBC query

Question

I have a test that creates an entity, updates it and checks that last modification date time is increased after update. I use JdbcClient to execute the following queries on PostgreSQL:

INSERT INTO entity (id, modified_on) VALUES (1, CURRENT_TIMESTAMP) RETURNING *;
UPDATE entity SET modified_on=CURRENT_TIMESTAMP WHERE id=1 RETURNING *;

The problem is that modified_on is not increased.

Here is a simplified test that shows the problem:

@Autowired
private JdbcClient jdbcClient;

@Test
void test() {
    var time1 = (java.sql.Timestamp) jdbcClient.sql("SELECT CURRENT_TIMESTAMP").query().singleValue();
    System.out.println(time1);
    var time2 = (java.sql.Timestamp) jdbcClient.sql("SELECT CURRENT_TIMESTAMP").query().singleValue();
    System.out.println(time2);
    assertThat(time2).isAfter(time1);
}

It's failed because time1 equals time2.

I guess that JdbcClient shoud commit queries immediately. But it seems that it executes them in a single transaction and that's why time is the same.

For sure I can use statement_timestamp() and it will fix the test. But I need CURRENT_TIMESTAMP. Is it possible to force commit between queries?

Answer 1

One way to make the test pass, while it will be kind of artificial, is to introduce a service class that creates a new transaction for every call.

import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;

@Service
@Transactional(propagation = Propagation.REQUIRES_NEW)
public class TimeService {

    private final JdbcClient jdbcClient;

    public TimeService(JdbcClient jdbcClient) {
        this.jdbcClient = jdbcClient;
    }

    public Timestamp getCurrentTimestamp() {
        return (java.sql.Timestamp) jdbcClient.sql("SELECT CURRENT_TIMESTAMP").query().singleValue();
    }
}

Please note that this class has to be imported in the test class using @Import(TimeService.class)

The test:

    @Autowired
    TimeService timeService;

    @Test
    void test() {
        Timestamp time1 = timeService.getCurrentTimestamp();
        Timestamp time2 = timeService.getCurrentTimestamp();

        assertThat(time2.getTime()).isGreaterThan(time1.getTime());
    }