java中的executeUpdate()需要很长时间才能执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18230986/
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
executeUpdate() in java takes long time to execute
提问by tharani dharan
I am using a simple piece of code to update a single row in Oracle DB, the query updates just a single row but still the execution hangs on stmt.executeUpdate().
我正在使用一段简单的代码来更新 Oracle DB 中的一行，查询只更新一行，但执行仍然挂在 stmt.executeUpdate() 上。
It does not throw any exception, but the control just hangs there.
它不会抛出任何异常，但控件只是挂在那里。
I am lost here as the same piece of code worked fine earlier.
我在这里迷路了，因为同一段代码之前工作得很好。
try {
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(url,username,pwd);
stmt = conn.createStatement();
String sql = "update report set status =" +"'"+NO+"'"+ " where name=" +"'"+ name+"'"+ " and user_id=" +"'"+sub+"'";
System.out.println(sql);
stmt.executeUpdate(sql)
采纳答案by Vincent Malgrat
You should never create statements by concatenating variables like this. This leaves you open to SQL injection. Furthermore, each statement will be seen by Oracle as a new statement which will need a hard parse, which can lead to performance problems. Instead, use preparedStatement.
您永远不应该通过连接这样的变量来创建语句。这让您对SQL 注入持开放态度。此外，每个语句都会被 Oracle 视为需要硬解析的新语句，这会导致性能问题。相反，使用prepareStatement。
However, I think that in your case it's not the cause of your problem. When a simple update hangs, it is almost always one of these reasons:
但是，我认为在您的情况下，这不是问题的原因。当一个简单的更新挂起时，几乎总是以下原因之一：
- The row is locked by another transaction.
- You are updating a key that is referenced by another table as an unindexed foreign key.
- The table has an ON UPDATE trigger that does lots of works.
I'm pretty sure that you're in the first case. Oracle prevents multiple users from updating the same row at the same time, for consistency reasons mostly. This means that a DML statement will wait upon another transaction if the modified row is locked. It will wait until the blocking transaction commits or rollbacks.
我很确定你是第一种情况。Oracle 阻止多个用户同时更新同一行，主要是出于一致性原因。这意味着如果修改的行被锁定，DML 语句将等待另一个事务。它将等到阻塞事务提交或回滚。
I advise you to always lock a row before trying to update it by doing a SELECT ... FOR UPDATE NOWAITbefore. This will fail if the row is already locked, and you can exit gracefully by telling the user that this row is currently being locked by another session.
我建议您在尝试通过执行 a SELECT ... FOR UPDATE NOWAITbefore更新它之前始终锁定一行。如果该行已被锁定，这将失败，您可以通过告诉用户该行当前正被另一个会话锁定来优雅地退出。
You also have to make sure that no transaction ends in an uncommited state. All sessions should commit or rollback their changes before exiting (don't use autocommit though).
您还必须确保没有事务以未提交状态结束。所有会话都应该在退出之前提交或回滚他们的更改（尽管不要使用自动提交）。
回答by LMK
Use JDBC PreparedStatement java docs
使用 JDBC PreparedStatement java 文档
Class.forName("org.apache.derby.jdbc.ClientDriver");
Connection con = DriverManager.getConnection
("jdbc:derby://localhost:1527/testDb","name","pass");
PreparedStatement updateemp = con.prepareStatement
("insert into emp values(?,?,?)");
updateemp.setInt(1,23);
updateemp.setString(2,"Roshan");
updateemp.setString(3, "CEO");
updateemp.executeUpdate();
回答by SpringLearner
use PreparedStatement instead
改用 PreparedStatement
http://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/
http://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/
If you statement then every time it server will check for syntax so it consumes time So go for PreparedStatement
如果你声明，那么每次它服务器都会检查语法，所以它会消耗时间 所以去 PreparedStatement
回答by Phil
I had the same problem you had, I don't know about others but I solved it by simply closing SQLplus! I didn't think that could solve it since some queries worked while sqlplus was running on the console, but I closed it and now every queries works and nothing hangs!
我遇到了和你一样的问题，我不知道其他人，但我通过简单地关闭 SQLplus 解决了它！我不认为这可以解决它，因为当 sqlplus 在控制台上运行时一些查询可以工作，但是我关闭了它，现在每个查询都可以工作并且没有任何挂起！
You may want to try this if you have our problem! exit Sqlplus on your console or any other database you are using, then try running your code again!
如果您遇到我们的问题，您可能想尝试一下！在您的控制台或您正在使用的任何其他数据库上退出 Sqlplus，然后再次尝试运行您的代码！
