Object Oriented ColdFusion : 7.3 : An Intermediate Data Access Object (DAO)

We recently* discussed how use a DAO to map a single record from a single database table to a Bean Object under a one to many relationship. Now we need to discuss how to use a DAO to populate a Bean with data from multiple tables under a many to many relationship.

*ok, so "5 months ago we discussed . . ."

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. A slight correction
      4. Test Page
3. What if I want the Category Labels for each associated Category?
4. How do I use this In Real Life?

Some people we know

A.1: A Contact Database

Let's take a look at the database layout from the Basic DAO example. This represents a one-to-many (1:*) relationship, where each Contact can have at most one Category, but each Category can be mapped to multiple Contacts.

But some of our contcts are related to more than one category. What we need to do is restructure our database to represent a many-to-many (*:*) relationship.

Now we can tell mark which students are also on a Quidditch team.

A.2: A Contact Bean

Now we need to alter the Bean we used in part 7.1 to handle more than one CategoryID. Let's change the CATEGORY_ID property from a numeric data type to an array. Now we have a single property that can handle multiple entries.

A.3: A Contact Bean Test Page

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

B: The Good, the Bad and the CRUDly

There's no change of the structure between the Basic and Intermediate DAO. We still have the same four basic methods:

1. Create
2. Read
3. Update
4. Delete

However, each method will have some alterations to account for having to interact with multiple database tables.

Constructor

The constructor for this DAO is the same as for the Basic DAO.

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

Here's the read() method for ContactDAO.cfc. Since we now want an array of associated Contact IDs, we're only going to account for data from two tables: CONTACTS and CONTACT_CATEGORIES.

B.2.a: Read() - Dissected

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 two function local variables (var scope) named qReadOne and categoryIDArray.
   
4. A SELECT query (named qReadOne) to read a specific record from the CONTACTS table along with its associated records in CONTACT_CATEGORIES.
   
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. In the FROM clause of the SQL statement, we're using an alias ( a )for the table name ( CONTACTS ) to specify table and column relationships. Specifying the relationship is required when two tables have the same column name. We could have written this query as "SELECT CONTACTS.CONTACT_ID, ...", but that can get really verbose.
   
7. We use a LEFT JOIN to get records from the CONTACT_CATEGORIES table that are associated to the CONTACT_ID we specified.
   
8. 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.
   
9. Once we've found at least one record matching the CONTACT_ID we requested, we can then prepare data to be placed into the Contact bean.
   
10. Since each Contact can now have more than one Category, the record set returned by the query can have more than one record. Convert the CATEGORY_ID values from the record set into a comma-delimited list and then from a list to an array.
    
11. Now that we have the CONTACT_ID values as an array, we can call the init method of the Contact bean, passing in values from the query.
     It's very important to note here that we're only calling init() ONCE, regardless of how many records are returned.
    
12. If we found at least one record and populated the Contact bean, we can return true. Otherwise, we return false. We then return to the calling process.
    

Edit 04/25/2008: [9] was

now

Thanks to Greg Morphis for pointing this out.

B.2.b: Read() - Test page

Now we'll update the test page to populate the bean via the DAO.

Remember that ColdFusion objects are passed by reference, so the contact bean is created on line 1, passed into the DAO's read() method on line 3 and populated inside it. Inside read(), "arguments.contact" references the contact bean created on line 1.

We're not returning another instance of contact, we only return a boolean to tell whether or not the bean was populated.

B.3: Create()

B.3.a: Create() - Dissected

1. The returntype is "boolean".
   
2. The function requires a single argument: a Contact bean.
   
3. We create a few function local variables (var scope) to handle data.
   1. qCreateContact - query
   2. qNewContact - query
   3. newContactID - integer
   4. categoryIDArray - array
   5. x - integer; the index of a CFLOOP
   6. qCreateContactCategory - query
   
   
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. A SELECT query (named qNewContact) to retrieve the newly created ID from the CONTACTS table. We then assign this to the variable newContactID.
   1. MySQL uses LAST_INSERT_ID()
   2. SQL Server uses SCOPE_IDENTITY
   3. Check your database for its specific function.
   
   
8. Get the category ID array from the contact bean and assign it to the variable categoryIDArray so we can get the individual elements. You can't reference getFoo()[x] unless you dive into the underlying Java.
   
9. Now we're going to loop over the Category ID Array and create a record in CONTACT_CATEGORIES for each element, using newContactID as the associated CONTACT_ID for each record.
   
