目录
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参数特别重要。