在一个事务中执行两个不同的查询 [英] Execute two different queries in one transaction

问题描述

我试图在一个Statement中执行两个插入查询，将它们放在一个事务中.

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

我正在查看addBatch方法，但是如果我理解正确，它可以与单个PreparedStatement一起使用，以使用不同的参数多次执行相同的插入操作，或者在Statement对象上使用添加对该批次有更多查询，但是没有添加参数的能力(因此我可以在sql字符串中添加值.SQL注入样式).

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).

我还尝试了一种天真的方法，即在一个sql语句(insert into table1 values(?, ?); insert into table2 values(?, ?);)中写入两个插入，但是PreparedStatement这样只能看到前两个参数，并尝试设置第3个和第4个抛出异常. /p>

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.

推荐答案

您可以禁用自动提交，执行两个单独的语句，然后手动提交事务:

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

这篇关于在一个事务中执行两个不同的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！