Friday, July 10, 2020

Spring Batch Processing Using JDBCTemplate batchUpdate() Method

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.

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.

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-

  1. Employee Bean class (Employee.java)
  2. DAO interface (EmployeeDAO.java)
  3. 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

  1. Spring Batch Processing With List of Objects in batchUpdate() Method
  2. JDBCTemplate With ResultSetExtractor Example in Spring
  3. Spring Transaction Management JDBC Example Using @Transactional Annotation
  4. Insert\Update Using NamedParameterJDBCTemplate in Spring Framework
  5. Batch Processing in Java-JDBC

You may also like-

  1. Benefits, Disadvantages And Limitations of Autowiring in Spring
  2. Injecting Inner Bean in Spring
  3. Different Bean Scopes in Spring
  4. Circular Dependency in Spring Framework
  5. Transient in Java
  6. Functional Interfaces in Java
  7. Try-With-Resources in Java Exception Handling
  8. BigDecimal in Java