| CategoryID: | </td></tr>
| First Name: | #contact.getFirstName()# |
| Last Name: | #contact.getLastName()# |
</table>
</cfoutput>
```
### output
| ContactID: | 0 |
| CategoryID: |
|
| 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:
- `C`reate
- `R`ead
- `U`pdate
- `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.
- we're going to inject the value of the data source into the DAO
- 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
- 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.
- The function requires a single argument: `a Contact bean.`
- We created two `function local variables (var scope)` named `qReadOne` and `categoryIDArray`.
- A `SELECT` query (named `qReadOne`) to read a specific record from the `CONTACTS` table along with its associated records in `CONTACT_CATEGORIES`.
- 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.
- 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.
- We use a LEFT JOIN to get records from the `CONTACT_CATEGORIES` table that are associated to the CONTACT_ID we specified.
- 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.
- 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.
- 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.
- 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.
- 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 ) />
| ContactID: | #contact.getContactID()# |
| CategoryID: | </td></tr>
| First Name: | #contact.getFirstName()# |
| Last Name: | #contact.getLastName()# |
</table>
</cfoutput>
```
### output
| ContactID: | 1 |
| CategoryID: |
|
| First Name: | Harry |
| Last Name: | Potter |
### B.3: Create()
### ContactDAO.cfc - create()
```cfscript
<cfset var categoryIDArray = arrayNew(1) />
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
- The `returntype` is "boolean".
- The function requires a single argument: `a Contact bean`.
- We create a few function local variables (`var scope`) to handle data.
- `qCreateContact` - query
- `qNewContact` - query
- `newContactID` - integer
- `categoryIDArray` - array
- `x` - integer; the index of a CFLOOP
- `qCreateContactCategory` - query
- If your database can handle transactions, use `cftransaction` to begin one.
- Use `cftry` (with cfcatch) to handle any errors we may encounter with the query.
- An `INSERT` query (named `qCreateContact`) to add a record to the Contacts table.
- A `SELECT` query (named `qNewContact`) to retrieve the newly created ID from the CONTACTS table. We then assign this to the variable `newContactID`.
- MySQL uses LAST_INSERT_ID()
- SQL Server uses SCOPE_IDENTITY
- Check your database for its specific function.
- 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.
- 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.
- Should an error occur during the `INSERT`, the `cfcatch` will be triggered, allowing us to
- 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.
- Since the `INSERT` failed, we can return `false` from the function and return to the calling process.
- 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.
- 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_ID | FIRST_NAME | LAST_NAME |
|---|
| 1 | Harry | Potter |
| 2 | Hermione | Granger |
| 3 | Ron | Weasley |
| 4 | Minerva | McGonagall |
| 5 | Argus | Filch |
| 6 | Draco | Malfoy |
</tbody></table>
| CONTACT_CATEGORIES |
|---|
| CONTACT_ID | CATEGORY_ID |
|---|
| 1 | 1 |
| 1 | 4 |
| 2 | 1 |
| 3 | 1 |
| 3 | 4 |
| 4 | 2 |
| 5 | 3 |
| 6 | 1 |
| 6 | 4 |
### B.4: Update()
### ContactDAO.cfc - update()
```cfscript
<cfset var categoryIDArray = arrayNew(1) />
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()`.
- `[6]`: The first query, `qUpdateContact`, updates the CONTACTS table using data from the bean. This time, including the `CONTACT_ID`.
- `[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.
- `[8]`: Again we get the category ID array from the contact bean and assign it to the local variable `categoryIDArray`.
- `[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,
- `[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_ID | FIRST_NAME | LAST_NAME |
|---|
| 1 | Harry | Potter |
| 2 | Hermione | Granger |
| 3 | Ron | Weasley |
| 4 | Minerva | McGonagall |
| 5 | Argus | Filch |
| 6 | Rubeus | Hagrid |
| CONTACT_CATEGORIES |
|---|
| CONTACT_ID | CATEGORY_ID |
|---|
| 1 | 1 |
| 1 | 4 |
| 2 | 1 |
| 3 | 1 |
| 3 | 4 |
| 4 | 2 |
| 5 | 3 |
| 6 | 2 |
| 6 | 3 |
### 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:
- We pass in a contact bean and we only really need to populate the CONTACT_ID.
- We delete that ID's record from `CONTACTS` table.
- 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 ) />
```
| |