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.

CONTACT_CATEGORIES
CONTACT_IDCATEGORY_ID
11
21
31
42
53

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

Hogwarts Database: Student and Quidditch players
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
view plain print about
1<cfcomponent>
2
3<cffunction name="init" access="public"
4    output="false" returntype="Contact">

5
6    <cfargument name="CONTACT_ID" required="false"
7        type="numeric" default="0" hint="Primary Key" />

8    <!--- Note the new default value of ArrayNew(1) --->
9    <cfargument name="CATEGORY_ID_ARRAY" required="false"
10        type="array" default="#arrayNew(1)#" hint="Foreign Keys" />

11    <cfargument name="FIRST_NAME" required="false"
12        type="string" default="" hint="Contact's first name" />
13    <cfargument name="
LAST_NAME" required="false"
14        type="
string" default="" hint="Contact's last name" />

15
16    <cfset variables.instance = structNew() />
17
18    <cfset setContactID( arguments.CONTACT_ID ) />
19    <cfset setCategoryIDArray( arguments.CATEGORY_ID_ARRAY ) />
20    <cfset setFirstName( arguments.FIRST_NAME ) />
21    <cfset setLastName( arguments.LAST_NAME ) />
22
23    <cfreturn this />
24
25</cffunction>
26
27<!--- CONTACTS.CONTACT_ID (Primary Key) --->
28
29<cffunction name="setContactID" access="private"
30    output="false" returntype="void">

31    <cfargument name="CONTACT_ID" required="true"
32            type="numeric" hint="Primary Key" />

33    <cfset variables.instance.CONTACT_ID = arguments.CONTACT_ID />
34</cffunction>
35
36<cffunction name="getContactID" access="public"
37    output="false" returntype="numeric">

38    <cfreturn variables.instance.CONTACT_ID />
39</cffunction>
40
41<!--- CONTACTS.CATEGORY_ID (Foreign Key: CATEGORIES.CATEGORY_ID) --->
42
43<cffunction name="setCategoryIDArray" access="private"
44    output="false" returntype="void">

45    <cfargument name="CATEGORY_ID_ARRAY" required="true"
46            type="array" hint="Foreign Keys" />

47    <cfset variables.instance.CATEGORY_ID_ARRAY = arguments.CATEGORY_ID_ARRAY />
48</cffunction>
49
50<cffunction name="getCategoryIDArray" access="public"
51    output="false" returntype="array">

52    <cfreturn variables.instance.CATEGORY_ID_ARRAY />
53</cffunction>
54
55<!--- CONTACTS.FIRST_NAME --->
56
57<cffunction name="setFirstName" access="private"
58    output="false" returntype="void">

59    <cfargument name="FIRST_NAME" required="true"
60            type="string" hint="First name" />

61    <cfset variables.instance.FIRST_NAME = arguments.FIRST_NAME />
62</cffunction>
63
64<cffunction name="getFirstName" access="public"
65    output="false" returntype="string">

66    <cfreturn variables.instance.FIRST_NAME />
67</cffunction>
68
69<!--- CONTACTS.LAST_NAME --->
70
71<cffunction name="setLastName" access="private"
72    output="false" returntype="void">

73    <cfargument name="LAST_NAME" required="true"
74            type="string" hint="Last Name" />

75    <cfset variables.instance.LAST_NAME = arguments.LAST_NAME />
76</cffunction>
77
78<cffunction name="getLastName" access="public"
79    output="false" returntype="string">

80    <cfreturn variables.instance.LAST_NAME />
81</cffunction>
82
83</cfcomponent>

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
view plain print about
1<cfset contact = createObject("component", "Contact").init() />
2
3<cfoutput>
4<table class="dbTable">
5    <tr><td>ContactID:</td><td>#contact.getContactID()#</td></tr>
6    <tr><td>CategoryID:</td><td><cfdump var="#contact.getCategoryIDArray()#"></td></tr>
7    <tr><td>First Name:</td><td>#contact.getFirstName()#</td></tr>
8    <tr><td>Last Name:</td><td>#contact.getLastName()#</td></tr>
9</table>
10</cfoutput>
output
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. 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

ContactDAO.cfc - init()
view plain print about
1<cffunction name="init" access="public" output="false"
2    returntype="ContactDAO">

3    <cfargument name="DSN" required="true" type="string"
4        hint="datasource" />

5    <cfset variables.DSN = arguments.DSN />
6    <cfreturn this />
7</cffunction>

Creating a instance:

view plain print about
1<cfset contactDAO = createObject("component", "ContactDAO").init( DSN = variables.DSN ) />

