How to put records from a query into DAOs?

Question

I have written a gateway to get a result set from my database. How do i store every row in a separate dao so that i can manipulate every record further? Or can i access the result set directly to get the records?

This is my Gateway (btw, should i write the conditional logic within the cfquery into a seperate cfc that extends this one?)

<cfcomponent name="MaterialDao" hint="data access object" output="false">
 <cffunction name="init" hint="constructor" access="public" output="false" returntype="MaterialDao">
  <cfargument name="dsn" type="String" required="true" hint="datasource" />
  <cfset variables.instance.dsn = arguments.dsn />
  <cfreturn this />
 </cffunction>

 <cffunction name="readMaterial" hint="read" access="public" output="false" returntype="Query">
  <cfargument name="district" type="String" />
  <cfset var qReadMaterial = "" />
  <cfquery name="qReadMaterial" datasource="#variables.instance.dsn#">
   <cfif StructKeyExists(arguments,"district")>
   SELECT A.NR, A.BEZ, D.BES, D.STA
   <cfelse>
   SELECT A.NR, A.BEZ
   </cfif>
   FROM  DEK AS D INNER JOIN ART AS A
   ON D.NR = A.NR
   WHERE 0=0
   <cfif StructKeyExists(arguments,"district")>
    AND D.BEZ = #arguments.district#
   </cfif>
   ORDER BY A.BEZ
 </cfquery>
 <cfreturn qReadMaterial />
 </cffunction>
</cfcomponent>

I have already read a lot of articles and it seems that there are different opinions about this matter (DAO vs. Gateway, DAO & Gateway etc.). What is the best practice, what do the pros do?

Answer 1

Peter Bell had a great presentation some months ago on his release of the Iterating Business Object CFC which allows you to take multiple records and iterate over one record at a time using this simple framework: http://ibo.riaforge.org/. Until CF is a little faster at generating objects, recycling a single instance of an object and repopulating the properties is likely your best best. Perhaps this can help you load one record at a time into your DAO.

Conditional logic can go in the Gateway or in a Manager CFC. Typically, I would include logic that is simple like the logic outlined in your post directly in the CFC.

A bit of advice, you may wish to make the arguments.distinct NOT required and do a simple check with if (structKeyExists(arguments, "distinct") ) { do something }.

Regards,

-Aaron Greenlee

Answer 2

The pros use just one pattern for the database access layer. The use of both a DAO and Gateway is a misnomer that I'm not really sure where it got started, but seems to only exist in the ColdFusion crowd. The DAO and Gateway patterns can pretty much serve the same function, but I think the DAO fits the bill more when talking about database interaction.

DAOs should include functionality for CRUD methods plus returning sets of records. Since CRUD and basic sets of records is highly repetitive, I use a code generator to create the code for this interaction and then customize what I need. This is a good place for conditional logic for selecting the records you need.

As Aaron mentioned, returning an array of objects for a set of records in your database is unfeasible in ColdFusion due the the performance overhead of creating objects. I typically just use the basic query returned from the DAO in my views. However, if the thing I'm modeling needs some behavior in a view, then I will put the query in an object using something similar to what Peter Bell does.

Answer 3

At our company we thought long and hard about this stuff for a few months, trying the Adobe CF DAO creator via RDS and some other older ones (anyone remember CFPowerTools?).

We decided in the end to write our own DAO code generator and I thought I'd share our thoughts here. The reason we decided was because we needed to add locking hints to out SQL, we wanted to make it more efficient, more secure and cleaner.

The setup we decided on was to create a pre-defined base DAO object (called DAO.cfc) which all generated 'table' DAOs extended. All it had was a few utility methods, but the key thing thing is we can add any other functions in there that we need all our generated DAOs to have access to.

So we auto-generate code by selecting a table from the database (using the CF admin API) and create the [TableName].cfc DAO with the usual init, setters and getters, so the basic CRUD stuff.

In addition to this, we also generate a [TableName]GatewayBase.cfc and a [TableName]Gateway.cfc. [TableName]Gateway.cfc extends [TableName]GatewayBase.cfc.

So for a sample DAO run on a table called 'Customers', the files created are:

Customers.cfc /* extends DAO.cfc [not created, already exists] */
CustomersGateway.cfc 
CustomersGatewayBase.cfc /* extends CustomersGateway */

So, the idea is that the gateway provides a way to deal with many 'Customer' records - the DAO is used when dealing with one and only one. All methods in the gateway will generally return a CF query object. CF is too inefficient to create massive arrays of DAO objects and in our mind the query object in CF is really flexible, so we're happy to use it.

When coding, the sub-class CustomerGateway.cfc is the only one instantiated and used. However, the base class it extends has some very useful generic functions that come for free, such as getFieldListByProperty() which based on passed parameters will return certain fields (i.e. table columns) by a certain property (i.e. a column value), so for example:

myGateway.getFieldListByProperty(property="status", value="1", fieldList="customerName,customerID", orderBy="createdOn") />

That call will return the 'customerName' and 'customerID' values for all customers with a status of 1, ordered by the date they were created on. The code is also hardened against SQL injection and validated so sensible exceptions are thrown.

This function will provide 99% (we hope!) of the multi-record querying you do on a table. If you need a more sophisiticated query then the CustomerGateway.cfc is there for you to add functions to.

Finally, we allow you add functions to CustomerGateway CFC only because if you change the customers table (say add a column), you will need to recreate the table, and tha will overwrite the Customers.cfc and the CustomersGatewayBase.cfc. However, your custom code (if any) is safe in the sub-class.

Anyway, this might be slightly off topic, but sure I thought someone might find our experience useful.