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

系统管理员 2022-12-12 01:56 913阅读 0赞

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/> 
        </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，速度快了很多。

[代码地址][Link 1]

[Link 1]: https://github.com/ChenXbFrank/springboot-jpa-batch