Application.cfc : onApplicationStart()
view plain print about
1<cfset application.DSN = "myData" />
2
3<cfset application.contactGateway = createObject("component", "cfc.mySite.contacts.ContactGateway").init( DSN = application.DSN ) />
4<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()
view plain print about
1<cffunction name="read" access="public" output="false"
2    returntype="boolean">
<!--- [1] --->
3
4    <!--- [2] --->
5    <cfargument name="contact" required="true"
6        type="Contact" hint="Contact bean" />

7
8    <!--- [3] --->
9    <cfset var qReadOne = "" />
10    <cfset var categoryIDArray = arrayNew(1) />
11
12    <!--- [4] --->
13    <cfquery name="qReadOne"
14        datasource="#variables.DSN#">
<!--- [5] --->
15        SELECT
16            a.CONTACT_ID,
17            a.FIRST_NAME,
18            a.LAST_NAME,
19            b.CATEGORY_ID
20        FROM
21            CONTACTS a <!--- [6] --->
22        LEFT JOIN <!--- [7] --->
23            CONTACT_CATEGORIES b
24            ON a.CONTACT_ID = b.CONTACT_ID
25        WHERE <!--- [8] --->
26            a.CONTACT_ID = <cfqueryparam
27                            value="#arguments.contact.getContactID()#"
28                            cfsqltype="cf_sql_integer" />
29    </cfquery>
30
31    <!--- [9] --->
32    <cfif qReadOne.recordcount gte 1>
33
34        <!--- [10] --->
35        <cfset categoryIDArray = listToArray( valueList( qReadOne.CATEGORY_ID ) ) />
36
37        <!--- [11] --->
38        <cfset arguments.contact.init(
39            CONTACT_ID = qReadOne.CONTACT_ID,
40            CATEGORY_ID_ARRAY = categoryIDArray,
41            FIRST_NAME = qReadOne.FIRST_NAME,
42            LAST_NAME = qReadOne.LAST_NAME
43            ) /
>

44
45        <!--- [12a] --->
46        <cfreturn true />
47
48    </cfif>
49
50    <!--- [12b] --->
51    <cfreturn false />
52
53</cffunction>

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

view plain print about
1<cfif qReadOne.recordcount gt 1>

now

view plain print about
1<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
view plain print about
1<cfset contact = createObject("component", "Contact").init( CONTACT_ID = 1 ) />
2<cfset contactDAO = createObject("component", "ContactDAO").init( DSN = application.DSN ) />
3
4<cfset contactDAO.read( contact ) />
5
6<cfoutput>
7<table class="dbTable">
8    <tr><td>ContactID:</td><td>#contact.getContactID()#</td></tr>
9    <tr><td>CategoryID:</td><td><cfdump var="#contact.getCategoryIDArray()#"></td></tr>
10    <tr><td>First Name:</td><td>#contact.getFirstName()#</td></tr>
11    <tr><td>Last Name:</td><td>#contact.getLastName()#</td></tr>
12</table>
13</cfoutput>
output
ContactID:1
CategoryID:
array
11
24
First Name:Harry
Last Name:Potter

B.3: Create()

ContactDAO.cfc - create()
view plain print about
1<cffunction name="create" access="public" output="false"
2    returntype="boolean">
<!--- [1] --->
3
4    <!--- [2] --->
5    <cfargument name="contact" required="true"
6        type="contact" hint="Contact bean" />

7
8    <!--- [3] --->
9     <cfset var qCreateContact = "" />
10    <cfset var qNewContact = "" />
11    <cfset var newContactID = 0 />
12    <cfset var categoryIDArray = arrayNew(1) />
13    <cfset var x = 0 />
14    <cfset var qCreateContactCategory = "" />
15
16    <!--- [4] --->
17    <cftransaction action="begin">
18
19        <!--- [5] --->
20        <cftry>
21
22            <!--- [6] --->
23            <cfquery name="qCreateContact"
24                datasource="#variables.DSN#">

25                INSERT INTO CONTACTS
26                (
27                    FIRST_NAME,
28                    LAST_NAME
29                )
30                VALUES
31                (
32                    <cfqueryparam
33                        value="#arguments.contact.getFirstName()#"
34                        cfsqltype="cf_sql_varchar" />,
35                    <cfqueryparam
36                        value="#arguments.contact.getLastName()#"
37                        cfsqltype="cf_sql_varchar" />
38                )
39            </cfquery>
40
41            <!--- [7] --->
42            <cfquery name="qNewContact"
43                datasource="#variables.DSN#">

44
45                SELECT LAST_INSERT_ID() AS NEW_ID
46
47            </cfquery>
48
49            <cfset newContactID = qNewContact.NEW_ID />
50
51            <!--- [8] --->
52            <cfset categoryIDArray = arguments.contact.getCategoryIDArray() />
53
54            <!--- [9] --->
55            <cfloop index="x"
56                from="1"
57                to="#arrayLen( categoryIDArray )#">

