Force commit JDBC query - Stack Overflow

admin管理员组

文章数量:1208155

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?

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?

• spring-jdbc

Share Improve this question Follow asked Jan 18 at 18:38 DenisDenis 1,27111 gold badge1313 silver badges3232 bronze badges 2

• Still looking into this, but with Postgresql testcontainer, test passes with SELECT clock_timestamp() – Roar S. Commented Jan 18 at 19:08
• 1 I use Zonky Embedded Postgres. Yes, clock_timestamp() fixes the test. But I can't use it because it's non-standard and CURRENT_TIMESTAMP is better suited for my App – Denis Commented Jan 18 at 19:20

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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());
    }

本文标签： Force commit JDBC queryStack Overflow