I am looking for a pattern to handle the retrieval of large result sets with a 'cursor'.

Here is the problem: We need to return a large (could be 10-25, or >40,000) set of Value Objects through a SessionBean to display in a list (the list will be generated by a TagLib). We do not want to create EJBs for each, as that would be overkill, but we need a subset of the properties of the EntityBean to display in the list. In addition, we do not want (or need) to return the entire list, only a portion of it. However, we need the ability to 'page' forwards and backwards through the list and have the list retrieve the appropriate data. It needs to work like a DB cursor does, but we cannot use a DB cursor because: 1) We do not want to keep that resource open between user invocations, and 2) some of these lists will be generated from legacy data (not DB).

We have discussed several options and I know that this not a unique problem and wanted to see how others solved it.

Thanks in advance!

Mike

Mike,

Large sets of rows can always be an interesting design issue. here are the issues to be considered. is the row-set being retreived from the database/datastore consistent for all the users in the application or can it change depending on the user,i.e, a different view for each user. if its different view for each user then this is how i will suggest. talk to ur database for each page load of row-set. the reason i say this the temptation is to retrieve the entire 1000 rows, cache them or ur app server. but ur user might never ever go beyond the 2 page. so for every next hit on ur page reach out to ur database and fetch the next 20-30 records. this way u r not hogging up ur app server memory with a piece of data that might never be viewed.

kapil

How do you fetch the next set of records without keeping a real db cursor open? Do you have to select the entire result set and then iterate through them to get to the point you left off at on the last fetch? That would be pretty inefficient.

Jim

To make sure that u fetch only records lets say from 20-40 and not 1-40, u will have to keep some kinda flag for that may be in session or something, thats the only way i guess.

kapil

There is a great article on exactly your problem by Joe Weinstein at the BEA Developer Center: developer.bea.com. The article is vendor agnostic and goes through all of these issues of how to address this problem using native JDBC. It's very cool.

Look for JDBC Expert Tips in the Expert Tips section.

Tyler

Here's my approach...

http://www.baosys.com/work/writings/query.jsp

regarding that article by Joe it will only work if you don't do any sorting...

--
Tinou
www.tinou.com

Thanks, this makes sense. It is in line with how we are kind of leaning. One issue I still have is the previous 'set' issue I mentioned in another reply.

I still have another thought I am trying to get my arms around. Who maintains the current 'marker' to be used to get the next/prevoius? Is it returned with the results (in other words, do I return a 'ScrollableResult' object that has the list and the marker rather than just an array of the current set? Or do I get the 'marker' from the list itself when I do the previous/next?

It seems that the first is more encapsulated and reusable. Anybody done anything like that before? Again, part of our struggle is that the mechanism we are trying to build does not always get its data from a DB, hence we cannot rely on straight JDBC. For a single set of data, we will always have a single source, but we want to reuse this mechanism to encapsulate a 'ScrollableResult' of data, regardless of the source.

Thanks,
Mike

That is part of the design we are struggling with. I agree, retrieving the entire list from the DB and then only pulling out a specific range would be very inefficient. Our current thinking is that we would need to keep an internal 'marker' to let us know what the next and previous 'set' is. The issue with this is follows:
We have a list of Members for a Group, say 20,000. The users have the ability to start at 'A' and 'page' through. When they do this, we are anticipating only returning ~100 at a time. However, they also have the ability to jump to a particular letter, say 'L'. At this point, we can get the set that starts with 'L' and get the next 100. One issue arises when they click previous. We then want the previous 100 Members, but we do not know where to start.

I know these are not new issues, and we have been struggling with these for years, EJBs just add a different twist because: 1) We are not keeping active DB cursors open, and 2) The list may not even come from a DB. We are looking for something that behaves like a virtual list that can fetch in either direction intelligently, kind of like a ScrollPane.

I just wanted to float this thought because I know we are not trying to do something really unique and I know there is a good solution out there!
Thanks!

retrieving one time from the database
and displaying them with dhtml
using the div in html to display the next
and previous page.
by using this method you don't use a lot of
resourse from the server.