首页 > 编程学习 > SpringBoot集成Spring Data JPA项目实操

SpringBoot集成Spring Data JPA项目实操

发布时间:2022/11/24 23:23:26

《从零打造项目》系列文章

工具

  • 比MyBatis Generator更强大的代码生成器

ORM框架选型

  • SpringBoot项目基础设施搭建

  • SpringBoot集成Mybatis项目实操

  • SpringBoot集成MybatisPlus项目实操

  • SpringBoot集成Spring Data JPA项目实操

前言

该说的在《SpringBoot集成Mybatis项目实操》一文中都讲了,本文只是将 Mybatis 换成了 Spring Data JPA,带大家将整个项目跑起来。

本文将实现 SpringBoot+ Spring Data JPA 的项目搭建,项目特色是针对 JPA 专门写了一套动态链式查询工具类,进而实现一对多查询和连表查询。

不说废话了,我们直接进入主题。

数据库

本项目采用的是 MySQL 数据库,版本为 8.x,建表语句如下:

CREATE TABLE `customer` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,`age` int(11) DEFAULT NULL,`address` varchar(100) DEFAULT NULL,`created_date` timestamp NULL DEFAULT NULL,`last_modified_date` timestamp NULL DEFAULT NULL,`del_flag` int(2) NOT NULL DEFAULT '0',`create_user_name` varchar(50) DEFAULT NULL,`last_modified_name` varchar(50) DEFAULT NULL,`version` int(11) NOT NULL DEFAULT '0',PRIMARY KEY (`id`),UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='父用户';CREATE TABLE `subUser` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,`customer_id` varchar(36) NOT NULL,`address` varchar(100) DEFAULT NULL,`created_date` timestamp NULL DEFAULT NULL,`last_modified_date` timestamp NULL DEFAULT NULL,`del_flag` int(2) NOT NULL DEFAULT '0',`create_user_name` varchar(50) DEFAULT NULL,`last_modified_name` varchar(50) DEFAULT NULL,`version` int(11) NOT NULL DEFAULT '0',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='子用户';

搭建SpringBoot项目

使用 IDEA 新建一个 Maven 项目,叫做 jpa-springboot。

一些共用的基础代码可以参考上篇文章,这里不做重复介绍,会介绍一些 JPA 相关的代码。

引入依赖

<parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.6.3</version>
</parent><properties><java.version>1.8</java.version><fastjson.version>1.2.73</fastjson.version><hutool.version>5.5.1</hutool.version><mysql.version>8.0.19</mysql.version><mybatis.version>2.1.4</mybatis.version><mapper.version>4.1.5</mapper.version><org.mapstruct.version>1.4.2.Final</org.mapstruct.version><org.projectlombok.version>1.18.20</org.projectlombok.version>
</properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-aop</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-security</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-validation</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>${fastjson.version}</version></dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>${hutool.version}</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>${org.projectlombok.version}</version><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>${mysql.version}</version><scope>runtime</scope></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>${mybatis.version}</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</artifactId></dependency><dependency><groupId>org.springdoc</groupId><artifactId>springdoc-openapi-ui</artifactId><version>1.6.9</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.1.18</version></dependency><dependency><groupId>org.mapstruct</groupId><artifactId>mapstruct</artifactId><version>${org.mapstruct.version}</version></dependency><dependency><groupId>org.mapstruct</groupId><artifactId>mapstruct-processor</artifactId><version>${org.mapstruct.version}</version></dependency>
</dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins>
</build>

有些依赖不一定是最新版本,而且你看到这篇文章时,可能已经发布了新版本,到时候可以先模仿着将项目跑起来后,再根据自己的需求来升级各项依赖,有问题咱再解决问题。

分页处理

某些业务场景是需要分页查询和排序功能的,所以我们需要考虑前端如何传递参数给后端,后端如何进行分页查询或者是排序查询。JPA 分页查询使用的是 Spring 自带的 Pageable。

分页基础类

