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?

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.

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.

A.3: A Contact Bean Test Page

Finally, let's make a test page and create an empty instance of the bean.

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. Create
2. Read
3. Update
4. Delete

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

B.2: Read()

Now we need to read a specific record and populate the bean.

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.

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

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
   <cftransaction action="commit" />
   before closing it.
    
9. Since the INSERT completed correctly, we can return true from the function and return to the calling process.
    

B.3.b: Create() - Test Page

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.

B.4: Update()

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

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.

B.5: Delete()

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

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.

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

C.2: Then update the Constructor to accept the new data

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.

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.