CODEWITHSUNDEEP
transactionsjdbijdbi3
alecswan
alecswan

Reputation: 3680

JDBI useTransaction does not commit preparedBatch

I am using JDBI to insert a few rows in MariaDB 10.6.8 using Hikari connection pool with autoCommit set to false.

My expectation was that useTransaction(..) will open a transaction and commit it after a successful batch insert. However, this is not what's happening and instead I have to explicitly call handle.commit() as shown at the bottom of the code snippet below.

I would like to avoid explicitly handling transaction's lifecycle, e.g. with an explicit commit() call, unless it is absolutely necessary.

Am I misunderstanding or misusing the useTransaction(..) API?

jdbi.useTransaction(handle -> {
            PreparedBatch batch = handle.prepareBatch(
                    "INSERT INTO table1 (col1, col2) " +
                            "VALUES (:col1, :col2) ");
            for (Data data : datas) {
                batch.bind("col1", data.getCol1())
                        .bind("col2", data.getCol2())
                        .add();
            }

            int[] batchResult = batch.execute();

            // handle.commit(); // uncommenting this causes the transaction to get committed and inserted rows to become visible in the database
        });

Upvotes: 0

Views: 524

Answers (1)

Ela Singh
Ela Singh

Reputation: 36

I have tested your code and it works fine without any commit statement. You don't have to add commit explicitly.

void dummyTest() {
    var dataDummy = new Data("dummy1", "dummy2");
    var datas = List.of(dataDummy);
    jdbi.useTransaction(
            handle -> {
                PreparedBatch batch = handle.prepareBatch("INSERT INTO table1 (col1, col2) VALUES (:col1, :col2)");
                for (Data data : datas) {
                    batch.bind("col1", data.col1())
                            .bind("col2", data.col2())
                            .add();
                }

                int[] batchResult = batch.execute();
            });
}

Output :

db=# select * from table1;
  col1  |  col2  
--------+--------
 dummy1 | dummy2

Note : I'm using jdbi-version - 3.28.0

Upvotes: 0

Related Questions