using JDBC preparedStatement in a batch

Question

Im using Statements batchs to query my data base. Iv'e done some research now and i want to rewrite my application to use preparedStatement instead but i'm having hard time to figure out how to add queries to a preparedStatement batch.

This is what i'm doing now:

private void addToBatch(String sql) throws SQLException{
sttmnt.addBatch(sql);
batchSize++;
if (batchSize == elementsPerExecute){
    executeBatches();
}
}

where sttmnt is a class member of type Statement.

What i want to do is to use the preparedStatement's setString(int, String) method to set some dynamic data and then add it to the batch.

Unfortunately, i don't fully understand how it works, and how i can use setString(int, String) to a specific sql in the batch OR create a new preparedStatemnt for every sql i have and then join them all to one batch.

is it possible to do that? or am i really missing something in my understanding of preparedStatement?

If you have found an answer accept the one that has helepd you the msot so others can learn as well — RMT, Jul 28 '11 at 15:24

score 28 · Accepted Answer · edited Jan 22 '13 at 10:35

Read the section 6.1.2 of this document for examples. Basically you use the same statement object and invoke the batch method after all the placeholders are set. Another IBM DB2 example which should work for any JDBC implementation. From the second site:

try {
  connection con.setAutoCommit(false);        
  PreparedStatement prepStmt = con.prepareStatement(    
    "UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?");
  prepStmt.setString(1,mgrnum1);            
  prepStmt.setString(2,deptnum1);
  prepStmt.addBatch();                      

  prepStmt.setString(1,mgrnum2);                        
  prepStmt.setString(2,deptnum2);
  prepStmt.addBatch();
  int [] numUpdates=prepStmt.executeBatch();
  for (int i=0; i < numUpdates.length; i++) {
    if (numUpdates[i] == -2)
      System.out.println("Execution " + i + 
        ": unknown number of rows updated");
    else
      System.out.println("Execution " + i + 
        "successful: " + numUpdates[i] + " rows updated");
  }
  con.commit();
} catch(BatchUpdateException b) {
  // process BatchUpdateException
}

but what if i want the statements to be different, lets say an INSERT, and then several updates? can i do that? — Boris C, Jul 30 '11 at 12:19
AFAIK, no. As you can see, `addBatch` is invoked on the `Statement` object and hence whatever statement holds would be added to batch. If you have statements of multiple types, it no longer is a batch; it kinda becomes a script. — Sanjay T. Sharma, Jul 30 '11 at 12:55
`Statement` has a method `void addBatch(String sql)` that allows to assemble batches (which bight be regarded as scripts) — Remigius Stalder, Sep 01 '15 at 09:08

RMT · Answer 2 · 2011-07-28T14:47:32.043

With PreparedStatement's you have wild cards in a way, for example

Sring query = "INSERT INTO users (id, user_name, password) VALUES(?,?,?)";
PreparedStatement statement = connection.preparedStatement(query);
for(User user: userList){
    statement.setString(1, user.getId()); //1 is the first ? (1 based counting)
    statement.setString(2, user.getUserName());
    statement.setString(3, user.getPassword()); 
    statement.addBatch();
}

This will create 1 PreparedStatement with that query shown above.You can loop through list when you want to insert or whatever you intentions are. When you want to execute you,

statement.executeBatch();
statement.clearBatch(); //If you want to add more, 
//(so you don't do the same thing twice)

score 6 · Answer 3 · edited Oct 11 '17 at 09:09

I'm adding an extra answer here specifically for MySQL.

I found that the time to do a batch of inserts was similar to the length of time to do individual inserts, even with the single transaction around the batch.

I added the parameter rewriteBatchedStatements=true to my jdbc url, and saw a dramatic improvement - in my case, a batch of 200 inserts went from 125 msec. without the parameter to about 10 to 15 msec. with the parameter.

See MySQL and JDBC with rewriteBatchedStatements=true

using JDBC preparedStatement in a batch

3 Answers3

Linked