Spring Batch Example

JDBC Template Batch update example, In the tutorial we have discussed about batchUpdate() method of class



Spring Batch Example


JDBC Template Batch update example, In the tutorial we have discussed about  batchUpdate()  method of class JdbcTemplate in Spring framework. The Batch update methods are used to executes multiple SQL updates query on a single JDBC Statement. The example given below consists of the code to delete and update data of the table Simultaneously.



<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
          <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" 
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
       <bean id="lobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
        <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/>
        <bean id="nativeJdbcExtractor" 
       <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>


ApplicationContext ac = new ClassPathXmlApplicationContext("context.xml", Main.class):-ApplicationContext is the interface that is used to  provide Bean factory methods for accessing application components. Here we Creates an instance of this interface to access context.xml and Main.java.

DataSource source = (DataSource) ac.getBean("dataSource"):-Data source is an Interface which provides a way for connecting to the physical data source. Here we created datasource for making connection to our xml document in which we have declared the bean.

jt.batchUpdate(new String[]{"update employee set departement = 'Finance#'", "delete from employee where EmployeeId =31" }):-With the use of this method we are executing two SQLquery simultaneously. This method executes multiple Sql updates on a single JDBC statement.

import javax.sql.DataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
class Main {
    public static void main(String args[]) {
        try {
            ApplicationContext ac = new 
                    ClassPathXmlApplicationContext("context.xml", Main.class);
            DataSource source = (DataSource) ac.getBean("dataSource");
            JdbcTemplate jt = new JdbcTemplate(source);
            jt.batchUpdate(new String[]{"update employee set departement = 'Finance#'",
                        "delete from employee where EmployeeId =31"
            System.out.println("Data updated successfully");
        } catch (Exception e) {

Output of the program

Data updated successfully
BUILD SUCCESSFUL (total time: 2 seconds)


  Table before updation    Table after updation

Download source code

Share on Google+Share on Google+

Spring Batch Example

Posted on: May 22, 2009 If you enjoyed this post then why not add us on Google+? Add us to your Circles



Discuss: Spring Batch Example   View All Comments

Post your Comment

Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
March 9, 2011
Good example of JDBC Template Batch update

Example of JDBC Template Batch update, which shows how you can run multiple queries in a batch is very good. Example code is also very useful. You can download the source code of JDBC Template Batch update example and quickly run on your computer. Is easy to use code part in your program. Really very good example of JDBC Template Batch update. Thanks
Steve White
April 17, 2011
notes on building, running, and making sense of it

build libs: /usr/share/java/spring-core.jar:/usr/share/java/spring-beans.jar:/usr/share/java/spring-jdbc.jar:/usr/share/java/spring-context.jar:/usr/share/java/spring-tx.jar run libs: .:/usr/share/java/spring-core.jar:/usr/share/java/spring-beans.jar:/usr/share/java/spring-jdbc.jar:/usr/share/java/spring-context.jar:/usr/share/java/spring-tx.jar:/usr/share/java/commons-logging.jar:/usr/share/java/commons-dbcp.jar:/usr/share/java/commons-pool.jar:/usr/share/java/mysql.jar Now it hangs. Oh. mysql server isn't installed. So install and start it. Still hangs. Hm. What database is it opening? Oh dear. It's opening a database that doesn't exist , as root, with password root. Well that isn't advisible. And context.xml refers to an explicit local IP address with the mysql port. This could not work, but there is no mention in the text that this must be changed. It would be better to put 'localhost' there for the address, and explain that one must first set up the database for a certain (non-root) user and a reasonable password. So created a user and password, updated the context.xml file accordingly org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Unknown database 'komal') Clearly it also needs a database to already have been created. In the context.xml file, it is called "komal" org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [delete from employee where EmployeeId =31]; nested exception is java.sql.BatchUpdateException: Table 'komal.employee' doesn't exist This is rather poor. Clearly a full database definition should have been provided. This could have been done with a single SQL script for mysql, loaded using the system-independent mysqladmin utility. CREATE USER [email protected] IDENTIFIED BY 'tut'; CREATE DATABASE springtut; USE springtut; GRANT ALL ON * TO [email protected]; CREATE TABLE employee (EmployeeID INTEGER, Employeename VARCHAR(128), firstname VARCHAR(32), departement VARCHAR(128)); (the last was guessed from the image provided in the tutorial. note departement (sic)) now can log in from command line mysql -u tut --password=tut -p springtut Altered context.xml accordingly <property name="url" value="jdbc:mysql://localhost:3306/springtut"/> <property name="username" value="tut"/> <property name="password" value="tut"/> Now it runs! It announces Data updated successfully However the statement delete from employee where EmployeeId =31 could not have worked. The database is empty. Oh. Main.java doesn't check for success. It just reports success whether it succeeds or not. That is poor. First let's put some values in the database. Log in as tut, and do: INSERT INTO employee VALUES (34,'Amit Kumar','kumar','IT'); INSERT INTO employee VALUES (35,'Girish Tewari','Tewari','IT'); INSERT INTO employee VALUES (36,'Mahendra Singh','Singh','IT'); INSERT INTO employee VALUES (38,'Sandeep Saurabh','Saurabh','IT'); INSERT INTO employee VALUES (97,'Vinod Kumar','Vinod','IT'); INSERT INTO employee VALUES (98,'Komal Singh','Singh','IT'); Alter Main.java at least to look at the return values. int [] successes = jt.batchUpdate(new String[]{ "update employee set departement = 'Finance#'", "delete from employee where EmployeeId =31" }); if( successes[0] > 0 ) System.out.println("employee updated successfully"); else System.out.println("employee update failed"); if( successes[1] > 0 ) System.out.println("employee deleted successfully"); else System.out.println("employee delete failed"); Now get employee updated successfully employee delete failed Oh dear, the SQL in Main.java doesn't make much sense. The first SQL line actually succeeds in the silly act of changing all the employee's departments to "Finance#". The second only can succeed if there is an employee 31, which I haven't yet provided... but it's more interesting that it fails.
December 2, 2011

There's no such word as "updation".
June 26, 2012

This code causes a Bean not found exception for the bean - dataSource THanks
June 26, 2012
Batch update

This code generates a Bean not found error for bean dataSource