5 Replies Latest reply on Sep 14, 2006 10:03 AM by Laszlo Fogas

    JbossQL and m.createQuery(.. OFFSET 5 LIMIT 23)

    Walter Herbert Newbie

      Hello,
      I need to Query with LIMIT the Reslult length. I found 11.6.4. JBossQL in the documentation, but I do not like to write a jbosscmp-jdbc.xml file. Is there a way to make it like below, the Query must also be dynamic because of Order :

      @PersistenceContext
      Entitymanager manager;
      
      public List<SomeObj> findAllBlub(Integer offset, Integer limit){
       String order = "ORDER BY ....."
       return manager.createQuery("SELECT o FROM SomeObj o "+ order +" OFFSET "+ offset +" LIMIT "+ limit).getResultList();
      }
      


      I'm using Jboss4.0.4 GA .

      thx for Help
      cjc

        • 1. Re: JbossQL and m.createQuery(.. OFFSET 5 LIMIT 23)
          Walter Herbert Newbie

          Hi again,
          the reselution is so easy and nice, and now i found the real documentation for that:
          http://docs.jboss.org/ejb3/app-server/HibernateEntityManager/reference/en/html_single/index.html

          @PersistenceContext
          Entitymanager manager;
          
          public List<SomeObj> findAllBlub(Integer offset, Integer limit){
           String order = "ORDER BY ....."
           Query q = manager.createQuery("SELECT o FROM SomeObj o "+ order );
           q.setFirstResult(offset);
           q.setMaxResults(limit);
           return q.getResultList();
          }
          


          thx all
          cjc

          • 2. Re: JbossQL and m.createQuery(.. OFFSET 5 LIMIT 23)
            Laszlo Fogas Newbie

            Hi,

            I found the same solution, but when i examined a bit more i realized that the whole resultset is generated every time, and these methods only cut a part of that. It does not generate offset/limit like sql query. Am I right? Because it could cause some performance problems.

            Lazlo

            • 3. Re: JbossQL and m.createQuery(.. OFFSET 5 LIMIT 23)
              Laszlo Fogas Newbie

              ok

              I was wrong:

              It generates the desired offset/limit queries unless you use join fetch in your query to initialize lazy collections.

              In the docs: (http://docs.jboss.org/ejb3/app-server/HibernateEntityManager/reference/en/html_single/index.html#d0e751)
              "Nor should fetch be used together with setMaxResults() or setFirstResult(). It is possible to create a cartesian product by join fetching more than one collection in a query (as in the example above), be careful the result of this product isn't bigger than you expect. Join fetching multiple collection roles also sometimes gives unexpected results for bag mappings, so be careful about how you formulate your queries in this case.

              TODO: The last statement is useless and typical developer thinking, please elaborate. The word "sometimes" should never appear in any technical documentation."

              funny :) So what is the status of this problem(?) now?

              I know the collection.size() workaround, but i don't like it!! I think that solution is much slower. Is there other solution?

              lazlo




              • 4. Re: JbossQL and m.createQuery(.. OFFSET 5 LIMIT 23)
                Laszlo Fogas Newbie

                ok, i try again :)

                So, i have an object with lots of lazy onetomany relations. I have second level cache enabled(TreeCache). I would like to use pagination with the setfirstresult/maxresult methods.

                If i initialize the lazy collections with calling the size method, and use pagination the entity manager generates the limit/offset into the sql query.
                (i set the a show sql param to true.)
                But this way the cache does not operate as good as i expect: It's slower than dbhitting.

                Here comes the sopution:
                http://www.jboss.com/index.html?module=bb&op=viewtopic&t=88317
                Which says that i should use fetching joins to initialize the collections, because the implementation likes that much more.. OK i did this.

                But than the pagination breaks.. there's no limit and offset in my queries.

                The documentation has a funny comment on this:

                "Note that the fetch construct may not be used in queries called using scroll() or iterate(). Nor should fetch be used together with setMaxResults() or setFirstResult(). "

                What does this mean?

                Noone faced the same problem before me?

                I have tested the cache with the fetching joins: i changed my query (id<10) in order to simulate the case with limit 10 to test the speed.. As the link said the cache working fine.. the query speed decreased after the first loading.

                So there are two bugs(?) which prevent me to use EJB3 efficently!!

                Any slutions? Workarounds?

                regards, Lazlo

                • 5. Re: JbossQL and m.createQuery(.. OFFSET 5 LIMIT 23)
                  Laszlo Fogas Newbie

                  nobody any idea? i think it's not a special need.. all i want to do is a select with joins and limit... If i'm asking stupid thing please let me know!

                  regards