CODEWITHSUNDEEP
javadatabasehibernatejpajpql
villat
villat

Reputation: 65

JPQL for Entries Many To Many relationship

I have table with many to many relationship:

@ManyToMany
@JoinTable(name = "book_category",joinColumns = @JoinColumn(name = "book_id"),
                           inverseJoinColumns = @JoinColumn(name = "category_id"))
private List<Category> categories;

What is the fastest method to find all Books which have selected category(by id).

Upvotes: 2

Views: 3403

Answers (2)

prem kumar
prem kumar

Reputation: 5877

You can write a jpql. 

select b from Book b inner join fetch b.categories as c where c.id=:category_id

If you want just book ids, you can write a native sql query to query directly on the table 'book_category'

Upvotes: 0

Maciej Kowalski
Maciej Kowalski

Reputation: 26522

If you want to use JPQL then try this:

select b
from Books b
  inner join b.categories c
where c.id = :id

Then in your repository:

Query query = session.createQuery(/* above query*/);
query.setInteger("id", 123);
query.list();

(If you are using JPA api then use EntityManager instead of Hiberantes Session)

Using a native query would be a bit more efficient as the JPQL has to make two joins, but you would loose the portability benefit.

On the side, you may need to use DISTINCT in the jpql query.

Upvotes: 2

Related Questions