Difference between revisions of "Query DB Store by using SQL"

Revision as of 00:34, 22 January 2013

It is possible to query the DB Store backend for CDO objects and also for more primitive data types.

Query for CDOObjects

This is done by using a CDOQuery object that can be obtained from a CDOView or CDOTransaction. The query language is "sql" and the select statement must return the CDOID(s) of the CDOObject(s) to fetch in the first column.

For example:

CDOQuery query = view.createQuery("sql", "SELECT cdo_id FROM Customer");

The objects can now be fetched by using:

List<Customer> customers = query.getResult();

The returned objects are valid in the context of the CDOView or CDOTransaction that the query has been created from and can be navigated with normal EMF APIs.

Note: Until Bug 397948 is fixed the result list may need to be copied into an ArrayList in rare cases such as:

List<Cutomer> result = query.getResult();
myObject.getCustomers().removeAll(new ArrayList<Customer>(result));

Query for Primitive Data

It is also possible to fetch more primitive types. In that case you have to set the "cdoObjectQuery" query parameter to "false" (see the code below). By default this is set to true to query CDO objects.

CDOQuery query = view.createQuery("sql", "SELECT city FROM Customer");
query.setParameter("cdoObjectQuery", false);

The result can now be fetched by using:

List<String> cities = query.getResult();

Query Asynchronously

In the previous examples we queried the DBStore backend in a synchronous way, i.e. query.getResult() blocked until the query was completed. The DBStore backend can also be queried in an asynchronous way. That is done by using the getResultAsync() method of CDOQuery:

CDOQuery query = view.createQuery("sql", "SELECT cdo_id FROM Customer");
CloseableIterator<Customer> iterator = query.getResultAsync(Customer.class);

The iterator can now be iterated and must be closed when finished:

iterator.close();