Execute two different queries in one transaction

Question

I am trying to execute two insert queries in one Statement, putting them together in one transaction.

I was looking at the addBatch method, but if I understand correctly it can be used with a single PreparedStatement to execute the same insert multiple times with different parameters, or be used on a Statement object to add more queries to the batch, but without the ability to add parameters (so I might be able to add the values in the sql string. SQL injection style).

I also tried a naive approach of writing both inserts in one sql statement (insert into table1 values(?, ?); insert into table2 values(?, ?);), but this way the PreparedStatement only sees the first two parameters, and trying to set the 3rd and 4th throws an exception.

Answer 1

You can disable autocommit, execute two separate statements and then commit a transaction manually:

connection.setAutoCommit(false);
try {
    ...
    stmt1.execute();
    ...
    stmt2.execute();
    connection.commit();
} catch (Exception ex) {
    connection.rollback();
}