9 Replies Latest reply on Jul 30, 2004 5:50 PM by chris chris

    findAll and Collection result optimization

    chris chris Newbie

      hi,
      I'm using JBoss 3.2.4 (EJB CMP 2.0) and MySQL 4.0.17. I have to display a list of records from a database using a JSP, in a friendly way.

      An easy solution would be a findAll (CMP) using an EJB QL query like :
      <ejb-ql>SELECT OBJECT(a) FROM App a</ejb-ql>.
      The result is a Collection with all the selected records from the database.
      This way might lead to an OutOfMemoyException because of the great number of records in the database.

      I'd like to get a Collection from a "SELECT *" like query without loading all the stored
      elements from the database (a fetch by page in a way, using EJB CMP).

      It is possible to fetch data from MySQL using the LIMIT clause :
      'SELECT ... FROM ... LIMIT start, size', where start is the position of the first database record (eg 0) and size is the size of the page (eg 20).

      To bad for me I can't use it from a CMP entity because CMP 2.0 can't manage it(Error compiling EJB-QL statement) :(:(

      Any idea to help me solve this CMP problem is welcome.

      Thanks

      Chris

        • 1. Re: findAll and Collection result optimization
          Kabir Khan Master

          Use jboss-ql in your query def in the jbosscmp-jdbc.xml file for added functionality

          • 2. Re: findAll and Collection result optimization
            chris chris Newbie

            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

            • 4. Re: findAll and Collection result optimization
              chris chris Newbie


              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
                Alexey Loubyansky Master

                You could check it out from CVS -r Branch_3_2

                • 6. Re: findAll and Collection result optimization
                  chris chris Newbie

                  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
                    Alexey Loubyansky Master

                    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
                      chris chris Newbie

                      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
                        chris chris Newbie

                        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