Home Spring Spring Batch Example
Questions:Ask|Latest

Related Tutorials


 
 

Share on Google+Share on Google+

Spring Batch Example

Advertisement
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.

context.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:util="http://www.springframework.org/schema/util"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:lang="http://www.springframework.org/schema/lang"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
       http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
       http://www.springframework.org/schema/util 
       http://www.springframework.org/schema/util/spring-util-2.0.xsd
       http://www.springframework.org/schema/tx 
       http://www.springframework.org/schema/tx/spring-tx-2.0.xsd
       http://www.springframework.org/schema/aop 
       http://www.springframework.org/schema/aop/spring-aop-2.0.xsd
       http://www.springframework.org/schema/lang 
       http://www.springframework.org/schema/lang/spring-lang-2.0.xsd">
          <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" 
                                 destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://192.168.10.75:3306/komal"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>
       <bean id="lobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
        <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/>
    </bean>
        <bean id="nativeJdbcExtractor" 
    class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"/>
       <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

Main.java

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) {
            e.printStackTrace();
                    }
    }
}

Output of the program

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

 

  Table before updation    Table after updation

Download source code

Advertisements

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: May 22, 2009

Related Tutorials

Ask Questions?    Discuss: Spring Batch Example   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
Dinesh
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 tut@localhost IDENTIFIED BY 'tut'; CREATE DATABASE springtut; USE springtut; GRANT ALL ON * TO tut@localhost; 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.
Arvin
December 2, 2011
Updation

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

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

This code generates a Bean not found error for bean dataSource
DMCA.com