5 Replies Latest reply on Nov 29, 2003 10:16 PM by jooooohn

    Does JBoss-3.2.1 support Order By Desc ?

    jooooohn Newbie

      Hello,

      I need help. I am using CMR with JBoss-3.2.1,MySQL 4.0.14. I used the following syntax in EJB finder to order the messages(threads) by the posted time.

      SELECT OBJECT(o) FROM Thread AS o WHERE o.forumID=?1 and o.parentID = ?2 ORDER BY o.datePosted DESC

      But I got the a complie error:

      (org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "ORDER" at line 1...

      does the JBoss-3.2.1 support "order by" or did I make any mistake?

      Thanks in advance!

      John

        • 1. Re: Does JBoss-3.2.1 support Order By Desc ?
          Julien Dubois Newbie

          Hi,

          The EJB 2.0 spec does not support the ORDER BY keyword. This is IMHO the biggest problem with CMP 2.0.
          So you just can't do it!!
          You have 2 choices : either do it in your business layer (my business layers spend 90% of their time ordering collections of value objects), either wait for EJB 2.1 which will have the ORDER BY statement (at least).

          HTH,

          Julien Dubois.

          • 2. Re: Does JBoss-3.2.1 support Order By Desc ?
            nefilim Newbie

            Hi Julien,

            Any tips on sorting a Collection of found beans? Do you use a TreeSet and Comparator or did you hand code the sorting?

            Cheers,
            Peter

            • 3. Re: Does JBoss-3.2.1 support Order By Desc ?
              Julien Dubois Newbie

              Hi,

              I'm using TreeMap in fact. There are some Open Source projects which provide faster implementations than the standard java.util.TreeMap, for example Jakarta Commons has an interesting FastTreeMap (see http://jakarta.apache.org/commons/collections.html ).

              I'm not using a Set because :
              1. My Value Objects are automatically generated by XDoclet, and I thought it was easier to do it this way than have them implement Comparable - which would have needed some XDoclet tweaking probably.

              2. Sometimes I want to sort my VO in 2 or 3 different ways (by Date, by ID, by name, etc..).

              HTH,

              Julien.

              • 4. Re: Does JBoss-3.2.1 support Order By Desc ?
                nefilim Newbie

                Hi Julien,

                Thanks for the comments. Just on the side, I did some quick tests this morning with "overloading" the query definition in jbosscmp-jdbc.xml to add an ORDER BY clause. It seems to work, something like this:


                <query-method>
                <method-name>findByDept</method-name>
                <method-params>
                <method-param>java.lang.Integer</method-param>
                </method-params>
                </query-method>
                <jboss-ql>
                SELECT OBJECT(p) FROM User AS p WHERE p.deptId=?1 ORDER BY p.createDate
                </jboss-ql>


                Of course, this doesn't strike me as a portable solution.

                Cheers,
                Peter

                • 5. Re: Does JBoss-3.2.1 support Order By Desc ?
                  jooooohn Newbie

                  Hi, Nefilm and Julien:

                  Thank you very much.

                  Nefilim, could you tell me which jboss version that you used for your qiuick tests.

                  I tried on the JBoss-3.2.1. the Select ... clause was in the ejb-jar.xml, not in the jbosscmp-jdbc.xml. It can not be compiled after I added the ORDER BY in the ejb-jar.xml.

                  So I guess you may used other version.

                  Cheers!

                  john