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

    findAll and Collection result optimization

    cchris

      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
          kabirkhan

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

          • 2. Re: findAll and Collection result optimization
            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

              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


                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

                  You could check it out from CVS -r Branch_3_2

                  • 6. Re: findAll and Collection result optimization
                    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

                      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

                        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

                          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