Spring data jpa Specification: How to filter a parent object by its children object property

maruf571 picture maruf571 · Dec 18, 2017 · Viewed 7.7k times · Source

My entity classes are following

@Entity
@table
public class User {

    @OneToOne
    private UserProfile userProfile;

    // others
}


@Entity
@Table
public class UserProfile {

    @OneToOne
    private Country country;
}

@Entity
@Table
public class Country {
    @OneToMany
    private List<Region> regions;
}

Now I want to get all the user in a particular region. I know the sql but I want to do it by spring data jpa Specification. Following code should not work, because regions is a list and I am trying to match with a single value. How to fetch regions list and compare with single object?

 public static Specification<User> userFilterByRegion(String region){


        return new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {

                return criteriaBuilder.equal(root.get("userProfile").get("country").get("regions").get("name"), regionalEntity);
            }
        };
    }

Edit: Thanks for the help. Actually I am looking for the equivalent criteria query for the following JPQL

SELECT u FROM User u JOIN FETCH u.userProfile.country.regions ur WHERE ur.name=:<region_name>

Answer

pvpkiran picture pvpkiran · Dec 18, 2017

Try this. This should work

criteriaBuilder.isMember(regionalEntity, root.get("userProfile").get("country").get("regions"))

You can define the condition for equality by overriding Equals method(also Hashcode) in Region class