<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.3</version>
<relativePath/>
</parent>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql-connector-java.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT&nullCatalogMeansCurrent=true
username: root
password: root
hikari:
maximum-pool-size: 10
minimum-idle: 5
数据库表结构
CREATE TABLE `cbsd_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`SERIAL_NUMBER` varchar(50) CHARACTER SET latin1 NOT NULL,
`cbsdid` varchar(256) CHARACTER SET latin1 DEFAULT NULL,
`msg` varchar(1000) CHARACTER SET latin1 DEFAULT NULL,
`time` datetime DEFAULT NULL,
`sas_state` char(1) CHARACTER SET latin1 DEFAULT NULL COMMENT '0-unregistered/1-registered/2-idl/3-granted/4-authorized/5-grant_suspended',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24001 DEFAULT CHARSET=utf8;
单元测试 2000的数据量
@SpringBootTest(classes= {SasDpApplication.class})
public class Test {
@Autowired
JdbcTemplate jdbcTemplate
@org.junit.jupiter.api.Test
public void testNoBatch() {
List<CbsdLog> logList = Lists.newArrayList()
for (int i = 0
CbsdLog log=new CbsdLog()
log.setCbsdid(""+i)
log.setSerialNumber(""+i)
log.setCreateTime(LocalDateTime.now())
logList.add(log)
Long startTime=System.currentTimeMillis()
System.out.println("开始时间"+startTime)
this.jdbcTemplate.batchUpdate("insert into cbsd_log (cbsdid,SERIAL_NUMBER,time) values (?,?,?)", logList, 1000, new ParameterizedPreparedStatementSetter<CbsdLog>() {
@Override
public void setValues(PreparedStatement ps, CbsdLog log) throws SQLException {
ps.setString(1,log.getCbsdid())
ps.setString(2,log.getSerialNumber())
ps.setDate(3,new Date(new java.util.Date().getTime()))
Long endTime=System.currentTimeMillis()
System.out.println("结束时间"+endTime)
System.out.println("消耗时间"+(endTime-startTime))
开始时间1629863033966
2021-08-25 11:43:53.979 INFO 4952 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2021-08-25 11:43:54.497 INFO 4952 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
结束时间1629863040136
消耗时间6170
单元测试 2000的数据量增加 &rewriteBatchedStatements=true
开始时间1629863142794
2021-08-25 11:45:42.807 INFO 10488 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2021-08-25 11:45:43.071 INFO 10488 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
结束时间1629863143228
消耗时间434
通过以上的对吧可以清楚地看出提高了入库速度
单元测试 20000的数据量
开始时间1629863295120
2021-08-25 11:48:15.132 INFO 3776 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2021-08-25 11:48:15.419 INFO 3776 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
结束时间1629863351409
消耗时间56289
单元测试 20000的数据量增加 &rewriteBatchedStatements=true
开始时间1629863245142
2021-08-25 11:47:25.154 INFO 9432 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2021-08-25 11:47:25.404 INFO 9432 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
结束时间1629863245908
消耗时间766
通过以上的2组对比，我们可以得出结论在进行批处理的在数据库连接池上增加&rewriteBatchedStatements=true该属性会提高执行效率
org.springframework.jdbc.core.JdbcTemplate
@Override
public <T> int[][] batchUpdate(String sql, final Collection<T> batchArgs, final int batchSize,
final ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException {
if (logger.isDebugEnabled()) {
logger.debug("Executing SQL batch update [" + sql + "] with a batch size of " + batchSize)
int[][] result = execute(sql, (PreparedStatementCallback<int[][]>) ps -> {
List<int[]> rowsAffected = new ArrayList<>()
try {
boolean batchSupported = JdbcUtils.supportsBatchUpdates(ps.getConnection())
int n = 0
for (T obj : batchArgs) {
pss.setValues(ps, obj)
n++
if (batchSupported) {
ps.addBatch()
if (n % batchSize == 0 || n == batchArgs.size()) {
if (logger.isTraceEnabled()) {
int batchIdx = (n % batchSize == 0) ? n / batchSize : (n / batchSize) + 1
int items = n - ((n % batchSize == 0) ? n / batchSize - 1 : (n / batchSize)) * batchSize
logger.trace("Sending SQL batch update
//重点：执行批量操作
rowsAffected.add(ps.executeBatch())
else {
int i = ps.executeUpdate()
rowsAffected.add(new int[] {i})
int[][] result1 = new int[rowsAffected.size()][]
for (int i = 0
result1[i] = rowsAffected.get(i)
return result1
finally {
if (pss instanceof ParameterDisposer) {
((ParameterDisposer) pss).cleanupParameters()
Assert.state(result != null, "No result array")
return result
com.mysql.cj.jdbc.ClientPreparedStatement#executeBatchInternal
@Override
protected long[] executeBatchInternal() throws SQLException {
synchronized (checkClosed().getConnectionMutex()) {
if (this.connection.isReadOnly()) {
throw new SQLException(Messages.getString("PreparedStatement.25") + Messages.getString("PreparedStatement.26"),
MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT);
if (this.query.getBatchedArgs() == null || this.query.getBatchedArgs().size() == 0) {
return new long[0];
int batchTimeout = getTimeoutInMillis();
setTimeoutInMillis(0);
resetCancelledState();
try {
statementBegins();
clearWarnings();
if (!this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) {
if (((PreparedQuery<?>) this.query).getParseInfo().canRewriteAsMultiValueInsertAtSqlLevel()) {
return executeBatchedInserts(batchTimeout);
if (!this.batchHasPlainStatements && this.query.getBatchedArgs() != null
&& this.query.getBatchedArgs().size() > 3 ) {
return executePreparedBatchAsMultiStatement(batchTimeout);
return executeBatchSerially(batchTimeout);
} finally {
this.query.getStatementExecuting().set(false);
clearBatch();
我们在开发过程中，在完成代码的前提下，可以更高的要求自己，提高性能和效率
- 9161
-
hryou0922
Spring
OKHttp
Debug
- 831
-
izecsonLee
Spring
Tomcat
