-
1. Re: findAll and Collection result optimization
kabirkhan Jul 14, 2004 9:53 AM (in response to cchris)Use jboss-ql in your query def in the jbosscmp-jdbc.xml file for added functionality
-
2. Re: findAll and Collection result optimization
cchris Jul 14, 2004 1:10 PM (in response to cchris)In jbosscmp-jdbc.xml, I added the following query :
<jbosscmp-jdbc>
...
<enterprise-beans>
...
<query-method>
<method-name>findAll</method-name>
<method-params>
<method-param>int</method-param>
<method-param>int</method-param>
</method-params>
</query-method>
<jboss-ql>SELECT DISTINCT OBJECT(a) FROM App a LIMIT ?1,?2</jboss-ql>
</enterprise-beans>
</jbosscmp-jdbc>
I get the following exception :
org.jboss.deployment.DeploymentException: Error compiling JBossQL statement 'SELECT DISTINCT OBJECT(a) FROM App a LIMIT ?1,?2'; - nested throwable: (org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "," at line 1, column 52.
Was expecting:
)
Am I doing something wrong ? It seems this jboss ql query cannot be parsed because of the comma between the 2 parameters (this is the correct mysql syntax).
Thanks
Chris -
3. Re: findAll and Collection result optimization
aloubyansky Jul 15, 2004 2:30 AM (in response to cchris)The syntax is OFFSET X LIMIT Y.
Also for future reference http://www.jboss.org/wiki/Wiki.jsp?page=LazyResultSetLoading -
4. Re: findAll and Collection result optimization
cchris Jul 15, 2004 12:42 PM (in response to cchris)
JBoss distribution
- According to your article (http://www.jboss.org/wiki/Wiki.jsp?page=LazyResultSetLoading),
lazy ResultSet loading feature is available since 3.2.6rc1.
The latest stable production download available from http://www.jboss.org/downloads/ is
jboss-3.2.5.zip (June 25, 2004). Could you please tell me where 3.2.6rc1 could be found?
- In the latest dtd available from http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_2.dtd, there is not
such 'lazy-resultset-loading' element for a query parent.
<!ELEMENT query (description?, query-method,
(jboss-ql | dynamic-ql | declared-sql)?, read-ahead?, ql-compiler?)>
May be the new dtd release will come together with JBoss 3.2.6rc1
MySQL syntax comment
According to the mysql documentation, syntax is :
SELECT
...
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
...
MySQL allows both of the following syntaxes :
SELECT * FROM table LIMIT my_offset, my_lim ;
or
SELECT * FROM table LIMIT my_lim OFFSET my_offset;
Using JBoss3.2.4 and JBoss3.2.5, what works is, for example :
<query-method>
<method-name>findAll</method-name>
<method-params />
</query-method>
<jboss-ql>SELECT DISTINCT OBJECT(a) FROM App a OFFSET 0 LIMIT 2 </jboss-ql>
But it's not really a lazy mode yet, just a page size limitation.
Thanks for your help.
Chris -
5. Re: findAll and Collection result optimization
aloubyansky Jul 16, 2004 2:35 AM (in response to cchris)You could check it out from CVS -r Branch_3_2
-
6. Re: findAll and Collection result optimization
cchris Jul 18, 2004 12:02 PM (in response to cchris)I'm still playing tester for the CMP lazy mode using JBoss 3.2.6rc1.
I used a very simple test query :
<query-method>
<method-name>findAll</method-name>
<method-params />
</query-method>
<jboss-ql><![CDATA[SELECT OBJECT(a) FROM Applica a]]></jboss-ql>
<lazy-resultset-loading>true</lazy-resultset-loading>
I get the following exception when I try to parse the first element retrieved using a Collection Iterator :
javax.ejb.FinderException: Find failed: java.sql.SQLException: Syntax error
or access violation, message from server:
"You have an error in your SQL syntax. Check the manual that corresponds to your
MySQL server version for the right syntax to use near 'SELECT count(t0_a.IDAPPLICA) FROM applica t0_a), t0_a.IDAPP".
Thanks for helping me solving this issue.
Chris -
7. Re: findAll and Collection result optimization
aloubyansky Jul 19, 2004 2:01 AM (in response to cchris)I tested it against MySql 4.0.18. Make sure the MySql version you are using supports subqueries in select.
-
8. Re: findAll and Collection result optimization
cchris Jul 25, 2004 9:17 AM (in response to cchris)Hi,
I tried mysql 4.0.18 and 4.1.3b-beta (the latest) and I always have the same error from MySQL server when JBoss performs sql query using lazy Collection (simple select * from applica).
Note :
Using JBoss and MySQL:
<jboss-ql><![CDATA[SELECT OBJECT(a) FROM Applica a]]></jboss-ql>
<lazy-resultset-loading>true</lazy-resultset-loading>
I get the following exception when I try to parse the first element retrieved using a Collection Iterator :
javax.ejb.FinderException: Find failed: java.sql.SQLException: Syntax error
or access violation, message from server:
"You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT count(t0_a.IDAPPLICA) FROM applica t0_a), t0_a.IDAPP".
Of course when performing the generated query in MySQL SQL interpretor, it gives the same error. It seems the generated query is not correct.
Using MySQL (without JBoss) (sample tables t1(id varchar(100)) and t2(id varchar(100)) ) :
Just to check what kind of subqueries are possible in MySQL, I performed the following sample queries in Mysql SQL interpretor (don't get confused it's just a trial):
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
and
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);
gives errors :
ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM t2)' at line 1
and
ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id)' at line 1
When I rewrite sample query as follows :
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
It works.
I'm afraid I've no time to investigate further in JBoss source code to check this issue. It would be kind of you if you could help. I really need this feature because my JSP pages will have to deal with a big number of items to display.
Thanks
Chris -
9. Desappointed that no answer is the answer
cchris Jul 30, 2004 5:50 PM (in response to cchris)I rather think no answer is no answer and it helps neither potential jboss users nor other people having the same problem and above all it's not time saving at all.
Chris