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

https://forums.hibernate.org/viewtopic.php?f=1&t=962905

http://opensource.atlassian.com/projects/hibernate/browse/HHH-3524

PS:if you use hibernate criteria API , please vote for the bug.

Comment are closed.