目录

1 背景

2 项目情况

2.1 依赖

2.2 插入代码

2.2.1 controller层

2.2.2 service层

2.2.3 dao层

2.2.4 repository查询

2.2.5 VO和PO

2.2.6 配置

3 监控SQL执行手段

3.1 监控bean注册spring容器，开启拦截器

3.2 监控界面

3.3 非批量验证

3 批量更新的三种方式

3.1 order_insert和order_update

3.1.1 打开配置

3.1.2 数据库清空后验证

 3.1.3 主键策略换成GenerationType.AUTO|GenerationType.SEQUENCE

---

1 背景

业务开发的过程中，常会遇到批量插入的需求，比如往数据库插入1000条用户的信息。

循环1000次插入数据的性能不如10个连接每次插入100条好，所以合理的批量插入很有必要。

本文只会将JPA+hibernate实现批量新增的方式。

2 项目情况

讲解项目基本情况，比如初始配置，依赖等

2.1 依赖

<?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.1.18.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>page-jpa</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>page-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-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-logging</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

2.2 插入代码

讲述插入代码，传统的MVC分层。

2.2.1 controller层

package com.example.pagejpa.controller;

import java.util.ArrayList;
import java.util.List;

import com.example.pagejpa.service.UserService;
import com.example.pagejpa.vo.Response;
import com.example.pagejpa.vo.user.UserVO;
import org.apache.catalina.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/v1/user")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/page/{pageNo}/{pageSize}")
    public Response<Page<UserVO>> pageUsers(@PathVariable Integer pageNo,@PathVariable Integer pageSize){

        // 这里前端传入第一页是1，但是jpa是从第0页开始的，需要减1
        Page<UserVO> voPage = userService.pageUsers(pageNo-1, pageSize);

        return Response.ok(voPage);
    }

    @GetMapping("/save/{size}")
    public Response<Integer> saveAll(@PathVariable Integer size){
        List<UserVO> voList =new ArrayList<>(size);

        for(int i=0;i<size;i++){
            UserVO userVO = new UserVO();
            userVO.setId(i);
            userVO.setName("name"+i);
            userVO.setAge(i);
            userVO.setAddress("address"+i);
            voList.add(userVO);
        }

        long l = System.currentTimeMillis();
        userService.saveAll(voList);
        long l1 = System.currentTimeMillis();

        return Response.ok(l1-l);
    }
}

2.2.2 service层

package com.example.pagejpa.service;

import java.util.List;

import com.example.pagejpa.vo.user.UserVO;
import org.springframework.data.domain.Page;

public interface UserService {

    Page<UserVO> pageUsers(int pageNo,int pageSize);

    void saveAll(List<UserVO> userVOList);
}

package com.example.pagejpa.service.impl;

import java.util.List;

import com.example.pagejpa.dao.UserDao;
import com.example.pagejpa.service.UserService;
import com.example.pagejpa.vo.user.UserVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public Page<UserVO> pageUsers(int pageNo, int pageSize) {

        Pageable pageable = PageRequest.of(pageNo, pageSize);

        return userDao.pageUsers(pageable);
    }

    @Override
    public void saveAll(List<UserVO> userVOList) {
        userDao.saveAll(userVOList);
    }
}

2.2.3 dao层

package com.example.pagejpa.dao.impl;

import com.example.pagejpa.dao.UserDao;
import com.example.pagejpa.po.UserPO;
import com.example.pagejpa.repository.UserRepository;
import com.example.pagejpa.vo.user.UserVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.stream.Collectors;

@Component
public class UserDaoImpl implements UserDao {

    @Autowired
    private UserRepository userRepository;

    @Override
    public Page<UserVO> pageUsers(Pageable pageable) {
        Page<UserPO> all = userRepository.findAll(pageable);

        List<UserVO> collect = all.getContent().stream().map(UserVO::convertToVO).collect(Collectors.toList());

        return new PageImpl<>(collect, pageable, all.getTotalElements());
    }

    @Override
    public void saveAll(List<UserVO> userVOList) {
        List<UserPO> userPOList = userVOList.stream().map(UserVO::convertToPO).collect(Collectors.toList());
        userRepository.saveAll(userPOList);
    }
}

2.2.4 repository查询

package com.example.pagejpa.repository;

import com.example.pagejpa.po.UserPO;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * 参数1 T:需要映射的实体
 * 参数2 ID:当前映射的实体中的OID类型
 */
public interface UserRepository extends JpaRepository<UserPO,Integer> {
}

2.2.5 VO和PO

主要讲述使用的VO和PO.

package com.example.pagejpa.vo.user;

import com.example.pagejpa.po.UserPO;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.databind.PropertyName;
import lombok.Getter;
import lombok.Setter;

@Setter
@Getter
public class UserVO {
    /**
     *  @Id 代表是主键
     *  @GeneratedValue 代表主键生成策略
     */
    private Integer id;

