9 Replies Latest reply on Dec 13, 2002 8:13 AM by Kenny Lam

    CMP findAll(iPage, pageNItem) method: How to limit the no of

    Kenny Lam Newbie

      Hi,

      I am using BMP's, looking to switch to CMP EJB-QL, but is stuck with a problem with findAll or (findBy ...) methods.

      Say, there are 5000 records in a table. In findAll(), I do not want all 5000 EJB's to be retrieved in the Collection. I would like to extract the records page by page like findAll(iPage, pageNItem) e.g. findAll(3, 50) will return Collection containing page 3 of the page size of 50 records i.e. records 101 to 150.

      In my current BMP, in the findAll(iPage, pageNItem). I could control the ResultSet which portion of the records to be returned.

      I do not know how to control this in the CMP EJB-QL.

      Intuitively, I think it could be done as paging through a large set of records is quite common (e.g. the pages of Q&A entries in this forum).

      Would very much appreciate if anyone could give some pointer on this, please.

      Thanks.

      KL

        • 1. Re: CMP findAll(iPage, pageNItem) method: How to limit the n
          Dain Sundstrom Master

          Currently, you can't. LIMIT x [OFFSET y] is planed for 4.0.

          • 2. Re: CMP findAll(iPage, pageNItem) method: How to limit the n
            Kenny Lam Newbie

            Thanks for you reply, Dain.

            Looks like it could be a serious issue with CMP in findXXX methods in which we could not upper bound how big the resultant Collection size is going to be (e.g. loading the whole table in the container space in case of findAll()).

            I guess EJB container will accommodate as many as its source (e.g. cache ... etc.) could support.

            Though I could implement this in BMP, CMP code is so much neater than BMP, wonder if there is any work around for paging through large display set before 4.0 comes in.

            The things I could think of is to limit the rows on Database side:
            (1) Define a VIEW in Oracle to limit the no. of rows, e.g.

            select * from table_a where row_num < ?

            but it can only start from row 1 to row ? and is database dependent; at least, it is better than retrieving the whole table.

            (2) Define a Package procedure (in which we have PL/SQL to limit the rows) that returns a Oracle cursor, but I (do not think) am not sure if EJB-QL could support it.

            Anyone, any ideas?

            I assume this www.jboss.org is using EJB (CMP??)(on Jboss). Wonder how the pages (i.e. can display, say, 15 replies in one page) of the forum Q&A are implemented?

            Thanks in advance.

            KL

            • 3. Re: CMP findAll(iPage, pageNItem) method: How to limit the n
              Kenny Lam Newbie

              Thanks for you reply, Dain.

              Looks like it could be a serious issue with CMP in findXXX methods in which we could not upper bound how big the resultant Collection size is going to be (e.g. loading the whole table in the container space in case of findAll()).

              I guess EJB container will accommodate as many as its source (e.g. cache ... etc.) could support.

              Though I could implement this in BMP, CMP code is so much neater than BMP, wonder if there is any work around for paging through large display set before 4.0 comes in.

              The things I could think of is to limit the rows on Database side:
              (1) Define a VIEW in Oracle to limit the no. of rows, e.g.

              select * from table_a where row_num < ?

              but it can only start from row 1 to row ? and is database dependent; at least, it is better than retrieving the whole table.

              (2) Define a Package procedure (in which we have PL/SQL to limit the rows) that returns a Oracle cursor, but I (do not think) am not sure if EJB-QL could support it.

              Anyone, any ideas?

              I assume this www.jboss.org is using EJB (CMP??)(on Jboss). Wonder how the pages (i.e. can display, say, 15 replies in one page) of the forum Q&A are implemented?

              Thanks in advance.

              KL

              • 4. Re: CMP findAll(iPage, pageNItem) method: How to limit the n
                Dain Sundstrom Master

                The reason it is not implemented is every database has a different syntax for limit offset, and Oracle has the most unique syntax. In 4.0 the query generator will be completely pluggable (and custom for each vendor), so it will be trivial feature to add.

                As for the JBoss website, it does run on JBoss, but the forums software we use comes from Jive and they use thier own wierd persistence engine and cache code (notice the multiple postings). There is a jboss project to rewrite the forums in EJB.

                • 5. Re: CMP findAll(iPage, pageNItem) method: How to limit the n
                  L.G. Newbie

                  This is not perfect solution, but it works for me...
                  I select keys, store them in SFSB or session object and then use with finder. This way I can display not only
                  next or previous pages but also first, last, and show to users how many pages etc...

                  • 6. Re: CMP findAll(iPage, pageNItem) method: How to limit the n
                    Loren Rosen Newbie

                    It's possible, although tricky, to write an Oracle query
                    that will return just the rows you want. This is moot
                    for CMP, of course, until the more customizable
                    CMP implementation is in place.

                    • 7. Re: CMP findAll(iPage, pageNItem) method: How to limit the n
                      Kevin O'Neill Newbie

                      I'm assuming your data has a fixed order.

                      what about setting cmp read ahead page size set to the number of items in the screen. The first findAll just does the ordered findAll, subsequent findAlls are in the form findAll(fromindex) the from index is the last item returned by the previous findAll and the query has something like myIndex > fromIndex.

                      Just a thought.

                      -k.

                      • 8. Re: CMP findAll(iPage, pageNItem) method: How to limit the n
                        Rafizan Baharum Newbie

                        settings:
                        postgresql 7.2
                        jboss3.0.4

                        with my settings, i use declared-sql with custom finder,
                        findAll(limit,offset). this can be easily done using xdoclet

                        * @jboss.declared-sql
                        * signature="java.util.Collection findAll(java.lang.Integer limit,java.lang.Integer offset)"
                        * other="LIMIT {0} OFFSET {1}

                        • 9. Re: CMP findAll(iPage, pageNItem) method: How to limit the n
                          Kenny Lam Newbie

                          Hi Guys,

                          Thanks for the ideas. I think it s mainly about pre-store the retreived information (keys or actual EJB's) in cache for later retrival (e.g. session ... etc.)

                          However, I think the actual issue is we need to limit the unncessary data retrived from database. Typical size of browse table (e.g. transactions, cashflows) is ~10 millions records in totally. Even for findBy[Conditions] methods, the count(*) could be quite large (can't be sure). Therefore, even storing the only Primary Key of EJB would be quite prohibited.

                          The way I think is to limit actual amount of info (EJB Collections or Primary Keys) to be returned to EJB container for caching.