Main Tutorials

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 Author

author image
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

Subscribe
Notify of
30 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
chiranjeevi munaga
7 years ago

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

Paul
4 years ago
Reply to  mkyong

Hi I removed the transactional annotation and as you said the rest of the batch was loaded. But the other next batches are not loaded. If JDBC finishes the corrupt batch, it doesn’t makes sense to not continue with the next correct batches. Is there a simple way to continue with all the batches?

prakash
6 years ago

Hi Mykyong,

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

Bruce
10 years ago

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
10 years ago
Reply to  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
10 years ago

Hi mkyong,
Can batchupdate be used for procedure call?

Rahul
11 years ago

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

Priyanka
1 year ago

Just adding @Transactional doesnot work for rollback. Any help?

shital
1 year ago

HI, I am trying to perform multiple batch update operation into single method call with @Transactional.like I have 2 insert with batch update and one delete operation how can use this @Transcation to make all 3 operation as single unit if any thing goes wrong will rollback from all these operations

Jorge
2 years ago
Doing what works is simple. Trying to do what doesn't work! That would help
InsaneCode
2 years ago

@Transactional does not work with jdbcTemplate.batchInsert. It returns 0 but the rows gets inserted in the database for correct records.

Nani
2 years ago

Hi Mykyong,

Can you please provide an example for bulk selection, insertion, update, and deletion in the same transaction(@Transactional) using Spring JdbcTemplate.

Priyanka
3 years ago

hi,
I am facing issue with chunk processing, if my chunk size is 1 it works but if I increase the chunk size its only inserting the last row of the chunk.
I have used customwriter.

sadia
3 years ago

let say there is a table C who has table Book FK and there is a requirement of insertion in book table and the same time table C, how it is achieve from batch update?

TrentMaker
3 years ago

Good one. Thanks for this

Kaushik G
3 years ago

Thank you, Good article.

Chethan
6 years ago

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
6 years ago

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
7 years ago

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
8 years ago

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

Shiv
5 years ago
Reply to  Mohit

use merge query.

david
8 years ago

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
10 years ago

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
10 years ago

its bit urgent 🙁

Abhinav
10 years ago

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
10 years ago

Great article

jarida theme free download
10 years ago

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
11 years ago

Is it working on Oracle Database