本文深入探讨JPA与SpringDataJPA中使用Specification与Criteria进行复杂查询的方法，包括多表查询、动态查询及排序。通过具体示例，讲解如何利用元模型和Predicate构建类型安全的查询条件。
【一目了然】Spring Data JPA使用Specification动态构建多表查询、复杂查询及排序示例
JPA 使用 Specification 复杂查询和 Criteria 查询
Spring data jpa 的使用与详解（二）：复杂动态查询及分页，排序
springboot JPA Specification 一对多关联构造查询
springboot JPA 一对多关联查询 ，动态复杂查询 去除重复数据 in语句使用
Specification 是 JPA 协议中支持动态参数查询的方式。其他方式还在探索中
示例
多表联查 JOIN (一对一)
单向关联
实体
Order
@Entity
@Table(name = "tbl_sy_quality_return_order")
@Data
public class Order{
@Id
@Column(name = "id")
private String id;
@Column(name = "applicant_id")
private String applicantId;
@Column(name = "applicant_name")
private String applicantName;
@Column(name = "last_modified_date")
private Instant lastModifiedDate;
//外键product_id, 关联Product类的id, 没有该外键列会报错
@Column(name = "product_id")
private String productId;
@OneToOne
@JoinColumn(name = "product_id", insertable = false, updatable = false)
private SyQualityReturnProduct product;
}
- @OneToOne，告知Hibernate，通过外键 productId 关联 Product类
- @JoinColumn，属性name指定的是本类中定义的外键列( product_id )
官方说明
@see javax.persistence.JoinColumn
public @interface JoinColumn {
/**
* (Optional) The name of the foreign key column.
* The table in which it is found depends upon the
* context.
* <ul>
* <li>If the join is for a OneToOne or ManyToOne
* mapping using a foreign key mapping strategy,
* the foreign key column is in the table of the
* source entity or embeddable.
* <li> If the join is for a unidirectional OneToMany mapping
* using a foreign key mapping strategy, the foreign key is in the
* table of the target entity.
* <li> If the join is for a ManyToMany mapping or for a OneToOne
* or bidirectional ManyToOne/OneToMany mapping using a join
* table, the foreign key is in a join table.
* <li> If the join is for an element collection, the foreign
* key is in a collection table.
*</ul>
* 如果加入是为了OneToOne或ManyToOne ,使用外键映射策略进行映射，外键列在表中,源实体或可嵌入
* 如果连接是单向的OneToMany映射,使用外键映射策略，外键位于,目标实体的表
*
* <p> Default (only applies if a single join column is used):
* The concatenation of the following: the name of the
* referencing relationship property or field of the referencing
* entity or embeddable class; "_"; the name of the referenced
* primary key column.
* If there is no such referencing relationship property or
* field in the entity, or if the join is for an element collection,
* the join column name is formed as the
* concatenation of the following: the name of the entity; "_";
* the name of the referenced primary key column.
*/
String name() default "";
}
Product
@Data
@Entity
@Table(name = "tbl_sy_quality_return_product")
public class Product{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
private String id;
@Column(name = "order_id")
private String orderId;
@Column(name = "user_name")
private String userName;
@Column(name = "user_phone")
private String userPhone;
}
查询代码
public static Specification<SyQualityReturnOrder> buildFromParamByOneToOne() {
return (root, query, builder) -> {
List<Predicate> list = new ArrayList<>();
Join<SyQualityReturnOrder, SyQualityReturnProduct> join = root.join("product", JoinType.INNER);
Predicate[] predicates = new Predicate[list.size()];
predicates = list.toArray(predicates);
return builder.and(predicates);
};
}
执行sql
select
syqualityr0_.id as id1_0_,
syqualityr0_.last_modified_date as last_mod2_0_,
syqualityr0_.product_id as product_3_0_
from
tbl_sy_quality_return_order syqualityr0_
inner join tbl_sy_quality_return_product syqualityr1_ on
syqualityr0_.product_id = syqualityr1_.id
where
1 = 1
order by
syqualityr0_.last_modified_date desc
limit ?
多表联查 JOIN (一对多)
有个订单Order，一个订单对应多个Product（1Order：N Product）。而且只是从Order内连接Product的单向关系
单向关联
实体
Order
@Entity
@Table(name = "tbl_order")
@Data
public class Order{
@Id
@Column(name = "id")
private String id;
@Column(name = "applicant_id")
private String applicantId;
@Column(name = "applicant_name")
private String applicantName;
@Column(name = "last_modified_date")
private Instant lastModifiedDate;
@OneToMany(fetch = FetchType.EAGER)
@JoinColumn(name = "order_id", insertable = false, updatable = false)
private Set<SyQualityReturnProduct> sub;
}
需要在Order下声明子类Product的列表，告诉Hibernate有多个Product。
- @OneToMany(fetch = FetchType.EAGER)，FetchType.EAGER 不使用懒加载
- @JoinColumn(name = “order_id”, insertable = false, updatable = false)，name说明关联字表的哪个字段，不支持写入，更新
Product
@Data
@Entity
@Table(name = "tbl_product")
public class Product{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
private String id;
@Column(name = "order_id")
private String orderId;
@Column(name = "user_name")
private String userName;
@Column(name = "user_phone")
private String userPhone;
}
查询语句
package com.example.demo;
import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.List;
public class OrderSpecification {
public OrderSpecification() {
}
public static Specification<Order> buildFromParam() {
return (root, query, builder) -> {
List<Predicate> list = new ArrayList<>();
//join参数前面是主表的类，后面是子表的类
//sub是主表数据库实体对象定义的一个属性
Join<Order, Product> join = root.join("sub", JoinType.INNER);
Predicate predicate = builder.equal(join.get("orderId"), "1");
list.add(predicate);
Predicate predicate1 = builder.equal(root.get("applicantId"), "2");
list.add(predicate1);
//一对多会出现重复的，通过groupby去重
query.groupBy(root.get("id"));
Predicate[] predicates = new Predicate[list.size()];
predicates = list.toArray(predicates);
return builder.and(predicates);
};
}
}
先关联查询，然后查询出多条重复的主表信息，然后再对每条主表信息进行一次子表查询，所以会出现重复数据。
通过使用groupby去重
对应sql
select
syqualityr0_.id as id1_0_,
syqualityr0_.applicant_id as applican2_0_,
syqualityr0_.applicant_name as applican3_0_,
syqualityr0_.last_modified_date as last_mod4_0_
from
tbl_order syqualityr0_
inner join tbl_product sub1_ on
syqualityr0_.id = sub1_.order_id
where
sub1_.order_id =?
and syqualityr0_.applicant_id =?
group by
syqualityr0_.id
order by
syqualityr0_.last_modified_date desc
limit ?
EXIST, OR, LIKE
public static Specification<Order> buildFromParam1() {
return (root, query, builder) -> {
List<Predicate> list = new ArrayList<>();
Subquery<Product> subquery = query.subquery(Product.class);
Root<Product> productRoot = subquery.from(Product.class);
subquery.select(productRoot);
List<Predicate> subList = new ArrayList<>();
Predicate p1 = builder.equal(productRoot.get("orderId"), "1");
Predicate p2 = builder.equal(productRoot.get("userName"), root.get("applicantName"));
Predicate p3 = builder.like(productRoot.get("userName"), "%" + "eason" + "%");
Predicate p4 = builder.equal(productRoot.get("userName"), "po");
Predicate or = builder.or(p3, p4);
subquery.where(builder.and(p2, p1, or));
list.add(builder.exists(subquery));
Predicate[] predicates = new Predicate[list.size()];
predicates = list.toArray(predicates);
return builder.and(predicates);
};
}
对应sql
select
syqualityr0_.id as id1_0_,
syqualityr0_.applicant_id as applican2_0_,
syqualityr0_.applicant_name as applican3_0_,
syqualityr0_.last_modified_date as last_mod4_0_
from
tbl_order syqualityr0_
where
exists (
select
syqualityr1_.id
from
tbl_product syqualityr1_
where
syqualityr1_.user_name = syqualityr0_.applicant_name
and syqualityr1_.order_id =?
and (syqualityr1_.user_name like ?
or syqualityr1_.user_name =?))
order by
syqualityr0_.last_modified_date desc
limit ?
IN
public static Specification<Order> buildFromParam2() {
return (root, query, builder) -> {
List<Predicate> list = new ArrayList<>();
List<String> statusList = new ArrayList<>();
statusList.add("1");
statusList.add("2");
list.add(builder.in(root.get("applicantName")).value(statusList));
Predicate[] predicates = new Predicate[list.size()];
predicates = list.toArray(predicates);
return builder.and(predicates);
};
}
对应sql
select
syqualityr0_.id as id1_0_,
syqualityr0_.applicant_id as applican2_0_,
syqualityr0_.applicant_name as applican3_0_,
syqualityr0_.last_modified_date as last_mod4_0_
from
tbl_order syqualityr0_
where
syqualityr0_.applicant_name in (? , ?)
order by
syqualityr0_.last_modified_date desc
limit ?
总结
lambda表达式中(root, query, builder)
- root, 代表的是数据库结构对象，可以获取到数据库实体对象相关属性等
- query 是获取和管理sql上查询的关键字接口，例如 query.select(), query.from(), query.where, query.grouyBy() 等, 对应 select，from，where ，group by
- builder 是管理每个查询关键字下的表达式，例如 builder.equal(), builder.and(), builder.or() 等，使用的是 Predicate 断言类型
环境配置
<!-- jpa -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
代码实现
public class xxxSpecification {
private xxxSpecification() {
}
public static Specification<xxxEntity> buildFromParam(Set<String> xxxList){
return (root,query,builder) -> {
//Predicate: 过滤条件,相当于构造 where 中的条件
ArrayList<Predicate> list = new ArrayList<>();
if(!CollectionUtils.isEmpty(xxxList)){
//root：从元模型中获取相应的字段
CriteriaBuilder.In<String> in = builder.in(root.get("id"));
for(String itemId:xxxList){
in.value(itemId);
}
list.add(in);
}
list.add(builder.equal(root.get("status"), TableStatusEnum.NORMAL_STATUS.getCode()));
Predicate[] predicates = new Predicate[list.size()];
predicates = list.toArray(predicates);
return builder.and(predicates);
};
}
}
以上的代码对应于
原理
JPA 提供动态接口(JpaSpecificationExecutor)，利用类型检查方式，进行复杂的条件查询，这个比自己写 SQL 更加安全
package org.springframework.data.jpa.repository;
import java.util.List;
import java.util.Optional;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.lang.Nullable;
public interface JpaSpecificationExecutor<T> {
Optional<T> findOne(@Nullable Specification<T> var1);
List<T> findAll(@Nullable Specification<T> var1);
Page<T> findAll(@Nullable Specification<T> var1, Pageable var2);
List<T> findAll(@Nullable Specification<T> var1, Sort var2);
long count(@Nullable Specification<T> var1);
}
Specification 是我们传入进去的查询参数，实际上它是一个接口，并且只有一个方法：
public interface Specification<T> {
Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb);
}
元模型
在JPA中,标准查询是以元模型的概念为基础的，元模型是为具体持久化单元的受管实体定义的.这些实体可以是实体类,嵌入类或者映射的父类.提供受管实体元信息的类就是元模型类.
简单的说就是元模型是实体类对应的一个“受管实体
例子：
实体类 Employee(com.demo.entities包中定义)
@Entity
@Table
public class Employee{
private int id;
private String name;
private int age;
@OneToMany
private List<Address> addresses;
// Other code…
}
Employee类的标准元模型类的名字是 Employee_
import javax.annotation.Generated;
import javax.persistence.metamodel.SingularAttribute;
import javax.persistence.metamodel.ListAttribute;
import javax.persistence.metamodel.StaticMetamodel;
@StaticMetamodel(Employee.class)
public class Employee_ {
public static volatile SingularAttribute<Employee, Integer> id;
public static volatile SingularAttribute<Employee, Integer> age;
public static volatile SingularAttribute<Employee, String> name;
public static volatile ListAttribute<Employee, Address> addresses;
}
Employee的每一个属性都会使用在JPA2规范中描述的以下规则在相应的元模型类中映射：
- 元模型类的属性全部是static和public的。
- 元模型类的属性全部是static和public的。Employee的每一个属性都会使用在JPA2规范中描述的以下规则在相应的元模型类中映射：
- 对于Addess这样的集合类型，会定义静态属性ListAttribute< A, B> b，这里List对象b是定义在类A中类型B的对象。其它集合类型可以是SetAttribute, MapAttribute 或 CollectionAttribute 类型。
为什么要使用元模型，答：查询类型更加安全
Criteria 查询
为了更好的理解criteria 查询，考虑拥有Employee实例集合的Dept实体，Employee和Dept的元模型类的代码如下：
//All Necessary Imports
@StaticMetamodel(Dept.class)
public class Dept_ {
public static volatile SingularAttribute<Dept, Integer> id;
public static volatile ListAttribute<Dept, Employee> employeeCollection;
public static volatile SingularAttribute<Dept, String> name;
}
//All Necessary Imports
@StaticMetamodel(Employee.class)
public class Employee_ {
public static volatile SingularAttribute<Employee, Integer> id;
public static volatile SingularAttribute<Employee, Integer> age;
public static volatile SingularAttribute<Employee, String> name;
public static volatile SingularAttribute<Employee, Dept> deptId;
}
下面的代码片段展示了一个criteria 查询，它用于获取所有年龄大于24岁的员工：
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Employee> criteriaQuery = criteriaBuilder.createQuery(Employee.class);
Root<Employee> employee = criteriaQuery.from(Employee.class);
Predicate condition = criteriaBuilder.gt(employee.get(Employee_.age), 24);
criteriaQuery.where(condition);
TypedQuery<Employee> typedQuery = em.createQuery(criteriaQuery);
List<Employee> result = typedQuery.getResultList();
对应的SQL: SELECT * FROM employee WHERE age > 24
CriteriaBuilder 安全查询创建工厂
CriteriaBuilder 安全查询创建工厂，创建 CriteriaQuery，创建查询具体条件 Predicate 等。
CriteriaBuilder是一个工厂对象,安全查询的开始.用于构建JPA安全查询.可以从EntityManager 或 EntityManagerFactory类中获得CriteriaBuilder。
比如:
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery 安全查询主语句
- 它通过调用 CriteriaBuilder, createQuery 或CriteriaBuilder.createTupleQuery 获得。
- CriteriaBuilder就像CriteriaQuery 的工厂一样。
- CriteriaQuery对象必须在实体类型或嵌入式类型上的Criteria 查询上起作用。
- Employee实体的 CriteriaQuery 对象以下面的方式创建：
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Employee> criteriaQuery = criteriaBuilder.createQuery(Employee.class);
Root 定义查询的 From 子句中能出现的类型
查询表达式被赋予泛型。一些典型的表达式是：
-
Root<T>, 相当于一个 From 子句,定义查询的 From 子句中能出现的类型
-
Criteria查询的查询根定义了实体类型，能为将来导航获得想要的结果，它与SQL查询中的FROM子句类似。
-
Root实例也是类型化的，且定义了查询的FROM子句中能够出现的类型。
-
查询根实例能通过传入一个实体类型给 AbstractQuery.from方法获得
-
Criteria查询，可以有多个查询根。
-
Employee 实体的查询根对象可以用以下语法获得：
Root<Employee> employee = criteriaQuery.from(Employee.class);
源码
package javax.persistence.criteria;
import javax.persistence.metamodel.EntityType;
public interface Root<X> extends From<X, X> {
EntityType<X> getModel();
}
可以看出 Root 继承自 From
public interface From<Z, X> extends Path<X>, FetchParent<Z, X>
而 From 又继承自 Path
package javax.persistence.criteria;
import java.util.Collection;
import java.util.Map;
import javax.persistence.metamodel.Bindable;
import javax.persistence.metamodel.MapAttribute;
import javax.persistence.metamodel.PluralAttribute;
import javax.persistence.metamodel.SingularAttribute;
public interface Path<X> extends Expression<X> {
Bindable<X> getModel();
Path<?> getParentPath();
<Y> Path<Y> get(SingularAttribute<? super X, Y> var1);
<E, C extends Collection<E>> Expression<C> get(PluralAttribute<X, C, E> var1);
<K, V, M extends Map<K, V>> Expression<M> get(MapAttribute<X, K, V> var1);
Expression<Class<? extends X>> type();
<Y> Path<Y> get(String var1);
}
Predicate 过滤条件
- 过滤条件应用到SQL语句的FROM子句中。
- 在criteria 查询中，查询条件通过Predicate 或Expression 实例应用到CriteriaQuery 对象上。
- 这些条件使用 CriteriaQuery .where 方法应用到CriteriaQuery 对象上。
- Predicate 实例也可以用Expression 实例的 isNull， isNotNull 和 in方法获得，复合的Predicate 语句可以使用CriteriaBuilder的and, or andnot 方法构建。
- CriteriaBuilder 也是作为Predicate 实例的工厂，Predicate 对象通过调用CriteriaBuilder 的条件方法（ equal，notEqual， gt， ge，lt， le，between，like等）创建。
- 这些条件使用 CriteriaQuery .where 方法应用到CriteriaQuery 对象上。
下面的代码片段展示了Predicate 实例检查年龄大于24岁的员工实例:
Predicate condition = criteriaBuilder.gt(employee.get(Employee_.age), 24);
criteriaQuery.where(condition);
初步结论
CriteriaBuilder: 构造 sql 语句
predicate：构造 where 中的条件语句，过滤条件
root：获取对应元模型的字段属性
扫一扫
392
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