10. 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.
       
    
    
11. 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.
    
12. Since the INSERT completed correctly, we can return true from the function and return to the calling process.
    

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

Just as with the Basic DAO's create() method, we don't pass in a value for CONTACT_ID. Since the table is using an auto-incrementing value for CONTACT_ID, the database will create it for us. That value will then be passed to the CONTACT_CATEGORIES table to create its associated records.

If the create method completed correctly, then both CONTACTS and CONTACT_CATEGORIES have new entires.

B.4: Update()

B.4.a: Update() - Dissected

Let's just go through the differences between create() and update().

1. [6]: The first query, qUpdateContact, updates the CONTACTS table using data from the bean. This time, including the CONTACT_ID.
   
2. [7]: We can't update the existing associated records in CONTACT_CATEGORIES. We could have more or less entires than the previous version of the data. Instead, we just delete all records associated with the CONTACT_ID that's being updated.
   
3. [8]: Again we get the category ID array from the contact bean and assign it to the local variable categoryIDArray.
4. [9]: Just as with create(), we're going to loop over the Category ID Array and create a record in CONTACT_CATEGORIES for each element. However,
   
5. [10]: This time we'll be getting each record's value for CONTACT_ID from the contact bean.

B.4.b: Update() - Test Page

At the end of part 7.2 I asked, "Where's Hagrid?" Let's update the database to replace Draco with Hagrid.

Since we're updating an existing record, we have to make sure to pass a value for CONTACT_ID.

If the update method completed correctly, not only will there be an update to the record in the CONTACTS table, there will also be an update to the associated records in the CONTACT_CATEGORIES table.

B.5: Delete()

B.5.a: Delete() - Dissected

The delete is pretty self-explanitory:

1. We pass in a contact bean and we only really need to populate the CONTACT_ID.
2. We delete that ID's record from CONTACTS table.
3. And then we follow with the deletion of that ID's records in the CONTACTS_CATEGORY table.

Word's come down that Professor Dumbledore has a mission for Hagrid. He'll be away from the school for a while and one of the other instructors will be taking over his tasks while he's away.

Let's delete Hagrid from the database and finish out this lesson.

B.5.b: Delete() - Test Page

Wait a minute!

Something went wrong and we can't delete Hagrid's records from the database.

We forgot that the column CONTACT_ID in the table CONTACT_CATEGORIES is a foreign key which references the primary key of the same name in the CONTACTS table.

This means that every value for CONTACT_CATEGORIES.CONTACT_ID must exist in CONTACTS.CONTACT_ID.

Since both queries were wrapped by CFTRANSACTION, CFTRY and CFCATCH, we deleted the record from CONTACTS, but when the deletion of records in CONTACT_CATEGORIES failed, the database rolled back the deletion of the record in CONTACTS.

So we have to rearrange the order in which we delete records from the database to accommodate this restriction.

B.5.c: Delete() - corrected

B.5.d: Delete() - Test Page

Now that the delete() method is removing records in the correct order, we should be back where we started.

C: What if I want the Category Labels for each associated Category?

In part 7.1, this was easy. We were only dealing with one possible Category per Contact, so we added CATEGORY_LABEL as a property of the CONTACT bean.

Since we now have an array of CategoryIDs, we could add an array of Category Labels, but there wouldn't be a direct association between each ID and LABEL. We'd have to just assume that the first array element of the CONTACT_ID array is associated to the first array element of the CATEGORY_LABEL array.

We could convert CATEGORY_ID_ARRAY to CATEGORY_ARRAY and store a two-dimensional array of data where position 1 is the ID and position 2 is the LABEL.

We could instead convert CATEGORY_ID_ARRAY to CATEGORY_ARRAY and store and array of structs with the struct keys of CATEGORY_ID and CATEGORY_LABEL, but that could be more work that it's worth as well.

What I think we would prefer is to store an array of Category Beans. This would allow us to encapsulate each Category's "record" of data and associate them all to the Contact bean as a single property. The only problem we would have here is interacting with the array when accessing it by its getter.

In the create() and update() methods we handled accessing the CATEGORY_ID_ARRAY in this manner:

We did this so that we could reference each element of the array returned by getCategoryIDArray() as categoryIDArray[x] since we can't reference each element returned by the method without dropping into the underlying Java Vector methods. However, there is an object that can handle groups of objects and give us the ability to access them in various ways.

Next up, we'll talk about Object Composition and Object Inheritance by learning about the Collection Object.