July 26 2016

Spring JdbcTemplate batchupdate example

Spring JdbcTemplate batchupdate example

Batch update option allows us to submit multiple queries to database at once. Spring JdbcTemplate supports batch updates through JDBC Statement and PreparedStatement. The Spring JdbcTemplate provides following overloaded methods for batch updates

int[] batchUpdate(String[] sql) throws DataAccessException

Executes multiple SQL queries on a single JDBC Statement

int[] batchUpdate(String sql, BatchPreparedStatementSetter pss) throws DataAccessException

Executes multiple SQL queries on a single PreparedStatement

The batch update support is introduced in JDBC 2.0.If your driver does not support batch updates then the above methods executes the queries as separate updates on single JDBC Statement and PreparedStatement.

Example

Employee.java


package com.answersz.springJdbcTemplate;

public class Employee {

private Integer age;
 private Integer employeeId;
 private String fatherName;
 private String name;

public Integer getAge() {
 return age;
 }

public void setAge(Integer age) {
 this.age = age;
 }

public Integer getEmployeeId() {
 return employeeId;
 }

public void setEmployeeId(Integer employeeId) {
 this.employeeId = employeeId;
 }

public String getFatherName() {
 return fatherName;
 }

public void setFatherName(String fatherName) {
 this.fatherName = fatherName;
 }

public String getName() {
 return name;
 }

public void setName(String name) {
 this.name = name;
 }

}

EmployeeDAO.java

package com.answersz.springJdbcTemplate;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class EmployeeDAO extends JdbcDaoSupport {

 public void insertEmployeeList(final List<Employee> employeeList) {

 String insertQuery = "INSERT INTO EMPLOYEE " + "(EMPLOYEE_ID, NAME, FATHER_NAME,AGE) VALUES (?, ?, ?,?)";

 getJdbcTemplate().batchUpdate(insertQuery, new BatchPreparedStatementSetter() {

 public void setValues(PreparedStatement pStmnt, int i) throws SQLException {
 Employee employee = employeeList.get(i);
 pStmnt.setInt(1, employee.getEmployeeId());
 pStmnt.setString(2, employee.getName());
 pStmnt.setString(3, employee.getFatherName());
 pStmnt.setInt(4, employee.getAge());

 }

 public int getBatchSize() {

 return employeeList.size();
 }
 });
 }

 public void insertEmployeeList(String[] queries) {
 getJdbcTemplate().batchUpdate(queries);
 }

}

configuration xml : spring-jdbctemplate-batch.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-2.5.xsd">

 <bean id="employeeDAO" class="com.answersz.springJdbcTemplate.EmployeeDAO">
 <property name="dataSource" ref="dataSource" />
 </bean>

 <bean id="dataSource"
 class="org.springframework.jdbc.datasource.DriverManagerDataSource">
 <property name="driverClassName" value="com.mysql.jdbc.Driver" />
 <property name="url" value="jdbc:mysql://localhost:3306/answersz" />
 <property name="username" value="root" />
 <property name="password" value="admin" />
 </bean>


</beans>

Test Class – SpringBatchUpdateTest.java

package com.answersz.springJdbcTemplate;

import java.util.ArrayList;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class SpringBatchUpdateTest {

 public static void main(String[] args) {

 // Spring JdbcTemplate batchupdate example

 ApplicationContext context = new ClassPathXmlApplicationContext("spring-jdbctemplate-batch.xml");

 EmployeeDAO employeeDAO = (EmployeeDAO) context.getBean("employeeDAO");

 List<Employee> employeeList = new ArrayList<Employee>();

 Employee employee = new Employee();
 employee.setEmployeeId(1);
 employee.setName("Robert");
 employee.setFatherName("William");
 employee.setAge(29);

 employeeList.add(employee);

 Employee employee2 = new Employee();
 employee2.setEmployeeId(2);
 employee2.setName("Jennifer");
 employee2.setFatherName("David");
 employee2.setAge(35);

 employeeList.add(employee2);

 employeeDAO.insertEmployeeList(employeeList);

 String[] queries = new String[2];

 queries[0] = "INSERT INTO EMPLOYEE VALUES (3, 'Mark', 'George',40)";

 queries[1] = "INSERT INTO EMPLOYEE VALUES (4, 'Jennifer', 'Edward',35)";

 employeeDAO.insertEmployeeList(queries);

 }
}

Database

SELECT * FROM EMPLOYEE;

EMPLOYEE_ID NAME FATHER_NAME AGE
1 Robert William 29
2 Jennifer David 35
3 Mark George 40
4 Jennifer Edward 35

 


Copyright © 2014. All rights reserved.

Posted July 26, 2016 by answersz in category Spring

Leave a Comment

Your email address will not be published. Required fields are marked *

*