How to convert the prepare statement sql string with an Object to the exact sql string?

Question

I am using spring. I have many update steps, Is there a way to used batchUpdate for this?

Original

jdbcTemplate.update("UPDATE USER SET name = ? WHERE user_id = ?", new Object[] {name, id});  
jdbcTemplate.update("UPDATE USER2 SET name = ? WHERE user_id = ?", new Object[] {name, id2});  
jdbcTemplate.update("UPDATE USER3 SET name = ? WHERE user_id = ?", new Object[] {name, id3});  

How to used batchUpdate

getJdbcTemplate().batchUpdate(sql);

Because the parameter for batchUpdate is Array[]. How to convert the prepare statement SQL string with an Object to the exact SQL string?

Answer 1

int\[\] batchUpdate(String... sql) is the wrong method for the job. It is intended for a case where you have a bunch of different SQL statements without parameters, that you want to execute in one go.

You need to use on of the other batchUpdate methods.

If you don't need to control the batch size public int[] batchUpdate(String sql, List<Object[]> batchArgs) should work fine. The call would look like this:

jdbcTemplate.update(
    "UPDATE USER SET name = ? WHERE user_id = ?", 
    Arrays.asList(
        new Object[] {name, id},
        new Object[] {name, id2}, 
        new Object[] {name, id3}
    )
); 

Also take a look at the other variants that allow to provide a batch size, the type of arguments or parameter setters which allow to set the parameters for each part of the batch individually.

See also this example.