views:

285

answers:

1

Hey all, Is there any way to maintain a database cursor using Hibernate between web requests? Basically, I'm trying to implement pagination, but the data that is being paged is consistently changing (i.e. new records are added into the database). We are trying to set it up such that when you do your initial search (returning a maximum of 5000 results), and you page through the results, those same records always appear on the same page (i.e. we're not continuously running the query each time next and previous page buttons are clicked). The way we're currently implementing this is by merely selecting 5000 (at most) primary keys from the table we're paging, storing those keys in memory, and then just using 20 primary keys at a time to fetch their details from the database. However, we want to get away from having to store these keys in memory and would much prefer a database cursor that we just keep going back to and moving backwards and forwards over the cursor to generate pages.

I tried doing this with Hibernate's ScrollableResults but found that I could not call methods like next() and previous() would cause an exception if you within a different web request / Hibernate session (no surprise there). Is there any way to reattach a ScrollableResults object to a Session, much the same way you would reattach a detached database object to make it persistent? Are there any other approaches to implement this data paging with consistent paging results without caching the primary keys?

+2  A: 

Essentially you're on your own for this one. What you want to do is take a look at the OpenSessionInView filter and build your own so that instead of making a new HibernateSession per request, you pull one out of a cache that's associated with the user's web session.

If you don't have a framework like Spring WebFlow that gives you some conversation structure, you're going to need to build that too. Since you probably want some way to manage the lifecycle of that Hibernate session beyond "When the web session expires." You also most likely do not want two user threads from the same web session but different browser tabs sharing a hibernate session. (Hilarity is likely to ensue.)

Affe