Object Oriented Coldfusion : 7.1 : A Basic Data Access Object (DAO)

As discussed part 6, a bean encapsulates a single record of data and that data doesn’t necessarily have to come from a single database table. But in this example, it does.

Table of Contents

1. Some People We Know
   1. A Contact Database
   2. A Contact Bean
   3. A Contact Bean Test Page
2. The Good, the Bad and the CRUDly
   1. Constructor
   2. Read()
      1. Dissected
      2. Test Page
   3. Create()
      1. Dissected
      2. Test Page
   4. Update()
      1. Dissected
      2. Test Page
   5. Delete()
      1. Dissected
      2. Test Page
3. What if I want the Contact's Category Label?
4. How do I use this In Real Life?</li> </ol> ## A: Some people we know ### A.1: A Contact Database Let's start off with a couple of tables: Contacts and Categories. Each Contact record can have at most one Category, but each Category record can be mapped to multiple Contacts. For those new to database design, this represents a one-to-many (1:*) relationship. ### Hogwarts Database

   CATEGORIES
   CATEGORY_ID	CATEGORY_LABEL
   1	Student
   2	Instructor
   3	Staff

   CONTACTS
   CONTACT_ID	CATEGORY_ID	FIRST_NAME	LAST_NAME
   1	1	Harry	Potter
   2	1	Hermione	Granger
   3	1	Ron	Weasley
   4	2	Minerva	McGonagall
   5	3	Argus	Filch

   From these tables, we can see that
   1. Harry, Ron and Hermione are students
   2. Minerva McGonagall is an Instructor
   3. Argus Filch is a member of the Staff
   ### A.2: A Contact Bean Now we need a Bean to encapsulate a record of Contact data. ### Contact.cfc ```cfscript <cfset variables.instance = structNew() /> <cfset setContactID( arguments.CONTACT_ID ) /> <cfset setCategoryID( arguments.CATEGORY_ID ) /> <cfset setFirstName( arguments.FIRST_NAME ) /> <cfset setLastName( arguments.LAST_NAME ) /> <cfset variables.instance.CONTACT_ID = arguments.CONTACT_ID /> <cfset variables.instance.CATEGORY_ID = arguments.CATEGORY_ID /> <cfset variables.instance.FIRST_NAME = arguments.FIRST_NAME /> <cfset variables.instance.LAST_NAME = arguments.LAST_NAME /> ``` ### A.3: A Contact Bean Test Page Finally, let's make a test page and create an empty instance of the bean. ### contact_bean.cfm ```cfscript <cfset contact = createObject("component", "Contact").init() /> ContactID: #contact.getContactID()#
   CategoryID: #contact.getCategoryID()#
   First Name: #contact.getFirstName()#
   Last Name: #contact.getLastName()# ``` ### output ContactID: 0
   CategoryID: 0
   First Name:
   Last Name:
   ## B: The Good, the Bad and the CRUDly Now we're going to create a basic Data Access Object that will abstract database interactions related to Contact data. We'll start with four simple database functions:
   1. `C`reate
   2. `R`ead
   3. `U`pdate
   4. `D`elete
   ### B.1: Constructor Before we get to the database functions, we need to create a constructor for the object. Remember that the constructor's returntype should be the same as the name of the object: ContactDAO. And just as with the Gateway Object,
   1. we're going to _inject_ the value of the data source into the DAO
   2. we're going to (most often) create only one instance of the DAO in the application scope so it can be referenced from memory by any process
   ### ContactDAO.cfc - init() ```cfscript <cfset variables.DSN = arguments.DSN /> ``` Creating a instance: ```cfscript <cfset contactDAO = createObject("component", "ContactDAO").init( DSN = variables.DSN ) /> ``` ### Application.cfc : onApplicationStart() ```cfscript <cfset application.contactGateway = createObject("component", "cfc.mySite.contacts.ContactGateway").init( DSN = application.DSN ) /> <cfset application.contactDAO = createObject("component", "cfc.mySite.contacts.ContactDAO").init( DSN = application.DSN ) /> ``` ### B.2: Read() Now we need to read a specific record and populate the bean. ### ContactDAO.cfc - read() ```cfscript SELECT CONTACT_ID, CATEGORY_ID, FIRST_NAME, LAST_NAME FROM CONTACTS WHERE CONTACT_ID = <cfif qReadOne.recordcount eq 1> <cfset arguments.contact.init( CONTACT_ID = qReadOne.CONTACT_ID, CATEGORY_ID = qReadOne.CATEGORY_ID, FIRST_NAME = qReadOne.FIRST_NAME, LAST_NAME = qReadOne.LAST_NAME ) /> </cfif> ``` ### B.2.a: Read() - Dissected Let's dissect the read` method:
   1. The `returntype` is "boolean". This makes it easy to know if you've populated the bean with a record from the database. Alternately, you could make the returntype a `struct`, so you could return multiple values like a boolean and a string. This would let you set and return a message when the read fails.
       
   2. The function requires a single argument: `a Contact bean`.
       
   3. We created a `function local variable (var scope)` named `qReadOne`.
       
   4. A `SELECT` query (named `qReadOne`) to read a specific record from the CONTACTS table.
       
   5. The `datasource` for the query is being read from the `variables scope` of the component. This value was set by the `init method` when the object was created.
       
   6. The `WHERE` clause of the query needs a single value, taken directly from the Contact bean itself. This means that the value of the bean's CONTACT_ID should have been set before it was passed to this method.
       
   7. Once we've found a single record matching the CONTACT_ID we requested, we can then call the `init` method of the Contact bean, passing in values from the query.
      
      Now that we have populated the Contact bean, we can return `true` from the function and return to the calling process.
       
   8. If we haven't found a matching record, then we return `false` from the function and return to the calling process.
   ### B.2.b: Read() - Test page Now we'll update the test page to populate the bean via the DAO. ### contact_bean_read.cfm ```cfscript <cfset contact = createObject("component", "Contact").init( CONTACT_ID = 4 ) /> ContactID: #contact.getContactID()#
   CategoryID: #contact.getCategoryID()#
   First Name: #contact.getFirstName()#
   Last Name: #contact.getLastName()#

   ---

   <cfset contactDAO = createObject("component", "ContactDAO").init( DSN = "myDSN" ) /> <cfset contactDAO.read( contact ) /> ContactID: #contact.getContactID()#
   CategoryID: #contact.getCategoryID()#
   First Name: #contact.getFirstName()#
   Last Name: #contact.getLastName()# ``` ### output ContactID: 4
   CategoryID: 0
   First Name:
   Last Name:
   

   ---

   ContactID: 4
   CategoryID: 2
   First Name: Minerva
   Last Name: McGonagall
   ### So what just happened? First, we instantiated a Contact bean, passing in a value for the CONTACT_ID at the same time. That value could come from, among other sources, a URL or FORM scoped variable. Next, we created an instance of ContactDAO. Then we `passed a reference to the Contact bean into the read method` of the DAO. We did `NOT` pass in the actual instance of the Contact bean, we only passed a `reference` to it.

   `With Coldfusion, Objects are passed by reference.`

   _(Read those last few lines over and over until they are burned into your memory.)_ Inside the DAO's read method, the database was read, the Contact bean's init() method was called and data from the SELECT query was passed into it. When this happens, we're populating the actual instance of the Contact bean `at the level of the calling process or page`. This is why we're returning a boolean value and not returning a Contact bean. The Contact bean was passed by reference, so there is no actual bean to return. ### B.3: Create() ### ContactDAO.cfc - create() ```cfscript INSERT INTO Contacts ( CATEGORY_ID, FIRST_NAME, LAST_NAME ) VALUES ( , , ) ``` ### B.3.a: Create() - Dissected Let's dissect the `create` method:
   1. The `returntype` is "boolean".
       
   2. The function requires a single argument: `a Contact bean`.
       
   3. We created a `function local variable (var scope)` named `qCreateContact`.
       
   4. If your database can handle transactions, use `cftransaction` to begin one.
       
   5. Use `cftry` (with cfcatch) to handle any errors we may encounter with the query.
       
   6. An `INSERT` query (named `qCreateContact`) to add a record to the Contacts table.
       
   7. Should an error occur during the `INSERT`, the `cfcatch` will be triggered, allowing us to
       
      1. Rollback the transaction: This means that all database processes within the `cftransaction` tags will be pulled back, as if they all had not run at all.
          
      2. Since the `INSERT` failed, we can return `false` from the function and return to the calling process.
          
   8. If the `INSERT` completed correctly, end (commit) the transaction to the database. If your database does not automatically commit transactions, then you'll have to add ```cfscript ``` before closing it.
       
   9. Since the `INSERT` completed correctly, we can return `true` from the function and return to the calling process.
       
   </ol> ### B.3.b: Create() - Test Page ### contact_bean_create.cfm ```cfscript <cfset contact = createObject("component", "Contact").init( CATEGORY_ID = 2, FIRST_NAME = "Quirinus", LAST_NAME = "Quirrell" ) /> <cfset contactDAO = createObject("component", "ContactDAO").init( DSN = "myDSN" ) /> <cfset contactDAO.create( contact ) /> ``` Notice that we did not pass in a value for the CONTACT_ID. More often than not, the Primary Key of a database table like our Contacts table uses an auto-incrementing numeric value of some sort. This mean that the value of CONTACT_ID for a new record will be generated by the database, so there's no need to pass one to the bean when creating a new record. If the create method completed correctly, then the Contacts table now has a new entry. ### Hogwarts Database

   CONTACTS
   CONTACT_ID	CATEGORY_ID	FIRST_NAME	LAST_NAME
   1	1	Harry	Potter
   2	1	Hermione	Granger
   3	1	Ron	Weasley
   4	2	Minerva	McGonagall
   5	3	Argus	Filch
   6	2	Quirinus	Quirrell

   ### B.4: Update() ### ContactDAO.cfc - update() ```cfscript UPDATE Contacts SET ( CATEGORY_ID = , FIRST_NAME = , LAST_NAME = ) WHERE CONTACT_ID = ``` ### B.4.a: Update() - Dissected There's no need to dissect this method since the only difference between create() and update() is the actual SQL involved. Now, it's commonly known that Hogwarts has a problem keeping teachers of a certain subject around. So let's update the database to show the replacement for Professor Quirrell. ### B.4.b: Update() - Test Page ### contact_bean_update.cfm ```cfscript <cfset contact = createObject("component", "Contact").init( CONTACT_ID = 6, CATEGORY_ID = 2, FIRST_NAME = "Gilderoy", LAST_NAME = "Lockhart" ) /> <cfset contactDAO = createObject("component", "ContactDAO").init( DSN = "myDSN" ) /> <cfset contactDAO.update( contact ) /> ``` Since we're updating an existing record, we have to make sure to pass in the CONTACT_ID this time. If the update method completed correctly, then Professor Quirrell has been replaced. ### Hogwarts Database

   CONTACTS
   CONTACT_ID	CATEGORY_ID	FIRST_NAME	LAST_NAME
   1	1	Harry	Potter
   2	1	Hermione	Granger
   3	1	Ron	Weasley
   4	2	Minerva	McGonagall
   5	3	Argus	Filch
   6	2	Gilderoy	Lockhart

   ### B.5: Delete() ### ContactDAO.cfc - delete() ```cfscript DELETE FROM Contacts WHERE CONTACT_ID = ``` ### B.5.a: Delete() - Dissected Again there should be no need to dissect this method as the SQL is all that different between create(), update() and delete(). Rather than try and keep up with all the changes or Instructors at Hogwarts, let's just get rid of Professor Lockhart instead of updating his position. ### B.5.b: Delete() - Test Page ### contact_bean_delete.cfm ```cfscript <cfset contact = createObject("component", "Contact").init( CONTACT_ID = 6 ) /> <cfset contactDAO = createObject("component", "ContactDAO").init( DSN = "myDSN" ) /> <cfset contactDAO.delete( contact ) /> ``` We're deleting a specific record, so all we really need in the Contact bean is the CONTACT_ID. However, you can populate it completely if you like. If the delete method completed correctly, then the record we created earlier will have been removed from the Contacts table. ### Hogwarts Database

   CONTACTS
   CONTACT_ID	CATEGORY_ID	FIRST_NAME	LAST_NAME
   1	1	Harry	Potter
   2	1	Hermione	Granger
   3	1	Ron	Weasley
   4	2	Minerva	McGonagall
   5	3	Argus	Filch

   ## C: What if I want the Contact's Category Label? This question (essentially) was asked by "Shane":

   _"Ok, so what about lookup tables? In procedural style, you could just perform the lookup joins in the query on the page. But with OO, your bean would only contain the lookup ID, and not the lookup value. So what's the best practice? Create a lookup object and feed your bean's lookup IDs to it whenever needed? Modify your read method in the bean to include lookup values? Create separate lookup methods in the bean or gateway?"_

   The answer is simple:

   `Add CATEGORY_LABEL as a Propery of the Bean.`

   ### C.1: First you need to add the correct _getter_ and _setter_ ### Contact.cfc - new methods ```cfscript <cfset variables.instance.CATEGORY_LABEL = arguments.CATEGORY_LABEL /> ``` ### C.2: Then update the Constructor to accept the new data ### Contact.cfc - update to init() ```cfscript <cfset variables.instance = structNew() /> <cfset setContactID( arguments.CONTACT_ID ) /> <cfset setCategoryID( arguments.CATEGORY_ID ) /> <cfset setFirstName( arguments.FIRST_NAME ) /> <cfset setLastName( arguments.LAST_NAME ) /> <cfset setCategoryLabel( arguments.CATEGORY_LABEL ) /> ``` ### C.3: Finally, you'll need to update the query in the `read()` method `Read()` should now also retrieve the Category Label for the selected Contact and pass it into the Contact bean with the rest of the data. ### ContactDAO.cfc - update to read() ```cfscript SELECT a.CONTACT_ID, a.CATEGORY_ID, a.FIRST_NAME, a.LAST_NAME, b.CATEGORY_LABEL FROM CONTACTS a LEFT JOIN CATEGORIES b ON b.CATEGORY_ID = a.CATEGORY_ID WHERE CONTACT_ID = <cfif qReadOne.recordcount eq 0> <cfset arguments.contact.init( CONTACT_ID = qReadOne.CONTACT_ID, CATEGORY_ID = qReadOne.CATEGORY_ID, FIRST_NAME = qReadOne.FIRST_NAME, LAST_NAME = qReadOne.LAST_NAME, CATEGORY_LABEL = qReadOne.CATEGORY_LABEL ) /> </cfif> </cffunction> ``` Simple, huh? ## D: Ok, I get it now (I think), but how do I use it In Real Life? Most of the examples shown are using hard-coded values, but in a real-world application they'll often be read from FORM or URL scoped variables. At times, they'll come from SESSION or APPLICATION scoped variables. If you're using a framework like Mach-II, Fusebox or Model-Glue, those values will be passed in from some process in the framework. Rather than extend this post past the code for a Basic Data Access Object, I'll move some examples to the next post.