How to I create an HQL query to return objects in a many to many relationship?

Question

Hi there. I have an application that includes 2 classes Club and Article. These are mapped in Hibernate as a many to many relationship.

As a result, hibernate has created a table called CLUB_ARTICLE which it uses to manage the many to many relation ship. The CLUB and ARTILCE tables have no direct reference to each other and the mapping is only represented in the CLUB_ARTICLE table.

I need to create an HQL query that returns a list of articles for a particlular club. So I need to supply the club id and get back a list of Article objects that belong to it. For some reason, I just can't work out how to do this. Any help would be very much appriciated!

Thanks.

Answer 1

What is the relation between Club and Article in the code? You need to forget about the database schema when you think your HQL. Only relations defined in the hibernate mapping (annotation or xml) can be used in hql.

Assuming your mapping is bidirectionnal and you have a collection of Club called clubs in Article, you can do something like:

String hql = "from Article where clubs = :club";

Then set your club entity in the query:

Query q = sess.createQuery(hql);
q.setEntity("club", club);

Now, if Article does not have a collection (list/set) of Club, it gets more complicated. You could select from Club and do a projection on article ids and then fetch them. I would however suggest that you simply add a collection property to the Article entity since it will not impact the database schema and it will ease queries.

Answer 2

from Article a join a.clubs c where c.id=:clubid

Answer 3

You need to think object and relation between objects, not tables, when writing your HQL. Here, to retrieve a list of Article for a particular Club given its id, you could do something like this:

select club.articles from Club c where c.id =:id