Posted on: May 22, 2009 By Deepak Kumar
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 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. Output of the program Recommend the tutorial
<?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>
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();
}
}
}
Data updated successfully
BUILD SUCCESSFUL (total time: 2 seconds)
Table before updation
Table after updation
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
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.
December 2, 2011
Updation
There's no such word as "updation".
June 26, 2012
DataSource
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
Ask Questions? Discuss: Spring Batch Example View All Comments
Post your Comment