I doubt if our team is unique in this respect, but we actually wrote our own DAO / Gateway library from scratch.
The main reason we did so was to ensure that the SQL being executed was exactly as we expected, but we could still use the ideas of a DAO / bean CFC for quick one-row reads and updates to tables.
So we generally will generate three CFCs from a SQL table definition. For example, for the
Customers table we would create
Customers.cfc– DAO / bean class
CustomersGateway.cfc– editable ‘gateway’ class for multi-row queries and multi-table joins
CustomerGatewayBase.cfc– the base / super class for the CustomersGateway.cfc – this contains helper functions generated specifically for this table.
I’ve been doing some Ruby programming lately, using Rails and I got exposure to Active Record – pretty much impossible to avoid when using Rails.
One of the ideas used in AR is that of Dynamic Finders: simple functions used to help lookup a database row. Example might be
The nice thing about these functions is that you don’t have to write them, they are inferred or dynamically created (sort of!) by some Ruby magic – explained pretty well here.
Even better, you can do almost exactly the same thing in ColdFusion!
The general idea:
- You have a table bean or DAO
- You catch any missing methods called on the class using
- You use the passed arguments to work out what the user was trying to do and give an appropriate response
- Imagine I call
- There is no actual function called
findByUsernameAndPassword(username, pwd)on my DAO so ColdFusion will execute
onMissingMethod()passing in the name of the function I tired to call and whatever arguments were passed to it
onMissingMethod()will examine the function name I was trying to call, and if it starts with ‘findBy’ it will assume I am trying to lookup some data, i.e. execute a dynamic finder
- Next a bit of parsing will work out the fields I am trying to find the data by, so by delimiting by the word ‘and’ and removing the ‘findBy’ we can work out the the search fields are ‘username’ and ‘password’
- Super, now it’s just a matter of constructing our SQL query based on these strings and the passed values. Really simple and powerful.
And that as they say is that! The only other things I did was check the passed finder search fields against a list of table fields I store in the DAO which just means I can throw a more sensible error than waiting for it to blow up executing the SQL.
You can get the code as a GitHub gist here.