public class SimplePageInfo {private Integer pageNum = 1;private Integer pageSize = 10;public Integer getPageNum() {return pageNum;}public void setPageNum(Integer pageNum) {this.pageNum = pageNum;}public Integer getPageSize() {return pageSize;}public void setPageSize(Integer pageSize) {this.pageSize = pageSize;}
}

排序包装类

@Getter
@Setter
public class OrderInfo {private boolean asc = true;private String column;
}

分页且排序包装类

@Getter
@Setter
@EqualsAndHashCode(callSuper = true)
public class PageSortInfo extends SimplePageInfo {@Schema(name = "排序信息")private List<OrderInfo> orderInfos;public String parseSort() {if (CollectionUtils.isEmpty(orderInfos)) {return null;}StringBuilder sb = new StringBuilder();for (OrderInfo orderInfo : orderInfos) {sb.append(orderInfo.getColumn()).append(" ");sb.append(orderInfo.isAsc() ? " ASC," : " DESC,");}sb.deleteCharAt(sb.length() - 1);return sb.toString();}
}

前端分页查询的请求体对象

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class CustomerQueryPageDTO {@JsonUnwrappedprivate PageSortInfo pageSortInfo;
}

服务层分页查询

Pageable pageable = SpecificationBuilder.getPageable(dto.getPageSortInfo());
Page<Customer> customerPage = customerRepository.findAll(pageable);

关于 PageHelper 的使用这里就不多做介绍了。

我们得到的分页查询结果是 Page 对象,可以直接使用,也可以根据需要进行修改,比如下面这个文件:

@Getter
@Setter
public class PageResult<T> {/*** 总条数*/private Long total;/*** 总页数*/private Integer pageCount;/*** 每页数量*/private Integer pageSize;/*** 当前页码*/private Integer pageNum;/*** 分页数据*/private List<T> data;/*** 处理Jpa分页结果,Jpa页码默认从0开始,所以返回结果加1*/public static <T> PageResult<T> ok(org.springframework.data.domain.Page<T> page) {PageResult<T> result = new PageResult<T>();result.setPageCount(page.getTotalPages());result.setPageNum(page.getNumber() + 1);result.setPageSize(page.getSize());result.setTotal(page.getTotalElements());result.setData(page.getContent());return result;}
}

JPA基础实体类

作为其他实体类的父类,封装了所有的公共字段,包括逻辑删除标志,版本号,创建人和修改人信息。到底是否需要那么多字段,结合实际情况,这里的示例代码比较全,其中@LogicDelete 和@Version 是 Mybatis 特有的注解,@CreatedBy、@CreatedDate 是Springframework 自带的注解,如果我们需要新建人和修改人姓名,则需要自定义注解。

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
@Getter
@EqualsAndHashCode(of = "id")
@SuperBuilder(toBuilder = true)
@NoArgsConstructor
@AllArgsConstructor
public class BaseDomain implements Serializable {@Id@GeneratedValue(strategy = GenerationType.IDENTITY)private Long id;@Schema(name = "创建人姓名")@CreatedBy@Column(name = "create_user_name")private String createUserName;@CreatedDateprivate LocalDateTime createdDate;@LastModifiedBy@Schema(name = "修改人姓名")@Column(name = "last_modified_name")private String lastModifiedName;@LastModifiedDateprivate LocalDateTime lastModifiedDate;@Schema(name = "")@Column(name = "del_flag")private Integer delFlag = 0;@Schema(name = "版本号")@Version@Column(name = "version")private Integer version;
}

可以发现,相较于 Mybatis 和 MybatisPlus 少了两个字段,分别是 createUserCode 和 lastModifiedCode,因为 Spring 提供的注解只有 CreatedBy 和 LastModifiedBy,我们可以基于 Spring Data 的 AuditorAware 审计功能来给这两个注解标识的字段赋值。

JPA审计

简单介绍一下审计功能:即由谁在什么时候创建或修改实体。Spring Data 提供了在实体类的属性上增加@CreatedBy,@LastModifiedBy,@CreatedDate,@LastModifiedDate 注解,并配置相应的配置项,即可实现审计功能,有系统自动记录createdBy 、CreatedDate 、lastModifiedBy 、lastModifiedDate四个属性的值。

