Search code examples
hibernatespring-data-jpaspring-datacriteria-api

CriteriaBuilder: join one-to-many with ON clause


Suppose you the following OneToMany relationships: School->Student->ScientificWork. Now you want to select all Schools that have Student with name 'John' and his scientific job is called 'Black Holes'.

I do it like the following, but for some reason it retrurns me all possible schools.

public static Specification<School> spec() {
    return (root, query, cb) -> {
        final SetJoin<School, Student> studs = root.joinSet("students", JoinType.LEFT);
        final SetJoin<Student, ScientificWork> works = root.joinSet("works", JoinType.LEFT);
        return cb.and(
                cb.equal(studs.get(Student_.name), 'John'),
                cb.equal(nodes.get(ScientificWork_.name), 'Black Holes')
        );
    };
}

Update

After finding this answer I tried the following, but with the same result (it returns me all Schools instead of one):

public static Specification<School> spec() {
    return (root, query, cb) -> {
        final SetJoin<School, Student> studs = root.joinSet("students", JoinType.LEFT);
        studs.on(cb.equal(studs.get(Student_.name), 'John'));
        final SetJoin<Student, ScientificWork> works = root.joinSet("works", JoinType.LEFT);          
        return cb.equal(nodes.get(ScientificWork_.name), 'Black Holes');
    };
}

Solution

  • public static Specification<School> spec() {
        return (root, query, cb) -> {
            final Join<School, Student> studs = root.join("students", JoinType.LEFT);
            studs.on(cb.equal(studs.get(Student_.name), "John"));
            final Join<Student, ScientificWork> works = studs.join("works", JoinType.LEFT);          
            return cb.equal(works.get(ScientificWork_.name), "Black Holes");
        };
    }
    

    I used join instead of joinSet and put **works**.get(ScientificWork_.name) instead of **nodes**.get(ScientificWork_.name)