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

时间：2025-04-24 07:18:36

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

2.创建springboot项目

3.引入pom依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="/POM/4.0.0" xmlns:xsi="http:///2001/XMLSchema-instance"
         xsi:schemaLocation="/POM/4.0.0 /xsd/maven-4.0.">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId></groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId></groupId>
    <artifactId>jpa</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spingboot-jpa</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <>1.8</>
    </properties>

    <dependencies>
        <dependency>
            <groupId></groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId></groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId></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></groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId></groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId></groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>


spring:
  datasource:
    driver-class-name: 
    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: .MySQL5InnoDBDialect
        format_sql: true
        # 开启批量插入
        jdbc:
          batch_size: 500
          batch_versioned_data: true
        order_inserts: true
        order_updates: true
    show-sql: true

5.实体类

package ;

import ;
import ;
import ;
import ;

import ;
import ;
import ;
import ;
import ;

/**
 * @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) {
         = userName;
         = passWord;
         = email;
         = nickName;
         = regTime;
    }

    public User() {

    }

}


package ;

import ;
import ;
import ;
import ;
import ;
import ;
import ;

import ;

/**
 * @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);
}


package ;

import ;
import ;
import .slf4j.Slf4j;
import ;
import ;
import ;
import ;
import ;

import ;
import ;
import ;

/**
 * @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 (!(list)) {
            for (int i = 0; i < (); i++) {
                ((i));
                if (i % 50 == 0) {
                    ();
                    ();
                }
            }
            ();
            ();
        }
    }

    /**
     * 批量更新
     *
     * @param list 实体类集合
     * @param <T>  表对应的实体类
     */
    public <T> void batchUpdate(List<T> list) {
        if (!(list)) {
            for (int i = 0; i < (); i++) {
                ((i));
                if (i % 50 == 0) {
                    ();
                    ();
                }
            }
            ();
            ();
        }
    }

    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 < (); i++) {
            (new Object[]{(i)});
        }
        (sql, batchArgs);*/


        StringBuilder insert = new StringBuilder("INSERT INTO `user` (`user_name`, `pass_word`, `nick_name`," +
                "`email`,`reg_time`) VALUES ");
        for (int i = 0; i < (); i++) {
            ("(")
                    .append("'")
                    .append((i).getUserName())
                    .append("'")
                    .append(",")
                    .append("'")
                    .append((i).getPassWord())
                    .append("'")
                    .append(",")
                    .append("'")
                    .append((i).getNickName())
                    .append("'")
                    .append(",")
                    .append("'")
                    .append((i).getEmail())
                    .append("'")
                    .append(",")
                    .append("'")
                    .append((i).getRegTime())
                    .append("'")
                    .append(")");
            if (i < () - 1) {
                (",");
            }
        }
        String sql = (String) (insert);
        ("SQL语句:{}", (insert));
        try {
            (sql);
        } catch (Exception e) {
            ("sql解析错误", ());
        }

    }
}

8.控制器

package ;

import ;
import ;
import ;
import .slf4j.Slf4j;
import ;
import ;
import ;

import ;
import ;
import ;
import ;

/**
 * @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();
            ("小石潭记" + i);
            (user);
        }
        long start = ();
        ("开始保存", start);
        // 500 条数据花费9s
        (list);
        long end = ();
        ("耗时{}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();
            ("frank" + i);
            (user);
        }
        long start = ();
        ("开始保存", start);
        // 500 条数据花费13s  yml开启批量操作9s
        (list);
        long end = ();
        ("耗时{}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();
            ("frank" + i);
            (i + "");
            ("小石潭记" + i);
            ("password" + i);
            (user);
        }
        long start = ();
        ("开始保存", start);
        (list);
        long end = ();
        ("耗时{}", end-start);
    }

    @GetMapping("/user-info")
    public List<User> getUserInfo(String userName, String email) {
        User user = (userName);
        ("查询的user,{}", user);
        List<User> byUserNameOrEmail = (userName, email);
        ("查询的byUserNameOrEmail,{}", byUserNameOrEmail);
        Iterable<User> iterable = ();
        List<User> list = new ArrayList<>();
        (single->{(single);});
        return list;
    }

    @GetMapping("/update-user")
    public int updateUserName(String id, String userName) {
        long userId = (id);
        return (userId, userName);
    }

}

9.启动类

package ;

import ;
import ;
import ;
import ;
import ;

/**
 * @EnableJpaRepositories(basePackages = “”) //扫描 @Repository 注解；
 * @ComponentScan(basePackages = “”) //扫描 @Controller、@Service 注解；
 * @EntityScan(basePackages = “”) //扫描 @Entity 注解；
 */
@SpringBootApplication
public class SpingbootJpaApplication {

    public static void main(String[] args) {
        (, args);
    }

}

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

 

代码地址

相关文章