所以如果在 AuditorAware 实现类中根据@CreatedBy 拿到的值去数据库中查询 userCode 信息,也不是不可以,这里项目比较简单,所以就不过多介绍了。

@Configuration
public class JpaAutoConfiguration implements AuditorAware<String> {@Overridepublic Optional<String> getCurrentAuditor() {SecurityContext ctx = SecurityContextHolder.getContext();Object principal = ctx.getAuthentication().getPrincipal();if (principal.getClass().isAssignableFrom(String.class)) {return Optional.of((String) principal);} else {return Optional.empty();}}
}

因为我们没有使用 SpringSecurity 来配置 token 信息,所以这里获取的 principal 是默认值,值为 anonymousUser。

动态链式查询

利用 JPA 的Specification<T>接口和元模型就实现动态查询,相较于这篇文章,本文实现借鉴了 Mybatis 中使用的 ExampleBuilder,个人觉得效果更佳。

1、查询关系匹配枚举

public enum MatchCondition {/*** equal-相等,notEqual-不等于,like-模糊匹配,notLike-, gt-大于,ge-大于等于,lt-小于,le-小于等于*/EQUAL,NOT_EQUAL,LIKE,NOT_LIKE,GT,GE,LT,LE,IN,NOT_IN,BETWEEN,NOT_BETWEEN
}

2、查询条件连接符枚举

public enum Operator {AND,OR
}

3、查询条件注解

@Target({ElementType.FIELD, ElementType.CONSTRUCTOR})
@Retention(RetentionPolicy.RUNTIME)
public @interface QueryCondition {/*** 数据库中字段名,默认为空字符串,则Query类中的字段要与数据库中字段一致*/String column() default "";/*** @see MatchCondition*/MatchCondition func() default MatchCondition.EQUAL;
}

4、连接符枚举

public enum Connector {ON,WHERE
}

5、查询条件包装类

//理论上会有多个QueryParam对象,当Connector是where时,joinName和joinType为null,当Connector为on时,根据joinName的不同,会生成不同的QueryParam对象,joinType包括INNER、LEFT
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class QueryParam {private Connector connector;private List<QueryItem> queryItems;private String joinName;private JoinType joinType;
}@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class QueryItem {private String fieldName;private Object fieldValue;private MatchCondition matchCondition;// between使用private Object startValue;private Object endValue;// in查询private Iterable<Object> iterable;private Operator operator;
}

6、集成工具类

JPA动态查询工具类

因代码篇幅受限,就只粘贴部分代码:

public SpecificationBuilder<T> andEqualTo(IFn<T, Object> fn, Object value) {QueryItem queryItem = QueryItem.builder().fieldName(Reflections.fnToFieldName(fn)).fieldValue(value).operator(Operator.AND).matchCondition(MatchCondition.EQUAL).build();addQueryItemToWhereParam(queryItem);return this;
}private void addQueryItemToWhereParam(QueryItem queryItem) {if (CollectionUtils.isEmpty(queryParams)) {queryParams.add(addQueryItem(queryItem));} else {Optional<QueryParam> queryParamOptional = queryParams.stream().filter(obj -> StringUtils.isEmpty(obj.getJoinName())).findFirst();if (queryParamOptional.isPresent()) {QueryParam queryParam = queryParamOptional.get();queryParam.getQueryItems().add(queryItem);} else {queryParams.add(addQueryItem(queryItem));}}
}

相较于 Mybatis 项目中的 ExampleBuilder,SpecificationBuilder 仅用于查询功能,暂时无法提供修改,删除等功能,不过也有自己的额特色:可以实现连表查询,比如说 left join 等,以及懒加载问题,避免连表查询时出现的 N+1 查询

