• SpringBoot集成Spring Data JPA项目实操

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

    工具

    ORM框架选型

    前言

    该说的在《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='子用户';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    搭建SpringBoot项目

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

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

    引入依赖

    <parent>
  <groupId>org.springframework.bootgroupId>
  <artifactId>spring-boot-starter-parentartifactId>
  <version>2.6.3version>
parent>

<properties>
  <java.version>1.8java.version>
  <fastjson.version>1.2.73fastjson.version>
  <hutool.version>5.5.1hutool.version>
  <mysql.version>8.0.19mysql.version>
  <mybatis.version>2.1.4mybatis.version>
  <mapper.version>4.1.5mapper.version>
  <org.mapstruct.version>1.4.2.Finalorg.mapstruct.version>
  <org.projectlombok.version>1.18.20org.projectlombok.version>
properties>

<dependencies>
  <dependency>
    <groupId>org.springframework.bootgroupId>
    <artifactId>spring-boot-starter-webartifactId>
  dependency>
  <dependency>
    <groupId>org.springframework.bootgroupId>
    <artifactId>spring-boot-starter-aopartifactId>
  dependency>
  <dependency>
    <groupId>org.springframework.bootgroupId>
    <artifactId>spring-boot-starter-securityartifactId>
  dependency>
  <dependency>
    <groupId>org.springframework.bootgroupId>
    <artifactId>spring-boot-starter-validationartifactId>
  dependency>
  <dependency>
    <groupId>com.alibabagroupId>
    <artifactId>fastjsonartifactId>
    <version>${fastjson.version}version>
  dependency>
  <dependency>
    <groupId>cn.hutoolgroupId>
    <artifactId>hutool-allartifactId>
    <version>${hutool.version}version>
  dependency>
  <dependency>
    <groupId>org.projectlombokgroupId>
    <artifactId>lombokartifactId>
    <version>${org.projectlombok.version}version>
    <optional>trueoptional>
  dependency>
  <dependency>
    <groupId>org.springframework.bootgroupId>
    <artifactId>spring-boot-starter-testartifactId>
    <scope>testscope>
  dependency>
  <dependency>
    <groupId>mysqlgroupId>
    <artifactId>mysql-connector-javaartifactId>
    <version>${mysql.version}version>
    <scope>runtimescope>
  dependency>
  <dependency>
    <groupId>org.mybatis.spring.bootgroupId>
    <artifactId>mybatis-spring-boot-starterartifactId>
    <version>${mybatis.version}version>
  dependency>
  <dependency>
      <groupId>org.springframework.bootgroupId>
      <artifactId>spring-boot-starter-data-jpaartifactId>
    dependency>
  <dependency>
    <groupId>org.springdocgroupId>
    <artifactId>springdoc-openapi-uiartifactId>
    <version>1.6.9version>
  dependency>
  <dependency>
    <groupId>com.alibabagroupId>
    <artifactId>druid-spring-boot-starterartifactId>
    <version>1.1.18version>
  dependency>

  <dependency>
    <groupId>org.mapstructgroupId>
    <artifactId>mapstructartifactId>
    <version>${org.mapstruct.version}version>
  dependency>
  <dependency>
    <groupId>org.mapstructgroupId>
    <artifactId>mapstruct-processorartifactId>
    <version>${org.mapstruct.version}version>
  dependency>
dependencies>

<build>
  <plugins>
    <plugin>
      <groupId>org.springframework.bootgroupId>
      <artifactId>spring-boot-maven-pluginartifactId>
    plugin>
  plugins>
build>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101

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

    分页处理

    某些业务场景是需要分页查询和排序功能的，所以我们需要考虑前端如何传递参数给后端，后端如何进行分页查询或者是排序查询。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;
  }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    排序包装类

    @Getter
@Setter
public class OrderInfo {

  private boolean asc = true;

  private String column;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    分页且排序包装类

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

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

    @Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class CustomerQueryPageDTO {

    @JsonUnwrapped
    private PageSortInfo pageSortInfo;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    服务层分页查询

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

    • 1
    • 2

    关于 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;
  }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

    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;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38

    可以发现，相较于 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();
    }
  }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

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

    动态链式查询

    利用 JPA 的Specification接口和元模型就实现动态查询，相较于这篇文章，本文实现借鉴了 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
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    2、查询条件连接符枚举

    public enum Operator {
  AND,
  OR
}

    • 1
    • 2
    • 3
    • 4

    3、查询条件注解

    @Target({ElementType.FIELD, ElementType.CONSTRUCTOR})
@Retention(RetentionPolicy.RUNTIME)
public @interface QueryCondition {

  /**
   * 数据库中字段名,默认为空字符串,则Query类中的字段要与数据库中字段一致
   */
  String column() default "";

  /**
   * @see MatchCondition
   */
  MatchCondition func() default MatchCondition.EQUAL;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    4、连接符枚举

    public enum Connector {
  ON,
  WHERE
}

    • 1
    • 2
    • 3
    • 4

    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;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    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));
    }
  }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    相较于 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"
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

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

    功能实现

    请求日志输出

    比如说我们访问 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}

    • 1

    可以看到，日志输出中包含前端传来的请求体，请求 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
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38

    如果是新增请求，返回结果为：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    异常处理

    下面简单演示一下参数异常的情况，在 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);
  }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    如果传递的 name 值为空，则返回结果为：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    分组校验和自定义校验

    修改 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;

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    最后修改 controller 文件

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

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

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

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

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    批量操作

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

      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);
  }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

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

    执行效果如下：

    sql查询结果

    分页查询

    前端参数传递：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    后端代码处理：

      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));
  }

    • 1
    • 2
    • 3
    • 4
    • 5

    返回结果为：

    {
	"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
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38

    动态查询

    查询方法如下：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    执行结果如下：

    {
	"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
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    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 ?)

    • 1

    如果是分页查询，可以这样处理：

      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 customerPage = customerRepository.findAll(pageable);
    return customerPage.map(customer -> customerStruct.modelToVO(customer));
  }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    查询结果为：

    {
	"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
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33

    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 ?

    • 1

    一对多查询

    使用 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;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    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);
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    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);
  }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    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
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49

    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);
  }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    对应的 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 ?)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30

    如果你想额外增加 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);
  }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    对应的 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 ?)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31

    关于 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:
      }
    }
  }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46

    如果你感兴趣，可以取消注释代码，试一下将 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));
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    对应的 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 ?)
	and subusers1_.name =?

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31

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

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

    • 1
    • 2

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

    不仅如此，上述 SQL 执行结果为：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

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

    修改数据

    在本项目中，表结构中都包含了 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);
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    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);
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    Swagger

    启动项目后，访问 swagger，页面展示如下：

    swagger

    总结

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

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

    参考文献

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

    spring data jpa 动态查询（工具类封装）

    springboot jpa 分页查询工具类

  • 相关阅读:
    yolo-word复现
    Linux操作系统：多进程拷贝
    【数据结构】结构体与链表
    【TCP/IP】【调试】丢包、流不通、错包等问题查证手段系列之三——Netfilter hook函数
    vue+elementui网上线上教学平台python+java
    自学Python第二十二天- Django框架(三) Admin后台、生成前端代码、cookie/session/token、AJAX、文件上传、多APP开发
    Robust Real-time LiDAR-inertial Initialization（实时鲁棒的LiDAR惯性初始化）论文学习
    前端 CSS 经典：多行文本擦除效果
    Java当中的BIO模型
    Web服务（Web Service）
  • 原文地址：https://blog.csdn.net/Herishwater/article/details/128019579