Generating SQL Batch Updates from Java
I want to update every row in a specific column of mySql database. I currently use java.sql.PreparedStatement
for each line and iteration in a for loop. I was wondering if there are other alternatives in terms of Java programming to do this in less time and resources (something like executing prepared statements in a batch). Updates are made from Java code because that's where I get the values. I am also not interested in creating stored procedures on the server as I do not have permission to do so.
source to share
Here is a link to an example that uses a prepared Java statement to perform a batch update. I've also included a 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
}
}
}
source to share
If you are using MySQL, I think the short answer to your question is "No." There is nothing you can do, it will be faster.
Indeed, even the prepared statement gets nothing. This may have changed with newer versions, but recently I checked (a few years ago), MySQL just turns prepared statements into regular statements. Nothing is cached.
source to share