至此,关于本项目中有价值的内容已经讲述完毕,因篇幅有限,未能展示所有代码。基于上述核心代码,我们只需要往项目中添加相关业务代码即可,接下来我们就可以运行之前写的脚本工具,根据数据库表信息快速生成模板代码。

一键式生成模版代码

运行 orm-generate 项目,在 swagger 上调用 /build 接口,调用参数如下:

{"database": "mysql_db","flat": true,"type": "jpa","group": "hresh","host": "127.0.0.1","module": "orm","password": "root","port": 3306,"table": ["customer","sub_user"],"username": "root","tableStartIndex":"0"
}

代码文件直接移到项目中就行了,稍微修改一下引用就好了。

功能实现

请求日志输出

比如说我们访问 customers/queryPage 接口,看看控制台输出情况:

Request Info      : {"classMethod":"com.msdn.orm.hresh.controller.CustomerController.queryPage","ip":"127.0.0.1","requestParams":{"dto":{"pageSortInfo":{"count":true,"pageSize":5,"orderInfos":[{"column":"name","asc":true}],"pageNum":1}}},"httpMethod":"GET","url":"http://localhost:8803/customers/queryPage","result":{"code":"200","message":"操作成功","success":true},"methodDesc":"获取父用户分页列表","timeCost":268}

可以看到,日志输出中包含前端传来的请求体,请求 API,返回结果,API 描述,API 耗时。

统一返回格式

比如说分页查询,返回结果如下:

{"data": {"total": 9,"pageCount": 2,"pageSize": 5,"pageNum": 1,"data": [{"name": "acorn","age": 38,"address": "湖北武汉"},{"name": "acorn2","age": 28,"address": "湖北武汉"},{"name": "hresh","age": 44,"address": "湖北武汉"},{"name": "love0","age": 26,"address": "湖北武汉"},{"name": "love1","age": 26,"address": "湖北武汉"}]},"code": "200","message": "操作成功","success": true
}

如果是新增请求,返回结果为:

{"data": {"name": "rookie3","age": 26,"address": "湖北武汉"},"code": "200","message": "操作成功","success": true
}

异常处理

下面简单演示一下参数异常的情况,在 add user 时校验参数值是否为空。

  public CustomerVO add(CustomerDTO dto) {if (StringUtils.isBlank(dto.getName())) {BusinessException.validateFailed("userName不能为空");}Customer customer = customerRepository.save(customerStruct.dtoToModel(dto));return customerStruct.modelToVO(customer);}

如果传递的 name 值为空,则返回结果为:

{"data": null,"code": "400","message": "userName不能为空","success": false
}

分组校验和自定义校验

修改 CustomerDTO,当新增数据时,校验 name 不为空,修改数据时,address 不为空。

public class CustomerDTO {@NotBlank(groups = {Add.class})@Schema(name = "")private String name;@EnumValidator(value = {"18", "20", "24"}, message = "age只能指定为18、20和24,其他值不合法")@Schema(name = "")private Integer age;@NotBlank(groups = {Update.class})@Schema(name = "")private String address;}

最后修改 controller 文件

  @PostMapping@Operation(description = "新增父用户")public Result<CustomerVO> add(@Validated(Add.class) @RequestBody CustomerDTO dto) {CustomerVO customerVO = customerService.add(dto);return Result.ok(customerVO);}

调用新增接口,故意将 name 置为空,返回结果为:

{"data": null,"code": "400","message": "name不能为空","success": false
}

如果 age 不为 18、20和24,则出现如下错误:

{"data": null,"code": "400","message": "ageage只能指定为18、20和24,其他值不合法","success": false
}

批量操作

这里简单演示一下关于批量新增的代码

  public void batchAdd(CustomerDTO dto) {List<Customer> customers = new ArrayList<>();for (int i = 0; i < 3; i++) {Customer customer = new Customer();customer.setName(dto.getName() + i);customer.setAge(dto.getAge());customer.setAddress(dto.getAddress());customers.add(customer);}customerRepository.saveAll(customers);}

注意,delFlag 没有对应的注解,所以只能手动赋值为 0,否则插入数据时会报错。

