How to implement a generic DAO object for Service pattern with Apex?

Question

Considering advice given for DAO objects or Service pattern from What is a good set of coding conventions for Salesforce development?, I'm wondering how can they be made generic enough?

Some generic DAOs from Java will solve that with an interface that has find(id), findAll(), search(ISearch) and so on. The themes are that:

• the object is always returned in its entirety
• filters can be passed to search interfaces which is passed to the DAO to limit the wanted records

Now, consider the following code. It is a one-off selection to get some settings.

List<Foo_Settings__c> fooSettings = [
    Select 
         Id, Name 
    from 
         Foo_Settings__c
    where
         Status__c = 'Active'
];

Just some fields are selected. I'm worried that reproducing the Service pattern from Java might have a negative impact on:

• governor limits (I sometimes need only a couple of fields)
• flexibility (it is not clear in advance which fields and conditions will be needed. It would be great to have a generic DAO from which other classes would inherit)
• security (I imagine a lot of dynamic SOQL would have to be used)

Apex offers query and queryMore in for loops to balance heap consumption (http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm):

for (Account a : [SELECT Id, Name FROM Account  WHERE Name LIKE 'Acme%'])

How can a flexible and generic DAO class be implemented with Apex?

Answer 1

Have a look into Andrew Fawcett's Enterprise Design Patterns Dreamforce Session, especially the Data Mapper (Selector) part

Source and links to the Video: https://github.com/financialforcedev/df12-apex-enterprise-patterns

In addition check out https://github.com/apex-commons/SoqlBuilder (former apex-lang) on Github.

Regarding your concerns

governor limits (I sometimes need only a couple of fields)
shouldnt be a problem, as long as you dont 'lazy load' in for loops (e.g. retrieve 200 accounts and query contacts for each account)

flexibility (it is not clear in advance which fields and conditions will be needed. It would be great to have a generic DAO from which other classes would inherit)
Andrew's sObjectSelector in combination with apex-commons SoqlBuilder should do the trick

security (I imagine a lot of dynamic SOQL would have to be used)

use

public class MysObjectSelector {}

for your DAO/Selector, so you can enforce sharing rules or not depending on the context. For example your triggers run in system mode, so should your selector, but when using a visualforce controller you might want to enforce sharing by using

public with sharing class MyVisualforceController{}