Hibernate: Many to Many contains specific IDs using Criteria

Question

I'm developing a JPA application where I've reached a problem with Many To Many association between these entities:

Movie

class Movie {
    @Id 
    String id;

    ...

    @ManyToMany
    @JoinTable(
        name = "movie_category",
        joinColumn = @JoinColumn(name = "movie_id", referencedColumnName = "id")
    )
    Set<Category> categories = new HashSet<>();
}

Category

class Category {

    @Id
    String id;
    ...
}

I want to select only the movies having all the categories (List<Category> for instance). In Criteria API, I could do something like this:

List<Category> requiredCategories = ... //from request


CriteriaBuilder b = em.getCriteriaBuilder();
CriteriaQuery<Movie> cq = b.createQuery(Movie.class);
Root<Movie> r = cq.from(Movie.class);

Expression<Set<Category>> categories = r.get(Movie_.categories);

Predicate predicate = b.and();
for(Category c : requiredCategories) {
    predicate = b.and(predicate, b.isMember(c, categories));
}

Which works fine, but the problem is that I have to fetch Category objects from Category table first and then use it in isMember function. I would like to do it somehow avoiding joining Category table.

For example: I want all movies having category COMEDY and FANTASY. (consider these keywords being Category.id), so I need to pull by id from Category table both entities and then use them in isMember function. I want to avoid that because the information category_id is already stored in the association table movie_category. In short, I'd like something like isMember but providing an Category.id instead of the entity.

Is something like this possible?

Konstantin Triger · Accepted Answer · 2019-09-05T22:35:17.137

1

To my best knowledge such a query is impossible using JPA builtin tools since join table is not directly accessible. But this is possible with FluentJPA:

public List<Movie> getMoviesByCategories(List<String> categoryIds) {
    int matchTotal = categoryIds.size();

    FluentQuery query = FluentJPA.SQL((Movie movie,
                                       JoinTable<Movie, Category> movieCategory) -> {

        discardSQL(movieCategory.join(movie, Movie::getCategories));

        List<String> movieIds = subQuery(() -> {
            String movieId = movieCategory.getJoined().getId();
            String catId = movieCategory.getInverseJoined().getId();

            SELECT(movieId);
            FROM(movieCategory);
            WHERE(categoryIds.contains(catId));
            GROUP(BY(movieId));
            HAVING(COUNT(movieId) == matchTotal); // COUNT(DISTINCT(movieId));
        });

        SELECT(movie);
        FROM(movie);
        WHERE(movieIds.contains(movie.getId()));

    });

    return query.createQuery(em, Movie.class).getResultList();
}

which produces the following SQL:

SELECT t0.* 
FROM t0 
WHERE (t0.id IN (SELECT t1.movie_id 
FROM movie_category t1 
WHERE (t1.CATEGORIES_id IN ?1 ) 
GROUP BY  t1.movie_id  
HAVING (COUNT(t1.movie_id) = ?2) ) )

The sub query is required to produce and semantics. See this answer. Many to many semantics in FluentJPA is explained here.

edited Sep 05 '19 at 22:35

answered Sep 05 '19 at 21:51

Konstantin Triger

1,576
14
11

Thanks for answer (and sorry for late response), but is there a way to use the FluentQuery as JPA Specification? – t4dohx Sep 09 '19 at 10:07
@t4dohx: not sure I understand your question. FluentJPA extends your existing JPA infrastructure and lets you write SQL queries using your Entitites. What do you mean by "use as JPA Specification"? – Konstantin Triger Sep 09 '19 at 15:40
I mean if its possible to convert it to Specification as I am using JpaSpecificationExecutor (because I want to have it as condition). – t4dohx Sep 09 '19 at 16:44
JPA and all API it has (including Criteria API) do not provide direct access to join table. So what you're asking is impossible. I looked on JpaSpecificationExecutor, it has findXXX and count methods. The sample I posted returns a List. What JpaSpecificationExecutor API do you intend to call? What are trying to achieve? – Konstantin Triger Sep 09 '19 at 17:29
My goal is to produce just Criteria for my prepared query (where clause), where I just pass the criteria/condition to the function (getMovies(Specification))) and it'll return list of movies. – t4dohx Sep 09 '19 at 19:22
1

Well, if you just want to return the list of movies, you can use the posted example as is. In case you have another [complex] query, that you want to add a condition to, you will need to rewrite it with FluentJPA. Since Criteria API does not support complex queries like this, FluentJPA cannot produce a Specification. – Konstantin Triger Sep 09 '19 at 19:30

Hibernate: Many to Many contains specific IDs using Criteria

1 Answers1