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

Posted on April 21, 2008

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.

Hogwarts Database: One to Many relationship

CATEGORIES
CATEGORY_IDCATEGORY_LABEL
1Student
2Instructor
3Staff
CONTACTS
CONTACT_IDCATEGORY_IDFIRST_NAME LAST_NAME
11HarryPotter
21HermioneGranger
31RonWeasley
42MinervaMcGonagall
53ArgusFilch

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.

Hogwarts Database: Many to Many relationship

CONTACTS
CONTACT_IDFIRST_NAMELAST_NAME
1HarryPotter
2HermioneGranger
3RonWeasley
4MinervaMcGonagall
5ArgusFilch
CATEGORIES
CATEGORY_IDCATEGORY_LABEL
1Student
2Instructor
3Staff

Association Table

This table associates the Primary Key from the CONTACTS table with Primary Keys from the CATEGORIES table. They are stored as Foreign Keys in each record. This table has no auto-incrementing Primary Key as the others do. Instead, you can define the combination of the Foreign Keys as a Primary Key for the table. This ensures that only one instance of any combination of CONTACT_ID and CATEGORY_ID exists in the table.

</table> Now we can tell mark which students are also on a Quidditch team. ### Hogwarts Database: Student and Quidditch players
CONTACT_CATEGORIES
CONTACT_IDCATEGORY_ID
11
21
31
42
53
CONTACTS
CONTACT_IDFIRST_NAMELAST_NAME
1HarryPotter
2HermioneGranger
3RonWeasley
4MinervaMcGonagall
5ArgusFilch
CATEGORIES
CATEGORY_IDCATEGORY_LABEL
1Student
2Instructor
3Staff
4Quidditch Team
CONTACT_CATEGORIES
CONTACT_IDCATEGORY_ID
11
14
21
31
34
42
53
### 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. ### Contact.cfc with ID as Array ```cfscript <cfset variables.instance = structNew() /> <cfset setContactID( arguments.CONTACT_ID ) /> <cfset setCategoryIDArray( arguments.CATEGORY_ID_ARRAY ) /> <cfset setFirstName( arguments.FIRST_NAME ) /> <cfset setLastName( arguments.LAST_NAME ) /> <cfset variables.instance.CONTACT_ID = arguments.CONTACT_ID /> <cfset variables.instance.CATEGORY_ID_ARRAY = arguments.CATEGORY_ID_ARRAY /> <cfset variables.instance.FIRST_NAME = arguments.FIRST_NAME /> <cfset variables.instance.LAST_NAME = arguments.LAST_NAME /> ``` ### A.3: A Contact Bean Test Page Now let's make a test page and create an empty instance of the bean. ### contact_bean.cfm ```cfscript <cfset contact = createObject("component", "Contact").init() /> </table> </cfoutput> ``` ### output
ContactID:#contact.getContactID()#
CategoryID:</td></tr>
First Name:#contact.getFirstName()#
Last Name:#contact.getLastName()#
ContactID:0
CategoryID:
array [empty]
First Name:
Last Name:
## 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. `C`reate
  2. `R`ead
  3. `U`pdate
  4. `D`elete
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
### 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() 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`. ### ContactDAO.cfc - read() ```cfscript <cfset var categoryIDArray = arrayNew(1) /> SELECT a.CONTACT_ID, a.FIRST_NAME, a.LAST_NAME, b.CATEGORY_ID FROM CONTACTS a LEFT JOIN CONTACT_CATEGORIES b ON a.CONTACT_ID = b.CONTACT_ID WHERE a.CONTACT_ID = <cfif qReadOne.recordcount gte 1> <cfset categoryIDArray = listToArray( valueList( qReadOne.CATEGORY_ID ) ) /> <cfset arguments.contact.init( CONTACT_ID = qReadOne.CONTACT_ID, CATEGORY_ID_ARRAY = categoryIDArray, FIRST_NAME = qReadOne.FIRST_NAME, LAST_NAME = qReadOne.LAST_NAME ) /> </cfif> ``` ### 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 ```cfscript <cfif qReadOne.recordcount gt 1> ``` now ```cfscript <cfif qReadOne.recordcount gte 1> ``` 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. ### contact_bean_read.cfm ```cfscript <cfset contact = createObject("component", "Contact").init( CONTACT_ID = 1 ) /> <cfset contactDAO = createObject("component", "ContactDAO").init( DSN = application.DSN ) /> <cfset contactDAO.read( contact ) /> </table> </cfoutput> ``` ### output
ContactID:#contact.getContactID()#
CategoryID:</td></tr>
First Name:#contact.getFirstName()#
Last Name:#contact.getLastName()#
ContactID:1
CategoryID:
array
11
24
First Name:Harry
Last Name:Potter
### B.3: Create() ### ContactDAO.cfc - create() ```cfscript <cfset var newContactID = 0 /> <cfset var categoryIDArray = arrayNew(1) /> <cfset var x = 0 /> INSERT INTO CONTACTS ( FIRST_NAME, LAST_NAME ) VALUES ( , ) SELECT LAST_INSERT_ID() AS NEW_ID <cfset newContactID = qNewContact.NEW_ID /> <cfset categoryIDArray = arguments.contact.getCategoryIDArray() /> INSERT INTO CONTACT_CATEGORIES ( CONTACT_ID, CATEGORY_ID ) VALUES ( , ) ``` ### 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 ```cfscript ``` 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 ### contact_bean_create.cfm ```cfscript <cfset categories = arrayNew(1) /> <cfset categories[1] = 1 /> <cfset categories[2] = 4 /> <cfset contact = createObject("component", "Contact").init( CATEGORY_ID_ARRAY = categories, FIRST_NAME = "Draco", LAST_NAME = "Malfoy" ) /> <cfset contactDAO = createObject("component", "ContactDAO").init( DSN = application.DSN ) /> <cfset contactDAO.create( contact ) /> ``` 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. ### Hogwarts Database CONTACTS CONTACT_IDFIRST_NAMELAST_NAME 1HarryPotter 2HermioneGranger 3RonWeasley 4MinervaMcGonagall 5ArgusFilch 6DracoMalfoy </tbody></table>
CONTACT_CATEGORIES
CONTACT_IDCATEGORY_ID
11
14
21
31
34
42
53
61
64
### B.4: Update() ### ContactDAO.cfc - update() ```cfscript <cfset var categoryIDArray = arrayNew(1) /> <cfset var x = 0 /> UPDATE CONTACTS SET FIRST_NAME = , LAST_NAME = WHERE CONTACT_ID = DELETE FROM CONTACT_CATEGORIES WHERE CONTACT_ID = <cfset categoryIDArray = arguments.contact.getCategoryIDArray() /> INSERT INTO CONTACT_CATEGORIES ( CONTACT_ID, CATEGORY_ID ) VALUES ( , ) ``` ### 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. ### ```cfscript <cfset categories = arrayNew(1) /> <cfset categories[1] = 2 /> <cfset categories[2] = 3 /> <cfset contact = createObject("component", "Contact").init( CONTACT_ID = 6, CATEGORY_ID_ARRAY = categories, FIRST_NAME = "Rubeus", LAST_NAME = "Hagrid" ) /> <cfset contactDAO = createObject("component", "ContactDAO").init( DSN = application.DSN ) /> <cfset contactDAO.update( contact ) /> ``` 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. ### Hogwarts Database
CONTACTS
CONTACT_IDFIRST_NAMELAST_NAME
1HarryPotter
2HermioneGranger
3RonWeasley
4MinervaMcGonagall
5ArgusFilch
6RubeusHagrid
CONTACT_CATEGORIES
CONTACT_IDCATEGORY_ID
11
14
21
31
34
42
53
62
63
### B.5: Delete() ### ContactDAO.cfc - delete() ```cfscript DELETE FROM CONTACTS WHERE CONTACT_ID = DELETE FROM CONTACT_CATEGORIES WHERE CONTACT_ID = </cfcatch> </cftry> </cftransaction> </cffunction> ``` ### 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 ### contact_bean_delete.cfm ```cfscript <cfset contact = createObject("component", "Contact").init( CONTACT_ID = 6 ) /> <cfset contactDAO = createObject("component", "ContactDAO").init( DSN = application.DSN ) /> <cfset contactDAO.delete( contact ) /> ``` ERROR Cannot delete or update a parent row: a foreign key constraint fails. `hogwarts2/contact_categories`, CONSTRAINT `FK_CONTACT_CATEGORIES_2` FOREIGN KEY (`CONTACT_ID`) REFERENCES `contacts` (`CONTACT_ID`)) `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 ### ContactDAO.cfc - delete() - in the correct order ```cfscript DELETE FROM CONTACT_CATEGORIES WHERE CONTACT_ID = DELETE FROM CONTACTS WHERE CONTACT_ID = </cfcatch> </cftry> </cftransaction> </cffunction> ``` ### B.5.d: Delete() - Test Page ### contact_bean_delete.cfm ```cfscript <cfset contact = createObject("component", "Contact").init( CONTACT_ID = 6 ) /> <cfset contactDAO = createObject("component", "ContactDAO").init( DSN = application.DSN ) /> <cfset contactDAO.delete( contact ) /> ``` Now that the delete() method is removing records in the correct order, we should be back where we started. ### Hogwarts Database
CONTACTS
CONTACT_IDFIRST_NAMELAST_NAME
1HarryPotter
2HermioneGranger
3RonWeasley
4MinervaMcGonagall
5ArgusFilch
CONTACT_CATEGORIES
CONTACT_IDCATEGORY_ID
11
14
21
31
34
42
53
## 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: ```cfscript <cfset categoryIDArray = arguments.contact.getCategoryIDArray() /> ``` 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.
About the Author
Adrian J. Moreno

Adrian is an enterprise solution architect and full stack developer. Which stack depends on which system is on fire at the time. More information