-
1. Re: CMP findAll(iPage, pageNItem) method: How to limit the n
dsundstrom Dec 10, 2002 4:34 PM (in response to kennyl)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
kennyl Dec 11, 2002 2:11 AM (in response to kennyl)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
kennyl Dec 11, 2002 2:18 AM (in response to kennyl)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
dsundstrom Dec 11, 2002 12:55 PM (in response to kennyl)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. Dec 12, 2002 10:02 AM (in response to kennyl)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
lorensrosen Dec 12, 2002 1:25 PM (in response to kennyl)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 Dec 12, 2002 3:33 PM (in response to kennyl)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
udabahar Dec 12, 2002 8:57 PM (in response to kennyl)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
kennyl Dec 13, 2002 8:13 AM (in response to kennyl)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.