Multiple SQL Statements in Java (with ?allowMultiQueries=true)

Question

I have a question. I work with a lot of SQL Queries and try to work out the best and fastest solution to handle a lot of queries (about 10'000 SQL-queries). I have figured out 2 ways of doing this and would like to hear your opinion on this. VERSION1: loop over prepared statements, VERSION2: allow multiple queries divided by semicolon (by adding "?allowMultiQueries=true" when connecting to the database).

Version2 worked out much faster (3 seconds) wheras Version1 was pretty slow (over 1 minute). So my question is, are there any downsides (or maybe safety concerns) about allowing multiple prepared statements?

Heres a short code example. Thx for all help!

// i want to execute the following 3 SQL queries:
String[] SQL = new String[3];
SQL[0] = "UPDATE tbl1 SET age=22 WHERE id=1;";
SQL[1] = "UPDATE tbl1 SET age=80 WHERE id=2;";
SQL[2] = "UPDATE tbl1 SET age=31 WHERE id=3;";

// VERSION1: loop over prepared statements
int[] age = {22,80,31};
int[] id  = { 1, 2, 3};
Connection conn1 = DriverManager.getConnection("jdbc:mysql://EXAMPLE", "user", "pw");
PreparedStatement stmt1_P = conn1.prepareStatement("UPDATE tbl1 SET age=? WHERE id=?;");
for (int i=0; i<SQL.length; i++) {
    stmt1_P.setInt(1, age[i]);
    stmt1_P.setInt(2, id[i]);
    stmt1_P.executeUpdate();
}               

// VERSION2: multiple queries divided by semicolon
Connection conn2 = DriverManager.getConnection("jdbc:mysql://EXAMPLE?allowMultiQueries=true", "user", "pw");
Statement stmt2 = conn2.createStatement();
StringBuilder s = new StringBuilder();
for (int i=0; i<SQL.length; i++) {
    s.append(SQL[i]);
}
stmt2.executeUpdate(s.toString());  

Answer 1

Concatenating statements is generally a good idea. As you've discovered, they generate less network traffic and are faster.

I don't see any security concerns to worry about.

One suggestion to think about: integrity. If one of your many concatenated statements fails (for whatever reason) you may have some difficulty figuring out which one failed and which ones succeeded. If you wrap each bunch of concatenated statements in a transaction, you can use ROLLBACK to restore the state of your database if any statements in your bunch fail.