I am not an Hibernate hacker but I know enough about relational databases because of my past Oracle DBA experience and enough about OO and ORM in order to find my way around. Still from time to time I find myself at a point where I just don't know how to achieve something but then my expert googling skills come into play and eventually I figure it out. Lately I was forced to use Hibernate's Criteria framework instead of HQL and there I was facing some steep challenges.
At the beginning I found my way around looking at other's people code making one-2-one relationships and INs working. But then I was faced with a simple yet not trivial question of how to join two tables (master / children) and selecting on the child table. The biggest problem was that I did not understand what an association path meant in Hibernate and the JavaDoc would not say anything about it. But eventually I found an example where I could make the connection and it turned out that the association path is the property that contains the list of children. This meant I could write my code like this:
DetachedCriteria masterCriteria = DetachedCriteria.forClass( Master.class );
// This assumes that on the Master there is a List of Child entries in a property called 'children'
DetachedCriteria childCriteria = masterCriteria.createCriteria( "children" );
childCriteria.add( Restrictions.ge( "age", minimumAge ) ); // Minimum Age is the given restriction
So far so good but then I had to write a query that would only find the masters with one or more children matching given criteria (which is the same as above but I will only receive on master even if multiple children do match the given criteria and I will not get the child records). I knew that I had to use the IN operator to avoid duplications of the master records but the problem was how to write that. First I create the selection on the children, limited the select statement on the id and said that the childrens' id must be in that list. But that provided duplicate master records because there was an inner join with the child table. This meant I had to do it the other way around and eventually I came up with this:
DetachedCriteria masterCriteria = DetachedCriteria.forClass( Master.class );
DetachedCriteria childCriteria = DetachedCriteria.forClass( Child.class );
// Given restriction DetachedCriteria childCriteria = masterCriteria.createCriteria( "children" );
// This assumes that we have a Reference to the Master record called 'master'
childCriteria.setProjection( Property.forName( "master.id" ) );
masterCriteria.add( Property.forName( "id" ).in( childCriteria ) );
For me the difficult thing was not how to compose the SQL statements but how to map them to the Hibernate Criteria framework. As soon as I did that then it is quite easy to apply to other queries.
Have fun - Andy