Parameter IN clause in Hibernate
I want to pass in a collection and use that collection in an IN clause. For example, given a collection of identifiers (ids), I want a collection of products that match those identifiers. Hibernate supports this by allowing the in clause to be a parameter as shown in the example below:
1: Collection<Integer> aIds = Arrays.asList(p1.getId(), p2.getId(), p3.getId());
2: ...
3: String s = "select p from Product p where p.mId in (:aIds)";
4: List<Product> aProducts = mManager.createQuery(s).setParameter("aIds", aIds).getResultList();
In the above example, aIds is the collection parameter used in the IN clause. The SQL generated by Hibernate is:
1: select
2: product0_.PRODUCT_ID as PRODUCT1_0_
3: from
4: PRODUCT product0_
5: where
6: product0_.PRODUCT_ID in (? , ? , ?)
Hibernate allows me to take this one step further. The Parameter IN clause can not only be primitive types but entities as well. For example, given a collection of products, I want a collection of order line items that match those products as shown in the example below:
1: String aQuery = "select o from OrderLineItem o where o.mProduct in (:aProducts)";
2: List<OrderLineItem> aOrderLineItems =
3: mManager.createQuery(aQuery).setParameter("aProducts", aProducts).getResultList();
In the above example, aProducts is the collection parameter used in the IN clause. The SQL generated by Hibernate is:
1: select
2: orderlinei0_.ORDER_LINE_ITEM_ID as ORDER1_2_, orderlinei0_.PRODUCT_ID as PRODUCT2_2_
3: from
4: ORDER_LINE_ITEM orderlinei0_
5: where
6: orderlinei0_.PRODUCT_ID in (? , ?)
Pretty cool!
Comments