JPA-Specification常用条件查询构造方式

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.常见单表查询

//like查询
predicateList.add(cb.like(root.get("instanceName").as(String.class), "%" + s + "%"));

//in查询
Path<Object> productIdPath = root.get("productId");
In<Object> in = cb.in(productIdPath);
for (Integer productId : request.getPermissionProductIds()) {
    in.value(productId);
}
predicateList.add(in);

//equal查询
predicateList.add(cb.equal(root.get("engine").as(String.class), request.getEngine()));

//or查询
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查询

• DbInstanceModel实体

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

• DbInstanceProductModel实体

@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.分页&排序

//方式1：
Pageable pageable = PageRequest.of(request.getPage() - 1,request.getLimit().intValue(),
Sort.Direction.DESC,"createTime");

//方式2（可根据多个字段排序）：
Sort sort = Sort.by(Sort.Direction.DESC, "id");
Pageable pageable = PageRequest.of(request.getPage() - 1, request.getLimit(), sort);

6.条件封装

//单纯AND条件
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);