SpringBoot集成Spring Data JPA项目实操

╰+攻爆jí腚メ 2023-10-03 21:46 3阅读 0赞

![format_png][]

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

> **工具**
> 
>  *  [比MyBatis Generator更强大的代码生成器][MyBatis Generator]
> 
> **ORM框架选型**
> 
>  *  [SpringBoot项目基础设施搭建][SpringBoot]
> 
>  *  [SpringBoot集成Mybatis项目实操][SpringBoot_Mybatis]
> 
>  *  [SpringBoot集成MybatisPlus项目实操][SpringBoot_MybatisPlus]
>  *  [SpringBoot集成Spring Data JPA项目实操][SpringBoot_Spring Data JPA]

### 前言 ###

该说的在《[SpringBoot集成Mybatis项目实操][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 {
    
        @JsonUnwrapped
        private 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;
    
      @CreatedDate
      private LocalDateTime createdDate;
    
      @LastModifiedBy
      @Schema(name = "修改人姓名")
      @Column(name = "last_modified_name")
      private String lastModifiedName;
    
      @LastModifiedDate
      private 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> {
    
      @Override
      public 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>`接口和元模型就实现动态查询，相较于这篇[文章][Link 1]，本文实现借鉴了 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、集成工具类

![format_png 1][]

因代码篇幅受限，就只粘贴部分代码：

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，否则插入数据时会报错。

执行效果如下：

![format_png 2][]

#### 分页查询 ####

前端参数传递：

{
    	"pageNum": 1,
    	"pageSize": 5,
    	"orderInfos":[
    		{
    			"column": "name",
    			"asc": 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 输出为：

#### 一对多查询 ####

使用 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
      @JoinColumn
      private 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 层修改查询方法

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 输出

![format_png 3][]

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

![format_png 4][]

#### 连表查询 ####

除了上述查询方法的使用，我们还可以手动来增加 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 如下：

select
    	customer0_.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__
    from
    	customer customer0_
    left outer join sub_user subusers1_ on
    	customer0_.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 为

select
    	customer0_.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__
    from
    	customer customer0_
    left outer join sub_user subusers1_ on
    	customer0_.id = subusers1_.customer_id
    	and (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 为：

为什么要这样实现呢？首先我们要了解 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][]，页面展示如下：

![format_png 5][]

### 总结 ###

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

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

### 参考文献 ###

[format_png]: https://img-blog.csdnimg.cn/img_convert/9ee830e170bb347fad0002e79345a4e6.webp?x-oss-process=image/format,png
[MyBatis Generator]: https://juejin.cn/post/6952520736720289805
[SpringBoot]: https://juejin.cn/post/7166760176899325988
[SpringBoot_Mybatis]: https://juejin.cn/post/7168245119022268430
[SpringBoot_MybatisPlus]: https://juejin.cn/post/7168731998549671967
[SpringBoot_Spring Data JPA]: https://juejin.cn/post/7169472183184719886
[Link 1]: https://link.juejin.cn/?target=https%3A%2F%2Fwww.jianshu.com%2Fp%2F700c07f86667
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/d99367741171f8322330e80eb1fc0fbc.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/0d27d6f7e95765eae8584265c913b594.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/729a9f6c8f50678e9266bc71503cfcc0.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/f39ca5abef1ddbbb02424f7cea0edc1d.webp?x-oss-process=image/format,png
[swagger]: https://link.juejin.cn/?target=http%3A%2F%2Flocalhost%3A8803%2Fswagger-ui.html
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/122dc54667088418e7ed901053e8312f.webp?x-oss-process=image/format,png
[Github]: https://link.juejin.cn/?target=https%3A%2F%2Fgithub.com%2FAcorn2%2Form-parent%2Ftree%2Fmain%2Fjpa-springboot