DAO pattern and the Open-Closed Principle

Question

I've seen and worked with a lot of older, JDBC-based DAO code that usually start out with CRUD methods. My question relates specifically to the retrieval methods, or 'finders'. Typically what I find is that the DAOs start out with two methods:

• find and return ALL
• retrieve a specific instance based on a unique identifier

More often than not, those two finders are insufficient. I usually end up seeing a DAO class repeatedly modified to add finder methods like the following:

• find and return ALL where {condition}

What happens is that more methods are added when a new {conditions} needs to be supported or existing methods are modified to add new parameters as flags to modify the SQL query inside the method to support the additional conditions.

It's an ugly approach and violates the Open-Closed Principle. It has always been a peeve of mine seeing DAO classes continuously modified whenever some new retrieval condition needs to be supported. Research on this question often points me to the Repository Pattern and encapsulating conditions for retrieval as Specifications or Query objects then passing them to a finder method. But this only seems feasible if you have an in-memory collection of the entire data set or if you are using some kind of ORM (I'm working with older JDBC code)

I've considered a solution that lazy loads the entire data set a DAO manages as a collection in-memory and then using the Specification Pattern as queries for retrieval. I then implement some kind of observer on the collection that just updates the database when creates, updates or deletes methods are called. But obviously performance and scalability suffer significantly.

Any thoughts on this?

---

Thank you for the responses so far. I do have one thought -- what are your opinions on the use of the Command/Policy pattern to encapsulate data access requests? Each individual Concrete Command can represent a specific kind of access and can be passed to an Invoker. I'd end up with numerous Concrete Command classes, but each one will be focused on just one kind of access and should be very testable and isolated.

    public abstract class Command<R>{
       public <R> execute();
       public void setArguments(CommandArguments args){
          //store arguments  
       }
    }

    //map based structure for storing and returning arguments
    public class CommandArguments{
         public String getAsString(String key);
         public String getAsInt(String key);
         //... others
    }

    //In some business class...
    Command command = CommandFactory.create("SearchByName");
    CommandArguments args = new CommandArguments();
    args.setValue("name", name);
    // others
    command.setArguments(args);
    List<Customer> list  = command.execute();

Answer 1

We have used iBatis for our data layer ORM and have been able to implement what you're proposing in one query by passing a parameter object with the various fields you might wish to use as paramaters.

Then in your WHERE clause you can specify each field as a condition clause but only if its populated in the parameter object. If only one field in the param obj is not null then its the only one that will be used to filter the results.

Thus if you need to add fields to your parameters you just change the SQL and the paramObj. You can then have 2 methods which return ALL or a subset based on the combo of paramters passed, or at least this approach would reduce the number of queries required.

e.g. something along the lines of...

SELECT * FROM MY_TABLE
WHERE FIELD_ZERO = paramObj.field0
<isNotNull property="paramObj.field1">AND FIELD_ONE = paramObj.field1</isNotNull>
<isNotNull property="paramObj.field2">AND FIELD_TWO = paramObj.field2</isNotNull>
<isNotNull property="paramObj.field3">AND FIELD_THREE = paramObj.field3</isNotNull>

Answer 2

Instead of creating a specific finder method for each possible condition as they become apparent, why not create a generic finder API? This could take the form of a DAO having an inner Enum to represent fields, and a method that takes a List of instances of an inner class of the DAO with fields representing which field of the DAO to filter, what filter to apply on it, and what condition (AND, OR, etc.).

It's a bit of work to set up, and for small projects may be overkill, but it's certainly doable. ORM frameworks typically have something similar already built in, so you might want to consider adopting one of those (or at least looking at how they've implemented it when designing your own solution for retrofitting into your legacy application).