问题描述
我想更新 mySql 数据库中特定列的每一行.目前我正在为每一行使用 java.sql.PreparedStatement 并在 for 循环中迭代.我想知道在 Java 编程方面是否还有其他选择可以减少时间和资源消耗(例如批量执行准备好的语句).更新是由 Java 代码进行的,因为这是我从中获取值的地方.我也对在服务器上创建存储过程不感兴趣,因为我没有权限.
I want to update every row on a specific column in a mySql database. Currently I am using a java.sql.PreparedStatement for each row and iterating in a for loop. I was wondering if there were any other alternatives in terms of Java programming to make this less time and resource consuming (something like executing the prepared statements in a batch). The updates are made from Java code because that is where I get the values from. I am also not interested in making stored procedures on the server as I do not have the rights for that.
推荐答案
这是一个使用 Java 的预处理语句来执行批量更新的示例的链接.我还包括了网站上的示例以供快速参考.
Here is a link to an example that uses Java's prepared statement to execute a batch update. I also included the sample from the site for quick reference.
http://www.exampledepot.com/egs/java.sql/BatchUpdate.html
try { // Disable auto-commit connection.setAutoCommit(false); // Create a prepared statement String sql = "INSERT INTO my_table VALUES(?)"; PreparedStatement pstmt = connection.prepareStatement(sql); // Insert 10 rows of data for (int i=0; i<10; i++) { pstmt.setString(1, ""+i); pstmt.addBatch(); } // Execute the batch int [] updateCounts = pstmt.executeBatch(); // All statements were successfully executed. // updateCounts contains one element for each batched statement. // updateCounts[i] contains the number of rows affected by that statement. processUpdateCounts(updateCounts); // Since there were no errors, commit connection.commit(); } catch (BatchUpdateException e) { // Not all of the statements were successfully executed int[] updateCounts = e.getUpdateCounts(); // Some databases will continue to execute after one fails. // If so, updateCounts.length will equal the number of batched statements. // If not, updateCounts.length will equal the number of successfully executed statements processUpdateCounts(updateCounts); // Either commit the successfully executed statements or rollback the entire batch connection.rollback(); } catch (SQLException e) { } public static void processUpdateCounts(int[] updateCounts) { for (int i=0; i<updateCounts.length; i++) { if (updateCounts[i] >= 0) { // Successfully executed; the number represents number of affected rows } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) { // Successfully executed; number of affected rows not available } else if (updateCounts[i] == Statement.EXECUTE_FAILED) { // Failed to execute } } }