Pizza shop 2: Totaling the JPA Order, use P6Spy to prevent stupidity

I’m digging up the original Pizza Shop project to illustrate another Hibernate gotcha (it’s probably applicable to other ORM libraries as well… admittedly, I haven’t tested it). If you didn’t read the original Pizza Shop post, you can find it here. To review, here is an ERD for the system:

Today I’d like to show how not to total up the cost of the customer order. First, we are going to add P6Spy to the project. P6Spy is an excellent “JDBC wrapper” tool that sits between your application code and your actual JDBC driver. It intercepts your application’s JDBC requests and logs the results. It’s an invaluable tool for optimizing the voodoo out of an ORM tool, and the great thing is that it’s simple to setup:

1.) Change your application’s JDBC driver from whatever it currently is (e.g., org.postgresql.Driver), to the P6Spy JDBC driver, com.p6spy.engine.spy.P6SpyDriver.
2.) Modify the spy.properties file by editing the line that starts with “realdriver=”, changing the value to your actual JDBC driver.
3.) Put spy.properties on your classpath.

That’s it! Now that we have that out of the way, let’s see just how badly we can screw up a simple method in our Model class. The method we are going to add will total up the price of an order. If you look at the ERD above, you can infer that the total cost of an order is the sum of the base price for each pizza, depending on its size, plus the price of each pizza’s individual toppings.

So for our example order, let’s say we have the following:
1 Small Pizza with Pepperoni and Mushroom
1 Medium Pizza with Sausage and Onions
1 Large Pizza with Extra Cheese

You’ll recall that we created a Model class which provides a method for retrieving a List of Pizza objects that are associated with an order ID. The temptation is to create a method which looks something like this:

public BigDecimal getOrderPriceWrong(Integer orderId) {
  BigDecimal result = new BigDecimal(0.0);
  Order order = this.getOrder(orderId);
  for (Pizza pizza : order.getPizzas()) {
    result.add(pizza.getSize().getBasePrice());
    for (Topping topping : pizza.getToppings()) {
      result.add(topping.getPrice());
    }
  }
  return result;
}

Because Hibernate does lazy-fetching, it’s not going to attempt to calculate the total cost with as few queries as possible. Instead, Hibernate’s general philosophy is to defer any queries until it knows that it absolutely needs to do them, substituting empty proxy objects for populated ones until required. Usually this is an optimization, but in this case, Hibernate will do the following queries:

1.) Query to obtain an order object.
2.) One Query for each Pizza, joined to the Size, to obtain the base price.
3.) One Query per Topping, to obtain the topping price.

In all, we get nine individual SQL queries to compute the total price of our single fictional order. The proof is in the P6Spy’s output, spy.log, truncated below:

select order0_.pizza_order_id as pizza1_2_, order0_.version ...
select pizzas0_.pizza_order_id as pizza4_2_, pizzas0_.pizza_id ...
select pizzas0_.pizza_order_id as pizza4_2_, pizzas0_.pizza_id ...
select pizzas0_.pizza_order_id as pizza4_2_, pizzas0_.pizza_id ...
select toppings0_.pizza_id as pizza1_1_, toppings0_.topping_id ...
select toppings0_.pizza_id as pizza1_1_, toppings0_.topping_id ...
select toppings0_.pizza_id as pizza1_1_, toppings0_.topping_id ...
select toppings0_.pizza_id as pizza1_1_, toppings0_.topping_id ...
select toppings0_.pizza_id as pizza1_1_, toppings0_.topping_id ...

If your application is totaling up the price of Pizza orders all day, this can really add up! An alternative approach is to use two named queries to compute the total base price, and total topping price, for an order. For example, we might add the following annotation to the Pizza class:

@NamedQueries(
{
@NamedQuery(
 name="basePrice",
 query="select SUM(p.size.basePrice) " +
       "  from Pizza p " +
       " where p.order.id = :orderId"),
@NamedQuery(
 name="toppingPrice",
 query="select SUM(topping.price) " +
       "  from Pizza p join p.toppings as topping " +
       " where p.order.id = :orderId")
})

Then, you could add the following methods to the OrderDao:

public BigDecimal nullGuard(Query query) {
  BigDecimal result = (BigDecimal)query.getSingleResult();
  return (result == null ? new BigDecimal() : result);
}
 
public BigDecimal getOrderPrice(Integer orderId) {
  Query query1 = getEntityManager().createNamedQuery("basePrice");
  query1.setParameter("orderId",orderId);
  Query query2 = getEntityManager().createNamedQuery("toppingPrice");
  query2.setParameter("orderId", orderId);
  return nullGuard(query1).add(nullGuard(query2));
}

…and call into the DAO from the Model class, thusly:

public BigDecimal getOrderPriceRight(Integer orderId) {
  return this.orderDao.getOrderPrice(orderId);
}

When we run the P6Spy test now, we see a meager two queries where we used to have nine:

select SUM(size1_.pizza_size_base_price) as col_0_0_ from PIZZA ...
select SUM(topping2_.topping_price) as col_0_0_ from PIZZA ...

It pays to periodically use a tool like P6Spy on your application, to look for easy wins like this one!

I’ve included a complete working eclipse project that demonstrates this… it’s actually a tweaked version of the earlier Pizza Shop project. You can get it here.