Can anyone point me to some examples of advanced hibernate queries? I have had many problems creating advanced queries, which do not seem to be covered in the reference manual.

1) I am trying to use the Criteria API to create the equivalent of:

select * from item where classifier in (select classifier from classifier_parent where parent in (parentList))

I have a set of elements "parentList", which I pass in from java. Then I want to select all items whose classifier is in xyz or the subtree of elements

In hibernate, I have "item" mapped to a class, with a many-to-one property "classifier" mapping to "Classifier" class/table. "Classifier" has a property, a many-to-many set of other Classifiers, represented by a table "Classifier_parent".

I haven't been able to solve this as either a subquery or a join. I've tried using query1.createQuery() and query1.createAlias(), but they have not worked as expected.

2) Thinking this may be too complex for Criteria, I tried using raw SQLQuery. For this API, I can't find a way to query inherited objects, since their superclasses are not properly loaded. A query like this:

SQLQuery query = service.getSession().createSQLQuery(
"SELECT {classifier.*} FROM classifier {classifier} WHERE {classifier}.id IN (SELECT classifier FROM classifier_parent WHERE parent in (:parentList))");

I get an error, "relation classifier_3_ does not exist". This refers to a superclass of the classifier, whcih is being loaded automatically by the {classifier.*} expression. I understand that, but how can i add the appropriate joins. That seems a strange mixture of automatic and manual behavior. Plus the hibernate naming conventions must be anticipated and matched (for names like "classifier_3_"). And further, trying to match the style, I can't get it to work, getting a syntax error.

*** I can detail all of this further, and send out files, but it would be much better to just get some better examples of related queries:
1) how to use Criteria to do a subquery with an "IN" condition
2) how to use SQLQuery with inherited types

Any help or references to examples would be appreciated.