Using the Query Tool - Revision history

Sichen at 18:16, 27 August 2010

2010-08-27T18:16:39Z

Sichen at 22:18, 21 April 2009

2009-04-21T22:18:24Z

Sichen at 00:24, 24 February 2009

2009-02-24T00:24:59Z

Sichen: /* How It Works */

2008-07-11T17:24:40Z

‎How It Works

Sichen: /* How It Works */

2008-07-11T17:24:17Z

‎How It Works

Sichen: /* How It Works */

2008-06-24T22:51:48Z

‎How It Works

Sichen at 01:03, 13 June 2008

2008-06-13T01:03:47Z

Sichen at 01:03, 13 June 2008

2008-06-13T01:03:05Z

Sichen at 00:58, 13 June 2008

2008-06-13T00:58:46Z

Sichen: /* How It Works */

2008-06-12T19:05:30Z

‎How It Works

@@ Line 1: / Line 1: @@
-'''IMPORTANT''': This query tool is now deprecated in favor of the [[opentaps Hibernate Integration]]
+Do you ever want to write queries in SQL, because it's second nature to you by now?  Or want to use a stored procedure or [http://www.onjava.com/pub/a/onjava/excerpt/oraclejdbc_19/index.html prepared query to improve performance]?
-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:
+The opentaps query tool was created to meet these needs.   It 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.
-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.
+This feature first became available in the opentaps 1.0 trunk and is available in future versions of opentaps.  For older versions of opentaps, such as 1.0.x, download the [http://sourceforge.net/tracker/index.php?func=detail&aid=1986968&group_id=145855&atid=948961 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 ==

← Older revision		Revision as of 22:18, 21 April 2009
Line 1:		Line 1:
		+	'''IMPORTANT''': This query tool is now deprecated in favor of the [[opentaps Hibernate Integration]]
		+
	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:		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:
	<pre>		<pre>

@@ Line 53: / Line 53: @@
 </pre>
-Because Query implements the standard JDBC PreparedStatement, you can set parameters to your Query as if it were a PreparedStatement:
+You can set parameters to your Query as if it were a PreparedStatement:
 <pre>
   Query q2 = qf.createQuery("SELECT * FROM STATUS_ITEM WHERE STATUS_ID LIKE ? AND STATUS_TYPE_ID LIKE ?");

@@ Line 32: / Line 32: @@
 </pre>
-'''Tip''' We recommend that you capitalize your SQL for compatibility reasons.
+'''Tip We recommend that you capitalize your SQL for compatibility reasons.'''  On Linux, for example, MySQL will not recognize lowercase table names.
 You can then run your query in the following ways:

@@ Line 31: / Line 31: @@
   Query q = qf.createQuery("SELECT * FROM STATUS_ITEM WHERE STATUS_ID LIKE 'INVOICE%'");
 </pre>
 You can then run your query in the following ways:

@@ Line 51: / Line 51: @@
 </pre>
-You can also use prepared queries.  Create the prepared query, then get the statement to set the parameters, and run it:
+Because Query implements the standard JDBC PreparedStatement, you can set parameters to your Query as if it were a PreparedStatement:
 <pre>
   Query q2 = qf.createQuery("SELECT * FROM STATUS_ITEM WHERE STATUS_ID LIKE ? AND STATUS_TYPE_ID LIKE ?");
-  PreparedStatement ps = q2.getStatement();
+  q2.setString(1, "%APPROVE%");
- ps.setString(1, "%APPROVE%");
+  q2.setString(2, "INVOICE%");
   List list5 = q2.list();
 </pre>

@@ Line 4: / Line 4: @@
   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'))
+  WHERE ((OI.STATUS_ID = 'ITEM_APPROVED') OR
   AND OI.PRODUCT_ID IN (SELECT PRODUCT_ID FROM PRODUCT_CATEGORY_MEMBER WHERE PRODUCT_CATEGORY_ID = '100')
 </pre>
-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.  This query could easily take 10 times as many lines of code, and 20 times as much, to write with the entity engine.  Most people seem to give up at this point and revert back to a simple query plus multiple for loops in Java--hardly efficient.
+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.

← Older revision		Revision as of 00:58, 13 June 2008
Line 1:		Line 1:
−	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 the results of your query as a ResultSet, a List of Maps, a List of entity engine GenericValues, or the entity engine's EntityListIterator.	+	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:
		+	<pre>
		+	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 (OH.STATUS_ID NOT IN ('ORDER_CANCELLED', 'ORDER_REJECTED', 'ORDER_COMPLETED') OR (OI.STATUS_ID = 'ITEM_APPROVED'))
		+	AND OI.PRODUCT_ID IN (SELECT PRODUCT_ID FROM PRODUCT_CATEGORY_MEMBER WHERE PRODUCT_CATEGORY_ID = '100')
		+	</pre>
		+
		+	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. This query could easily take 10 times as many lines of code to write with the entity engine.
		+
		+	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 [http://www.onjava.com/pub/a/onjava/excerpt/oraclejdbc_19/index.html this article about JDBC performance tuning].)		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 [http://www.onjava.com/pub/a/onjava/excerpt/oraclejdbc_19/index.html this article about JDBC performance tuning].)