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.

Hogwarts Database

CATEGORIES
CATEGORY_IDCATEGORY_LABEL
1Student
2Instructor
3Staff

CONTACTS
CONTACT_IDCATEGORY_IDFIRST_NAME LAST_NAME
11HarryPotter
21HermioneGranger
31RonWeasley
42MinervaMcGonagall
53ArgusFilch

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
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    <cfargument name="CATEGORY_ID" required="false"
9        type="numeric" default="0" hint="Foreign Key" />

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

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

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

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

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

44    <cfargument name="CATEGORY_ID" required="true"
45        type="numeric" hint="Foreign Key" />

46    <cfset variables.instance.CATEGORY_ID = arguments.CATEGORY_ID />
47</cffunction>
48
49<cffunction name="getCategoryID" access="public"
50    output="false" returntype="numeric">

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

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

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

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

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

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

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

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

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. 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

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:

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

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

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    
11    <!--- [4] --->
12    <cfquery name="qReadOne"
13        datasource="#variables.DSN#">
<!--- [5] --->
14        SELECT
15            CONTACT_ID,
16            CATEGORY_ID,
17            FIRST_NAME,
18            LAST_NAME
19        FROM
20            CONTACTS
21        WHERE <!--- [6] --->
22            CONTACT_ID = <cfqueryparam
23                    value="#arguments.contact.getContactID()#"
24                    cfsqltype="cf_sql_integer" />
25    </cfquery>
26    
27    <!--- [7] --->
28    <cfif qReadOne.recordcount eq 1>
29    
30        <cfset arguments.contact.init(
31                CONTACT_ID = qReadOne.CONTACT_ID,
32                CATEGORY_ID = qReadOne.CATEGORY_ID,
33                FIRST_NAME = qReadOne.FIRST_NAME,
34                LAST_NAME = qReadOne.LAST_NAME
35                ) /
>

36
37        <cfreturn true />
38
39    </cfif>
40    
41    <!--- [8] --->
42    <cfreturn false />
43    
44</cffunction>

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
view plain print about
1<!--- Create an Contact bean, populating the CONTACT_ID --->
2<cfset contact = createObject("component", "Contact").init( CONTACT_ID = 4 ) />
3
4<cfoutput>
5ContactID: #contact.getContactID()#<br />
6CategoryID: #contact.getCategoryID()#<br />
7First Name: #contact.getFirstName()#<br />
8Last Name: #contact.getLastName()#
9</cfoutput>
10
11<hr />
12
13<!--- Create an instace of the Contact Data Access Object --->
14<cfset contactDAO = createObject("component", "ContactDAO").init( DSN = "myDSN" ) />
15
16<!--- Pass the existing instance of the Contact bean to the read method --->
17<cfset contactDAO.read( contact ) />
18
19<!--- Output the data from the COTNACTS table where CONTACT_ID = 4 --->
20<cfoutput>
21ContactID: #contact.getContactID()#<br />
22CategoryID: #contact.getCategoryID()#<br />
23First Name: #contact.getFirstName()#<br />
24Last Name: #contact.getLastName()#
25</cfoutput>

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()
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    <!--- [3] --->
8    <cfset var qCreateContact = "" />
9    <!--- [4] --->
10    <cftransaction action="begin">
11
12        <!--- [5] --->
13        <cftry>
14
15            <!--- [6] --->
16            <cfquery name="qCreateContact"
17                datasource="#variables.DSN#">

18                INSERT INTO
19                    Contacts
20                    (
21                        CATEGORY_ID,
22                        FIRST_NAME,
23                        LAST_NAME
24                    )
25                VALUES
26                    (
27                        <cfqueryparam
28                            value="#arguments.contact.getCategoryID()#"
29                            cfsqltype="cf_sql_integer" />,
30                        <cfqueryparam
31                            value="#arguments.contact.getFirstName()#"
32                            cfsqltype="cf_sql_varchar" />,
33                        <cfqueryparam
34                            value="#arguments.contact.getLastName()#"
35                            cfsqltype="cf_sql_varchar" />
36                    )
37            </cfquery>
38
39            <!--- [7] --->
40            <cfcatch type="database">
41                <!--- [7.1] --->
42                <cftransaction action="rollback" />
43                <!--- [7.2] --->
44                <cfreturn false />
45            </cfcatch>
46
47        </cftry>
48
49    <!--- [8] --->
50    </cftransaction>
51
52    <!--- [9] --->
53    <cfreturn true />
54
55</cffunction>

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

contact_bean_create.cfm
view plain print about
1<cfset contact = createObject("component", "Contact").init(
2        CATEGORY_ID = 2,
3        FIRST_NAME = "Quirinus",
4        LAST_NAME = "Quirrell"
5        ) /
>

