java 使用 PreparedStatement executeBatch() 的最佳方式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17207407/
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
Best way of using PreparedStatement executeBatch()
提问by susparsy
I am trying to figure out the best way of using PreparedStatement executeBatch() method.
我试图找出使用 PreparedStatement executeBatch() 方法的最佳方法。
One way i tried is:
我尝试过的一种方法是：
try{
prepStmt1 = conn.prepareStatement("update table set done='yes' where phone=?");
while (operatorsQuery.next() ) {
logger.info("phone: "+ phone + ", operator: " + operator);
process(); //0.5-1 second long
prepStmt1.setString(1, "0"+phone);
prepStmt1.addBatch();
}
prepStmt1.executeBatch();
}
catch{...}
finally{
closeStatmentand(prepStmt1);
}
The problem i am having with this code is that the program can exit in the middle and then it might not reach the executeBatch() method.
我对这段代码的问题是程序可以在中间退出，然后它可能无法到达 executeBatch() 方法。
The second way i tried:
我尝试的第二种方法：
try{
prepStmt1 = conn.prepareStatement("update table set done='yes' where phone=?");
while (operatorsQuery.next() ) {
logger.info("phone: "+ phone + ", operator: " + operator);
process(); //0.5-1 second long
prepStmt1.setString(1, "0"+phone);
prepStmt1.addBatch();
if ((j + 1) % 100 == 0) {
prepStmt1.executeBatch();
}
}
prepStmt1.executeBatch();
}
catch{...}
finally{
closeStatmentand(prepStmt1);
}
Which is the most preferred way to do this ?
哪个是最首选的方式来做到这一点？
回答by mel3kings
by using batch update, queries are not sent to the database unless there is a specific call to executeBatch();if you are worried that the user might exit the program and the execute is not reached, why not execute the updates one by one. and connections are set autoCommit(true);by default.
通过使用批量更新，除非有特定调用，否则查询不会发送到数据库，executeBatch();如果您担心用户可能会退出程序并且执行未到达，为什么不一个一个执行更新。和连接是autoCommit(true);默认设置的。
you cannot invoke a commit if the application is closed, and with batch updates queries are not yet sent to the database until an explicit call to executeis called.
如果应用程序关闭，您将无法调用提交，并且在调用显式调用之前，批量更新查询尚未发送到数据库execute。
executing an incremental batch would do.
执行增量批处理就可以了。
=======EDIT=======
========编辑========
If your problem really is performance and you have issues with abrupt exit of your application try using Java Message Service or JMS. JMS enables you to send messages asynchronously, meaning your application would forward these "data" to the JMS and not wait for the response, you will then program JMS to insert them to the database. JMS also is persistent enough that when application/server goes down, the data sent (also known as the queue) will still be alive once it goes back up.
如果您的问题确实是性能问题，并且您的应用程序突然退出有问题，请尝试使用 Java 消息服务或 JMS。JMS 使您能够异步发送消息，这意味着您的应用程序会将这些“数据”转发到 JMS，而不是等待响应，然后您将对 JMS 进行编程以将它们插入到数据库中。JMS 也足够持久，以至于当应用程序/服务器出现故障时，发送的数据（也称为队列）在它恢复后仍然处于活动状态。
Although JMS is not for beginners and will be hard to implement from scratch. hope this helps: http://docs.oracle.com/javaee/6/tutorial/doc/bncdq.html
尽管 JMS 不适合初学者，并且很难从头开始实现。希望这有帮助：http: //docs.oracle.com/javaee/6/tutorial/doc/bncdq.html
回答by pitchblack408
https://examples.javacodegeeks.com/core-java/sql/jdbc-batch-update-example/
https://examples.javacodegeeks.com/core-java/sql/jdbc-batch-update-example/
public void batchUpdateUsingStatement() throws SQLException {
// This is to hold the response of executeBatch()
int[] result = null;
try {
Statement stmt = connection.createStatement();
connection.setAutoCommit(false); // Setting auto-commit off
String SQL = "update person set firstName='New First Name', lastName='New Last Name' where id=1";
stmt.addBatch(SQL); // add statement to Batch
SQL = "update person set firstName='First Name',lastName='Last Name' where id=2";
stmt.addBatch(SQL); // add second statement to Batch
result = stmt.executeBatch(); // execute the Batch
connection.commit(); // commit
} catch (SQLException e) {
connection.rollback(); // rollBack in case of an exception
e.printStackTrace();
} finally {
if (connection != null)
connection.close(); // finally close the connection
}
System.out.println("Number of rows affected: " + result.length);
}
