Spring Data JPA Specification with MayToMany relation

Question

I'm trying to use Specification to build a search criteria with Spring Data JPA.

I have 2 entities

@Entity
public class User {
    @Id
    private Long userId;

    @ManyToMany
    @JoinColumn(name="locationId")
    @JsonIgnore
    private List<Location> locations;

    //getters and setters

And Location

public class Location {

    @Id
    private long locationId;

    @ManyToMany(fetch = FetchType.LAZY, cascade = { CascadeType.PERSIST, CascadeType.REMOVE, CascadeType.DETACH,
            CascadeType.REFRESH }, mappedBy = "locations")
    @JsonIgnore
    private List<User> users;

    //getters and setters

Now I would like to find all users with a given list of locations, so I'm trying to build the predicate.

My problem is getting the relation table between the 2 entities... I'm trying in this way

Root<Location> second = query.from(Location.class);
Expression<Collection<Root>> userLocations = second.get("users");
return builder.and(builder.isMember(root, userLocations));

This is the sql query i need

select distinct * from User u 
join User_Location ul on u.userId = ul.`users_userId`
where ul.`locations_locationId` in (1,2,3...)

The main difficult for me is telling Spring to use the relation table User_Location which is not an entity...

But I indeed miss something...

You don't need to use the join table in your query. Simply do "select distrinct u from User u where u.locations in (l1, l2, l3, …)" In criteriaBuilder the class is a "Join" class, so Join locations = root.join("locations)" if I remember correctly. Then "query.where( builder.in(locations, locationList))". Something along those lines. — K.Nicholas, Nov 13 '18 at 20:00
You can break down a bidirectional Many-to-Many relationship to two One-To-Many relationships. The config can be found [**here**](https://vladmihalcea.com/the-best-way-to-map-a-many-to-many-association-with-extra-columns-when-using-jpa-and-hibernate/). — huytmb, Nov 13 '18 at 08:52

Spring Data JPA Specification with MayToMany relation

0 Answers0