jdbctemplate batchUpdate example
This Section contains the example of batch update using jdbcTemplate.
This Section contains the example of batch update using jdbcTemplate.jdbctemplate batchUpdate example
The performance of JDBC driver improves if the same prepared statement is used for the multiple calls' batch. This Section contains the example of batch update using 'jdbcTemplate'. Two methods are uses for batchupdate i.e. 'BatchPreparedStatementSetter' & 'batchUpdate' . The BatchPreparedStatementSetter' is passed as second argument in 'batchUpdate' method. The 'getBatchSize' method is used to find the size of the batch. This method is necessary because without it you can't execute 'BatchPreparedStatementSetter' method. The 'setValues' method is used here to set the values in place of '?'. This method is invoked number of times the value returned by 'getBatchSize' method.
EXAMPLE
In this example, 'batchUpdate' is used to insert the bunch of values in database table. Here we are inserting 100 values as batch in table.
batchUpdate.java
package net.roseindia;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
public class batchUpdate {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public void doBatch() {
final int count = 100;
final List firstNames = new ArrayList(count);
final List lastNames = new ArrayList(count);
for (int i = 0; i < count; i++) {
firstNames.add("First Name " + i);
lastNames.add("Last Name " + i);
}
jdbcTemplate.batchUpdate(
"insert into customer (id, first_name, last_name, "
+ "last_login, comments) values (?, ?, ?, ?, ?)",
new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i)
throws SQLException {
ps.setLong(1, i + 1);
ps.setString(2, firstNames.get(i));
ps.setString(3, lastNames.get(i));
ps.setNull(4, Types.TIMESTAMP);
ps.setNull(5, Types.CLOB);
}
public int getBatchSize() {
return count;
}
});
}
}
batchUpdate.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:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id="batchUpdate" class="net.roseindia.batchUpdate">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver" />
<property name="jdbcUrl" value="jdbc:mysql://192.168.10.13:3306/ankdb" />
<property name="user" value="root" />
<property name="password" value="root" />
</bean>
<context:property-placeholder location="jdbc.properties" />
</beans>
batchUpdateMain.java
package net.roseindia;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;
public class batchUpdateMain {
public static void main(String[] args) {
XmlBeanFactory beanFactory = new XmlBeanFactory(new ClassPathResource(
"batchUpdate.xml"));
batchUpdate myBean = (batchUpdate) beanFactory.getBean("batchUpdate");
myBean.doBatch();
}
}
OUTPUT
After execution 'customer' database table (100 rows are inserted) :
