There are many type of queries that must be done on domain objects, this document covers all the options and possibilities that can be used to write these Queries.

Contents

1. Query Construct
2. Examples
   1. Query Type
   2. Basic query
   3. Dual field query
   4. Or/And clauses
   5. Inner join
   6. Nested inner joins
   7. Query with OrderBy clause
3. Notes

Querying the database involves creating a UOW (Unit Of Work) object, a criteria object with appropriate code for the query, and finally invoking the query method of the UOW passing the criteria. The persistence engine will transparently generate the appropriate SQL, and then return the ResultSet contents wrapped inside domain objects. The postLoad trigger of the domain object will be invoked by the persistence engine, before returning the object to the client.

// create a UnitOfWork
UOW uow = new UOW();
// create a Criteria object and specify the query
Criteria c = new Criteria();
...
...
...
// perform the query
Collection results = uow.query(c)
// iterate through the results
for (Iterator itr = results.iterator(); itr.hasNext(); ) {
    SampleDomain domainObject = (SampleDomain) itr.next();
    ...
    ...
}
// close the UnitOfWork
uow.rollback();

1. Basic query

   SQL Query
   Select * from item where part_no = 'P1' and serial is not null

   Jaffa Query
   Criteria c = new Criteria(); c.setTable(ItemMeta.getName()); c.addCriteria(ItemMeta.PART_NO, "P1"); c.addCriteria(ItemMeta.SERIAL, Criteria.RELATIONAL_IS_NOT_NULL);

2. Dual field query

   SQL Query
   Select * from item where part_no = 'P1' and item_id = received_item_id and status_2 != status_3

   Jaffa Query
   Criteria c = new Criteria(); c.setTable(ItemMeta.getName()); c.addCriteria(ItemMeta.PART_NO, "P1"); c.addDualCriteria(ItemMeta.ITEM_ID, ItemMeta.RECEIVED_ITEM_ID); c.addDualCriteria(ItemMeta.STATUS2, Criteria.RELATIONAL_NOT_EQUALS, ItemMeta.STATUS3);

3. Or/And clauses

   SQL Query
   Select * from item where ((status_1 is null or status_2 is null or status_3 = 'A') and condition like '%NE%') or (serial is not null)

   Jaffa Query

   AtomicCriteria ac1 = new AtomicCriteria(); ac1.addCriteria(ItemMeta.STATUS1, Criteria.RELATIONAL_IS_NULL); ac1.addOrCriteria(ItemMeta.STATUS2, Criteria.RELATIONAL_IS_NULL); ac1.addOrCriteria(ItemMeta.STATUS3, "A"); AtomicCriteria ac2 = new AtomicCriteria(); ac2.addAtomic(ac1); ac2.addCriteria(ItemMeta.CONDITION, Criteria.RELATIONAL_LIKE, "NE"); Criteria c = new Criteria(); c.setTable(ItemMeta.getName()); c.addAtomic(ac2); c.addOrCriteria(ItemMeta.SERIAL, Criteria. RELATIONAL_IS_NOT_NULL);

4. Inner join

   SQL Query
   Select distinct T1.item_id, T1.part_no ... from item "T1", part "T2" where T2.part_no = T1.part_no and T2.description ='D1' and T1.serial is null

   Jaffa Query
   Criteria c1 = new Criteria(); c1.setTable(PartMeta.getName()); c1.addInnerCriteria(PartMeta.PART_NO, ItemMeta.PART_NO); c1.addCriteria(PartMeta.DESCRIPTION, "D1"); Criteria c = new Criteria(); c.setTable(ItemMeta.getName()); c.addAggregate(c1); c.addCriteria(ItemMeta.SERIAL, Criteria.RELATIONAL_IS_ NULL);

5. Nested inner joins

   SQL Query
   Select distinct T1.item_id, T1.part_no ... from item "T1", part "T2", syci "T3" where T2.part_no = T1.part_no and T3.category_instrument = T2.category_instrument and T3.category_instrument like '%Z%' and T3.support_equip_b = 'T'

   Jaffa Query

   Criteria c1 = new Criteria(); c1.setTable(SyciMeta.getName()); c1.addInnerCriteria(SyciMeta.CATEGORY_INSTRUMENT, PartMeta.CATEGORY_INSTRUMENT); c1.addCriteria(SyciMeta.CATEGORY_INSTRUMENT, Criteria.RELATIONAL_LIKE, "Z"); c1.addCriteria(SyciMeta.SUPPORT_EQUIP, Boolean.TRUE); Criteria c2 = new Criteria(); c2.setTable(PartMeta.getName()); c2.addInnerCriteria(PartMeta.PART_NO, ItemMeta.PART_NO); c2.addAggregate(c1); Criteria c = new Criteria(); c.setTable(ItemMeta.getName());c.addAggregate(c2);

6. Query with OrderBy clause

   SQL Query
   Select * from item where part_no = 'P1' order by condition, serial desc

   Jaffa Query
   Criteria c = new Criteria(); c.setTable(ItemMeta.getName()); c.addCriteria(ItemMeta.PART_NO, "P1"); c.addOrderBy(ItemMeta.CONDITION, Criteria.ORDER_BY_ASC); c.addOrderBy(ItemMeta.SERIAL, Criteria.ORDER_BY_DESC);

Functionality can vary between Persistence Engine implementations, because of the underlying capability of the persistence layer it uses. The intent is that the Criteria object is a general interface for any engine. It is the job of each engine to translate the query into something it supports, like SQL, OQL, EQL, etc.

For this reason, make sure you use an engine that supports the features you want, for example don't use an EQL engine based on CMP Entity Beans on EJB Spec 2.0, and expect the 'OrderBy' clause to work, unless the engine explicitly provides the ordering capability itself.

If an engine doesn't support a feature, it is up to the implementation of that engine as to whether it just ignores the request for that feature, or whether it throws an exception.