58
59                <cfquery name="qCreateContactCategory"
60                    datasource="#variables.DSN#">

61                    INSERT INTO CONTACT_CATEGORIES
62                    (
63                        CONTACT_ID,
64                        CATEGORY_ID
65                    )
66                    VALUES
67                    (
68                        <!--- [10] --->
69                        <cfqueryparam
70                            value="#newContactID#"
71                            cfsqltype="cf_sql_integer" />,
72                        <!--- [11] --->
73                        <cfqueryparam
74                            value="#categoryIDArray[x]#"
75                            cfsqltype="cf_sql_integer" />
76                    )
77                </cfquery>
78
79            </cfloop>
80
81            <!--- [12] --->
82            <cfcatch type="database">
83                <!--- [12.1] --->
84                <cftransaction action="rollback" />
85     <!--- [12.2] --->
86     <cfreturn false />
87            </cfcatch>
88
89        </cftry>
90
91    <!--- [13] --->
92    </cftransaction>
93
94    <!--- [14] --->
95    <cfreturn true />
96
97</cffunction>

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
    view plain print about
    1<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

contact_bean_create.cfm
view plain print about
1<cfset categories = arrayNew(1) />
2<cfset categories[1] = 1 /><!--- Student --->
3<cfset categories[2] = 4 /><!--- Quidditch Team --->
4
5<cfset contact = createObject("component", "Contact").init(
6                                    CATEGORY_ID_ARRAY = categories,
7                                    FIRST_NAME = "Draco",
8                                    LAST_NAME = "Malfoy" ) /
>

9
10<cfset contactDAO = createObject("component", "ContactDAO").init( DSN = application.DSN ) />
11
12<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

CONTACT_CATEGORIES
CONTACT_IDCATEGORY_ID
11
14
21
31
34
42
53
61
64

B.4: Update()

ContactDAO.cfc - update()
view plain print about
1<cffunction name="update" access="public" output="false"
2    returntype="boolean">
<!--- [1] --->
3
4    <!--- [2] --->
5    <cfargument name="contact" required="true"
6        type="contact" hint="Contact bean" />

7
8    <!--- [3] --->
9     <cfset var qUpdateContact = "" />
10    <cfset var qDeleteContactCategories = "" />
11    <cfset var categoryIDArray = arrayNew(1) />
12    <cfset var x = 0 />
13    <cfset var qUpdateContactCategory = "" />
14
15    <!--- [4] --->
16    <cftransaction action="begin">
17
18        <!--- [5] --->
19        <cftry>
20
21            <!--- [6] --->
22            <cfquery name="qUpdateContact"
23                datasource="#variables.DSN#">

24                UPDATE CONTACTS
25                SET
26                    FIRST_NAME = <cfqueryparam
27                            value="#arguments.contact.getFirstName()#"
28                            cfsqltype="cf_sql_varchar" />,
29                    LAST_NAME = <cfqueryparam
30                            value="#arguments.contact.getLastName()#"
31                            cfsqltype="cf_sql_varchar" />
32                WHERE
33                    CONTACT_ID = <cfqueryparam
34                            value="#arguments.contact.getContactID()#"
35                            cfsqltype="cf_sql_integer" />
36            </cfquery>
37
38            <!--- [7] --->
39            <cfquery name="qDeleteContactCategories"
40                datasource="#variables.DSN#">

41
42                DELETE FROM CONTACT_CATEGORIES
43                WHERE
44                    CONTACT_ID = <cfqueryparam
45                            value="#arguments.contact.getContactID()#"
46                            cfsqltype="cf_sql_integer" />
47
48            </cfquery>
49
50            <!--- [8] --->
51            <cfset categoryIDArray = arguments.contact.getCategoryIDArray() />
52
53            <!--- [9] --->
54            <cfloop index="x"
55                from="1"
56                to="#arrayLen( categoryIDArray )#">

57
58                <cfquery name="qUpdateContactCategory"
59                    datasource="#variables.DSN#">

60                    INSERT INTO CONTACT_CATEGORIES
61                    (
62                        CONTACT_ID,
63                        CATEGORY_ID
64                    )
65                    VALUES
66                    (
67                        <!--- [10] --->
68                        <cfqueryparam
69                            value="#arguments.contact.getContactID()#"
70                            cfsqltype="cf_sql_integer" />,
71                        <!--- [11] --->
72                        <cfqueryparam
73                            value="#categoryIDArray[x]#"
74                            cfsqltype="cf_sql_integer" />
75                    )
76                </cfquery>
77
78            </cfloop>
79
80            <!--- [12] --->
81            <cfcatch type="database">
82                <!--- [12.1] --->
83                <cftransaction action="rollback" />
84     <!--- [12.2] --->
85     <cfreturn false />
86            </cfcatch>
87
88        </cftry>
89
90    <!--- [13] --->
91    </cftransaction>
92
93    <!--- [14] --->
94    <cfreturn true />
95
96</cffunction>

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.

