JPA WHERE clause on many-to-many entity

Question

I have two table, Book and Genre. Each book may have many genre, and the many-to-many table only contain the Book ID and Genre ID. I want to create a filter which would filter books by it's genre(s).

  @JoinTable(name = "bookGenre", joinColumns = {
    @JoinColumn(name = "bookId", referencedColumnName = "id")}, inverseJoinColumns = {
    @JoinColumn(name = "genreId", referencedColumnName = "id")})
  @ManyToMany
  private Collection<Genre> genreCollection;

Genre Class:

  @Id
  @Basic(optional = false)
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "id")
  private Integer id;
  @Size(max = 255)
  @Column(name = "name")
  private String name;
  @Column(name = "status")
  private Integer status;
  @ManyToMany(mappedBy = "genreCollection")
  private Collection<Book> bookCollection;

This is the query that I'm trying to do on JPA:

SELECT b.title FROM book b
JOIN bookGenre bg on bg.bookId = b.id
JOIN genre g on bg.genreId = g.id
WHERE g.id = 1
--Filter books that have genre with id 1

I've tried the following on JPA: SELECT b FROM Book b WHERE b.genreCollection.id = 1

But it gave me the following error: "The state field path 'b.genreCollection.id' cannot be resolved to a valid type."

EDIT: I found out that i can use native sql query with built in result set mapping to Book POJO EntityManager.createNativeQuery(query, Book.class). So i just use that and string together my query with StringJoiner.

Does this answer your question? [How to properly express JPQL "join fetch" with "where" clause as JPA 2 CriteriaQuery?](https://stackoverflow.com/questions/5816417/how-to-properly-express-jpql-join-fetch-with-where-clause-as-jpa-2-criteriaq) — Turing85, Aug 13 '20 at 21:41
I tried using INNER JOIN and yes it worked but now the list of books is repeating. Just like selecting from detail table or in my case bookGenre table. — Norman Seiro, Aug 13 '20 at 21:59

score 0 · Answer 1 · answered Aug 13 '20 at 21:51

0

I think your JPA Query should be

SELECT b FROM Book b INNER JOIN b.genreCollection g WHERE g.id = 1

answered Aug 13 '20 at 21:51

César Alves

441
2
9

It works but now the list of books is repeating, just like selecting from the detail table – Norman Seiro Aug 13 '20 at 22:00
Check the generated SQL query, is being correctly generated? – César Alves Aug 13 '20 at 22:05

JPA WHERE clause on many-to-many entity

1 Answers1