在基于Spring的项目中,在某些应用场景下可能需要对数据库进行批量的增删改操作,此种情况下如果仍旧使用循进行单条数据操作的话,操作耗时将会非常巨大,如果使用批量操作,操作耗时将会变得非常小,本文将通过使用Spring JdbcTemplate 批量操作来做对比说明这种优势, 同时介绍相关api的用法;
一,基础环境准备
本文主要通过SpringBoot JdbcTemplate和JPA进行演示,版本为2.1.0,db使用MySQL;
1.1,引入相关依赖
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
1.2,创建实体类&实体类持久层api
package com.springboot;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import javax.validation.constraints.NotNull;
import java.io.Serializable;
@Entity
@Data
@Table(name = "promotion")
@Builder
public class Promotion implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@NotNull
@Column(name = "promoter_id")
private Long promoterId;
@NotNull
@Column(name = "account_id")
private Long accountId;
@Column(name = "content")
private String content;
}
package com.springboot;
import com.springboot.bean.Promotion;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
@Repository
public interface PromotionRepository extends JpaRepository<Promotion, Long>, JpaSpecificationExecutor<Promotion> {
}
sql脚本如下:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for promotion
-- ----------------------------
DROP TABLE IF EXISTS `promotion`;
CREATE TABLE `promotion` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`promoter_id` int(11) NULL DEFAULT NULL,
`account_id` int(11) NULL DEFAULT NULL,
`content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 39025 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
二,循环进行单条数据操作
在SpringBoot应用中创建一个SingleInsertTask任务;
package com.springboot;
import com.springboot.bean.Promotion;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
/**循环进行单体数据插入操作演示
* @author kevin chen
*/
@Component
@Slf4j
public class SingleInsertTask implements CommandLineRunner {
@Autowired
JdbcTemplate jdbcTemplate;
@Autowired
PromotionRepository promotionRepository;
@Override
public void run(String... args) {
final int size = 1000;
long begin = System.currentTimeMillis();
log.info("SingleInsertTask exec begin============");
for (long i = 0; i < size; i++) {
Promotion promotion = Promotion.builder().accountId(i)
.promoterId(i)
.content("hello world!" + i)
.build();
// 将此条数据插入db
promotionRepository.save(promotion);
}
long end = System.currentTimeMillis();
log.info("SingleInsertTask exec end,cost time:{}", end - begin);
}
}
启动应用后,SingleInsertTask便会开始执行,为使演示结果贴近实际场景,本文选择连接的是部署在aliyun上的db,连续执行3次结果平均耗时约为60s;
三,使用JdbcTemplate进行批量插入/更新;
创建进行批量操作的基类(核心代码)
package com.springboot;
import lombok.Data;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
/**
* 批量插入/更新基类
* @Author: kevin
*/
@Data
public abstract class BatchOperator<T> {
protected String sql;
protected JdbcTemplate jdbcTemplate;
protected List<T> items;
public BatchOperator(List<T> items, JdbcTemplate jdbcTemplate, String sql) {
this.items = items;
this.setJdbcTemplate(jdbcTemplate);
this.sql = sql;
}
/**子类重写此方法
* @param item 需要操作对应表的实体类
* @param ps 自动注入,无需传入
* @throws SQLException
*/
public abstract void bind(T item, PreparedStatement ps) throws SQLException;
public void exec() {
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
bind(items.get(i), ps);
}
@Override
public int getBatchSize() {
return items.size();
}
});
}
}
创建实体更新器类继承BatchOperator
package com.springboot;
import com.springboot.bean.Promotion;
import org.springframework.jdbc.core.JdbcTemplate;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
/**
* @Author: kevin
*/
public class PromotionBatchInserter extends BatchOperator<Promotion> {
public PromotionBatchInserter(List<Promotion> items, JdbcTemplate jdbcTemplate, String sql) {
super(items, jdbcTemplate, sql);
}
@Override
public void bind(Promotion promotion, PreparedStatement ps) throws SQLException {
ps.setLong(1, promotion.getPromoterId());
ps.setLong(2, promotion.getAccountId());
ps.setString(3,promotion.getContent());
}
}
在SpringBoot应用中创建一个 BatchInsertTask 任务;
package com.springboot;
import com.springboot.bean.Promotion;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
/**使用jdbcTemplate进行批量插入
* @author kevin chen
*/
@Component
@Slf4j
public class BatchInsertTask implements CommandLineRunner {
@Autowired
JdbcTemplate jdbcTemplate;
@Autowired
PromotionRepository promotionRepository;
@Override
public void run(String... args) {
final int size = 1000;
long begin = System.currentTimeMillis();
log.info("BatchInsertTask exec begin============");
String sql = "INSERT into promotion2(promoter_id,account_id,content) VALUES(?,?,?)";
List<Promotion> list = new ArrayList<>(size);
for (long i = 0; i < size; i++) {
Promotion promotion = Promotion.builder().accountId(i)
.promoterId(i)
.content("hello world!" + i)
.build();
list.add(promotion);
}
PromotionBatchInserter batchInserter = new PromotionBatchInserter(list, jdbcTemplate, sql);
batchInserter.exec();
long end = System.currentTimeMillis();
log.info("BatchInsertTask exec end,cost time:{}", end - begin);
}
}
连续三次启动应用并执行BatchInsertTask,执行结果耗时平均值约为0.1s;
四,总结
根据执行结果进行对比发现:使用JdbcTemplate进行批量插入操作的耗时比循环单条数据插入执行耗时降低了几个几何级数,提升效果异常明显,原因在于JdbcTemplate批量插入操作时采用的一次事务插入所有数据,而单条数据插入时每次插入都要重建事务,事务的频繁创建和提交耗时巨大,而使用批量操作则不会出现这样的问题;
掘友如有需要可根据本示例继承 BatchOperator 来创建自定义的批量操作器完成批量操作;
码字不易,示例若能提供帮助,烦请掘友给个赞鼓励一下,谢谢;
