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
$ cd spring-jdbc
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) ?
The default behaviors will skip the failed and continue the remaining rows. Logs the failed insert or roll back the entire batch operation with
@Transactional
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?
Hi Mykyong,
Please suggest how to rollback an insert if an exception occurred while inserting data using Spring JdbcTemplate batchUpdate() method.
import org.springframework.transaction.annotation.Transactional;
Article is updated with
@Transactional
roll back example, please refer to #4 exampleTHIS 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.
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;
Hi mkyong,
Can batchupdate be used for procedure call?
Hi,
How can we get the ids of autoincremented inserts via batchupdate.
Thanks
Rahul
Just adding @Transactional doesnot work for rollback. Any help?
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
@Transactional does not work with jdbcTemplate.batchInsert. It returns 0 but the rows gets inserted in the database for correct records.
Hi Mykyong,
Can you please provide an example for bulk selection, insertion, update, and deletion in the same transaction(@Transactional) using Spring JdbcTemplate.
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.
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?
Good one. Thanks for this
Thank you, Good article.
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.
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)
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
Hi Guys, How we can handled the duplicate exception while insert data in batch.
use merge query.
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
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)
its bit urgent 🙁
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????
Great article
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.
Is it working on Oracle Database