Execute two different queries in one transaction

Question

I am trying to execute two insertion requests in one Statement , combining them into one transaction.

I looked at the addBatch method, but if I understand correctly, it can be used with one PreparedStatement to perform the same insertion several times with different parameters or for use in the Statement object to add more requests to the package, but without the ability to add parameters (therefore I could add values ​​to sql string. SQL injection style).

I also tried a naive approach to writing both inserts in the same SQL statement ( insert into table1 values(?, ?); insert into table2 values(?, ?); ), But in this way PreparedStatement sees only the first two parameters and tries to set 3 4th and 4th number of exceptions.

Answer 1

You can disable autorun, execute two separate statements, and then complete the transaction manually:

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