执行效果如下:

sql查询结果

分页查询

前端参数传递:

{"pageNum": 1,"pageSize": 5,"orderInfos":[{"column": "name","asc": true}]
}

后端代码处理:

  public Page<CustomerVO> queryPage(CustomerQueryPageDTO dto) {Pageable pageable = SpecificationBuilder.getPageable(dto.getPageSortInfo());Page<Customer> customerPage = customerRepository.findAll(pageable);return customerPage.map(customer -> customerStruct.modelToVO(customer));}

返回结果为:

{"data": {"total": 9,"pageCount": 2,"pageSize": 5,"pageNum": 1,"data": [{"name": "acorn","age": 38,"address": "湖北武汉"},{"name": "acorn2","age": 28,"address": "湖北武汉"},{"name": "hresh","age": 44,"address": "湖北武汉"},{"name": "love0","age": 26,"address": "湖北武汉"},{"name": "love1","age": 26,"address": "湖北武汉"}]},"code": "200","message": "操作成功","success": true
}

动态查询

查询方法如下:

  public List<CustomerVO> queryList(CustomerDTO dto) {List<Customer> customers = SpecificationBuilder.create(CustomerRepository.class).andLike(Customer::getName, dto.getName() + "%").select();return customerStruct.modelToVO(customers);}

执行结果如下:

{"data": [{"name": "rookie","age": 26,"address": "湖北武汉"},{"name": "rookie1","age": 26,"address": "湖北武汉"},{"name": "rookie2","age": 26,"address": "湖北武汉"},{"name": "rookie3","age": 26,"address": "湖北武汉"}],"code": "200","message": "操作成功","success": true
}

SQL 输出为:

select customer0_.id as id1_0_, customer0_.create_user_name as create_u2_0_, customer0_.created_date as created_3_0_, customer0_.del_flag as del_flag4_0_, customer0_.last_modified_date as last_mod5_0_, customer0_.last_modified_name as last_mod6_0_, customer0_.version as version7_0_, customer0_.address as address8_0_, customer0_.age as age9_0_, customer0_.name as name10_0_ from customer customer0_ where ( customer0_.del_flag=0) and (customer0_.name like ?)

