Greetings...

I am trying to use Hibernate's Criteria query API to search against a set of tables with the following relationships:

* A course can be offered in a particular year, managed in the OFFERING table
* A course can have one or more subject code/course number representations, managed in the PROGRAM_OFFERING table
* A course can have one or more course attributes, such as its being designated as a Science course or a Humanities course, managed in the OFFERING_ATTR table

So... I have two 1 -> n relationships here to query against. The primary goal is the following:

* Be able to query by subject code, if one exists
* Be able to query by attribute, if one exists
* Be able to produce a result set, with one record per offering and the list of subject code/course numbers per offering, and the list of course attributes per offering

The challenge is how to do this without resorting to either stored procedures to concatenate these lists (or other Oracle-isms such as user-defined aggregation functions) in the driving query or manually condensing duplicate records after producing the result set which seems inelegant as I would think Hibernate should have some way of handling this use case. Regarding the former, I would prefer to avoid having to write any DB code as so far we have not had to for anything be they views, SQL types, functions, packages etc.; we chose Hibernate specifically for that reason. Regarding the latter, I thought I could do something with the ResultTransformer but it's either not the appropriate thing to use or I'm not doing it right.

I hope I explained this sufficiently; by all means let me know if I need to supply more information.

Gratefully,

dan kefford
technical lead
yale university