Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
CDO/Hibernate Store/HQL
Contents
The CDO Hibernate Store supports HQL as the query language. For a detailed description the HQL syntax visit the HQL Documentation.
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 name. 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);
Note: there is one special parameter which has a special meaning: firstResult (see below, paged queries). This parameter should not be used explicitly in a query.
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.
Sorting is also supported:
CDOQuery customerQuery = transaction.createQuery("hql", "from Customer order by name"); List<Customer> customers = customerQuery.getResult(Customer.class);
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");
Joins, Associations and Expressions
Hibernate has a set of very powerful expressions which can be used in the where clause. The expression functions support string operations, date/time handling and advanced collection functions (elements, maxelement, minelement, etc.).
Here is an example which performs a join and uses the elements functions. It returns all sales order header which have a sales order line with a specific product. The elements method is used to ensure that a sales order header is only returned once (which could also be done by a group by).
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);
Query for primitive values
Most examples above queried for CDO objects, you can also query for primitive values.
The following query shows a combination of different values returned.
CDOQuery query = transaction.createQuery("hql", "select od, od.order, od.product.vat, od.price from OrderDetail as od where" + " od.product.vat=:vat"); query.setParameter("vat", VAT.VAT15); for (Object[] values : query.getResult(Object[].class)) { assertTrue(values[0] instanceof OrderDetail); assertTrue(values[1] instanceof SalesOrder); assertTrue(values[2] instanceof VAT); assertEquals(VAT.VAT15, values[2]); assertTrue(values[3] instanceof Float); final SalesOrder order = (SalesOrder)values[1]; assertTrue(order.getOrderDetails().contains(values[0])); }
The code snippet also shows the test part (the asserts) to illustrate what is returned. The result is a List with Object[], each object array contains the values in the same order/type as specified in the select clause.
Paged Queries
When showing large dataset in a grid it is often required to load the data in pages. This is supported by the Hibernate store. Here is an example:
CDOQuery productQuery = transaction.createQuery("hql", "from Product"); productQuery.setMaxResults(pageSize); // NOTE: firstResult is a special parameter for the hql query language productQuery.setParameter("firstResult", page * pageSize); List<Product> queriedProducts = productQuery.getResult(Product.class);
Some remarks:
- the page size is set by calling setMaxResults on the CDOQuery object
- the firstResult parameter is a special parameter used to denote the row number from which to start querying. This is also the reason that this parameter can not be used as a query parameter inside the query.
Asynchronous queries
The CDO query api also supports asynchronous queries. This is also supported by the Hibernate Store:
CDOQuery productQuery = transaction.createQuery("hql", "from Product"); final CloseableIterator<Product> iterator = productQuery.getResultAsync(Product.class);