Can I limit the size of Collection returned by an EntityBean's finder method?

[Henry Leung]

I have thousands of rows in a table and I need to retrieve them all out to process. But I think it's not a good idea to fetch them all by a single call of findAll(). I would like to retrieve them, say, 50 by 50 repeatedly. So, I wonder if there is any tricks to declare a finder method, so that it retrieves at most 50 records at a time?

For reference, I am using JBoss 2.4.8 and Sybase ASE 12.5. But you can just ignore my server environment and tell me any approach you know. THX~~

[Balamaci Serban]

Not a general solution but for MySQL you can say SELECT * from bla LIMIT 5,10 where actualy 5, 10 could be passed as parameters in the finder method. But that's only MySql and BMP beans. On MS SQL server they have TOP, but that can be run only like this i guess SELECT TOP 10 FROM bla no more can u specify that i can skip the first x records. On PostgreSQL u've got SELECT FROM bla LIMIT 10 OFFSET 5. On Oracle, DB2 and others i would not know.

Anyway what's the catch with Statement.setFetchSize(100) and ResultSet.setFetchSize(100) do i need to call both? What does one and the other? Anyway that can't help us skipp past the result we already processed.

[vu lee]

Henry,
Using findAll() on an entity bean creates much overhead for your application in this case since this method involves a list of EJBObject references. Entity bean may not be the solution for this problem. You can use DAO to query and create a disconnected resultset and process a chunk of records at a time. Take a look at value list handler pattern

[Henry Leung]

thx both of you~~

But in fact what I need to do is to delete those rows. So the value list handler (I guess the main idea is to cache)won't help here. And, if I use JDBC to execute a delete SQL statement, with EntityBean deployed, there will be data persistency problem, right?

[Mishti Sengupta]

hi,
using DAO is best choice in such circumstances..

entity bean becomes less performing if you deal with huge chunk of data..
u can also use...session bean + jdbc...



_____________________________________
For Global Circle of Friends
http://sakshi.bravehost.com

[Henry Leung]

Originally posted by Mishti Sengupta:
hi,
using DAO is best choice in such circumstances..

entity bean becomes less performing if you deal with huge chunk of data..
u can also use...session bean + jdbc...

oh, I thought that with EntityBean OR-mapped to a table, I can't use JDBC to modify or delete rows from the table, or else the data persistency will fail. So this is not true??



_____________________________________
For Global Circle of Friends
http://sakshi.bravehost.com[/qb]
[ July 20, 2005: Message edited by: Henry Leung ]

[Osuwari Inu]

So basically your problem is:
How do I delete a large amount of rows using beans?

[Henry Leung]

yes, or more generally,
How to delete large amount of rows in a table, where the table is OR-mapped with an EntityBean?


I always thought that JDBC is not applicable in my case, and calling EJBObject.remove() for each row is the only way to do it (though I know it's slow). I would be glad to know that if I am wrong......