The second way is a tad efficienter, but a much better way is to execute them in batches:
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 1000; i++) {
preparedStatement.setObject(1, someValue);
preparedStatement.addBatch();
}
preparedStatement.executeQuery();
preparedStatement.close(); // Do this in the finally block!
You're however dependent on the JDBC driver implementation how many batches you could execute at once. You may for example want to execute them every 100 batches:
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 1000; i++) {
preparedStatement.setObject(1, someValue);
preparedStatement.addBatch();
if ((i + 1) % 100 == 0) {
preparedStatement.executeBatch();
}
}
preparedStatement.executeBatch();
preparedStatement.close(); // Do this in the finally block!
As to the multithreaded environments, you don't need to worry about this if you acquire and close the connection and the statement in the shortest possible scope inside the same method block according the normal JDBC idiom. Here's a basic example:
public void executeBatch(/* ... */) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = database.getConnection();
preparedStatement = connection.prepareStatement(SQL);
// ...
} finally {
close(preparedStatement);
close(connection);
}
}
Update: as per the comments, depending on the functional requirement, you would often indeed like to only commit the transaction when all batches are finished, else it may be unpredictable which ones of the batches are already inserted/updated and which not. The DB would get dirty then. Here's how you could do it:
public void executeBatch(/* ... */) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = database.getConnection();
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(SQL);
// ...
connection.commit();
} catch (SQLException e) {
connection.rollback();
throw e;
} finally {
close(preparedStatement);
close(connection);
}
}