Using the Query Tool

A common problem with the ofbiz entity engine and delegator is that it could be difficult to construct complex queries. For example, try writing this query using the delegator:

 SELECT OI.PRODUCT_ID, OI.QUANTITY FROM ORDER_ITEM AS OI
 LEFT JOIN ORDER_HEADER AS OH WHERE OI.ORDER_ID = OH.ORDER_ID AND OH.ORDER_TYPE_ID = 'SALES_ORDER' 
 LEFT JOIN PRODUCT AS PR WHERE OI.PRODUCT_ID = PR.PRODUCT_ID
 WHERE ((OI.STATUS_ID = 'ITEM_APPROVED') OR 
        (OI.STATUS_ID <> 'ITEM_CANCELLED' AND 
         OH.STATUS_ID NOT IN ('ORDER_CANCELLED', 'ORDER_REJECTED', 'ORDER_COMPLETED')))
 AND OI.PRODUCT_ID IN (SELECT PRODUCT_ID FROM PRODUCT_CATEGORY_MEMBER WHERE PRODUCT_CATEGORY_ID = '100')

You will find yourself having to declare either a view entity in XML or a DynamicViewEntity in Java, then write the conditions out in reverse Polish notation with EntityOperators and lists of sub-conditions, and then realize that there is actually no support for sub selects. So then you will have to write a query first and use an EntityUtil method to filter out the list of product IDs so that you can create an EntityOperator.IN for it.

To solve this problem, we created an opentaps query tool, which works with the ofbiz delegator but allows you to write your queries in familiar SQL. The query tool could then return the results of your query as a ResultSet, a List of Maps, a List of entity engine GenericValues, or the entity engine's EntityListIterator.

Another benefit of the query tool is that it allows you to use JDBC prepared statements, which is more efficient because the query can be reused for different parameters, rather than a different query being passed to your database each time, which is the case with the delegator. (See this article about JDBC performance tuning.)

This feature has been implemented in the opentaps 1.0 trunk and will be available in future versions of opentaps, such as 1.2/1.4 or beyond. For older versions of opentaps, such as 1.0.x, download the querytool.patch. For older versions of opentaps, such as 0.9.x, you can put the query package classes into another opentaps component such as crmsfa.

How It Works

To use the query tool, create a QueryFactory from the delegator:

  import org.opentaps.common.query.*;
  QueryFactory qf = new QueryFactory(delegator);  // creates QueryFactory from the default "org.ofbiz" group-name in entitygroup.xml
  QueryFactory qf2 = new QueryFactory(delegator, "com.mine");  // in case you had a different group-name

Then, use your QueryFactory to create either a Query. The syntax should be similar to Hibernate querying:

 Query q = qf.createQuery("SELECT * FROM STATUS_ITEM WHERE STATUS_ID LIKE 'INVOICE%'");

You can then run your query in the following ways:

 // run the query and get result set
 q.executeQuery();
 ResultSet rs = q.getResultSet();

 // run the query and get it back as a List of Maps or the first value as a Map
 List list1 = q.list();
 Map map1 = q.firstResult();

 // run the query and get an EntityListIterator.  Specify the entity name and optionally a list of fields
 EntityListIterator eli1 = q.entityListIterator("StatusItem");
 EntityListIterator eli2 = q.entityListIterator("StatusItem", UtilMisc.toMap("statusId", "statusTypeId", "description"));

 // run the query and get a List of GenericValues.  Specify the entity name and optionally a list of fields
 List list3 = q.entitiesList("StatusItem");
 List list4 = q.entitiesList("StatusItem", UtilMisc.toList("statusId", "statusTypeId", "description"));

Because Query implements the standard JDBC PreparedStatement, you can set parameters to your Query as if it were a PreparedStatement:

 Query q2 = qf.createQuery("SELECT * FROM STATUS_ITEM WHERE STATUS_ID LIKE ? AND STATUS_TYPE_ID LIKE ?");
 q2.setString(1, "%APPROVE%");
 q2.setString(2, "INVOICE%");
 List list5 = q2.list();

Technical Notes

When the Query is first instantiated, a PreparedStatement is instantiated, and on the first call to a method which would cause the query to be executed, such as .list(), the PreparedStatement is called, a ResultSet is obtained, converted to a List, and then closed. Subsequent calls to .list() only return the previously stored list and does not cause another query to be run. If you need to run the query again, call .clearQueryResults();

Converting the query results to GenericValues/GenericEntities requires the use of the ofbiz entity engine's EntityListIterator. If you use the .entityListIterator(..) method, the EntityListIterator will be returned to you, and it will handle the closing of the connection with its own .close() method. If you use the .entitiesList(..) methods, the EntityListIterator and the ResultSet will be automatically closed.

The ResultSet is automatically closed on finalize().

The Query and QueryFactory throw a QueryException. If GenericValues/GenericEntities are involved, they also will throw the GenericEntityException.