Spring SimpleJdbcTemplate batchUpdate


 

Spring SimpleJdbcTemplate batchUpdate

This section is about batchUpdate method for issuing multiple SQL updates using SimpleJdbcTemplate.

This section is about batchUpdate method for issuing multiple SQL updates using SimpleJdbcTemplate.

Spring SimpleJdbcTemplate batchUpdate

The performance of Jdbc driver improves when same prepared statement is using for batch of multiple calls. In simplejdbctemplate, you don't need to implement special batch interface(like in JdbcTemplate). You just need to pass all the parameters value in the same call. It has a internal  prepared statement setter which iterate over these value. The batchUpdate method  issues multiple SQL updates on a single Statement.

EXAMPLE

In this example we are using "?" placeholder inside SQL statement. For this, the updated values should be pass in a list containing an object array. For each placeholder, the object array must have one entry , The order must be the same as it's order in SQL query or statement.

BatchUpdateSJT.java

package net.roseindia;

import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

public class BatchUpdateSJT {
	private SimpleJdbcTemplate simpleJdbcTemplate;

	public void setDataSource(DataSource dataSource) {
		this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	public int[] insertRows() {
	List batchArgs = new ArrayList();
	batchArgs.add(new Object[] { "A", "K", 1101 });
	batchArgs.add(new Object[] { "B", "K", 1102 });
	batchArgs.add(new Object[] { "C", "K", 1103 });
	batchArgs.add(new Object[] { "D", "K", 1104 });
	batchArgs.add(new Object[] { "E", "K", 1105 });
	int[] updateCounts = simpleJdbcTemplate.batchUpdate(
		"update skills set first_name = ?, last_name = ? where id = ?",
		batchArgs);
	return updateCounts;
	}
}

BatchUpdateSJTMain.java

package net.roseindia;

import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;

public class BatchUpdateSJTMain {

	public static void main(String[] args) {

	XmlBeanFactory beanFactory = new XmlBeanFactory(new ClassPathResource(
				"BatchUpdateSJT.xml"));

	BatchUpdateSJT myBean = (BatchUpdateSJT) beanFactory.getBean("BUSJT");

	myBean.insertRows();

	}
}

BatchUpdateSJT.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="BUSJT" class="net.roseindia.BatchUpdateSJT">
<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>
</beans>



OUTPUT

Before code execution, database table:



After Code execution , database table :



Download Source Code

Ads