Spring DATA JPA嵌套子查询

JPA 子查询： select * from A where filed in (select filed from B) 

Specification<Role> specification = new Specification<Role>() {

	@Override
	public Predicate toPredicate(Root<Role> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
		
		Subquery<Long> subQuery = query.subquery(Long.class);
		Root<UserRole> fromUR = subQuery.from(UserRole.class);
		
		subQuery.select(fromUR.get("id").get("roleId")).where(cb.equal(fromUR.get("id").get("userId"), 2059));
		
		return cb.not(cb.in(root.get("roleId")).value(subQuery));
		
	}
};

对应SQL：

    select
        role0_.role_id as role_id1_4_,
        role0_.create_date as create_d2_4_,
        role0_.description as descript3_4_,
        role0_.modify_date as modify_d4_4_,
        role0_.name as name5_4_,
        role0_.role_key as role_key6_4_ 
    from
        role_ role0_ 
    where
        role0_.role_id not in  (
            select
                userrole1_.role_id 
            from
                user_role userrole1_ 
            where
                userrole1_.user_id=2059
        ) 
    order by
        role0_.name asc limit ?

 

转载于:https://my.oschina.net/u/3359/blog/1329953