SpringBoot JPA 批量插入实现，使用原生sql解决SaveAll插入慢的问题

1.项目中遇到了批量插入的操作，但是使用saveAll，数据量5000条而已，速度感人，用户体验贼差，哈哈，现在将解决方法记录一下。

2.创建springboot项目

3.引入pom依赖

<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.6.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.frank</groupId> <artifactId>jpa</artifactId> <version>0.0.1-SNAPSHOT</version> <name>spingboot-jpa</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.48</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.47</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>

4.application.yml

spring: datasource: driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true username: root password: 123456 jpa: properties: hibernate: hbm2ddl: auto: update dialect: org.hibernate.dialect.MySQL5InnoDBDialect format_sql: true # 开启批量插入 jdbc: batch_size: 500 batch_versioned_data: true order_inserts: true order_updates: true show-sql: true

5.实体类

package com.frank.jpa.entity; import lombok.AllArgsConstructor; import lombok.Data; import lombok.Getter; import lombok.NoArgsConstructor; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import java.io.Serializable; /** * @author 小石潭记 * @date 2020/10/4 14:14 * @Description: ${todo} */ @Entity @Data public class User implements Serializable { @Id @GeneratedValue private Long id; @Column(nullable = false, unique = true) private String userName; @Column(nullable = false) private String passWord; @Column(nullable = false, unique = true) private String email; @Column(nullable = true, unique = true) private String nickName; @Column(nullable = false) private String regTime; public User(String userName, String passWord, String email, String nickName, String regTime) { this.userName = userName; this.passWord = passWord; this.email = email; this.nickName = nickName; this.regTime = regTime; } public User() { } }

6.repository

