Clever Geek Handbook

Hibernation criteria - queries of tables in the ratio n: m

I am trying to create a query with hibernation criteria for the following scenario:

  • Two objects: an indicator and a report (each with its own tables, classes, etc.).
  • the indicator can be used to zero for many reports
  • the report uses "0" indicators for many indicators.
  • so I have an intersection table to store the relationship
  • communication is defined both in both classes and in their hibernate mappings
  • in the UI the user can select one or more reports (among others) 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 weird SQL statement with

where this_.Indicator_ID in (?)

and then JDBC exception (missing parameter)

Any ideas? Thank.

Note. I looked at a ManyToManyrelationship query with Hibernate criteria , but the accepted solution is to create a custom sql string ...

0


source to share


3 answers 


  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 child criteria for an object that is stored in a collection on some other object.

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


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

Also it might shed some light on what you can do with criteria: http://www.hibernate.org/hib_docs/v3/api/org/hibernate/criterion/Restrictions.html

+4


source


If you need, here are some suggestions that Google returned 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 forum:

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

Hope for this help.

0


source


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 what zmf suggested, all I added was a disjunction to build criteria from the collection that is passed in.

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

0


source






More articles:


All Articles