    private String name;

    private Integer age;

    private String address;

    public static UserVO convertToVO(UserPO userPO){
        UserVO userVO = new UserVO();
        userVO.setAddress(userPO.getAddress());
        userVO.setAge(userPO.getAge());
        userVO.setId(userPO.getId());
        userVO.setName(userPO.getName());

        return userVO;
    }

    public static UserPO convertToPO(UserVO userVO){
        UserPO userPO = new UserPO();
        userPO.setAddress(userVO.getAddress());
        userPO.setAge(userVO.getAge());
        userPO.setId(userVO.getId());
        userPO.setName(userVO.getName());

        return userPO;
    }
}

package com.example.pagejpa.po;

import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

import javax.persistence.*;

@Slf4j
@Entity
@Table(name="user")
@Getter
@Setter
public class UserPO {

    /**
     *  @Id 代表是主键 @GeneratedValue(strategy = GenerationType.AUTO/SEQUENCE) //不能使用GenerationType.IDENTITY
     *  @GeneratedValue 代表主键生成策略
     */
    @Id
    //@GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="id")
    private Integer id;

    @Column(name="name")
    private String name;

    @Column(name="age")
    private Integer age;

    @Column(name="address")
    private String address;
}

2.2.6 配置

spring:
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update
    database-platform: org.hibernate.dialect.MySQL5Dialect
  datasource:
    druid:
      url: jdbc:mysql://192.168.40.129:3306/tpuc_account?useSSL=false&connectTimeout=1000&socketTimeout=3000&cachePrepStmts=true&useServerPrepStmts=true
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
      test-while-idle: true
      initial-size: 2
      validation-query: select 1
      min-evictable-idle-time-millis: 40000
      time-between-eviction-runs-millis: 36000
      max-active: 10
      test-on-borrow: false
      test-on-return: false
      max-wait: 6000
    type: com.alibaba.druid.pool.DruidDataSource

3 监控SQL执行手段

3.1 监控bean注册spring容器，开启拦截器

引入的druid线程池可以进行监控，前提是要注册bean到spring容器，具体如下：

  /**
     * 配置Druid的监控  配置一个管理后台的Servlet
     */
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String, String> initParams = new HashMap<>();

        // 登录用户名和密码
        initParams.put("loginUsername", "admin");
        initParams.put("loginPassword", "123456");

        bean.setInitParameters(initParams);
        return bean;
    }

    /** 配置一个web监控的filter
     * 
     * @return
     */
    @Bean
    public FilterRegistrationBean webStatFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js,*.css,/druid/*");

        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }

3.2 监控界面

输入http://127.0.0.1:8080/druid/login.html

账户和密码来自3.1步骤代码种的username和password

3.3 非批量验证

  【非批量】插入10条，http://127.0.0.1:8080/v1/user/save/10

可以看见，没有批量，sql执行了10次。

3 批量更新的三种方式

这里会讲解三种方式和验证的手段。

3.1 order_insert和order_update

3.1.1 打开配置

spring:
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update
    database-platform: org.hibernate.dialect.MySQL5Dialect
    properties:
      hibernate:
        jdbc:
          batch_size: 10                        // 批量数据大小
          batch_versioned_data: true            // 批量时建议打开
        order_inserts: true                     // 批量插入开关
        order_updates: true                     // 批量更新开关 
  datasource:
    druid:
      url: jdbc:mysql://192.168.40.129:3306/tpuc_account?useSSL=false&connectTimeout=1000&socketTimeout=3000&cachePrepStmts=true&useServerPrepStmts=true&rewriteBatchedStatements=true  // rewriteBatchedStatements=true,可填
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
      test-while-idle: true
      initial-size: 2
      validation-query: select 1
      min-evictable-idle-time-millis: 40000
      time-between-eviction-runs-millis: 36000
      max-active: 10
      test-on-borrow: false
      test-on-return: false
      max-wait: 6000
    type: com.alibaba.druid.pool.DruidDataSource

3.1.2 数据库清空后验证

可以看见打开开关后，sql还是执行了10次，难道是批量配置打开后不成功

 不是批量配置不生效，是实体的主键生成策略没有用对。

 GenerationType.IDENTITY在批量插入时无效！！！

主键策略使用GenerationType.AUTO、GenerationType.SEQUENCE有效。

 3.1.3 主键策略换成GenerationType.AUTO|GenerationType.SEQUENCE

ps：
1、hibernate插入值之前，会先进行查询，根据查询结果判断是否需要插入。
2、不使用主键生成策略，业务生成主键id,开启批量配置插入，也会实现批量插入功能。
3、这里只谈论Mysql

PS:启动方式 -Ddruid.mysql.usePingMethod=false，validate检查不用ping

-Ddruid.filters=mergeStat -Ddruid.useGlobalDataSourceStat=true druid线程池监控页面需要

特别是第二个JVM参数特别重要。