Search code examples
hibernatemany-to-manycriteria

hibernate criteria - querying tables in n:m relationship

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 ...

Solution 
  •   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: https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/querycriteria.html#querycriteria-associations

    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