Difference between revisions of "CDO/Hibernate Store/HQL"

Revision as of 12:25, 21 January 2010

The CDO Hibernate Store supports HQL as the query language. For a detailed description the HQL syntax visit the documentation here.

This page shows examples of different HQL queries to give a feel for what the possibilities are to use HQL directly from the client. As far as is known there are no limits in using HQL on the client.

Note that HQL is supported not the Hibernate Criteria api.

Example projects

The HQL queries discussed here are available in the example project: org.eclipse.emf.cdo.examples.hibernate.client, in the HibernateQueryTest class.

The model which is used here is available as an example project: org.eclipse.emf.cdo.examples.company.

The download & install page describes how/where to find these example projects.

Some simple queries

Let's start with a simple query to also introduce the client side query api. The following code snippet shows how to retrieve all instances of the Product from the backend:

CDOSession session = openSession();
CDOTransaction transaction = session.openTransaction();
 
CDOQuery cdoQuery = transaction.createQuery("hql", "from Product"); //$NON-NLS-1$  //$NON-NLS-2$
List<Product> products = cdoQuery.getResult(Product.class);
transaction.commit();

This snippet shows a number of things:

• querying is done through a transaction and its createQuery method.
• the HQL query uses the name: Product to query for products. This is the entity name. The entity name is normally the same as the EClass. You can control the entity name through an @Entity annotation or through a so-called entity naming strategy.
• the createQuery method expects two parameters: 1) the query language (for the hibernate store always "hql" has to be used) and the query itself.
• the createQuery method returns a CDOQuery object
• the CDOQuery object has a getResult method which always returns a List, it is a generic parameterized method so the result is in the correct type (by passing the Product.class)

This was the first step, let's now introduce the usage of parameters (now without the open session and open transaction code).

The next query shows how to do use parameters in a query:

CDOQuery cdoQuery = transaction.createQuery("hql", "from Product where name=:name");
cdoQuery.setParameter("name", "" + 1);

Parameters can be of any type, here an enum passed as a parameter:

CDOQuery cdoQuery = transaction.createQuery("hql", "from Product where vat=:vat");
cdoQuery.setParameter("vat", VAT.VAT15);

But you can also use CDO objects as a parameter (this query is explained in more detail later also):

CDOQuery orderQuery = transaction.createQuery("hql",
    "select so from SalesOrder so, OrderDetail od where so.customer=:customer and od in elements(so.orderDetails) and od.product=:product");
orderQuery.setParameter("customer", customer);
orderQuery.setParameter("product", product);

or if you really want to use the id itself (but you need to do a little bit more work):

CDOQuery orderQuery = transaction.createQuery("hql",
            "select so from SalesOrder so where so.customer.id=:customerId");
CDOObject cdoObject = (CDOObject)customer;
CDOID cdoID = cdoObject.cdoID();
orderQuery.setParameter("customerId", getIdValue(cdoID));

As you can see a getIdValue method is called. If you want to use the id itself you need to pass in the id value (so the long or string which denotes the real id value). This is not difficult, see the source code in the example project for the implementation of the method.

Queries with functions (sum, max, etc.)

HQL also suppports aggregate functions in queries. Here are some example using the CDO query api:

CDOQuery cdoQuery = transaction.createQuery("hql", "select count(*) from Product");
final List<Long> counts = cdoQuery.getResult(Long.class);

Here an example with a sum and a group by:

CDOQuery cdoQuery = transaction.createQuery("hql",
          "select sum(od.price) from SalesOrder so, OrderDetail od where od.order=so group by so.id");

Wikis: CDO | Net4j | EMF | Eclipse