blocks|key|4117601|text|通常，您将使用连接的setAutoCommit()方法启动事务，运行语句，最后适当地调用commit()或rollback()。这是一种与数据库无关的在事务中工作的方法。不确定，为什么要为相同的对象构造SQL！你能详细说明一下用例吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4117602|Connection+con+=+dataSource.getConnection();
try+{
++++con.setAutoCommit(false);
++++Statement+stmt+=+con.prepareStatement("<your+update+statement+with+placeholders+for++parameters");
++++stmt.set(1,+<Value1>);
++++stmt.set(2,+<Value2>);
++++stmt.execute();
++++con.commit();
}
catch+(SQLException+sqle)+{
++++//+Handle+the+exception
++++con.rollback();
}
finally+{
++++con.close();
}|code-block|syntax|javascript|4117603|注意:在获得连接和关闭连接时，在上面的代码中有许多需要处理的角落情况，为了使这看起来现在看起来很简单而忽略了它！|4117604|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Generally, you would using the connection's setAutoCommit() method to start a transaction, run the statements, and finally call commit() or rollback() appropriately. This is kind of a DB-agnostic way to work in transactions. Not sure, why you want to construct SQL for the same! Could you elaborate on your use case?

<pre><code>Connection con = dataSource.getConnection();
try {
 con.setAutoCommit(false);
 Statement stmt = con.prepareStatement("&lt;your update statement with placeholders for parameters");
 stmt.set(1, &lt;Value1&gt;);
 stmt.set(2, &lt;Value2&gt;);
 stmt.execute();
 con.commit();
}
catch (SQLException sqle) {
 // Handle the exception
 con.rollback();
}
finally {
 con.close();
}
</code></pre>

Note: There are many corner cases to handle in the above code while obtaining connection and while closing it, left out to make this look simple for now!

blocks|key|3235188|text|尝试在分隔的addBatch()中添加每个更新语句|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3235189|entityMap^0|6|A|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Try to add each Update Statement in a separated <code>addBatch()</code>

blocks|key|4117626|text|试试看|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4117627|connection.setAutoCommit(false);
statement.addBatch("UPDATE+FIRST_TABLE+SET+FIRST_FIELD+=+1");
statement.addBatch("UPDATE+SECOND_TABLE+SET+SECOND_FIELD+=+2");
statement.addBatch("UPDATE+THIRD_TABLE+SET+THIRD_FIELD+=+3");
int+[]+counts+=+statement.executeBatch();
connection.commit();|code-block|syntax|javascript|4117628|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try,

<pre><code>connection.setAutoCommit(false);
statement.addBatch("UPDATE FIRST_TABLE SET FIRST_FIELD = 1");
statement.addBatch("UPDATE SECOND_TABLE SET SECOND_FIELD = 2");
statement.addBatch("UPDATE THIRD_TABLE SET THIRD_FIELD = 3");
int [] counts = statement.executeBatch();
connection.commit();
</code></pre>

blocks|key|3235226|text|另外，为了创建一批SQL，您必须将每个语句分别添加到批处理中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3235227|因此，与其：|3235228|+++++statement.addBatch("START+TRANSACTION;"+%2B
++++++++++++"UPDATE+FIRST_TABLE+SET+FIRST_FIELD+=+1;"+%2B
++++++++++++"UPDATE+SECOND_TABLE+SET+SECOND_FIELD+=+2;"+%2B
++++++++++++"UPDATE+THIRD_TABLE+SET+THIRD_FIELD+=+3;"+%2B
++++++++++++"COMMIT;");|code-block|syntax|javascript|3235229|使用：|3235230|+++++statement.addBatch("START+TRANSACTION;");
+++++statement.addBatch("UPDATE+FIRST_TABLE+SET+FIRST_FIELD+=+1;");
+++++....|3235231|但是，这不是运行事务的标准方式。要运行事务：|3235232|++++con.setAutoCommit(false);+//so+that+a+transaction+is+not+committed+after+each
++++++++++++++++++++++++++++++//statement

++++//run+you+queries
++++statement.executeUpdate();+//or+statement.executeBatch();
++++...
++++//in+the+end+commit
++++con.commit();|3235233|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

As a side note,in order to create a batch of SQLs you have to separately add each statement to the batch. 

So, instead of: 

<pre><code> statement.addBatch("START TRANSACTION;" +
 "UPDATE FIRST_TABLE SET FIRST_FIELD = 1;" +
 "UPDATE SECOND_TABLE SET SECOND_FIELD = 2;" +
 "UPDATE THIRD_TABLE SET THIRD_FIELD = 3;" +
 "COMMIT;");
</code></pre>

Use: 

<pre><code> statement.addBatch("START TRANSACTION;");
 statement.addBatch("UPDATE FIRST_TABLE SET FIRST_FIELD = 1;");
 ....
</code></pre>

But, this is not the standard way of running transactions. To run a transaction:

<pre><code> con.setAutoCommit(false); //so that a transaction is not committed after each
 //statement

 //run you queries
 statement.executeUpdate(); //or statement.executeBatch();
 ...
 //in the end commit
 con.commit();
</code></pre>

My statement:

<pre><code>statement.addBatch("START TRANSACTION;" +
 "UPDATE FIRST_TABLE SET FIRST_FIELD = 1;" +
 "UPDATE SECOND_TABLE SET SECOND_FIELD = 2;" +
 "UPDATE THIRD_TABLE SET THIRD_FIELD = 3;" +
 "COMMIT;");
</code></pre>

And exception throws in next line:

<pre><code>statement.executeBatch();
</code></pre>

The same thing happens if I use the method <code>execute</code>:

<pre><code>statement.execute(myTransaction);
</code></pre>

The exception:

<pre><code>java.sql.BatchUpdateException: ORA-00900: invalid SQL statement
</code></pre>

I use Oracle. I don't have any exception if I execute this statement in command line or SQL IDE. It works properly in this case. There is this exception only in java code. What is wrong?

Oracle - How to execute transaction correctly?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我的发言：statement.addBatch("START TRANSACTION;" +                "UPDATE FIRST_TABLE SET FIRST_FIELD = 1;" +                "UPDATE SECOND_TABLE SET SECOND_FIELD =...

问Oracle -如何正确执行事务？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle -如何正确执行事务？EN