JPA CriteriaBuilder 子查询方法

给自己做个备忘：

/** 检索关键字 */
if (keyWord != null && !keyWord.trim().equals(""))
{
  // 创建子查询对象
Subquery<Store> subquery = criteriaQuery.subquery(Store.class);
Root<Store> root2 = subquery.from(Store.class);
subquery.select(root2.<Store> get("id"));
subquery.where(criteriaBuilder.like(root2.<String> get("name"), "%" + keyWord + "%"));

// 给读者稍微解释一下，也给自己留个记忆
  // 这里的意图是将关键做两个模糊检索，一个是在本表中检索title字段
  // 就是criteriaBuilder.like(root.<String> get("title"), "%" + keyWord + "%")
  // 二是将字段与子表中的检索出来的id与本表中store字段做匹配
  // criteriaBuilder.in(root.get("store")).value(subquery))
  // 至于id怎么检索出来的，用子查询模糊检索出来的，参照上面的子查询 
restrictions = criteriaBuilder.and(restrictions,criteriaBuilder.or(criteriaBuilder.like(root.<String> get("title"), "%" + keyWord + "%"),criteriaBuilder.in(root.get("store")).value(subquery)));
}

Jpa在CriteriaBuilder中添加where条件NotIn子查询

final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
final CriteriaQuery<Person> cq = cb.createQuery(Person.class);
final Root<Person> root = cq.from(Person.class);
cq.select(root);
final Subquery<Integer> subquery = cq.subquery(Integer.class);
final Root<Person> validityIDSQ = subquery.from(Person.class);
subquery.select(cb.max(validityIDSQ.get(Person_.validityID)));
subquery.groupBy(validityIDSQ.get(Person_.personID));
cq.where(cb.in(root.get(Person_.validityID)).value(subquery));
// 将生成
select
  person0_.id as id1_0_,
  person0_.personID as personID2_0_,
  person0_.validityID as validity3_0_,
  person0_.value as value4_0_
from
  person person0_
where
  person0_.validityID in (
          select
              max(person1_.validityID)
          from
              person person1_
          group by
              person1_.personID)

自身所需NotIn语法，所以是

Subquery<String> manageUserSubquery = query.subquery(String.class);
              Root<RelationUserManagePlateDomain> relationUserManagePlateDomainRoot = manageUserSubquery.from(RelationUserManagePlateDomain.class);
              Join<RelationUserManagePlateDomain, UserDomain> relationUserManagePlateDomainUserDomainJoin = relationUserManagePlateDomainRoot.join(RelationUserManagePlateDomain_.user, JoinType.INNER);
              Subquery<String> subquery = manageUserSubquery.select(relationUserManagePlateDomainUserDomainJoin.get(UserDomain_.id));
              Predicate predicate1 = criteriaBuilder.and(criteriaBuilder.not(criteriaBuilder.in(root.get(UserDomain_.id)).value(manageUserSubquery)));
              predicate.getExpressions().add(predicate1);

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程宝库。

public List<InstitutionInfo> getAllInstitution(final Application app){ String zdGljg = null; S ...