EJB3QL - Queries - Part II

Hi all, since I was so busy it has been a long time, too long, since my last post. Last month I visited our headquarters at San-Diego, and I hoped that on the flight I will write a post, or two, or three (well it takes more than 20 hours to get from Tel-Aviv to San-Diego...). But it was too dense, uncomfortable, and noisy to write anything. Anyhow, I want to write more (here is the first part) about EJB3 queries.

Environment

Same as previous entry:

• Hibernate Entity Manager – 3.2.1.GA
• Hibernate core – 3.2.1.GA
• Hibernate annotations- 3.2.1.GA
• Database – I build this test using Java 6, so I used the embedded Java DB (Derby)

Select By a Collection Member

In many cases we want to fetch an entity which references a collection of other entities based on a referenced entity. For example if a customer has set of orders we might want to fetch all of the customers which ordered a specific item. It means that for an instance of item we have to fetch its orders, and for the orders we have to fetch the owning customers. One “naive”, and wrong, way to do it is something like that:

 public List getCustomersForItem(Item i) {
   Item i2 =   entityManager.merge(i); //First DB hit
   List lo =    i2.getOtders();  //Second database hit (assume lazy)
   List result = new LinkedList();
   for (Order o : lo) {
      result.add(o.getCustomer); //Third DB hit * lo.size() times! (assume proxies)
   }
   return result;
}

If the reference from item to order is lazy, and the reference from order to its customer uses a proxy the sample above works, but it won't perform well...On the other hand usually a mapping from order to item will be a unidirectional many to many association from orders to items, so lucky for us the code above won't compile (i2.getOrders() will fail on 'no such method'). An EJB3QL which can perform the above in one database hit is:

select c from Customer c join c.orders  o where :i member of o.items

See the usage of the member of operator, and the join between the customer and it's orders. This query will hit the database only once, generating the following SQL:

select customer0_.id as id12_, customer0_.NAME as NAME12_, customer0_.birthDate as birthDate12_ 
from CUSTOMERS customer0_ inner join ORDERS orders1_ on customer0_.id=orders1_.customer_id where ? in (select 
item3_.id from ORDER_ITEMS items2_, ITEMS item3_ where orders1_.id=items2_.ORDER_ID and 
items2_.ITEM_ID=item3_.id)

One more thing, the query is in the form of “select c from Customer c join c.orders.....” - the join is important here. The query “select c from Customer c , Order o where :i member of o.items” (without the join keyword) is a different one!

Select By Collection Size

Sometimes we want to fetch an entity which references an collection of other entities, based on the collection's size (for example, get all customers with more than 2 orders). That's easy:

select c from Customer c where SIZE(c.orders) > 2

We should notice the usage of the SIZE operator which return the number of element in the collection. We can also use it in the select clause ('select SIZE(c.order) from Customer c'). The SQL generated by the above query is

select customer0_.id as id12_, customer0_.NAME as NAME12_, customer0_.birthDate as birthDate12_ 
from CUSTOMERS customer0_ where (select count(orders1_.customer_id) from ORDERS orders1_ where 
customer0_.id=orders1_.customer_id)>2

If we want to access the orders collection (which is mapped lazy) Hibernate will hit the database again for each collection we access (as explained in here), again we will have to use the 'join fetch':

select c from Customer c join fetch c.orders where SIZE(c.orders) > 2

If we want to check for an empty collection/or not empty we don't need to compare the SIZE() to zero. The EJB3QL supports the “is [not] empty” syntax, for example:

select c from Customer c where c.orders is empty

Some Time and Date EJB3QL Functions

One more thing which is nice to know is that the EJB3QL defines the next three functions for returning time and dates information:

• CURRENT_DATE – Current date, of type java.sql.Date
• CURRENT_TIME – Current time, of type java.sql.Time. And
• CURRENT_TIMESTAMP – Current timestamp, of type java.sql.Timestamp

For example:

        select CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP from Customer

Please notice: I had recover this blog post from my old blog at http://www.jroller.com/eyallupu since jroller.com is no longer available.  As such the styling might be a bit wobbly ... If something seems 'too broken' please contact me and I'll adjust