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: 0Download Source Code
$ git clone https://github.com/favtuts/java-spring-boot-tutorials.git
$ cd spring-jdbc-full