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

Revision as of 19:26, 13 August 2009

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

Query for CDO object

This is done by using a CDOQuery object that can be obtained by a transaction. As query language "sql" must be used and the select statement must return the CDO ID of the object to fetch in the first column.
For example:

CDOQuery cdoQuery = transaction.createQuery("sql", "SELECT CDO_ID FROM CUSTOMER");

The objects can now be fetched by using:

final List<Product1> products = cdoQuery.getResult(Product1.class);

Query for primitive data type

It is also possible to fetch more primitive types. In that case you have to switch the option "cdoObjectQuery" of the CDOQuery object (see the code below. By default this is set to true to query CDO objects.

CDOQuery cdoQuery = transaction.createQuery("sql", "SELECT CITY FROM REPO1.CUSTOMER");
cdoQuery.setParameter("cdoObjectQuery", false);

The result can now be fetched by using:

List<String> cities = new ArrayList<String>(cdoQuery.getResult(String.class));

Do an asynchronous query

In the previous examples we queried the DB Store backend in a synchronously way (we always waited for the query to be completed). This is done automatically when using the getResult method of CDOQuery. The DB Store backend can also be queried in an asynchronously way. That is done by using the getResultAsync method of CDOQuery:

CDOQuery productQuery = transaction.createQuery("sql", "SELECT CDO_ID FROM PRODUCT1");
CloseableIterator<Product1> iterator = productQuery.getResultAsync(Product1.class);

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

iterator.close();