hibernate criteria API bug: unnecessary fetch
I unfortunately came across a bug on hibernate and I think it is pretty match important for anyone who is using hibernate criteria API and caring about performance.
Although this is an important, it is a hidden bug unless you trace you generated SQL for hibernate.
simply the two lines of code below should generate same SQL
Criteria rootCriteria = session.createCriteria(OrderItem.class); Criteria criteria = rootCriteria.createCriteria("order").createCriteria("customer"); List<OrderItem> list1 = criteria.add(Restrictions.eq("city", "city-1")).list(); List<OrderItem> list2 = session.createQuery("select s from OrderItem s where s.order.customer.city=?").setString(0,"city-1").list(); assertEquals(list1, list2);
but when you trace the generated SQL you can see the huge difference. it simply changes fetchmode defaults and ignore any manual fetchmode setting
SQL from criteria API
select this_.id as id1_2_, this_.name as name1_2_, this_.order_id as order5_1_2_, this_.product_id as product6_1_2_, this_.quantity as quantity1_2_, this_.totalPrice as totalPrice1_2_, order1_.id as id0_0_, order1_.customer_id as customer7_0_0_, order1_.deliveryDate as delivery2_0_0_, order1_.name as name0_0_, order1_.orderDate as orderDate0_0_, order1_.priority as priority0_0_, order1_.totalValue as totalValue0_0_, customer2_.id as id3_1_, customer2_.city as city3_1_, customer2_.name as name3_1_ from erp_orderitem this_ inner join erp_order order1_ on this_.order_id=order1_.id inner join erp_customer customer2_ on order1_.customer_id=customer2_.id where customer2_.city=?
SQL generate from hql
select orderitem0_.id as id1_, orderitem0_.name as name1_, orderitem0_.order_id as order5_1_, orderitem0_.product_id as product6_1_, orderitem0_.quantity as quantity1_, orderitem0_.totalPrice as totalPrice1_ from erp_orderitem orderitem0_ cross join erp_order order1_ cross join erp_customer customer2_ where orderitem0_.order_id=order1_.id and order1_.customer_id=customer2_.id and customer2_.city=?
you can find more discussion on
PS:if you use hibernate criteria API , please vote for the bug.