JPA-Specification常用条件查询构造方式
1.toPredicate方法
Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder);
1.1 Root< User >root
<Y> Path<Y> get(String attributeName);
- 这个root就相当于查询和操作的实体对象的根,我们就可以通过Path get(xx)的方法,来获取我们想要操作的字段。
predicateList.add(cb.like(root.get("name"),userQuery.getName()));
1.2 CriteriaQuery<?> query
public interface CriteriaQuery<T> extends AbstractQuery<T> {
CriteriaQuery<T> select(Selection<? extends T> var1);
CriteriaQuery<T> multiselect(Selection<?>... var1);
CriteriaQuery<T> multiselect(List<Selection<?>> var1);
CriteriaQuery<T> where(Expression<Boolean> var1);
CriteriaQuery<T> where(Predicate... var1);
CriteriaQuery<T> groupBy(Expression<?>... var1);
CriteriaQuery<T> groupBy(List<Expression<?>> var1);
CriteriaQuery<T> having(Expression<Boolean> var1);
CriteriaQuery<T> having(Predicate... var1);
CriteriaQuery<T> orderBy(Order... var1);
CriteriaQuery<T> orderBy(List<Order> var1);
CriteriaQuery<T> distinct(boolean var1);
List<Order> getOrderList();
Set<ParameterExpression<?>> getParameters();
}
- 这是一个Specific的顶层查询对象,它包含着查询的各个部分,比如select、from、where、group by 、Order by、distinct等。提供查询Root的方法
return query.where(predicateList.toArray(new Predicate[predicateList.size()]))
.groupBy(root.get("age")).getRestriction();
1.3 CriteriaBuilder cb
- CriteriaBuilder是用来构建CritiaQuery的构建对象,其实就相当于条件或者条件组合,并以Predicate的形式返回,基本提供了所有常用的方法。
predicateList.add(cb.equal(root.get("sex"),userQuery.getSex()));
predicateList.add(cb.like(root.get("name"),userQuery.getName()));
predicateList.add(cb.greaterThanOrEqualTo(root.get("age"),userQuery.getAge()));
2.常见单表查询
predicateList.add(cb.like(root.get("instanceName").as(String.class), "%" + s + "%"));
Path<Object> productIdPath = root.get("productId");
In<Object> in = cb.in(productIdPath);
for (Integer productId : request.getPermissionProductIds()) {
in.value(productId);
}
predicateList.add(in);
predicateList.add(cb.equal(root.get("engine").as(String.class), request.getEngine()));
predicates.add(cb.or(
cb.like(root.get("note").as(String.class), "%" + request.getKeyword() + "%"),
cb.like(root.get("wanIp").as(String.class), "%" + request.getKeyword() + "%")
));
if (!StringUtils.isEmpty(request.getStartDate())) {
predicates.add(cb.greaterThan(root.get("beginTime").as(String.class), request.getStartDate() + " 00:00:00"));
}
if (!StringUtils.isEmpty(request.getEndDate())) {
predicates.add(cb.lessThan(root.get("beginTime").as(String.class), request.getEndDate() + " 23:59:59"));
}
3.多表Join查询
@Entity
@Table(name = "cc_cloud_db_instance")
public class DbInstanceModel extends BaseModel {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String instanceName;
private String instanceId;
@OneToMany(cascade = {CascadeType.REMOVE}, fetch = FetchType.EAGER)
@Fetch(FetchMode.SUBSELECT)
@JoinColumn(name = "instanceId", referencedColumnName = "instanceId")
@NotFound(action=NotFoundAction.IGNORE)
private List<DbInstanceProductModel> products;
}
@Data
@Entity
@Table(name = "cc_cloud_db_instance_product")
public class DbInstanceProductModel extends BaseModel {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String instanceId;
private Integer productId;
@ManyToOne
@JoinColumn(name = "instanceId", referencedColumnName = "instanceId", insertable = false, updatable = false)
public DbInstanceModel instance;
}
Join<DbInstanceModel, DbInstanceProductModel> productJoin = root.join("products", JoinType.LEFT);
predicates.add(cb.equal(productJoin.get("productId"), request.getProductId()));
4.子查询
Subquery<Integer> subQuery = query.subquery(Integer.class);
Root<DbInstanceProductModel> fromUR = subQuery.from(DbInstanceProductModel.class);
subQuery.select(fromUR.get("instanceId"));
permissionPredicates.add(cb.in(root.get("instanceId")).value(subQuery));
5.分页&排序
Pageable pageable = PageRequest.of(request.getPage() - 1,request.getLimit().intValue(),
Sort.Direction.DESC,"createTime");
Sort sort = Sort.by(Sort.Direction.DESC, "id");
Pageable pageable = PageRequest.of(request.getPage() - 1, request.getLimit(), sort);
6.条件封装
Predicate[] pre = new Predicate[predicates.size()];
return query.where(predicates.toArray(pre)).getRestriction();
return cb.and(cb.and(cb.and(predicates.toArray(new Predicate[0])),
cb.or(permissionPredicates.toArray(new Predicate[0]))), constPredicate);