hibernate criteria - querying tables in n:m relationship

Question

I'm trying to build a query with hibernate criteria for the following scenario:

• Two entities: Indicator and report (each with their own tables, classes etc.)
• an indicator can be used in zero to many reports
• a report uses zero to many indicators
• therefore, I have an intersection table to store the relationship
• the relationship is defined in both classes and their hibernate mappings
• in the UI, the user can select one or many reports (among other things), and I would like to query the DB for the Indicators used in these reports

I've tried the following:

criteria.add(Restrictions.in("Reports", selectedReports));

but all I get is a strange SQL Statement with

where this_.Indicator_ID in (?)

and then a JDBC exception (missing parameter)

Any ideas? Thanks.

Note: I've looked into Querying ManyToManyrelationship with Hibernate Criteria, but the accepted solution there is to build a custom sql-string ...

Answer 1

Sounds like you're thinking about SQL too much and not enough about objects.

Hibernate is an ORM tool. An m:n relationship should be written in terms of the objects. If a Foo has a Set, and a Bar a Set, that's the proper starting place.

I know that Hibernate will generate a JOIN using a join table, but if you're only thinking in terms of the SQL why use Hibernate at all?

Answer 2

  Criteria c = session.createCriteria(Indicator.class);
    c.add(Restrictions.eq("someField", myObject).createCriteria("reports")
    .add(Restrictions.eq("reportName", name);
    c.list();

You need to create a sub criteria to the entity that is being held in a collection on some other entity.

String[] selectedReportsId = {"1", "2", "3"};
 c.add(Restrictions.eq("someField",myObject).createCriteria("reports")
    .add(Restrictions.in("id", selectedReportsId);

Then check out the bit about transforming the results from here: http://www.hibernate.org/hib_docs/v3/reference/en-US/html/querycriteria-associations.html

Also this may shed some light on what you can do w/ criteria: http://www.hibernate.org/hib_docs/v3/api/org/hibernate/criterion/Restrictions.html

Answer 3

If you must, here are a couple of suggestions that Google came back with after searching for "hibernate hql many-to-many":

http://patf.net/blogs/index.php?blog=2&c=1&more=1&pb=1&tb=1&title=many_to_many_in_hql

And from the Hibernate forums:

http://forum.hibernate.org/viewtopic.php?p=2340747&sid=d4c8d2fcc16aed0201f73eb74619692a

And from the Spring forums:

http://forum.springframework.org/showthread.php?t=36870

Hope these help.

Answer 4

For now, this is how I got it to work (thanks to zmf).

Criteria subcrit = criteria.createCriteria("Reports");
Disjunction dis = Restrictions.disjunction();
for (Reports r : selectedReports) {
    dis.add(Restrictions.idEq(r.getID()));
}
subcrit.add(dis);

This is almost exactly what zmf suggested, all I added was the disjunction to build the criteria from the Collection that is passed around.

All that's left to do is to try to use the collection directly ...