SELECT OBJECT(o) FROM Order oFind all orders that need to be shipped to Colorado:
SELECT OBJECT(o) FROM Order o WHERE o.shipping_address.state = 'CO'Find all states for which there are orders:
SELECT DISTINCT o.shipping_address.state FROM Order oFind all orders that have line items:
SELECT DISTINCT OBJECT(o) FROM Order o, IN(o.lineItems) lThis query can also be written as:
SELECT OBJECT(o) FROM Order o WHERE o.lineItems IS NOT EMPTYFind all orders that have no line items:
SELECT OBJECT(o) FROM Order o WHERE o.lineItems IS EMPTYFind all pending orders:
SELECT DISTINCT OBJECT(o) FROM Order o, IN(o.lineItems) l WHERE l.shipped = FALSEFind all orders in which the shipping address differs from the billing address. This example assumes that the Bean Provider uses two distinct entity beans to designate shipping and billing addresses:
SELECT OBJECT(o) FROM Order o WHERE NOT (o.shipping_address.state = o.billing_address.state AND o.shipping_address.city = o.billing_address.city AND o.shipping_address.street = o.billing_address.street)If the Bean Provider uses a single entity bean in two different relationships for both the shipping address and the billing address, the above expression can be rewritten as:
SELECT OBJECT(o) FROM Order o WHERE o.shipping_address <> o.billing_addressFind all orders for a book titled 'Head First EJB':
SELECT DISTINCT OBJECT(o) FROM Order o, IN(o.lineItems) l WHERE l.product.type = 'book' AND l.product.name = 'Head First EJB'The following query finds the orders for a product whose name is designated by an input parameter (the input parameter must be of the type of the product name, i.e., a String):
SELECT DISTINCT OBJECT(o) FROM Order o, IN(o.lineItems) l WHERE l.product.name = ?1The following EJB QL query selects the names of all products that have been ordered. It illustrates the selection of values other than entity beans, therefore it can be used only for SELECT methods, not for FINDER methods:
SELECT DISTINCT l.product.name FROM Order o, IN(o.lineItems) lThe following query finds the names of all products in the order specified by a particular order number.
SELECT l.product.name FROM Order o, IN(o.lineItems) l WHERE o.ordernumber = ?1The following query returns the names of all the cities of the shipping addresses of all orders. The result type of the SELECT method, which is either java.util.Collection or java.util.Set, determines whether the query may return duplicate city names.
SELECT o.shipping_address.city FROM Order o
Posted on: April 18, 2011 If you enjoyed this post then why not add us on Google+? Add us to your Circles