How can I execute the following SQL in a scalable way using JdbcTemplate running on mySQL. In this case, scalable means:
Here's the statement:
INSERT INTO myTable (foo, bar) VALUES ("asdf", "asdf"), ("qwer", "qwer")
Assume that I have a list of POJO's with foo
and bar
fields. I realize that I could just iterate over the list and execute:
jdbcTemplate.update("INSERT INTO myTable(foo, bar) VALUES (?, ?)", paramMap)
but that doesn't doesn't accomplish the first criterion.
I believe I could also execute:
jdbcTemplate.batchUpdate("INSERT INTO myTable(foo, bar) VALUES (?, ?)", paramMapArray)
but from what I can tell, that will just compile the SQL once and execute it multiple times, failing the first criterion again.
The final possibility, which seems to pass both criteria, would be to simply build the SQL myself with a StringBuffer
, but I'd like to avoid that.
You can use BatchPreparedStatementSetter like below.
public void insertListOfPojos(final List<MyPojo> myPojoList) {
String sql = "INSERT INTO "
+ "MY_TABLE "
+ "(FIELD_1,FIELD_2,FIELD_3) "
+ "VALUES " + "(?,?,?)";
getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i)
throws SQLException {
MyPojo myPojo = myPojoList.get(i);
ps.setString(1, myPojo.getField1());
ps.setString(2, myPojo.getField2());
ps.setString(3, myPojo.getField3());
}
@Override
public int getBatchSize() {
return myPojoList.size();
}
});
}