Spring JdbcTemplate batchUpdate() Example

Spring JdbcTemplate batch insert, batch update and also @Transactional examples.

Technologies used :

  • Spring Boot 2.1.2.RELEASE
  • Spring JDBC 5.1.4.RELEASE
  • Maven 3
  • Java 8

1. Batch Insert

1.1 Insert a batch of SQL Inserts together.

BookRepository.java

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;

    public int[] batchInsert(List<Book> books) {

        return this.jdbcTemplate.batchUpdate(
			"insert into books (name, price) values(?,?)",
			new BatchPreparedStatementSetter() {

				public void setValues(PreparedStatement ps, int i) throws SQLException {
					ps.setString(1, books.get(i).getName());
					ps.setBigDecimal(2, books.get(i).getPrice());
				}

				public int getBatchSize() {
					return books.size();
				}

			});
    }

1.2 If the batch is too big, we can split it by a smaller batch size.

BookRepository.java

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;

    public int[][] batchInsert(List<Book> books, int batchSize) {

        int[][] updateCounts = jdbcTemplate.batchUpdate(
                "insert into books (name, price) values(?,?)",
                books,
                batchSize,
                new ParameterizedPreparedStatementSetter<Book>() {
                    public void setValues(PreparedStatement ps, Book argument) 
						throws SQLException {
                        ps.setString(1, argument.getName());
                        ps.setBigDecimal(2, argument.getPrice());
                    }
                });
        return updateCounts;

    }

2. Batch Update

2.1 Same to SQL update statement.

BookRepository.java

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;

	public int[] batchUpdate(List<Book> books) {

        return this.jdbcTemplate.batchUpdate(
                "update books set price = ? where id = ?",
                new BatchPreparedStatementSetter() {

                    public void setValues(PreparedStatement ps, int i) 
						throws SQLException {
                        ps.setBigDecimal(1, books.get(i).getPrice());
                        ps.setLong(2, books.get(i).getId());
                    }

                    public int getBatchSize() {
                        return books.size();
                    }

                });

    }

2.2 Update by batchSize.

BookRepository.java

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;

	public int[][] batchUpdate(List<Book> books, int batchSize) {

        int[][] updateCounts = jdbcTemplate.batchUpdate(
                "update books set price = ? where id = ?",
                books,
                batchSize,
                new ParameterizedPreparedStatementSetter<Book>() {
                    public void setValues(PreparedStatement ps, Book argument) 
						throws SQLException {
                        ps.setBigDecimal(1, argument.getPrice());
                        ps.setLong(2, argument.getId());
                    }
                });
        return updateCounts;

    }
	

3. Run

3.1 Create a table to test the batch insert and update.

SpringBootApplication.java

startBookBatchUpdateApp(1000);

void startBookBatchUpdateApp(int size) {

	jdbcTemplate.execute("CREATE TABLE books(" +
			"id SERIAL, name VARCHAR(255), price NUMERIC(15, 2))");

	List<Book> books = new ArrayList();
	for (int count = 0; count < size; count++) {
		books.add(new Book(NameGenerator.randomName(20), new BigDecimal(1.99)));
	}

	// batch insert
	bookRepository.batchInsert(books);

	List<Book> bookFromDatabase = bookRepository.findAll();

	// count
	log.info("Total books: {}", bookFromDatabase.size());
	// random
	log.info("{}", bookRepository.findById(2L).orElseThrow(IllegalArgumentException::new));
	log.info("{}", bookRepository.findById(500L).orElseThrow(IllegalArgumentException::new));

	// update all books to 9.99
	bookFromDatabase.forEach(x -> x.setPrice(new BigDecimal(9.99)));

	// batch update
	bookRepository.batchUpdate(bookFromDatabase);

	List<Book> updatedList = bookRepository.findAll();

	// count
	log.info("Total books: {}", updatedList.size());
	// random
	log.info("{}", bookRepository.findById(2L).orElseThrow(IllegalArgumentException::new));
	log.info("{}", bookRepository.findById(500L).orElseThrow(IllegalArgumentException::new));
	
}

Output


Total books: 1000
Book{id=2, name='FcRzgpauFtwfWibpzWog', price=1.99}
Book{id=500, name='htDvtGmksjfGmXGKOCaR', price=1.99}

Total books: 1000
Book{id=2, name='FcRzgpauFtwfWibpzWog', price=9.99}
Book{id=500, name='htDvtGmksjfGmXGKOCaR', price=9.99}

4. @Transactional

4.1 With @Transactional, any failure causes the entire operation to roll back, none of the books will be added.

BookRepository.java

    @Transactional
    public int[][] batchInsert(List<Book> books, int batchSize) {

        int[][] updateCounts = jdbcTemplate.batchUpdate(
                "insert into books (name, price) values(?,?)",
                books,
                batchSize,
                new ParameterizedPreparedStatementSetter<Book>() {
                    public void setValues(PreparedStatement ps, Book argument) throws SQLException {
                        ps.setString(1, argument.getName());
                        ps.setBigDecimal(2, argument.getPrice());
                    }
                });
        return updateCounts;

    }

4.2 Try batch insert a 1000 books, the #500 contains an error, and the entire batch will be rolled back, no book will be inserted.

SpringBootApplication.java

startBookBatchUpdateRollBack(1000);

void startBookBatchUpdateRollBack(int size) {

	jdbcTemplate.execute("CREATE TABLE books(" +
			"id SERIAL, name VARCHAR(255), price NUMERIC(15, 2))");

	List<Book> books = new ArrayList();
	for (int count = 0; count < size; count++) {
		if (count == 500) {
			// Create an invalid data for id 500, test rollback
			// Name max 255, this book has length of 300
			books.add(new Book(NameGenerator.randomName(300), new BigDecimal(1.99)));
			continue;
		}
		books.add(new Book(NameGenerator.randomName(20), new BigDecimal(1.99)));
	}

	try {
		// with @Transactional, any error, entire batch will be rolled back
		bookRepository.batchInsert(books, 100);
	} catch (Exception e) {
		System.err.println(e.getMessage());
	}

	List<Book> bookFromDatabase = bookRepository.findAll();

	// count = 0 , id 500 error, roll back all
	log.info("Total books: {}", bookFromDatabase.size());

}

Output


PreparedStatementCallback; SQL [insert into books (name, price) values(?,?)]; Value too long for column "NAME VARCHAR(255)" 

Total books: 0

Download Source Code

$ git clone https://github.com/mkyong/spring-boot.git
$ cd spring-jdbc

References

About the Author

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

Comments

avatar
16 Comment threads
5 Thread replies
1 Followers
 
Most reacted comment
Hottest comment thread
17 Comment authors
PaulmkyongShivprakashChethan 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.

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) ?

Rahul
Guest
Rahul

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

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;

raju
Guest
raju

Hi mkyong,
Can batchupdate be used for procedure call?

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)

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.

Prabhudatta garnayak
Guest
Prabhudatta garnayak

Is it working on Oracle Database