I'm trying to retrieve a large list of Product, each of which is associated with 0..n ProductHistory that represent a historical price and date for the product.

I want each Products List<ProductHistory> to be sorted by date when it is loaded, so I've added a @OrderBy("datePosted DESC") annotation on the product side of the association. This works like I would expect if the ProductHistory use FetchMode.LAZY, but when I change to FetchMode.EAGER(need to do this for performance reasons; there's thousands of products and a separate select for each product is taking several minutes) there's two problems.

-A separate product is created for each history item(was able to work around this by adding setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY), see below
-The Products are always ordered by the ProductHistory's posted date

Does anyone know of a way I can avoid these problems without incurring the performance hit of loading the List<ProductHistory> with seperate selects?


Hibernate version: 3.26GA

Mapping documents:

PRODUCT
import javax.persistence.OneToMany;
import javax.persistence.OrderBy;
public class Product {
private BigDecimal currentPrice;
private Date datePosted;
...
@OneToMany(cascade={CascadeType.ALL}, mappedBy="product", fetch=FetchType.EAGER)
@OrderBy("datePosted DESC")
private List<ProductHistory> history;
}

PRODUCT HISTORY
import javax.persistence.ManyToOne;
public class ProductHistory {
private BigDecimal oldPrice;
private Date datePosted;
...
@ManyToOne
@JoinColumn(name="PRODUCT_ID")
private Product product;
}

Code between sessionFactory.openSession() and session.close():

Session hibernateSession = (Session)getEntityManager().getDelegate();
Criteria productCriteria = hibernateSession.createCriteria(Product.class);
productCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
List<Product> results = crit.list();

Name and version of the database you are using: Oracle

The generated SQL (show_sql=true):

select this_.id as id2_1_, this_.DATE_POSTED as DATE2_2_1_, this_.PRICE as PRICE2_1_,
history2_.PRODUCT_ID as PRODUCT4_3_, history2_.id as id3_, history2_.id as id9_0_, history2_.PRODUCT_ID as PRODUCT4_9_0_, history2_.DATE_POSTED as DATE2_9_0_, history2_.PRICE as PRICE9_0_
from PRODUCT this_, PRODUCT_HISTORY history2_
where this_.id=history2_.PRODUCT_ID(+)
order by history2_.DATE_POSTED DESC