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();

Gelatinize

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>

Superphosphate

+1 I've done the same thing in iBatis, supporting optional date ranges, optional LIKE predicates, and so on. It works well. (BTW, you don't want the primary key condition in your example.) – Frag

I guess this could work in relatively simpler where clauses. But it would be difficult to scale if the conditions became more complex or when the some retrievals would depend on joins from another table. For example, I can have a CustomerDAO that just retrieves Customers based on a name. But what if I also want to retrieve a Customer based on outstanding balances which will need to information from another table. I'd end up having a complex query builder inside my DAOs just to anticipate the various different parameters that can be passed. – Gelatinize

@eplozada true you would need more options then, your question starts out referencing simple CRUD and how that expands. You could have a separate finder method for your join table example and then have parameters on that as well. If you're looking for one method call that will satisfy all possible scenarios you might envisage for querying your Customer table along with linked tables I think it could become horrendously complicated. Separate methods in your DAO is preferable to that IMHO. For more complex conditions the method name would indicate the type of query to the consumer – Superphosphate

Thanks @Jim, shocking mistake but in my defense its a Friday arvo here in Sydney and its been a very long week. – Superphosphate

and @Jim - Thanks for the responses so far. I usually fall back to something similar to what you guys do. But realistically, I just end up adding more methods to the DAO :(. Ah well, back to the design board. (deleted and replaced my previous comment due to horrendous grammar problems) – Gelatinize

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).

Backgammon

A generic finder or rather an extensible finder was what I was trying to achieve when I implemented the Specification pattern. It was close to what I was looking for since the SearchCriteria objects passed in the single finder method were also implemented from a domain perspective. I didn't want the SearchCrietria objects themselves tied to a specific implementation since it would defeat the purpose of DAOs in the first place. – Gelatinize

with generics, you could create a SearchCriteria<DAOClass> type infrastructure where you have a separate criteria class linked to each DAO without the DAO being linked back to the criteria class. – Backgammon