I have the following scenario that I am trying to solve using Hibernate as the persistence provider, Hibernate Criteria API to build my queries.

I have two classes called Order and Item with 1:n unidirectional association from Order->Item. Both are JPA/Hibernate entities and are mapped to ORDER and ITEM table in the database respectively.

In my application, I have a search screen which allows the user to put an item price in a text field. The search results should only include those Orders that have at least one item with price < specified price. Another requirement is that since an Order can have multiple items, it is possible that some items on the Order can have price > specified price. In that case, the Order should be selected but its items collection should not contain all items associated with that order but those items with price < specified price.

For example:

I have two orders Order1: Item 1: price 100, Item 2: price 200.
Order 2: Item 3: price 50, Item 4 price 40

Now, if the query says price < 150, then, the result set should be:
Order 1 with its items collection only containing Item 1. Item 2 is taken out since its price is 200 which is > 150.
Order 2 with its items collection containing both Item 3 and Item 4.

I am using HIbernate Criteria API to build my search query. I am setting the fetch type for the items association on Order to EAGER. WHen I execute the crieria.list() (criteria already has the price < 150 criterion added to it), I get following queries:

select order.id, item.... from order, item where order.order_id = item.order_id and item.price < 150.

This query returns the desired result set. Unfortunately, Hibernate then runs another query in addition to the query above where it gets:

select item...from item where item.order_id=?
and so, it puts the 2nd item (Item 2) also in Order 1.

I am trying to understand why Hibernate is executing the 2nd query and thus, ends up loading the second Item (Item 2) on Order 1. Could this be because of the fetch mode?

In a way, I am trying to use Hibernate Criteria API to do a two-level filtering: one on the Order objects and then, on individual Items within the Order object.

I am thinking that Hibernate Criteria API does not apply to two-level filtering and so, I will have to use a Hibernate filter after criteria.list() to filter out Item 2 from Order 1.

Put the filter condition on your 1 to many as well. The class level filter is only applying to the initial select. The collection level filter will then apply when hibernate tries to load the items.