spring-data subquery within a Specification

Question

Spring-data, Oliver Gierke's excellent library, has something called a Specification (org.springframework.data.jpa.domain.Specification). With it you can generate several predicates to narrow your criteria for searching.

Can someone provide an example of using a Subquery from within a Specification?

I have an object graph and the search criteria can get pretty hairy. I would like to use a Specification to help with the narrowing of the search, but I need to use a Subquery to see if some of the sub-elements (within a collection) in the object graph meet the needs of my search.

Thanks in advance.

score 37 · Answer 1 · answered Dec 28 '12 at 10:37

String projectName = "project1";
List<Employee> result = employeeRepository.findAll(
    new Specification<Employee>() {
        @Override
        public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Subquery<Employee> sq = query.subquery(Employee.class);
            Root<Project> project = sq.from(Project.class);
            Join<Project, Employee> sqEmp = project.join("employees");
            sq.select(sqEmp).where(cb.equal(project.get("name"),
                    cb.parameter(String.class, projectName)));
            return cb.in(root).value(sq);
        }
    }
);

is the equivalent of the following jpql query:

SELECT e FROM Employee e WHERE e IN (
    SELECT emp FROM Project p JOIN p.employees emp WHERE p.name = :projectName
)

Is it possible explicitly use a constant SQL statement in a similar manner? — zygimantus, Mar 29 '18 at 12:52
Yes. To pass constant value use cb.literal(1000) <-- any constant. — wheeleruniverse, Jul 13 '18 at 16:32
Is it possible to use subquery in select clause? In this case, subquery is in where clause. — AlpacaMan, Mar 28 '19 at 06:48

spring-data subquery within a Specification

1 Answers1

Linked

Related