package com.frank.jpa.repository; import com.frank.jpa.entity.User; import org.springframework.data.jpa.repository.Modifying; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.PagingAndSortingRepository; import org.springframework.data.repository.query.Param; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; import java.util.List; /** * @author 小石潭记 * @date 2020/10/4 14:15 * @Description: ${todo} * 1. 使用jpa的 CrudRepository 基本查询 * 2. 使用jpa的 PagingAndSortingRepository 分页查询和排序 * 3. 使用jpa的 Repository 自定义声明式查询方法 * public interface PersonQueryRepo extends Repository<Person, Long> { // declare query method // 声明式查询方法 // 1. count 计数 long countByName(String name); // 2. get/find/stream/query/read 查询 Person readFirstByAge(int age); // 3. delete/remove 删除 @Transactional int deleteById(long id); } * 4. 使用jpa的 JpaRepository 使用hql、jpql或sql查询，@Query等注解 public interface PersonHqlDao extends JpaRepository<Person, Long> { // 使用hql 或者 jpql 查询 @Query("from Person where name = ?1 order by id desc") List<Person> listByName(String name); // 前几种方法中均未介绍update操作，要完成update操作，可使用以下方法 // 更新时需要加上 @Transactional 和 @Modifying @Transactional @Modifying // QueryExecutionRequestException: Not supported for DML operations @Query("update Person set name=?2 where id=?1") int updateNameById(long id, String name); } */ @Repository public interface UserRepository extends PagingAndSortingRepository<User, Long> { List<User> findByUserNameOrEmail(@Param("userName")String userName, @Param("email")String email); User findByUserName(@Param("userName") String userName); @Transactional @Modifying // QueryExecutionRequestException: Not supported for DML operations @Query("update User set user_name=?2 where id=?1") int updateUserNameById(long id, @Param("userName")String userName); }

7.service

package com.frank.jpa.service; import com.alibaba.fastjson.JSON; import com.frank.jpa.entity.User; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import org.springframework.util.ObjectUtils; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import java.util.List; /** * @author 小石潭记 * @date 2020/10/4 17:03 * @Description: ${todo} */ @Service @Transactional @Slf4j public class UserService { @PersistenceContext private EntityManager entityManager; @Autowired private JdbcTemplate jdbcTemplate; /** * 批量插入 * * @param list 实体类集合 * @param <T> 表对应的实体类 */ public <T> void batchInsert(List<T> list) { if (!ObjectUtils.isEmpty(list)) { for (int i = 0; i < list.size(); i++) { entityManager.persist(list.get(i)); if (i % 50 == 0) { entityManager.flush(); entityManager.clear(); } } entityManager.flush(); entityManager.clear(); } } /** * 批量更新 * * @param list 实体类集合 * @param <T> 表对应的实体类 */ public <T> void batchUpdate(List<T> list) { if (!ObjectUtils.isEmpty(list)) { for (int i = 0; i < list.size(); i++) { entityManager.merge(list.get(i)); if (i % 50 == 0) { entityManager.flush(); entityManager.clear(); } } entityManager.flush(); entityManager.clear(); } } public void saveBatch(List<User> list) { /*String sql="insert into user " + "(user_name)" + " values (?)"; List<Object[]> batchArgs=new ArrayList<Object[]>(); for (int i = 0; i < list.size(); i++) { batchArgs.add(new Object[]{list.get(i)}); } jdbcTemplate.batchUpdate(sql, batchArgs);*/ StringBuilder insert = new StringBuilder("INSERT INTO `user` (`user_name`, `pass_word`, `nick_name`," + "`email`,`reg_time`) VALUES "); for (int i = 0; i < list.size(); i++) { insert.append("(") .append("'") .append(list.get(i).getUserName()) .append("'") .append(",") .append("'") .append(list.get(i).getPassWord()) .append("'") .append(",") .append("'") .append(list.get(i).getNickName()) .append("'") .append(",") .append("'") .append(list.get(i).getEmail()) .append("'") .append(",") .append("'") .append(list.get(i).getRegTime()) .append("'") .append(")"); if (i < list.size() - 1) { insert.append(","); } } String sql = (String) JSON.toJSON(insert); log.info("SQL语句:{}", JSON.toJSON(insert)); try { jdbcTemplate.execute(sql); } catch (Exception e) { log.error("sql解析错误", e.getMessage()); } } }

8.控制器

package com.frank.jpa.web; import com.frank.jpa.entity.User; import com.frank.jpa.repository.UserRepository; import com.frank.jpa.service.UserService; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; /** * @author 小石潭记 * @date 2020/10/4 14:25 * @Description: ${todo} */ @RestController @Slf4j public class UserController { @Autowired private UserRepository repository; @Autowired private UserService service; @GetMapping("/save") public void save(){ List<User> list = new ArrayList<>(); for (int i = 0; i < 500; i++) { User user = new User(); user.setUserName("小石潭记" + i); list.add(user); } long start = System.currentTimeMillis(); log.info("开始保存", start); // 500 条数据花费9s repository.saveAll(list); long end = System.currentTimeMillis(); log.info("耗时{}s", (end-start) / 1000); } @GetMapping("/saveAll") public void saveAll(){ List<User> list = new ArrayList<>(); for (int i = 0; i < 500; i++) { User user = new User(); user.setUserName("frank" + i); list.add(user); } long start = System.currentTimeMillis(); log.info("开始保存", start); // 500 条数据花费13s yml开启批量操作9s service.batchInsert(list); long end = System.currentTimeMillis(); log.info("耗时{}s", (end-start) / 1000); } /** * 使用原生的jdbcTemplate批量插入数据 速度比上面的两个都快 */ @GetMapping("/saveBatch") public void saveBatch(){ List<User> list = new ArrayList<>(); // 耗时1792ms 速度很快了 for (int i = 0; i < 5000; i++) { User user = new User(); user.setUserName("frank" + i); user.setEmail(i + "qq.com"); user.setNickName("小石潭记" + i); user.setPassWord("password" + i); list.add(user); } long start = System.currentTimeMillis(); log.info("开始保存", start); service.saveBatch(list); long end = System.currentTimeMillis(); log.info("耗时{}", end-start); } @GetMapping("/user-info") public List<User> getUserInfo(String userName, String email) { User user = repository.findByUserName(userName); log.info("查询的user,{}", user); List<User> byUserNameOrEmail = repository.findByUserNameOrEmail(userName, email); log.info("查询的byUserNameOrEmail,{}", byUserNameOrEmail); Iterable<User> iterable = repository.findAll(); List<User> list = new ArrayList<>(); iterable.forEach(single->{list.add(single);}); return list; } @GetMapping("/update-user") public int updateUserName(String id, String userName) { long userId = Long.parseLong(id); return repository.updateUserNameById(userId, userName); } }

9.启动类

package com.frank.jpa; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.boot.autoconfigure.domain.EntityScan; import org.springframework.context.annotation.ComponentScan; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; /** * @EnableJpaRepositories(basePackages = “xxx.xxx.xxx”) //扫描 @Repository 注解； * @ComponentScan(basePackages = “xxx.xxx.xxx”) //扫描 @Controller、@Service 注解； * @EntityScan(basePackages = “xxx.xxx.xxx”) //扫描 @Entity 注解； */ @SpringBootApplication public class SpingbootJpaApplication { public static void main(String[] args) { SpringApplication.run(SpingbootJpaApplication.class, args); } }

经过测试，使用saveAll500条数据花费9s时间，yml开启批量插入：500 条数据花费13s yml开启批量操作9s，使用jdbcTemplate插入数据，5000条数据花费1792ms，速度快了很多。

 

代码地址