java.sql.SQLException“数据库处于自动提交模式”的原因
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12194972/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reason for java.sql.SQLException "database in auto-commit mode"
提问by nuoritoveri
I use sqlitedatabase and java.sqlclasses in servlet application to batch-insert some data into database.
There are consecutive four inserts of different kinds of data.
Each one looks like this:
我在 servlet 应用程序中使用sqlite数据库和java.sql类将一些数据批量插入到数据库中。连续插入了四次不同类型的数据。每个看起来像这样：
PreparedStatement statement = conn
.prepareStatement("insert or ignore into nodes(name,jid,available,reachable,responsive) values(?,?,?,?,?);");
for (NodeInfo n : nodes)
{
statement.setString(1, n.name);
statement.setString(2, n.jid);
statement.setBoolean(3, n.available);
statement.setBoolean(4, n.reachable);
statement.setBoolean(5, n.responsive);
statement.addBatch();
}
conn.setAutoCommit(false);
statement.executeBatch();
conn.commit();
conn.setAutoCommit(true);
statement.close();
But sometimes I get the
但有时我得到
java.sql.SQLException: database in auto-commit mode
I found in source code of java.sql.Connectionthat this exception is thrown when calling commit()while database is in autocommit mode. But I turn autocommit off before and I can't see any place for some parallel execution related issues as for now application is only turned on once.
我在源代码中发现，在数据库处于自动提交模式java.sql.Connection时调用commit()时会抛出此异常。但是我之前关闭了自动提交，我看不到任何与并行执行相关的问题的地方，因为现在应用程序只打开一次。
Do you have any idea how to debug this issue? Maybe there's some other reason for this error (because I just found that exception about database not found or not well configured can be thrown when inserting nullinto non-null field)?.
你知道如何调试这个问题吗？也许这个错误还有其他一些原因（因为我刚刚发现在插入null非空字段时会抛出关于数据库未找到或配置不当的异常）？。
采纳答案by adatapost
May be an issue iswith orderof statements. Your database statement should be :
可能是一个问题是有顺序的语句。您的数据库语句应该是：
PreparedStatement statement1 = null;
PreparedStatement statement2 = null;
Connection connection=null;
try {
//1. Obtain connection and set `false` to autoCommit
connection.setAutoCommit(false);
//2. Prepare and execute statements
statement1=connection.prepareStatement(sql1);
statement2=connection.prepareStatement(sql2);
...
//3. Execute the statements
statement1.executeUpdate();
statement2.executeUpdate();
//4. Commit the changes
connection.commit();
}
} catch (SQLException e ) {
if (connection!=null) {
try {
connection.rollback();
} catch(SQLException excep) {}
}
}finally {
if (statement1 != null) {
statement1.close();
}
if (statement2 != null) {
statement2.close();
}
if(connection != null){
connection.setAutoCommit(true);
connection.close();
}
}
回答by Kai
You have to prepare your Statement and create the batch after conn.setAutoCommit(false);.
您必须准备您的 Statement 并在 之后创建批次conn.setAutoCommit(false);。
回答by tbl
When running this from a servlet, you have to make sure that the usage of the Connectionis synchronized. Multiple requests could set the Connectionto a different auto commit mode at nearly the same time. If you use one Connectionper request, this will not be an issue. Otherwise, protect the above part with a critical section.
当从 servlet 运行它时，您必须确保 的使用Connection是同步的。多个请求几乎可以同时将 设置Connection为不同的自动提交模式。如果您Connection为每个请求使用一个，这将不是问题。否则，用临界区保护上述部分。
A tip regarding debugging which is applicable for tomcat / eclipse.
适用于 tomcat/eclipse 的调试提示。
1) Enable JDPA debugging for your application server. In tomcat you can do this by adding the following lines to catalina.sh / catalina.bat:
1) 为您的应用服务器启用 JDPA 调试。在 tomcat 中，您可以通过将以下几行添加到 catalina.sh / catalina.bat 来完成此操作：
set JPDA_ADDRESS=8000
set JPDA_TRANSPORT=dt_socket
2) Restart the application server
2）重启应用服务器
3) Connect with eclipse to your application server. "Debug as" --> "Remote Java Application"
3）用eclipse连接到你的应用服务器。“调试为”-->“远程 Java 应用程序”
4) Set a break point in above code.
4) 在上面的代码中设置一个断点。
5) Run the servlet.
5) 运行 servlet。