view plain print about
1<cfset categories = arrayNew(1) />
2<cfset categories[1] = 2 /><!--- Instructor --->
3<cfset categories[2] = 3 /><!--- Staff --->
4
5<cfset contact = createObject("component", "Contact").init(
6                                    CONTACT_ID = 6,
7                                    CATEGORY_ID_ARRAY = categories,
8                                    FIRST_NAME = "Rubeus",
9                                    LAST_NAME = "Hagrid" ) /
>

10
11<cfset contactDAO = createObject("component", "ContactDAO").init( DSN = application.DSN ) />
12
13<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()
view plain print about
1<cffunction name="delete" access="public" output="true"
2    returntype="boolean">
<!--- [1] --->
3
4    <!--- [2] --->
5    <cfargument name="contact" required="true"
6        type="contact" hint="Contact bean" />

7
8    <!--- [3] --->
9     <cfset var qDeleteContact = "" />
10    <cfset var qDeleteContactCategories = "" />
11
12    <!--- [4] --->
13    <cftransaction action="begin">
14
15        <!--- [5] --->
16        <cftry>
17
18            <!--- [6] --->
19            <cfquery name="qDeleteContact"
20                datasource="#variables.DSN#">

21                DELETE FROM CONTACTS
22                WHERE
23                    CONTACT_ID = <cfqueryparam
24                            value="#arguments.contact.getContactID()#"
25                            cfsqltype="cf_sql_integer" />
26            </cfquery>
27
28            <!--- [7] --->
29            <cfquery name="qDeleteContactCategories"
30                datasource="#variables.DSN#">

31
32                DELETE FROM CONTACT_CATEGORIES
33                WHERE
34                    CONTACT_ID = <cfqueryparam
35                            value="#arguments.contact.getContactID()#"
36                            cfsqltype="cf_sql_integer" />
37
38            </cfquery>
39
40            <!--- [8] --->
41            <cfcatch type="database">
42                <cfdump var="#cfcatch#">
43                <!--- [8.1] --->
44                <cftransaction action="rollback" />
45     <!--- [8.2] --->
46     <cfreturn false />
47            </cfcatch>
48
49        </cftry>
50
51    <!--- [9] --->
52    </cftransaction>
53
54    <!--- [10] --->
55    <cfreturn true />
56
57</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
view plain print about
1<cfset contact = createObject("component", "Contact").init( CONTACT_ID = 6 ) />
2
3<cfset contactDAO = createObject("component", "ContactDAO").init( DSN = application.DSN ) />
4
5<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
view plain print about
1<cffunction name="delete" access="public" output="true"
2    returntype="boolean">
<!--- [1] --->
3
4    <!--- [2] --->
5    <cfargument name="contact" required="true"
6        type="contact" hint="Contact bean" />

7
8    <!--- [3] --->
9     <cfset var qDeleteContact = "" />
10    <cfset var qDeleteContactCategories = "" />
11
12    <!--- [4] --->
13    <cftransaction action="begin">
14
15        <!--- [5] --->
16        <cftry>
17
18            <!--- ***[6]*** --->
19            <cfquery name="qDeleteContactCategories"
20                datasource="#variables.DSN#">

21
22                DELETE FROM CONTACT_CATEGORIES
23                WHERE
24                    CONTACT_ID = <cfqueryparam
25                            value="#arguments.contact.getContactID()#"
26                            cfsqltype="cf_sql_integer" />
27
28            </cfquery>
29
30            <!--- ***[7]*** --->
31            <cfquery name="qDeleteContact"
32                datasource="#variables.DSN#">

33                DELETE FROM CONTACTS
34                WHERE
35                    CONTACT_ID = <cfqueryparam
36                            value="#arguments.contact.getContactID()#"
37                            cfsqltype="cf_sql_integer" />
38            </cfquery>
39
40            <!--- [8] --->
41            <cfcatch type="database">
42                <cfdump var="#cfcatch#">
43                <!--- [8.1] --->
44                <cftransaction action="rollback" />
45     <!--- [8.2] --->
46     <cfreturn false />
47            </cfcatch>
48
49        </cftry>
50
51    <!--- [9] --->
52    </cftransaction>
53
54    <!--- [10] --->
55    <cfreturn true />
56
57</cffunction>

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

contact_bean_delete.cfm
view plain print about
1<cfset contact = createObject("component", "Contact").init( CONTACT_ID = 6 ) />
2
3<cfset contactDAO = createObject("component", "ContactDAO").init( DSN = application.DSN ) />
4
5<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:

view plain print about
1<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.