Is there a common design pattern for making a collection class page-able?

Question

I've run across this design issue a number of times and was wondering if there is a common OOP design pattern that addresses it.

Design Issue: I need to implement a class that represents a collection of objects that can get quite large. For performance reasons, the presentation layer will present the data in individual pages requesting only a small subset of the objects at a time as the user navigates through the data. Ideally the object would selectively query the DB on demand also instead of pre-loading everything into memory, when it is very likely that for really large collections the client/user won't ever request all of the data in the collection.

I've implemented this a number of ways, but none of them feel very modular, clean, or have a really intuitive interface.

Is there a common OOP design pattern for implementing an object that allows the client to pull the data one page at a time and is smart about querying the data from the data-tier only as needed?

Answer 1

I would extend (or create) an Iterator class and add a constructor parameter that specifies the number of items per page and add a nextPage() method that returns a collection of the appropriate size (or less if there isn't enough items to fill a page).

Another options would be to simple extend or create an Iterator class and create a nextPage() method that takes an integer that is the maximum number of items that should be on the page, returning a collection of the appropriate size or smaller.

The Iterator pattern is in the GoF book, if you need a reference.

Answer 2

If you want to fetch from the database only a page at a time, then that can be done as a database pattern. Whether you implement it in the database or in an access layer, you need to know: Total number of items, the page size, which page is being requested.

The code then needs to create a complete indexable (it has item Numbers) list of key values for all the items, sorted as necessary or specified, and then return all the data for just those items with indexes that put them in the requested Page.

In SQL server, for example, (with appropriate indices are in place), you can so this in a single Stored Proc using a Table variable to hold the sorted list of Keys, and return any page of data very rapidly