6        
7<cfset contactDAO = createObject("component", "ContactDAO").init( DSN = "myDSN" ) />
8
9<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_IDCATEGORY_IDFIRST_NAME LAST_NAME
11HarryPotter
21HermioneGranger
31RonWeasley
42MinervaMcGonagall
53ArgusFilch
62QuirinusQuirrell

B.4: Update()

ContactDAO.cfc - 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

contact_bean_update.cfm

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_IDCATEGORY_IDFIRST_NAME LAST_NAME
11HarryPotter
21HermioneGranger
31RonWeasley
42MinervaMcGonagall
53ArgusFilch
62GilderoyLockhart

B.5: Delete()

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

3
4    <cfargument name="contact" required="true"
5        type="Contact" hint="Contact bean" />

6
7    <cfset var qDeleteContact = "" />
8
9    <cftransaction action="begin">
10
11        <cftry>
12
13            <cfquery name="qDeleteContact"
14                datasource="#variables.DSN#">

15                DELETE
16                FROM
17                    Contacts
18                WHERE
19                    CONTACT_ID = <cfqueryparam
20                            value="#arguments.contact.getContactID()#"
21                            cfsqltype="cf_sql_integer" />
22            </cfquery>
23            
24            <cfcatch type="database">
25                <cftransaction action="rollback" />
26                <cfreturn false />
27            </cfcatch>
28
29        </cftry>
30
31    </cftransaction>
32
33    <cfreturn true />
34
35</cffunction>

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

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_IDCATEGORY_IDFIRST_NAME LAST_NAME
11HarryPotter
21HermioneGranger
31RonWeasley
42MinervaMcGonagall
53ArgusFilch

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
view plain print about
1<cffunction name="setCategoryLabel" access="private" output="false" returntype="void">
2    <cfargument name="CATEGORY_LABEL" required="true" type="string" hint="Category Label" />
3    <cfset variables.instance.CATEGORY_LABEL = arguments.CATEGORY_LABEL />
4</cffunction>
5<cffunction name="getCategoryLabel" access="public" output="false" returntype="string">
6    <cfreturn variables.instance.CATEGORY_LABEL />
7</cffunction>

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

Contact.cfc - update to init()
view plain print about
1<cffunction name="init" access="public" output="false" returntype="Contact">
2
3    <cfargument name="CONTACT_ID" required="false" type="numeric" default="0" hint="Primary Key" />
4    <cfargument name="CATEGORY_ID" required="false" type="numeric" default="0" hint="Foreign Key" />
5    <cfargument name="FIRST_NAME" required="false" type="string" default="" hint="Contact's first name" />
6    <cfargument name="
LAST_NAME" required="false" type="string" default="" hint="Contact's last name" />

7    <cfargument name="CATEGORY_LABEL" required="false" type="string" default="" hint="Contact's Category Label" />
8
9    <cfset variables.instance = structNew() />

10
11    <cfset setContactID( arguments.CONTACT_ID ) />

12    <cfset setCategoryID( arguments.CATEGORY_ID ) />

13    <cfset setFirstName( arguments.FIRST_NAME ) />
14    <cfset setLastName( arguments.LAST_NAME ) />
15    <cfset setCategoryLabel( arguments.CATEGORY_LABEL ) />
16
17    <cfreturn this />
18
19</cffunction>

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()
view plain print about
1<cffunction name="read" access="public" output="false" returntype="boolean">
2
3    <cfargument name="contact" required="true" type="Contact" hint="Contact bean" />
4
5    <cfset var qReadOne = "" />
6
7    <cfquery name="qReadOne" datasource="#variables.DSN#">
8        SELECT
9            a.CONTACT_ID,
10            a.CATEGORY_ID,
11            a.FIRST_NAME,
12            a.LAST_NAME,
13            b.CATEGORY_LABEL
14        FROM
15            CONTACTS a
16        LEFT JOIN
17            CATEGORIES b
18                ON b.CATEGORY_ID = a.CATEGORY_ID
19        WHERE
20            CONTACT_ID = <cfqueryparam
21                            value="#arguments.contact.getContactID()#"
22                            cfsqltype="cf_sql_integer" />
23    </cfquery>
24
25    <cfif qReadOne.recordcount eq 0>
26
27        <cfreturn false />
28
29    <cfelse>
30
31        <cfset arguments.contact.init(
32                CONTACT_ID = qReadOne.CONTACT_ID,
33                CATEGORY_ID = qReadOne.CATEGORY_ID,
34                FIRST_NAME = qReadOne.FIRST_NAME,
35                LAST_NAME = qReadOne.LAST_NAME,
36                CATEGORY_LABEL = qReadOne.CATEGORY_LABEL
37                ) /
>

38
39    </cfif>
40
41    <cfreturn true />
42
43</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.