Home Tutorial Spring Spring3 Jdbc jdbctemplate batchUpdate example

 
 

Share on Google+Share on Google+
jdbctemplate batchUpdate example
Posted on: August 21, 2010 at 12:00 AM
Advertisement
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) :

Download Source Code

Advertisement

Related Tags for jdbctemplate batchUpdate example:


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: August 21, 2010

Recommend the tutorial

Advertisements Advertisements
 

 

 

DMCA.com