最近使用JPA多表关联查询,记录一下,以便后续回忆使用
(root, query, cb) -> {
List<Predicate> p = new ArrayList<>();
//增加筛选条件
// AL一次不良数据
if (CollectionUtils.isNotEmpty(data.getNotHandleTargets()) && StringUtils.isNotBlank(data.getNotBadType())) {
Subquery<StockDetail> subQuery = query.subquery(StockDetail.class);
Root<StockDetail> subRoot = subQuery.from(StockDetail.class);
Root<Factory> factoryRoot = subQuery.from(Factory.class);
//这里是子查询的条件,前者是自身的条件,后者是主表的关联条件,当然where方法的参
//数是个可变参数,可以根据自己需要加条件
List<Predicate> predicates=new ArrayList<>();
Predicate equal = cb.equal(root.get("monoblockCode"), subRoot.get("monoblockCode"));
predicates.add(equal);
Predicate in = cb.in(subRoot.get("handleTarget")).value(data.getNotHandleTargets());
predicates.add(in);
Predicate like = cb.like(subRoot.get("badType"), "%" + data.getNotBadType() + "%");
predicates.add(like);
Predicate factory = cb.equal(factoryRoot.get("code"), "AL");
predicates.add(factory);
Predicate factoryId = cb.equal(subRoot.get("factoryId"), factoryRoot.get("id"));
predicates.add(factoryId);
subQuery.where(predicates.toArray(new Predicate[predicates.size()]));
//这句话不加会报错,因为他不知道你子查询要查出什么字段
subQuery.select(subRoot.get("id"));
Predicate not = cb.not(cb.exists(subQuery));
p.add(not);
}
return cb.and(p.toArray(new Predicate[p.size()]));
};