We have an object model where we have a collection of categorized items and need to query using multiple conditions about these items.

E.g. Retrieve all Orders where there is an item of category "shoes" with name = "nike" AND an item of category "missiles" with name = "nike".

We're not really selling shoes or missiles, but that's the basic idea.

We have a graphical query builder that sets up Criterion objects (with aliases) to do these queries, but we are having problems with AND queries and with NOT queries (we think they are skippping NULLs) in these category-based situations.

My questions are: 1) Should we use HQL or somehow leverage SQL rather than use criteria for these complex queries?

2) Are there any tools or existing guis that can help us make a good graphical query builder on top of hibernate?

many thanks....

For complex queries like these, from experience i would suggest using HQL.
As for GUIs, sorry but the closest you can get is using the hibernate plugin for eclipse, where you can incrementally build your query and test each condition.