Difference between revisions of "Using the Query Tool"

From Opentaps Wiki
Jump to navigationJump to search
Line 1: Line 1:
−
This feature is under development and is not available for general use yet.
 
−
 
 
A common problem with the ofbiz entity engine and delegator is that it could be difficult to construct complex queries.  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 either lists or EntityListIterator of GenericEntity or simply lists of maps.
 
A common problem with the ofbiz entity engine and delegator is that it could be difficult to construct complex queries.  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 either lists or EntityListIterator of GenericEntity or simply lists of maps.
  
Line 9: Line 7:
 
To use the query tool, create a QueryFactory from the delegator:
 
To use the query tool, create a QueryFactory from the delegator:
 
<pre>
 
<pre>
−
   String entityGroupName = "org.ofbiz";
+
   import org.opentaps.common.query.*;
−
   QueryFactory qf = new QueryFactory(delegator, entityGroupName);
+
   QueryFactory qf = new QueryFactory(delegator);
 
</pre>
 
</pre>
  
−
Then, use your QueryFactory to create either an EntityQuery or a Query.  The syntax should be similar to [http://www.hibernate.org/hib_docs/reference/en/html/objectstate.html#objectstate-querying Hibernate querying].  The EntityQuery can be used to cast the result of your query as lists or an EntityListIterator of GenericEntity:
+
Then, use your QueryFactory to create either a Query.  The syntax should be similar to [http://www.hibernate.org/hib_docs/reference/en/html/objectstate.html#objectstate-querying Hibernate querying]:
 
<pre>
 
<pre>
−
  EntityQuery query1 = qf.createEntityQuery("StatusItem", "SELECT * FROM STATUS_ITEM WHERE STATUS_TYPE_ID = 'INVOICE_STATUS'");
+
Query q = qf.createQuery("SELECT * FROM STATUS_ITEM WHERE STATUS_ID LIKE 'INVOICE%'");
−
  List list1 = query1.list();
 
−
  EntityListIterator eli = query1.eli();
 
−
  Iterator it2 = query1.it();
 
 
</pre>
 
</pre>
  
−
The Query object can be used to pass prepared statements to your database through the delegator's connection and return either a list, an iterator, or result set:
+
You can then run your query in the following ways:
 
<pre>
 
<pre>
−
  Query query2 = qf.createQuery("StatusItem", "SELECT * FROM STATUS_ITEM WHERE STATUS_TYPE_ID = ?");
+
// run the query and get result set
−
  query2.setString(1, "INVOICE_STATUS");
+
q.executeQuery();
−
  List list2 = query2.list();
+
ResultSet rs = q.getResultSet();
−
  Iterator it2 = query2.iterator();
+
 
−
  ResultSet rs = query2.resultSet();
+
// run the query and get it back as a List or the first value as a Map
−
  ResultSetMetaData rsm = query2.resultSetMetaData();
+
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"));
 
</pre>
 
</pre>
  
−
Every object in the List returned from a Query will be a Map.
+
You can also add prepared queries, get the statement, and run it:
 +
<pre>
 +
Query q2 = qf.createQuery("SELECT * FROM STATUS_ITEM WHERE STATUS_ID LIKE ? AND STATUS_TYPE_ID LIKE ?");
 +
PreparedStatement ps = q2.getStatement();
 +
ps.setString(1, "%APPROVE%");
 +
ps.setString(2, "INVOICE%");
 +
List list5 = q2.list();
 +
</pre>

Revision as of 17:22, 12 June 2008

A common problem with the ofbiz entity engine and delegator is that it could be difficult to construct complex queries. 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 either lists or EntityListIterator of GenericEntity or simply lists of maps.

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.)

How It Works

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

  import org.opentaps.common.query.*;
  QueryFactory qf = new QueryFactory(delegator);

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 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"));

You can also add prepared queries, get the statement, and run it: 

 Query q2 = qf.createQuery("SELECT * FROM STATUS_ITEM WHERE STATUS_ID LIKE ? AND STATUS_TYPE_ID LIKE ?");
 PreparedStatement ps = q2.getStatement();
 ps.setString(1, "%APPROVE%");
 ps.setString(2, "INVOICE%");
 List list5 = q2.list();
Retrieved from "https://docs.opentaps.org/docs/index.php?title=Using_the_Query_Tool&oldid=3036"