In one-to-many or many-to-many relationship, we often configure Hibernate to lazily fetch the children collection for performance reasons by reducing the database join operation and the amount of the data been pulled and transmitted from database to application. The application, if needs the child entities, can query the database again. This is the typical n+1 operation in Hibernation. When the data size is small, you won’t notice too much performance problem. However, this can impose some undesired issue with the O(n2) operations as data grows.
Fortunately Hibernate provides a few ways to fetch those lazy entities in much efficient way, with Criteria, HQL or native SQL. You can refer the official document for comprehensive guide.
This post will simply provides two examples, using HQL and native SQL. The entity in the following sections means Java beans mapped by Hibernate with @Entity or xml configuration. The non-entity means plain Java Bean without Hibernate awareness.
Example 1: Using HQL to fetch parents and their children into entities
This example fetches data into a Hibernate aware entity bean Order, which has a set of OrderItems.
public List<Order> getOrdersByItemName(String accountNumber,String itemName) {
Query query = currentSession.createQuery(
"from Order o "
+ "join fetch o.orderItems i "
+ "where o.accountNumber = :accountNumber and i.name = :itemName"
+ "order by o.id asc"
).setParameter("accountNumber", accountNumber)
.setParameter("itemName", itemName)
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
return query.list();
}
Example 2: Using native HQL to fetch parents and their children into non-entities
This examples converts the query results into a non-entity bean FlattenedOrder. It demonstrates how complicated the native query can go (using SqlServer). It also shows how to map two column values to two user defined Enums OrderSource and ItemSize.
public List<FlattenedOrder> getFlattenedOrderByItemName(String accountNumber, String itemName) {
String queryString =
"select distinct o.orderIdStr as orderId, o.orderSource, o.orderDate,o.expiryDate "
+ "CASE WHEN o.expiryDate <= GETDATE() THEN 0 ELSE DATEDIFF(day, o.orderDate, o.expiryDate) END as daysExpiryIn, "
+ "i.name as itemName, i.size as itemSize, "
+ "(select sum(innerI.quantity) from dbo.OrderItem innerI join dbo.Order innerO on innerI.orderId=innerO.id where innerI.id=i.id) as total, "
+ "from dbo.Order as o "
+ "join OrderItem as i on o.id=j.orderId "
+ "where o.accountNumber=:accountNumber and i.name = : itemName";
Properties orderSourceParams = new Properties();
orderSourceParams.put("enumClass", "ca.zl.OrderSource");
orderSourceParams.put("type", "12"); /*EnumType.STRING type = 12 */
Type orderSourceEnumType = new TypeLocatorImpl(new TypeResolver()).custom(EnumType.class, orderSourceParams);
Properties orderItemSizeProps = new Properties();
orderItemSizeProps.put("enumClass", "ca.zl.ItemSize");
orderItemSizeProps.put("type", "12"); /*EnumType.STRING type = 12 */
Type itemSizeEnumType= new TypeLocatorImpl(new TypeResolver()).custom(EnumType.class, orderItemSizeProps);
SQLQuery query = currentSession.createSQLQuery(queryString);
query.setParameter("accountNumber", accountNumber);
query.setParameter("itemName", itemName);
query.addScalar("orderId", new StringType())
.addScalar("orderSource", orderSourceEnumType)
.addScalar("orderDate", new DateType())
.addScalar("expiryDate", new DateType())
.addScalar("daysExpiryIn", new IntegerType())
.addScalar("itemName", new StringType())
.addScalar("itemSize", itemSizeEnumType)
.addScalar("total", new IntegerType())
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.setResultTransformer(Transformers.aliasToBean(FlattenedOrder.class));
return query.list();
SW Notes 