Spring JdbcTemplate batchUpdate（）示例

客官°小女子只卖身不卖艺 2023-02-15 07:37 8阅读 0赞

Spring `JdbcTemplate`批处理插入，批处理更新以及`@Transactional`示例。

使用的技术：

*  Spring Boot 2.1.2发布
 *  Spring JDBC 5.1.4.RELEASE
 *  Maven 3
 *  Java 8

## 1.批量插入 ##

1.1一起插入一批SQL插入。

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如果批量太大，我们可以将其拆分为较小的批量。

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.批量更新 ##

2.1与SQL更新语句相同。

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按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.运行 ##

3.1创建一个表以测试批处理的插入和更新。

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));
    	
    }

输出量

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. @交易 ##

4.1使用`@Transactional` ，任何失败都会导致整个操作回滚，因此不会添加任何书籍。

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尝试批量插入1000本书，＃500包含错误，整个批次将被回滚，不插入任何书籍。

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());
    
    }

输出量

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

## 下载源代码 ##

> $ git clone [https://github.com/mkyong/spring-boot.git][https_github.com_mkyong_spring-boot.git]  
> $ cd spring-jdbc

## 参考文献 ##

*  [JDBC批处理操作JavaDocs][JDBC_JavaDocs]
 *  [Spring JDBC文档][Spring JDBC]
 *  [Spring Boot JDBC示例][Spring Boot JDBC] \_
 *  [Java JDBC教程][Java JDBC]

标签： [jdbc][] [春天][Link 1] [春天jdbc][jdbc 1] [交易][Link 2]

> 翻译自: [https://mkyong.com/spring/spring-jdbctemplate-batchupdate-example/][https_mkyong.com_spring_spring-jdbctemplate-batchupdate-example]

[https_github.com_mkyong_spring-boot.git]: https://github.com/mkyong/spring-boot.git
[JDBC_JavaDocs]: https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access.html#jdbc-advanced-jdbc
[Spring JDBC]: https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access.html#jdbc
[Spring Boot JDBC]: https://www.mkyong.com/spring-boot/spring-boot-jdbc-examples/
[Java JDBC]: https://www.mkyong.com/tutorials/jdbc-tutorials/
[jdbc]: https://mkyong.com/tag/jdbc/
[Link 1]: https://mkyong.com/tag/spring/
[jdbc 1]: https://mkyong.com/tag/spring-jdbc/
[Link 2]: https://mkyong.com/tag/transaction/
[https_mkyong.com_spring_spring-jdbctemplate-batchupdate-example]: https://mkyong.com/spring/spring-jdbctemplate-batchupdate-example/