Searching...
Sunday, 8 December 2013

Spring JdbcTemplate BatchUpdate Example

08:58
Spring API provides JdbcTemplate to do database operations, it simplifies the use of JDBC creation and release resources. In this example we will see how to use JdbcBatchUpdate BatchUpdate to insert and update records.

Software Used

Java 1.7
Spring 3.0.5
MySql

Project Structure

image

1. Create a Student table

In this example we are creating a simple Student table and inserting one record.
--
-- Create schema springschema
--
CREATE DATABASE IF NOT EXISTS springschema;
USE springschema;
--
-- Definition of table `student`
--
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `name` varchar(10) NOT NULL,
  `standard` varchar(20) NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `standard` (`standard`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--

2. Create a Maven project and add below dependencies

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>3.0.5.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>3.0.5.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>3.0.5.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.17</version>
    </dependency>
</dependencies>

3. Student.java

public class Student {
private String name;
private String standard;
//Getters and Setters

4. SpringJdbcDAO.java

package com.pretech.jdbc;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import com.mysql.jdbc.PreparedStatement;
public class SpringJdbcDAO {
 JdbcTemplate template;
 public void setTemplate(JdbcTemplate template) {
  this.template = template;
 }
 public void getStudentDetails() {

  List<?> studentList = template.queryForList("select * from student");
  Iterator<?> it = studentList.iterator();
  while (it.hasNext()) {
   Object object = it.next();
   System.out.println(object.toString());
  }
 }
 
   public int[] inserttStudentBatchUpdate(final List<Student> students) {
         int[] updateCnt = template.batchUpdate(
                 "insert into student (name, standard) values (?, ?)",
                 new BatchPreparedStatementSetter() {
                     public int getBatchSize() {
                         return students.size();
                     }
      public void setValues(java.sql.PreparedStatement ps,
        int i) throws SQLException {
                      ps.setString(1,students.get(i).getName());
                         ps.setString(2,students.get(i).getStandard());
                     }
                 } );
         return updateCnt;
     }
   
   public int[] updateStudentBatchUpdate(final List<Student> students) {
         int[] updateCnt = template.batchUpdate(
                 "update student set standard=? where name=? ",
                 new BatchPreparedStatementSetter() {
                     public int getBatchSize() {
                         return students.size();
                     }
      public void setValues(java.sql.PreparedStatement ps,
        int i) throws SQLException {
                      ps.setString(1,students.get(i).getStandard());
                         ps.setString(2,students.get(i).getName());
                     }
                 } );
         return updateCnt;
     }
}

5. Create Spring configuration file (SpringConfig.xml)


In this file we have to define JdbcTemplate
<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"
>
   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName"  value="com.mysql.jdbc.Driver"></property>
  <property name="url" value="jdbc:mysql://localhost:3306/springschema"></property>
  <property name="username" value="root"></property>
  <property name="password" value="root"></property>
 </bean>
  <bean id="jdbctemplate" class="org.springframework.jdbc.core.JdbcTemplate">
     <constructor-arg>
        <ref bean="dataSource"/>
     </constructor-arg>    
     </bean>
    <bean id="springjdbc" class="com.pretech.jdbc.SpringJdbcDAO">
       <property name="template">
       <ref bean="jdbctemplate"/>
    </property>
</bean>
</beans>

6. Create a Main class (SpringJdbcMain.java)

package com.pretech.jdbc;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
public class SpringJdbcMain {
 public static void main(String[] args) {
  Resource resource = new ClassPathResource("SpringConfig.xml");
  BeanFactory factory = new XmlBeanFactory(resource);
  SpringJdbcDAO springjdbc = (SpringJdbcDAO) factory.getBean("springjdbc");
  
  Student s1=new Student("Apar","1st standard");
  Student s2=new Student("Shiva","2st standard");
  Student s3=new Student("Sundar","3st standard");
  List<Student> studentList=new ArrayList<Student>();
  studentList.add(s1);
  studentList.add(s2);
  studentList.add(s3);
  
  springjdbc.inserttStudentBatchUpdate(studentList);
  springjdbc.getStudentDetails();
  System.out.println("STUDENT DETAILS INSERTED");
  
  Student s4=new Student("Sundar","10th Standard");
  List<Student> studentList1=new ArrayList<Student>();
  studentList1.add(s4);
  springjdbc.updateStudentBatchUpdate(studentList1);
  springjdbc.getStudentDetails();
  System.out.println("STUDENT DETAILS UPDATED");
  
 }
}

7.Run it



Output
{name=Apar, standard=1st standard}
{name=Shiva, standard=2st standard}
{name=Sundar, standard=3st standard}
STUDENT DETAILS INSERTED
{name=Apar, standard=1st standard}
{name=Shiva, standard=2st standard}
{name=Sundar, standard=10th Standard}
STUDENT DETAILS UPDATED


Download Spring JdbcTemplate Example

0 comments: