Spring JdbcTemplate batchUpdate() example

In some cases, you may required to insert a batch of records into database in one shot. If you call a single insert method for every record, the SQL statement will be compiled repeatedly and causing your system slow to perform.

In above case, you can use JdbcTemplate batchUpdate() method to perform the batch insert operations. With this method, the statement is compiled only once and executed multiple times.

See batchUpdate() example in JdbcTemplate class.


//insert batch example
public void insertBatch(final List<Customer> customers){
		
  String sql = "INSERT INTO CUSTOMER " +
	"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
			
  getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
			
	@Override
	public void setValues(PreparedStatement ps, int i) throws SQLException {
		Customer customer = customers.get(i);
		ps.setLong(1, customer.getCustId());
		ps.setString(2, customer.getName());
		ps.setInt(3, customer.getAge() );
	}
			
	@Override
	public int getBatchSize() {
		return customers.size();
	}
  });
}

Alternatively, you can execute the SQL directly.


//insert batch example with SQL
public void insertBatchSQL(final String sql){
		
	getJdbcTemplate().batchUpdate(new String[]{sql});
		
}

Spring’s bean configuration file


<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

	<bean id="customerDAO" class="com.mkyong.customer.dao.impl.JdbcCustomerDAO">
		<property name="dataSource" ref="dataSource" />
	</bean>
	
	<bean id="dataSource" 
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">

		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/mkyongjava" />
		<property name="username" value="root" />
		<property name="password" value="password" />
	</bean>
	
</beans>

Run it


package com.mkyong.common;

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

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.mkyong.customer.dao.CustomerDAO;
import com.mkyong.customer.model.Customer;

public class App 
{
    public static void main( String[] args )
    {
    	ApplicationContext context = 
    		new ClassPathXmlApplicationContext("Spring-Customer.xml");
    	 
        CustomerDAO customerDAO = (CustomerDAO) context.getBean("customerDAO");
        Customer customer1 = new Customer(1, "mkyong1",21);
        Customer customer3 = new Customer(2, "mkyong2",22);
        Customer customer2 = new Customer(3, "mkyong3",23);
  
        List<Customer>customers = new ArrayList<Customer>();
        customers.add(customer1);
        customers.add(customer2);
        customers.add(customer3);
        
        customerDAO.insertBatch(customers);

        String sql = "UPDATE CUSTOMER SET NAME ='BATCHUPDATE'";
        customerDAO.insertBatchSQL(sql);
      
    }
}

In this example, you are inserted three customers’ records and update all customer’s name in batch.

Download Source Code

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
16 Comment threads
2 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
15 Comment authors
ShivprakashChethankholofelo Malomachiranjeevi munaga Recent comment authors
newest oldest most voted
prakash
Guest
prakash

Hi Mykyong,

Please suggest how to rollback an insert if an exception occurred while inserting data using Spring JdbcTemplate batchUpdate() method.

Bruce
Guest
Bruce

THIS DOES NOT DO A BATCH UPDATE!!!!!!!!!!!!! This executes the statement for iteration.

I am looking for the equivalent of:

int count[] = {0};
Statement st = con.createStatement();
for (String where : whereList) {
st.addBatch(delete + where);
sqlList.add(delete + where);
}
count = st.executeBatch();

Add the SQL to the batch then execute the batch.

Bruce
Guest
Bruce

This works:

int ct = 0;
String sql = “DELETE MY_TABLE WHERE RCD_ID = ?”;
List deleteArgsList = new ArrayList();
for (Foo f: fooList) {
deleteArgsList.add(new Object[] {f.getRcdId()});
}

int[] rslts = capsJdbcTemplate.batchUpdate(sql, deleteArgsList);

ct += rslts.length;

return ct;

Chethan
Guest
Chethan

Hi Mykyong,

I tried to use batch Update in my application and we used BatchPreparedStatement and HashMap for passing the values. It’s not throwing any exception instead the status int array values are -2, For testing purpose we are inserting only one row. it’s not inserting data into table.

I tried to inserting same data using same query but row by row, instead batch update, we used update, then its working fine and returning status as 1.

i con’t figure out the issue, could please suggest me.

kholofelo Maloma
Guest
kholofelo Maloma

Thank you so much.
Your examples help us on our daily work. Easy to read and understand. Literally, we do well at work and deliver good quality work EFFICIENTLY because of people like you.

Re a leboga (Thank you)

chiranjeevi munaga
Guest
chiranjeevi munaga

Hi, Using Spring JDBC batch update, How to handle the scenario like what if a row failed to insert ? Suppose out of 1000 rows to be inserted, the 100th row failed to insert and the program ends abruptly without inserting the remaining rows(i.e. from 100th row to 1000th row). How to handle this kind of scenario such that even if the 100th row fails to be inserted, the process should carry on for the remaining rows(i.e. from 101th row to 1000th row) ?

ashutosh mishra
Guest
ashutosh mishra

Hi mkyong,

Nice article….that exactly i am looking for. But i have to also write Junit test case for this.
please also provide the Junit test case for the above example using jMock.

Thanks
Ashutosh

Mohit
Guest
Mohit

Hi Guys, How we can handled the duplicate exception while insert data in batch.

Shiv
Guest
Shiv

use merge query.

david
Guest
david

Hi
i did a batch update for single table and i want to do for 6 more tables, so i created 6 methods so for every method should i internally call different set methods for prepared statements

Bruce
Guest
Bruce

This is not a batch update!!!!! This executes on statement at a time.

I am looking for the equivalent of
for (Object o: objList) {
st.addBatch(sql)

Abhinav
Guest
Abhinav

its bit urgent :(

Abhinav
Guest
Abhinav

i tried using above bit of code but when I’m running the code I’m gettin following exception;
Throwable occurred: java.sql.BatchUpdateException: Duplicate entry ‘abhi21’ for key ‘PRIMARY’

i suppose it is trying to insert the last row twice which is the reason of this exception.
can u help me to avoid it????

jason
Guest
jason

Great article

jarida theme free download
Guest
jarida theme free download

Leave as is if you value it or pick from any of the displayed background textures.
Change the background to any color you’d like, of course. You might even discover a new passion for yourself or your kids.

raju
Guest
raju

Hi mkyong,
Can batchupdate be used for procedure call?

Prabhudatta garnayak
Guest
Prabhudatta garnayak

Is it working on Oracle Database

Rahul
Guest
Rahul

Hi,
How can we get the ids of autoincremented inserts via batchupdate.
Thanks
Rahul