How should I design my DAO layer

Question

Lets say I wanted a web page that would represent a zoo. There should be a list of enclosures (about a ten thousand of them) and it should be possible to display it in three ways:

• all enclosures,
• only enclosures that the currently logged in user has marked as favorite,
• only enclosures that the currently logged in user has commented on.

In all of these cases the list could be too long to fit on a single page and therefore should be divided into multiple pages with a pagination bar.

In order to ease searching for a particular enclosure, all three modes should support additional filtering by a keyword (full-text search in enclosure names). I.e. the user should be able to e.g. display all enclosures marked as favorite that contain a given string in their names. Of course, the list can still be to large and pagination would be applicable here as well.

The question is - how to design the DAO layer to avoid code dupplication and spaghetti code full of conditions? Also, it would be fine to have the code divided into layers/areas of abstraction, so that e.g. the code for building the final SQL queries would not be scattered inconsistently across many different classes from different abstraction layers.

Answer 1

Assuming a traditional request/response web application style here is a sketch:

Represent the various filtering options as classes in supporting code for your DAO. Have the web client specify URL parameters representing the filtering options. You'll need a way to ensure that the filtering options are always sent in on each request, or store them on the user's session.

Map the filtering parameters to the filtering options and pass the options to your DAO. In your DAO's queries "expand" the filtering options into appropriate where claus(es) against the database.

For paging, have the concept of a paging "window". For example, you could have a class that represents the starting row and how many rows to return. Again, expand that class into a predicate executed against the database.

There are other ways to accomplish this (perhaps with one of the million frameworks that are around), but this is how I'd approach it if I had to develop it all from scratch.

Answer 2

Editing my original answer since I misread your criteria. Your DAO will be the same as any other basic DAO. It will (essentially) have a GET method for each of the three queries. If the user wants to narrow down the criteria after that, I would suggest using a jquery plugin like DataTables., assuming the amount of data that gets returned in the DAO methods isn't some outrageously huge amount. That plugin will allow you to add filters to each column that updates as you type, and also has sort, search, and paginate functionality.