Spring JDBC: Introduction to JdbcTemplate (IV)--Batch Operations
1. As we know, it is meaningless to execute batch query.
? ? There is only batchUpdate provided in JdbcTemplate.
/** * @param sql defining an array of SQL statements that will be executed. */public int[] batchUpdate(final String[] sql);/** * @param sql defining PreparedStatement that will be reused. * @param pss object to set parameters on the PreparedStatement */public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss);/** * @param sql the SQL statement to execute * @param batchArgs the List of Object arrays containing the batch of arguments for the query */public int[] batchUpdate(String sql, List<Object[]> batchArgs);/** * @param sql the SQL statement to execute. * @param batchArgs the List of Object arrays containing the batch of arguments for the query * @param argTypes SQL types of the arguments */public int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes);/** * @param sql the SQL statement to execute. * @param batchArgs the List of Object arrays containing the batch of arguments for the query * @param batchSize batch size * @param pss ParameterizedPreparedStatementSetter to use */public <T> int[][] batchUpdate(String sql, final Collection<T> batchArgs, final int batchSize, final ParameterizedPreparedStatementSetter<T> pss);
Example:
package edu.xmu.jdbc.dao;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import org.springframework.jdbc.core.BatchPreparedStatementSetter;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;import org.springframework.jdbc.core.support.JdbcDaoSupport;import edu.xmu.jdbc.bean.Student;public class BatchDao extends JdbcDaoSupport { public int[] batchExecution() {JdbcTemplate jdbcTemplate = getJdbcTemplate();String sql = "update student set name='aaa' where id=1";String sql2 = "update student set name='bbb' where id=2";String sql3 = "update student set name='ccc' where id=3";final String[] sqls = new String[] { sql, sql2, sql3 };return jdbcTemplate.batchUpdate(sqls); } public int[] batchCreate(final List<Student> studentList) {JdbcTemplate jdbcTemplate = getJdbcTemplate();String sql = "insert into student(name, age) values(?, ?)";int[] updateCounts = jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException {Student student = studentList.get(i);ps.setString(1, student.getName());ps.setInt(2, student.getAge()); } public int getBatchSize() {return studentList.size(); }});return updateCounts; } public int[] batchCreate2(final List<Student> studentList) {JdbcTemplate jdbcTemplate = getJdbcTemplate();String sql = "insert into student(name, age) values(?, ?)";List<Object[]> batchArgs = new ArrayList<Object[]>();for (Student student : studentList) { String name = student.getName(); int age = student.getAge(); Object[] objects = new Object[] { name, age }; batchArgs.add(objects);}return jdbcTemplate.batchUpdate(sql, batchArgs); } public int[] batchCreate3(final List<Student> studentList) {JdbcTemplate jdbcTemplate = getJdbcTemplate();String sql = "insert into student(name, age) values(?, ?)";List<Object[]> batchArgs = new ArrayList<Object[]>();for (Student student : studentList) { String name = student.getName(); int age = student.getAge(); Object[] objects = new Object[] { name, age }; batchArgs.add(objects);}return jdbcTemplate.batchUpdate(sql, batchArgs, new int[] {java.sql.Types.VARCHAR, java.sql.Types.INTEGER }); } public int[][] batchCreate4(final List<Student> studentList) {JdbcTemplate jdbcTemplate = getJdbcTemplate();String sql = "insert into student(name, age) values(?, ?)";List<Object[]> batchArgs = new ArrayList<Object[]>();for (Student student : studentList) { String name = student.getName(); int age = student.getAge(); Object[] objects = new Object[] { name, age }; batchArgs.add(objects);}return jdbcTemplate.batchUpdate(sql, studentList, studentList.size(),new ParameterizedPreparedStatementSetter<Student>() { public void setValues(PreparedStatement ps, Student student) throws SQLException {ps.setString(1, student.getName());ps.setInt(2, student.getAge()); }}); }}package edu.xmu.jdbc.dao;import java.util.ArrayList;import java.util.List;import org.junit.After;import org.junit.Before;import org.junit.Test;import org.springframework.jdbc.datasource.DriverManagerDataSource;import edu.xmu.jdbc.bean.Student;public class BatchDaoTest { private DriverManagerDataSource dataSource; private String url = "jdbc:mysql://localhost:3306/jdbctest"; private String username = "root"; private String password = "root"; private BatchDao dao; @Before public void setUp() {dataSource = new DriverManagerDataSource(url, username, password);dataSource.setDriverClassName("com.mysql.jdbc.Driver");dao = new BatchDao();dao.setDataSource(dataSource); } @Test public void batchCreateTest() {List<Student> studentList = new ArrayList<Student>();Student student = new Student("Davy", 24);studentList.add(student);student = new Student("Jones", 25);studentList.add(student);int[] rowCounts = dao.batchCreate(studentList);System.out.println("start batchCreateTest");for (int i : rowCounts) { System.out.println(i + " row affected.");}System.out.println("======================"); } @Test public void batchCreate2Test() {List<Student> studentList = new ArrayList<Student>();Student student = new Student("Davy", 24);studentList.add(student);student = new Student("Jones", 25);studentList.add(student);int[] rowCounts = dao.batchCreate2(studentList);System.out.println("start batchCreate2Test");for (int i : rowCounts) { System.out.println(i + " row affected.");}System.out.println("======================"); } @Test public void batchCreate3Test() {List<Student> studentList = new ArrayList<Student>();Student student = new Student("Davy", 24);studentList.add(student);student = new Student("Jones", 25);studentList.add(student);int[] rowCounts = dao.batchCreate3(studentList);System.out.println("start batchCreate3Test");for (int i : rowCounts) { System.out.println(i + " row affected.");}System.out.println("======================"); } @Test public void batchCreate4Test() {List<Student> studentList = new ArrayList<Student>();Student student = new Student("Davy", 24);studentList.add(student);student = new Student("Jones", 25);studentList.add(student);int[][] rowCounts = dao.batchCreate4(studentList);System.out.println("start batchCreateTest");for (int i = 0; i < rowCounts.length; i++) { int[] list = rowCounts[i]; for (int j = 0; j < list.length; j++) {int count = list[j];System.out.println("Batch " + i + " execute. " + count+ " row affected."); }}System.out.println("======================"); } @After public void tearDown() { }}Comments:
? ? Still have some question about the ParameterizedPreparedStatementSetter approach.
? ? Why it will return int[][] instead of int[]?
?
2. SimpleJdbcTemplate provided an additional ?approach for batchUpdate
public int[] batchUpdate(String sql, SqlParameterSource[] batchArgs);
As we can see, we can pass a list of SqlParameterSource as batch parameters.
And now since SimpleJdbcTemplate is depreciated, we still can not find alternative method in JdbcTemplate.
And why?
package edu.xmu.jdbc.dao;import java.util.List;import org.springframework.jdbc.core.namedparam.SqlParameterSource;import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;import edu.xmu.jdbc.bean.Student;@SuppressWarnings("deprecation")public class SimpleBatchDao extends SimpleJdbcDaoSupport { public int[] batchCreate(List<Student> studentList) {String sql = "insert into student(name, age) values(:name, :age)";SimpleJdbcTemplate simpleJdbcTemplate = getSimpleJdbcTemplate();SqlParameterSource[] paramSources = SqlParameterSourceUtils.createBatch(studentList.toArray());return simpleJdbcTemplate.batchUpdate(sql, paramSources); }}package edu.xmu.jdbc.dao;import java.util.ArrayList;import java.util.List;import org.junit.After;import org.junit.Before;import org.junit.Test;import org.springframework.jdbc.datasource.DriverManagerDataSource;import edu.xmu.jdbc.bean.Student;public class SimpleBatchDaoTest { private DriverManagerDataSource dataSource; private String url = "jdbc:mysql://localhost:3306/jdbctest"; private String username = "root"; private String password = "root"; private SimpleBatchDao dao; @Before public void setUp() {dataSource = new DriverManagerDataSource(url, username, password);dataSource.setDriverClassName("com.mysql.jdbc.Driver");dao = new SimpleBatchDao();dao.setDataSource(dataSource); } @Test public void batchCreateTest() {List<Student> studentList = new ArrayList<Student>();Student student = new Student("Davy", 24);studentList.add(student);student = new Student("Jones", 25);studentList.add(student);int[] rowCounts = dao.batchCreate(studentList);System.out.println("start batchCreateTest");for (int i : rowCounts) { System.out.println(i + " row affected.");}System.out.println("======================"); } @After public void tearDown() { }}?
3. BatchUpdate and auto-generated keys.
Still, we may wonder that how can we get the auto-generated keys when execute batchUpdate?
Sadly, there is no provided solution for this.<See ref-link-2>
?
Reference Links:
1)?http://examples.oreilly.com/0636920025405/justspring-data-src/src/main/java/com/madhusudhan/jsd/adv/JdbcTemplateBatchTest.java
2)?http://stackoverflow.com/questions/6272272/batchsqlupdate-how-to-get-auto-generated-keys?testify that there is no auto-generated key fetching solution. Also, it offers a good solution solving this.
?