I have a query that returns millions of records from Oracle database, and I need to present this data to the user in JSPs. I need to show this data page by page. So I will have buttons called "Next", "Previous" in the JSP and allow the users to navigate through the records. While pressing the Next button I don't want to connect to database and show the next page. Which is the best way to achieve this?
If you absolutely insist on not going back to the database, you'll have to read in all the millions of records into some data structure in your middle tier, store that in say, session state, and write JSP to access it. This can be done, but will consume lots of middle tier memory, network bandwidth, database resources, and take a long time to run. And your users won't actually look at more than a tiny fraction of these millions of records. Are you sure this is what you want?
Also, as has been pointed out, millions of records will be quite overwhelming to a user, as a user is likely to access only a small fraction. Serve them up a menu, presenting the data in logical units. For instance, if you have millions of items in an inventory, break the set down logically (e.g. cosmetics, sporting goods, etc). This way, a user can retrieve a manageable set of data at any given time.
Sure, each time the user desires to access a new logical unit, you need to fire off a request to the database, but I believe this strategy is a bit more elegant that taking the entire cake and eating it in one mouthful.