该说的在《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='子用户';
复制代码
使用 IDEA 新建一个 Maven 项目,叫做 jpa-springboot。
一些共用的基础代码可以参考上篇文章,这里不做重复介绍,会介绍一些 JPA 相关的代码。
org.springframework.boot
spring-boot-starter-parent
2.6.3
1.8
1.2.73
5.5.1
8.0.19
2.1.4
4.1.5
1.4.2.Final
1.18.20
org.springframework.boot
spring-boot-starter-web
org.springframework.boot
spring-boot-starter-aop
org.springframework.boot
spring-boot-starter-security
org.springframework.boot
spring-boot-starter-validation
com.alibaba
fastjson
${fastjson.version}
cn.hutool
hutool-all
${hutool.version}
org.projectlombok
lombok
${org.projectlombok.version}
true
org.springframework.boot
spring-boot-starter-test
test
mysql
mysql-connector-java
${mysql.version}
runtime
org.mybatis.spring.boot
mybatis-spring-boot-starter
${mybatis.version}
org.springframework.boot
spring-boot-starter-data-jpa
org.springdoc
springdoc-openapi-ui
1.6.9
com.alibaba
druid-spring-boot-starter
1.1.18
org.mapstruct
mapstruct
${org.mapstruct.version}
org.mapstruct
mapstruct-processor
${org.mapstruct.version}
org.springframework.boot
spring-boot-maven-plugin
复制代码
有些依赖不一定是最新版本,而且你看到这篇文章时,可能已经发布了新版本,到时候可以先模仿着将项目跑起来后,再根据自己的需求来升级各项依赖,有问题咱再解决问题。
某些业务场景是需要分页查询和排序功能的,所以我们需要考虑前端如何传递参数给后端,后端如何进行分页查询或者是排序查询。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 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 customerPage = customerRepository.findAll(pageable);
复制代码
关于 PageHelper 的使用这里就不多做介绍了。
我们得到的分页查询结果是 Page 对象,可以直接使用,也可以根据需要进行修改,比如下面这个文件:
@Getter
@Setter
public class PageResult {
/**
* 总条数
*/
private Long total;
/**
* 总页数
*/
private Integer pageCount;
/**
* 每页数量
*/
private Integer pageSize;
/**
* 当前页码
*/
private Integer pageNum;
/**
* 分页数据
*/
private List data;
/**
* 处理Jpa分页结果,Jpa页码默认从0开始,所以返回结果加1
*/
public static PageResult ok(org.springframework.data.domain.Page page) {
PageResult result = new PageResult();
result.setPageCount(page.getTotalPages());
result.setPageNum(page.getNumber() + 1);
result.setPageSize(page.getSize());
result.setTotal(page.getTotalElements());
result.setData(page.getContent());
return result;
}
}
复制代码
作为其他实体类的父类,封装了所有的公共字段,包括逻辑删除标志,版本号,创建人和修改人信息。到底是否需要那么多字段,结合实际情况,这里的示例代码比较全,其中@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 审计功能来给这两个注解标识的字段赋值。
简单介绍一下审计功能:即由谁在什么时候创建或修改实体。Spring Data 提供了在实体类的属性上增加@CreatedBy,@LastModifiedBy,@CreatedDate,@LastModifiedDate 注解,并配置相应的配置项,即可实现审计功能,有系统自动记录createdBy 、CreatedDate 、lastModifiedBy 、lastModifiedDate四个属性的值。
所以如果在 AuditorAware 实现类中根据@CreatedBy 拿到的值去数据库中查询 userCode 信息,也不是不可以,这里项目比较简单,所以就不过多介绍了。
@Configuration
public class JpaAutoConfiguration implements AuditorAware {
@Override
public Optional 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
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 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
6、集成工具类
因代码篇幅受限,就只粘贴部分代码:
public SpecificationBuilder andEqualTo(IFn 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 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 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 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,否则插入数据时会报错。
执行效果如下:
前端参数传递:
{
"pageNum": 1,
"pageSize": 5,
"orderInfos":[
{
"column": "name",
"asc": true
}
]
}
复制代码
后端代码处理:
public Page queryPage(CustomerQueryPageDTO dto) {
Pageable pageable = SpecificationBuilder.getPageable(dto.getPageSortInfo());
Page 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 queryList(CustomerDTO dto) {
List 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 queryPage(CustomerQueryPageDTO dto) {
Pageable pageable = SpecificationBuilder.getPageable(dto.getPageSortInfo());
Page customerPage = SpecificationBuilder.create(CustomerRepository.class)
.andLike(Customer::getName, dto.getName() + "%")
.select(pageable);
// Page 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 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,
JpaSpecificationExecutor {
@Override
@EntityGraph(
attributePaths = {"subUsers"}
)
List findAll(Specification spec);
@Override
@EntityGraph(
attributePaths = {"subUsers"}
)
Page findAll(Specification spec, Pageable pageable);
@Override
@EntityGraph(
attributePaths = {"subUsers"}
)
List findAll(Specification spec, Sort sort);
}
复制代码
3、Service 层修改查询方法
public List queryList(CustomerDTO dto) {
List 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 queryList(CustomerDTO dto) {
List 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 queryList(CustomerDTO dto) {
List 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 onQueryParams = queryParams.stream()
.filter(obj -> StringUtils.isNotEmpty(obj.getJoinName()))
.collect(Collectors.toList());
for (QueryParam onQueryParam : onQueryParams) {
List queryItems = onQueryParam.getQueryItems();
if (CollectionUtils.isEmpty(queryItems)) {
root.join(onQueryParam.getJoinName(), onQueryParam.getJoinType());
} else {
Join
如果你感兴趣,可以取消注释代码,试一下将 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 为:
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 =?
复制代码
为什么要这样实现呢?首先我们要了解 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,
JpaSpecificationExecutor {
@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,页面展示如下:
上文中本人对于实现的 SpecificationBuilder 类还算满意,一开始只是为了实现分页查询,慢慢想实现 Mybatis Plus 的那种链式查询,以及最后想要实现连接查询。代码实现目前就这样吧,如果大家有什么好玩的想法,欢迎大家留言评论。
感兴趣的朋友可以去我的 Github 下载相关代码,如果对你有所帮助,不妨 Star 一下,谢谢大家支持!
原文链接:https://juejin.cn/post/7169472183184719886
页面更新:2024-04-27
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2020-2024 All Rights Reserved. Powered By 71396.com 闽ICP备11008920号-4
闽公网安备35020302034903号