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
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.
Good example of JDBC Template Batch updateDinesh March 9, 2011 at 7:44 AM
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
notes on building, running, and making sense of itSteve White April 17, 2011 at 4:31 PM
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.
UpdationArvin December 2, 2011 at 11:32 AM
There's no such word as "updation".
DataSourceUma June 26, 2012 at 2:05 PM
This code causes a Bean not found exception for the bean - dataSource THanks
Batch update Uma June 26, 2012 at 2:07 PM
This code generates a Bean not found error for bean dataSource
Post your Comment