If you have a large number of similar queries it is better to process them in a batch. Processing them as a batch provides better performance as you send a group of queries in a single network communication rather than sending individual queries one by one. Spring JdbcTemplate class supports batch processing using batchUpdate() method.
Spring JdbcTemplate batch processing
For batch processing you can use batchUpdate() method of the Spring JdbcTemplate. As the first parameter of the batchUpdate() you will pass the query that has to be used for batch processing and as second parameter you need to pass the interface BatchPreparedStatementSetter. This interface has two methods which you need to implement.
- setValues()- This method is used to set the values for the parameters of the prepared statement.
- getBatchSize()- This method is used to provide the size of the current batch.
Spring JdbcTemplate batch processing using batchUpdate() example
Let’s see an example of inserting rows in a DB table as a batch using Spring JdbcTemplate batch processing.
Technologies used
- Spring 5.0.4
- Apache DBCP2
- MYSQL 5.1.39
- Java 10
- Apache Maven 3.3.3
In this batch processing tutorial Apache DBCP is used which provides pooled datasource and MYSQL is used as the back end.
Maven dependencies
If you are using Apache Maven then you can provide dependencies in your pom.xml. Alternatively you can download the jars and add them to the class path.
- Refer Creating a Maven project in Eclipse to see how to set up Maven project.
With all the dependencies your pom.xml should look something like this -
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.netjs</groupId>
<artifactId>SpringExp</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>SpringExp</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<spring.version>5.0.4.RELEASE</spring.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- Spring JDBC Support -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- MySQL Driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>
<!-- Apache DBCP connection pool -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
Database table
For this example I have created a table called employee with the columns id, name and age in the MYSQL DB. Column id is configured as auto increment checked so no need to pass id from your query as DB will generate value for it.
CREATE TABLE `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(35) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
DataSource Configuration
DataBase properties are retrieved from a properties file rather than hardcoding in the configuration file. Properties file is stored at the location config/db.properties.
- Refer How to Read Properties File in Spring Framework to see ways of reading properties file in Spring.
db.properties
db.driverClassName=com.mysql.jdbc.Driver db.url=jdbc:mysql://localhost:3306/netjs db.username= db.password= pool.initialSize=5
Java Classes
Java classes needed for Spring batch processing example are as follows-
- Employee Bean class (Employee.java)
- DAO interface (EmployeeDAO.java)
- DAO interface implementation class (EmployeeDAOImpl.java)
Employee.java
public class Employee {
private int empId;
private String empName;
private int age;
public Employee(String empName, int age){
this.empName = empName;
this.age = age;
}
public int getEmpId() {
return empId;
}
public void setEmpId(int empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
EmployeeDAO.java
public interface EmployeeDAO {
public int[] batchInsert(final List<Employee> employees);
}
EmployeeDAOImpl.java
@Repository
public class EmployeeDAOImpl implements EmployeeDAO{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int[] batchInsert(final List<Employee> employees) {
final String INSERT_EMP_QUERY = "insert into employee (name, age) values (?, ?)";
return this.jdbcTemplate.batchUpdate(INSERT_EMP_QUERY,
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
// emp id is auto generated so not provided
ps.setString(1, employees.get(i).getEmpName());
ps.setInt(2, employees.get(i).getAge());
}
@Override
public int getBatchSize() {
return employees.size();
}
});
}
}
Logic for Spring batch processing is in the bacthInsert() method, where jdbcTemplate.batchUpdate method is called
with the query and instance of BatchPreparedStatementSetter. Note that in the class Spring autowiring is used to inject dependencies. Also class is annotated with @Repository annotation so that the class can be automatically discovered using component scanning in Spring.
batchUpdate() method retruns an array of the number of rows affected by each statement.
XML Configuration (appContext.xml)
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<context:component-scan base-package="org.netjs.DAOImpl" />
<!-- For reading properties files -->
<context:property-placeholder location="classpath:config/db.properties" />
<!-- Data Source configuration -->
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value = "${db.driverClassName}" />
<property name="url" value = "${db.url}" />
<property name="username" value = "${db.username}" />
<property name="password" value = "${db.password}" />
<property name="initialSize" value = "${pool.initialSize}" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
Test class
You can use the following code in order to test the batch insertion of rows in DB table.
public class App {
public static void main(String[] args) {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext
("appcontext.xml");
EmployeeDAO empDAO = context.getBean("employeeDAOImpl", EmployeeDAOImpl.class);
List<Employee> empList = createEmpList();
int[] rows = empDAO.batchInsert(empList);
System.out.println("Number of rows inserted- " + rows.length);
}
private static List<Employee> createEmpList(){
Employee emp1 = new Employee("Ben", 25);
Employee emp2 = new Employee("Virat", 29);
Employee emp3 = new Employee("Joe", 26);
List<Employee> empList= new ArrayList<Employee>();
empList.add(emp1);
empList.add(emp2);
empList.add(emp3);
return empList;
}
}
Output
Aug 06, 2018 12:25:45 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@6fc6f14e: startup date [Mon Aug 06 12:25:44 IST 2018]; root of context hierarchy Aug 06, 2018 12:25:45 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions INFO: Loading XML bean definitions from class path resource [appcontext.xml] Number of rows inserted- 3
Batch operation with multiple batches
You may want to break your batches into several small batches. Though it can be done by making several calls to batchUpdate() method but there is an overloaded variant of batchUpdate() method where you can pass the number of updates to make for each batch and an interface ParameterizedPreparedStatementSetter to set the values for the parameters of the prepared statement. The framework loops over the provided values and breaks the update calls into batches of the size specified.
The batch update methods for this call returns an array of int arrays containing an array entry for each batch with an array of the number of affected rows for each update. The top level array’s length indicates the number of batches executed and the second level array’s length indicates the number of updates in that batch.
Here is an example which shows a batch update using a batch size of 3:
Spring JdbcTemplate batchUpdate example with multiple batches
EmployeeDAO.java
public interface EmployeeDAO {
public int[][] batchInsert(final List<Employee> employees);
}
EmployeeDAOImpl.java
@Repository
public class EmployeeDAOImpl implements EmployeeDAO{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int[][] batchInsert(final List<Employee> employees) {
final String INSERT_EMP_QUERY = "insert into employee (name, age) values (?, ?)";
return this.jdbcTemplate.batchUpdate(INSERT_EMP_QUERY, employees, 3,
new ParameterizedPreparedStatementSetter<Employee>() {
@Override
public void setValues(PreparedStatement ps, Employee emp) throws SQLException {
ps.setString(1, emp.getEmpName());
ps.setInt(2, emp.getAge());
}
});
}
}
XML file is same as above. You can run the Spring batch processing example using the following code.
public class App {
public static void main(String[] args) {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext
("appcontext.xml");
EmployeeDAO empDAO = context.getBean("employeeDAOImpl", EmployeeDAOImpl.class);
List<Employee> empList = createEmpList();
int[][] rows = empDAO.batchInsert(empList);
for(int i = 0; i < rows.length; i++) {
System.out.println("Number of rows inserted- " + rows[i].length);
}
}
private static List<Employee> createEmpList(){
Employee emp1 = new Employee("Mike", 32);
Employee emp2 = new Employee("Rahul", 27);
Employee emp3 = new Employee("Smith", 28);
Employee emp4 = new Employee("Steve", 45);
Employee emp5 = new Employee("Rajesh", 21);
List<Employee> empList= new ArrayList<Employee>();
empList.add(emp1);
empList.add(emp2);
empList.add(emp3);
empList.add(emp4);
empList.add(emp5);
return empList;
}
}
Output
Aug 07, 2018 10:21:36 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@6fc6f14e: startup date [Tue Aug 07 10:21:36 IST 2018]; root of context hierarchy Aug 07, 2018 10:21:36 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions INFO: Loading XML bean definitions from class path resource [appcontext.xml] Number of rows inserted- 3 Number of rows inserted- 2
That's all for this topic Spring Batch Processing Using JDBCTemplate batchUpdate() Method. If you have any doubt or any suggestions to make please drop a comment. Thanks!
>>>Return to Spring Tutorial Page
Related Topics
You may also like-