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.