如果是分页查询,可以这样处理:

  public Page<CustomerVO> queryPage(CustomerQueryPageDTO dto) {Pageable pageable = SpecificationBuilder.getPageable(dto.getPageSortInfo());Page<Customer> customerPage = SpecificationBuilder.create(CustomerRepository.class).andLike(Customer::getName, dto.getName() + "%").select(pageable);
//    Page<Customer> customerPage = customerRepository.findAll(pageable);return customerPage.map(customer -> customerStruct.modelToVO(customer));}

查询结果为:

{"data": {"total": 4,"pageCount": 1,"pageSize": 5,"pageNum": 1,"data": [{"name": "rookie","age": 26,"address": "湖北武汉"},{"name": "rookie1","age": 26,"address": "湖北武汉"},{"name": "rookie2","age": 26,"address": "湖北武汉"},{"name": "rookie3","age": 26,"address": "湖北武汉"}]},"code": "200","message": "操作成功","success": true
}

SQL 输出为:

select customer0_.id as id1_0_, customer0_.create_user_name as create_u2_0_, customer0_.created_date as created_3_0_, customer0_.del_flag as del_flag4_0_, customer0_.last_modified_date as last_mod5_0_, customer0_.last_modified_name as last_mod6_0_, customer0_.version as version7_0_, customer0_.address as address8_0_, customer0_.age as age9_0_, customer0_.name as name10_0_ from customer customer0_ where ( customer0_.del_flag=0) and (customer0_.name like ?) order by case when customer0_.name is null then 1 else 0 end, customer0_.name asc limit ?

一对多查询

使用 JPA 的好处就是可以使用 @OneToMany 等注解,以及懒加载查询优化注解 @EntityGraph。

1、修改实体类

public class Customer extends BaseDomain {private static final long serialVersionUID = 1L;@Schema(name = "")@Column(name = "name")private String name;@Schema(name = "")@Column(name = "age")private Integer age;@Schema(name = "")@Column(name = "address")private String address;@OneToMany(cascade = CascadeType.ALL)@JoinColumn(name = "customer_id")private List<SubUser> subUsers;
}public class SubUser extends BaseDomain {private static final long serialVersionUID = 1L;@Schema(name = "")@Column(name = "name")private String name;//  @Schema(name = "")
//  @Column(name = "customer_id")
//  private String customerId;@Schema(name = "")@Column(name = "address")private String address;@ManyToOne@JoinColumnprivate Customer customer;
}

2、更改 CustomerRepository,这点格外重要,只有如此,才能使得 SpecificationBuilder 更有意义,否则即使可以连表查询,也会出现 N+1 问题。

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long>,JpaSpecificationExecutor<Customer> {@Override@EntityGraph(attributePaths = {"subUsers"})List<Customer> findAll(Specification<Customer> spec);@Override@EntityGraph(attributePaths = {"subUsers"})Page<Customer> findAll(Specification<Customer> spec, Pageable pageable);@Override@EntityGraph(attributePaths = {"subUsers"})List<Customer> findAll(Specification<Customer> spec, Sort sort);
}

3、Service 层修改查询方法

  public List<CustomerVO> queryList(CustomerDTO dto) {List<Customer> customers = SpecificationBuilder.create(CustomerRepository.class).andLike(Customer::getName, dto.getName() + "%").select();return customerStruct.modelToVO(customers);}

4、查询结果为

{"data": [{"name": "rookie","age": 26,"address": "湖北武汉","subUserVOS": [{"name": "a1","address": "青藏高原"},{"name": "a2","address": "青藏高原"}]},{"name": "rookie1","age": 26,"address": "湖北武汉","subUserVOS": [{"name": "c1","address": "黄土高原"},{"name": "c2","address": "黄土高原"}]},{"name": "rookie2","age": 26,"address": "湖北武汉","subUserVOS": []},{"name": "rookie3","age": 26,"address": "湖北武汉","subUserVOS": []}],"code": "200","message": "操作成功","success": true
}

5、SQL 输出

一对多查询日志

如果没有 CustomerRepository 的重写方法,则会出现 N+1 问题。

一对多查询日志

连表查询

除了上述查询方法的使用,我们还可以手动来增加 left join 的查询条件,比如说我们连表查询时,还要在 on 查询上增加额外的条件,也可以通过 SpecificationBuilder 来实现。

假设 Customer 和 SubUser 没有使用@JoinColumn 进行关联,而你此时想进行关联查询,可以这样做。

  public List<CustomerVO> queryList(CustomerDTO dto) {List<Customer> customers = SpecificationBuilder.create(CustomerRepository.class).andLike(Customer::getName, dto.getName() + "%").leftJoin(Customer::getSubUsers)// 表示 left join sub_user.select();return customerStruct.modelToVO(customers);}

对应的 SQL 如下:

selectcustomer0_.id as id1_0_0_,subusers1_.id as id1_1_1_,customer0_.create_user_name as create_u2_0_0_,customer0_.created_date as created_3_0_0_,customer0_.del_flag as del_flag4_0_0_,customer0_.last_modified_date as last_mod5_0_0_,customer0_.last_modified_name as last_mod6_0_0_,customer0_.version as version7_0_0_,customer0_.address as address8_0_0_,customer0_.age as age9_0_0_,customer0_.name as name10_0_0_,subusers1_.create_user_name as create_u2_1_1_,subusers1_.created_date as created_3_1_1_,subusers1_.del_flag as del_flag4_1_1_,subusers1_.last_modified_date as last_mod5_1_1_,subusers1_.last_modified_name as last_mod6_1_1_,subusers1_.version as version7_1_1_,subusers1_.address as address8_1_1_,subusers1_.customer_id as custome10_1_1_,subusers1_.name as name9_1_1_,subusers1_.customer_id as custome10_1_0__,subusers1_.id as id1_1_0__
fromcustomer customer0_
left outer join sub_user subusers1_ oncustomer0_.id = subusers1_.customer_id
where( customer0_.del_flag = 0)and (customer0_.name like ?)

如果你想额外增加 on 查询条件,可以这样实现。

  public List<CustomerVO> queryList(CustomerDTO dto) {List<Customer> customers = SpecificationBuilder.create(CustomerRepository.class).andLike(Customer::getName, dto.getName() + "%")
//        .leftJoin(Customer::getSubUsers).leftJoinAndOnEqualTo(Customer::getSubUsers,"name","a1").select();return customerStruct.modelToVO(customers);}

对应的 SQL 为

selectcustomer0_.id as id1_0_0_,subusers1_.id as id1_1_1_,customer0_.create_user_name as create_u2_0_0_,customer0_.created_date as created_3_0_0_,customer0_.del_flag as del_flag4_0_0_,customer0_.last_modified_date as last_mod5_0_0_,customer0_.last_modified_name as last_mod6_0_0_,customer0_.version as version7_0_0_,customer0_.address as address8_0_0_,customer0_.age as age9_0_0_,customer0_.name as name10_0_0_,subusers1_.create_user_name as create_u2_1_1_,subusers1_.created_date as created_3_1_1_,subusers1_.del_flag as del_flag4_1_1_,subusers1_.last_modified_date as last_mod5_1_1_,subusers1_.last_modified_name as last_mod6_1_1_,subusers1_.version as version7_1_1_,subusers1_.address as address8_1_1_,subusers1_.customer_id as custome10_1_1_,subusers1_.name as name9_1_1_,subusers1_.customer_id as custome10_1_0__,subusers1_.id as id1_1_0__
fromcustomer customer0_
left outer join sub_user subusers1_ oncustomer0_.id = subusers1_.customer_idand (subusers1_.name =?)
where( customer0_.del_flag = 0)and (customer0_.name like ?)

关于 on 条件查询的实现,可以查看 SpecificationBuilder 中这段源码:

List<QueryParam> onQueryParams = queryParams.stream().filter(obj -> StringUtils.isNotEmpty(obj.getJoinName())).collect(Collectors.toList());
for (QueryParam onQueryParam : onQueryParams) {List<QueryItem> queryItems = onQueryParam.getQueryItems();if (CollectionUtils.isEmpty(queryItems)) {root.join(onQueryParam.getJoinName(), onQueryParam.getJoinType());} else {Join<Object, Object> join = root.join(onQueryParam.getJoinName(), onQueryParam.getJoinType());for (QueryItem queryItem : queryItems) {Object value = queryItem.getFieldValue();switch (queryItem.getMatchCondition()) {case EQUAL:if (value instanceof String) {// 关联表where查询//                  andPredicates.add(criteriaBuilder//                      .equal(join.get(queryItem.getFieldName()).as(String.class), value));// 关联表on查询join.on(criteriaBuilder.equal(join.get(queryItem.getFieldName()).as(String.class), value));} else if (value instanceof Integer) {//                  andPredicates.add(criteriaBuilder//                      .equal(join.get(queryItem.getFieldName()).as(Integer.class), value));join.on(criteriaBuilder.equal(join.get(queryItem.getFieldName()).as(Integer.class), value));} else if (value instanceof Long) {//                  andPredicates.add(criteriaBuilder//                      .equal(join.get(queryItem.getFieldName()).as(Long.class), value));// on查询join.on(criteriaBuilder.equal(join.get(queryItem.getFieldName()).as(Long.class), value));}break;case LIKE:if (value instanceof String) {join.on(criteriaBuilder.like(join.get(queryItem.getFieldName()).as(String.class),"%" + value + "%"));}break;default:}}}
}

如果你感兴趣,可以取消注释代码,试一下将 subUser.name 查询放到 where 条件中。

if (value instanceof String) {// 关联表where查询andPredicates.add(criteriaBuilder.equal(join.get(queryItem.getFieldName()).as(String.class), value));// 关联表on查询//                  join.on(criteriaBuilder//                      .equal(join.get(queryItem.getFieldName()).as(String.class), value));
}

对应的 SQL 为:

selectcustomer0_.id as id1_0_0_,subusers1_.id as id1_1_1_,customer0_.create_user_name as create_u2_0_0_,customer0_.created_date as created_3_0_0_,customer0_.del_flag as del_flag4_0_0_,customer0_.last_modified_date as last_mod5_0_0_,customer0_.last_modified_name as last_mod6_0_0_,customer0_.version as version7_0_0_,customer0_.address as address8_0_0_,customer0_.age as age9_0_0_,customer0_.name as name10_0_0_,subusers1_.create_user_name as create_u2_1_1_,subusers1_.created_date as created_3_1_1_,subusers1_.del_flag as del_flag4_1_1_,subusers1_.last_modified_date as last_mod5_1_1_,subusers1_.last_modified_name as last_mod6_1_1_,subusers1_.version as version7_1_1_,subusers1_.address as address8_1_1_,subusers1_.customer_id as custome10_1_1_,subusers1_.name as name9_1_1_,subusers1_.customer_id as custome10_1_0__,subusers1_.id as id1_1_0__
fromcustomer customer0_
left outer join sub_user subusers1_ oncustomer0_.id = subusers1_.customer_id
where( customer0_.del_flag = 0)and (customer0_.name like ?)and subusers1_.name =?

为什么要这样实现呢?首先我们要了解 on 和 where 的区别:

连接查询中,on是用来确定两张表的关联关系,关联好之后生成一个临时表,之后where对这个临时表再进行过滤筛选。
先执行on,后执行 where;on是建立关联关系在生成临时表时候执行,where是在临时表生成后对数据进行筛选的。

所以优先执行on条件查询,效率更高。

不仅如此,上述 SQL 执行结果为:

{"data": [{"name": "rookie","age": 26,"address": "湖北武汉","subUserVOS": [{"name": "a1","address": "青藏高原"}]}],"code": "200","message": "操作成功","success": true
}

这明显不是我们想要的结果。

修改数据

在本项目中,表结构中都包含了 version 这个字段,即每次更新操作,version 都应该加1。

CustomerRepository文件

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long>,JpaSpecificationExecutor<Customer> {@Modifying@Query(value = "update customer set age = :age where name = :name and del_flag=0", nativeQuery = true)void updateByName(@Param("name") String name, @Param("age") int age);Customer findByName(String name);
}

Service 修改方法

@Transactional
@Override
public CustomerVO edit(CustomerDTO dto) {// 通过自定义修改方法的方式来达到修改数据,先修改再查询,version没有改变,修改时间也不变//    customerRepository.updateByName(dto.getName(), dto.getAge());//    return customerStruct.modelToVO(customerRepository.findByName(dto.getName()));// 先查询,再修改,这种方式才会触发乐观锁,即where条件中有version条件,更新操作verison+1,修改时间也会变化Customer customer = customerRepository.findByName(dto.getName());customer.setAge(dto.getAge());customerRepository.save(customer);return customerStruct.modelToVO(customer);
}

Swagger

启动项目后,访问 swagger,页面展示如下:

swagger

总结

上文中本人对于实现的 SpecificationBuilder 类还算满意,一开始只是为了实现分页查询,慢慢想实现 Mybatis Plus 的那种链式查询,以及最后想要实现连接查询。代码实现目前就这样吧,如果大家有什么好玩的想法,欢迎大家留言评论。

感兴趣的朋友可以去我的 Github 下载相关代码,如果对你有所帮助,不妨 Star 一下,谢谢大家支持!

参考文献

Spring Data JPA中多表联合查询最佳实践

spring data jpa 动态查询(工具类封装)

springboot jpa 分页查询工具类


本文链接:https://www.ngui.cc/article/show-701468.html
Copyright © 2010-2022 ngui.cc 版权所有 |关于我们| 联系方式| 豫B2-20100000