Criteria JPA 2 join many to many unidirectional realtion

user_7346425 0
Viewed 159

I have two entities, Movie and Genre, with a many to many relationship from genre to movie. Genre being the "parent" of the relationship.
This generates three tables: genre, movie and genre_movie

@Entity
public class Genre {
  @Id
  @GeneratedValue(strategy = AUTO)
  private Long id;

  @ManyToMany(fetch = LAZY)
  @JoinTable( name = "genre_movie",
    joinColumns = {@JoinColumn(name = "genre_id")},
    inverseJoinColumns = {@JoinColumn(name = "movie_id")}
  )
  private Set<Movie> movies = new HashSet<>();

  ...
}


@Entity
public class Movie {
  @Id
  @GeneratedValue(strategy = AUTO)
  private Long id;
  ...
}

Entity Diagram

I it possible perform this query using the criteria query api? Filter movies based on their genre id.

select *
from movie
  join genre_movie on movie.id = genre_movie.movie_id
where genre_id = 19;
criteria-api jpa hibernate sql java
1 Answers

It is not possible because the Movie entity does not have a reference to Genre as it is not bidirectional.

But you can restructure the query to get the same results:

Select * from genre
  inner join genre_movie on genre.id = genre_movie.genre_id
  inner join movie on genre_movie.movie_id = movie.id
  where genre.genre_id = 19;

The query would be implemented as follows:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Movie> cq = cb.createQuery(Movie.class);

Root<Genre> rootGenre = cq.from(Genre.class);
Join<Genre,Movie> joinMovie = rootGenre.join("movies");
//Join<Genre,Movie> joinMovie = rootGenre.join(Genre_.movies);

cq.select(joinMovie);
cq.where(cb.equals(rootGenre.get("id"), 19));
//cq.where(cb.equals(rootGenre.get(Genre_.id), 19));

return this.em.createQuery(cq).getResultList();

I particularly recommend using metamodels (commented lines) since when accessing the properties by name in String format, errors can occur that with Metamodels not in more complex queries.

I also think it would be interesting to define the relationship as bidirectional to be able to set the Movie class as Root

user_104652740
Related