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~~
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.
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
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?
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......