Spring JDBC - Multiple Batches Operation

---

---

Overview

Following example will demonstrate how to make multiple batch updates in a single call using Spring JDBC. We'll update the available records in Student table in a multiple batch operation where batch size is 1.

Syntax

String SQL = "update Student set age = ? where id = ?";
int[][] updateCounts = jdbcTemplateObject.batchUpdate(SQL,students,1,
   new ParameterizedPreparedStatementSetter<Student>() {
   
   public void setValues(PreparedStatement ps, Student student)
      throws SQLException {
      ps.setInt(1, student.getAge());						
      ps.setInt(2, student.getId());	
   }
}); 

Where,

• SQL − Update query to update student's age.

• jdbcTemplateObject − StudentJDBCTemplate object to update student object in the database.

• ParameterizedPreparedStatementSetter − Batch executor, set values in PerparedStatement per item identified by the list of objects student.

• updateCounts − Int[][] array containing updated row count per update query per batch.

Example

To understand the above-mentioned concepts related to Spring JDBC, let us write an example which will update multiple batch operation. To write our example, let us have a working Eclipse IDE in place and use the following steps to create a Spring application.

StudentDAO.java

Following is the content of the Data Access Object interface file StudentDAO.java.

package com.tutorialspoint;

import java.util.List;
import javax.sql.DataSource;

public interface StudentDAO {
   /** 
    * This is the method to be used to initialize
    * database resources ie. connection.
   */
   public void setDataSource(DataSource ds);
   
   /** 
    * This is the method to be used to list down
    * all the records from the Student table.
   */
   public List<Student> listStudents();  
   public void batchUpdate(final List<Student> students);
}

Student.java

Following is the content of the Student.java file.

package com.tutorialspoint;

// Student POJO for Student Table
public class Student {
   private Integer age;
   private String name;
   private Integer id;

   // setter/getter methods
   public void setAge(Integer age) {
      this.age = age;
   }
   public Integer getAge() {
      return age;
   }
   public void setName(String name) {
      this.name = name;
   }
   public String getName() {
      return name;
   }
   public void setId(Integer id) {
      this.id = id;
   }
   public Integer getId() {
      return id;
   }
}

StudentMapper.java

Following is the content of the StudentMapper.java file.

package com.tutorialspoint;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

// Row Mapper Object to map Student table entry with Student Object
public class StudentMapper implements RowMapper<Student> {
   public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
      Student student = new Student();
      student.setId(rs.getInt("id"));
      student.setName(rs.getString("name"));
      student.setAge(rs.getInt("age"));
      return student;
   }
}

StudentJDBCTemplate.java

Following is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO.

package com.tutorialspoint;

import java.sql.PreparedStatement;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import java.sql.SQLException;

// DAO instance to persist Student values
public class StudentJDBCTemplate implements StudentDAO {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   
   // set the datasource and jdbctemplate
   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }
   
   // get all the students from the database
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
   
   // update a batch of multiple students
   public void batchUpdate(final List<Student> students){
      String SQL = "update Student set age = ? where id = ?";
      int[][] updateCounts = jdbcTemplateObject.batchUpdate(
         SQL,students,1,new ParameterizedPreparedStatementSetter<Student>() {
            public void setValues(PreparedStatement ps, Student student)
               throws SQLException {
               ps.setInt(1, student.getAge());						
               ps.setInt(2, student.getId());	
            }
         }
      );
      System.out.println("Records updated!");
   }
}

MainApp.java

Following is the content of the MainApp.java file.

package com.tutorialspoint;

import java.util.ArrayList;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MainApp {
   public static void main(String[] args) {
      // Create the application context by reading Beans.xml
      ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
	  
      // Create the JDBCTemplate instance from spring context
      StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");

      // get all the students
      List<Student> initialStudents = studentJDBCTemplate.listStudents();
      System.out.println("Initial Students");

      // print all the students
      for(Student student2: initialStudents){
         System.out.print("ID : " + student2.getId() );
         System.out.println(", Age : " + student2.getAge()); 
      }
      
      // create a new student to persist
      Student student = new Student();
      student.setId(1);
      student.setAge(17);

      // create another student to persist
      Student student1 = new Student();
      student1.setId(3);
      student1.setAge(18);

      // create a list of students to persist
      List<Student> students = new ArrayList<Student>();
      students.add(student);
      students.add(student1);
	  
      // persist list of students
      studentJDBCTemplate.batchUpdate(students);

      // get updated list of students      
      List<Student> updatedStudents = studentJDBCTemplate.listStudents();
      System.out.println("Updated Students");

      // print all the students including new students
      for(Student student3: updatedStudents){
         System.out.print("ID : " + student3.getId() );
         System.out.println(", Age : " + student3.getAge()); 
      }
   }
}

Beans.xml

Following is the configuration file Beans.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" 
   xsi:schemaLocation = "http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">

   <!-- Initialization for data source -->
   <bean id = "dataSource" 
      class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name = "driverClassName" value = "com.mysql.cj.jdbc.Driver"/>
      <property name = "url" value = "jdbc:mysql://localhost:3306/TEST"/>
      <property name = "username" value = "root"/>
      <property name = "password" value = "admin"/>
   </bean>

   <!-- Definition for studentJDBCTemplate bean -->
   <bean id = "studentJDBCTemplate" 
      class = "com.tutorialspoint.StudentJDBCTemplate">
      <property name = "dataSource" ref = "dataSource" />    
   </bean>      
</beans>

Output

Once you are done creating the source and bean configuration files, let us run the application. If everything is fine with your application, it will print the following message.

Initial Students
ID : 1, Age : 15
ID : 3, Age : 16
records updated!
Updated Students
ID : 1, Age : 17
ID : 3, Age : 18