Spring Data JPA: Creating Specification Subquery from different tables

Question

I am trying to build a specifications to predicate subquery for the below query. Select u.* from User u where u.login in (select ur.role_id from userRoles ur where ur.role_Id = roleId).

till now this is the part I have built

public static Specification<User> userRoleId(String roleId) {
        return new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
                //return builder.equal(root.<LocalDate> get("scheduledDisbursementDate"), scheduledDisbursementDate);
                Subquery<UserRole> subquery = query.subquery(UserRole.class);
                Root<UserRole> subqueryRoot = subquery.from(UserRole.class);
                subquery.select(subqueryRoot);
                Predicate roleIdList = builder.equal(subqueryRoot.get("roleId"), roleId);
                subquery.select(subqueryRoot).where(roleIdList);
                return builder.exists(subquery);
            }
        };
    }

Can you please help me out to link the subquery with main query.

Note: There are no joins defined in the Entity classes. evrything should be done through subquerys only

score 10 · Accepted Answer · answered May 25 '17 at 05:44

Found Answer

first predicate will join UserRole userId column with and User table login column. Second predicate will filter the condition based on roleId.

public static Specification<User> userRoleId(String roleId) {
    return new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
            Subquery<UserRole> subquery = query.subquery(UserRole.class);
            Root<UserRole> subqueryRoot = subquery.from(UserRole.class);
            subquery.select(subqueryRoot);
            Predicate userIdPredicate = builder.equal(subqueryRoot.get("userId"), root.<String> get("login"));
            Predicate rolePredicate = builder.equal(subqueryRoot.get("roleId"), roleId);
            subquery.select(subqueryRoot).where(userIdPredicate, rolePredicate);
            return builder.exists(subquery);

        }
    };
}

Why do you have `subquery.select(subqueryRoot);` twice in the code? Isn't the first one redundant? — Dalibor Filus, Feb 12 '19 at 12:21
Well @dalibor-filus I can confirm that `subquery.select(subqueryRoot);` is redundant; I have another subquestion: why is it needed a boolean returning? — Stefano Scarpanti, Apr 22 '22 at 07:46
how to implement `not in` instead of `(not) exist`? I'm doing like this `return builder.not(builder.in(subQuery.select(subQueryRoot.get("other")).where(userIdPredicate))); ` but getting such sql: `select .... (here everything is ok)) not in (null)` — littleAlien, Jan 31 '23 at 13:33

Spring Data JPA: Creating Specification Subquery from different tables

1 Answers1