Vinny,
On one of my bigger applications I took a cue from Java and implemented
a database access object like structure. Basically in plain English is
you create a CFC or CFCs strictly to talk to your db.
So if you have an Employee Class I would built a corresponding
Employe_Store cfc. Within that CFC add a method (i.e. getEmployeInfo or
Open()) which grabs the data from the database and returns it. I have
chosen to return the information not as a query (what if you switch to
xml) but as an object.
So for example my constructor in my Employee.cfc would be something like
<cffunction name="construct">
<cfargument name="employeeID" type="numeric" required="yes">
<cfset construct = CreateObject('Component','Employee_Store') />
<cfset construct.open(arguments.employeeID)>
<cfset employee.setName( construct.employeeName ) />
</cffunction>
This will give you maximum flexibility while separating your db from
your model classes
HTH
Sean Scott
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Kairoh!
Sent: Monday, December 08, 2003 7:43 AM
To: [EMAIL PROTECTED]
Subject: [CFCDev] Wanted: OOP Best Practice for CFC's with database
interaction
Hi everyone,
I have build a simple employee CFC - shown below - that interacts with
a
database. This CFC uses a utilities CFC for handling zero-length strings
and
preparing datefields for a database insert or update. The CFC does the
job
as expected. So far so good. But there are two questions bothering me:
1. From an OOP perspective an employee.cfc should not contain a
listEmployees method. How could I solve this? Sometimes you need a list
of
employees. How should this be done in a OOP best practice example? Two
CFC's? A design pattern?
2. I have the idea my implementation for accessing, inserting and
updating
could be done in a much cleaner fashion. Does anyone has a suggestion
how to
improve it.
In summary, can anyone of you provide me with a best practice example
for
building an OOP-based CFC with integrated database interaction that does
listings too. I have read Hal's book, but it does not discuss database
access implementations. Ben's book has a nice example of OOP and
database
access but the example is based on a CFC/CFM combination. I am planning
to
use the CFC as part of a RIA application(CFC/SWF combination). All
suggestions, links, book titles, articles are welcome.
This is my first time on this list. (Don't shoot me, I have searched the
archives but could not find the answer to my question).
Vinny
<cfcomponent displayname="Employee dataprocessing services" hint="This
component provides employee information services">
<cffunction name="listEmployees" access="remote" output="no"
returntype="query" displayname="List employees" hint="Returns a list of
employees">
<!--- Optional keyword argument --->
<cfargument name="Keyword" type="string" required="false"
hint="Optional
search criteria; if not given, all employees are returned.">
<!--- Get the employees information --->
<cfset var q_EmployeesInfo="">
<cfquery name="q_EmployeesInfo"
datasource="#APPLICATION.DataSource#">
SELECT Employees.EmployeeID,
Offices.OfficeName,Employees.FirstName
FROM Employees, Offices
WHERE Employees.Office = Offices.OfficeID
<!--- If a keyword has been specified --->
<cfif IsDefined("ARGUMENTS.Keyword")>
AND FirstName LIKE '%#ARGUMENTS.Keyword#%' OR
LastName
LIKE '%#ARGUMENTS.Keyword#%')
</cfif>
ORDER BY Employees.LastName
</cfquery>
<!--- Finally, return the populated employee query. --->
<cfreturn q_EmployeesInfo>
</cffunction>
<cffunction name="getEmployee" access="remote" output="no"
returntype="struct" displayname="Get employee information" hint="Returns
detailed information about the specified employee">
<cfargument name="EmployeeID" type="numeric" required="true"
hint="ID
specifying the particular employee to get">
<!--- Get the employee information --->
<cfset var q_EmployeeInfo="">
<cfquery name="q_EmployeeInfo"
datasource="#APPLICATION.DataSource#">
SELECT *
FROM Employees, Offices
WHERE Employees.Office = Offices.OfficeID
AND EmployeeID =
<cfqueryparamvalue="#ARGUMENTS.EmployeeID#" cfsqltype="cf_sql_integer">
</cfquery>
<!--- If we do not find a record for the employee, the object ID
is
invalid. Throw an exception to notify the caller --->
<cfif not q_EmployeeInfo.RecordCount>
<cfthrow message="ERROR_UNKNOWN_USER">
</cfif>
<!--- Create a structure for the employee information. --->
<cfset st_Employee = StructNew()>
<!--- Populate the employee structure with employee
information.--->
<cfset st_Employee.EmployeeID =
q_EmployeeInfo.EmployeeID>
<cfset st_Employee.OfficeID =
q_EmployeeInfo.OfficeID>
<cfset st_Employee.FirstName =
q_EmployeeInfo.FirstName>
<cfset st_Employee.Login = q_EmployeeInfo.Login>
<cfset st_Employee.Password =
q_EmployeeInfo.Password>
<cfset st_Employee.LastName =
q_EmployeeInfo.LastName>
<cfset st_Employee.BirthDate =
q_EmployeeInfo.BirthDate>
<cfset st_Employee.Email = q_EmployeeInfo.Email>
<cfset st_Employee.Remarks =
q_EmployeeInfo.Remarks>
<cfset st_Employee.Picture =
q_EmployeeInfo.Picture>
<!--- Finally, return the populated employee structure. --->
<cfreturn st_Employee>
</cffunction>
<cffunction name="createEmployee" access="remote" output="no"
returntype="struct" displayname="Insert new employee" hint="Inserts a
new
employee in the system">
<cfargument name="Office" type="numeric" required="false"
default=""
hint="The employee's office">
<cfargument name="Login" type="string" required="true" hint="The
user's login">
<cfargument name="Password" type="string" required="true"
hint="The
user's password">
<cfargument name="LastName" type="string" required="false"
default="" hint="The user's last name">
<cfargument name="BirthDate" type="date" required="false"
default=""
hint="The user's birthdate">
<cfargument name="Email" type="string" required="false"
default=""
hint="The user's email">
<cfargument name="Remarks" type="string" required="false"
default=""
hint="Remarks concerning the user">
<cfargument name="Picture" type="string" required="false"
default=""
hint="The user's photo">
<!--- Create a structure for the employee information. --->
<cfset st_Employee = StructNew()>
<cfset st_Employee.Login = " ">
<!--- Before we add the employee, we need to make sure that the
employee's login does not already exist. If the employee does exist,
we'll
throw an exception. --->
<cfif IsDefined("ARGUMENTS.Login")>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.Login">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Login#">
</cfinvoke>
</cfif>
<cfquery name="EmployeeCheck"
datasource="#APPLICATION.DataSource#">
SELECT *
FROM Employees
WHERE Login = <cfqueryparam
value="#st_Employee.Login#"
cfsqltype="cf_sql_varchar">
</cfquery>
<cfif EmployeeCheck.RecordCount>
<cfthrow message="ERROR_USER_EXISTS">
</cfif>
<cftransaction action="begin"/>
<cfset st_Employee.Office = " ">
<cfset st_Employee.Login = " ">
<cfset st_Employee.Password = " ">
<cfset st_Employee.LastName = " ">
<cfset st_Employee.BirthDate = " ">
<cfset st_Employee.Email = " ">
<cfset st_Employee.Remarks = " ">
<cfset st_Employee.Picture = " ">
<!--- Add the information from the arguments if they exist. --->
<cfif IsDefined("ARGUMENTS.Office")>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.Office">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Office#">
</cfinvoke>
</cfif>
<cfif IsDefined("ARGUMENTS.Login")>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.login">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Login#">
</cfinvoke>
</cfif>
<cfif IsDefined("ARGUMENTS.Password")>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.password">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Password#">
</cfinvoke>
</cfif>
<cfif IsDefined("ARGUMENTS.LastName")>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.LastName">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.LastName#">
</cfinvoke>
</cfif>
<cfif IsDefined("ARGUMENTS.BirthDate")>
<cfinvoke component="common" method="prepareDateField"
returnvariable="st_Employee.BirthDate">
<cfinvokeargument name="DateString"
value="#ARGUMENTS.BirthDate#">
</cfinvoke>
</cfif>
<cfif IsDefined("ARGUMENTS.Email")>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.email">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Email#">
</cfinvoke>
</cfif>
<cfif IsDefined("ARGUMENTS.Remarks")>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.Remarks">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Remarks#">
</cfinvoke>
</cfif>
<cfif IsDefined("ARGUMENTS.Picture")>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.Picture">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Picture#">
</cfinvoke>
</cfif>
<!--- Insert a employee info record into the database. --->
<cfquery datasource="#APPLICATION.DataSource#">
INSERT INTO Employees (Office,
Login,
Password,
LastName,
BirthDate,
Email,
Remarks,
Picture)
VALUES (<cfqueryparam
value="#st_Employee.Office#"
cfsqltype="cf_sql_integer">,
<cfqueryparam
value="#st_Employee.Login#" cfsqltype="cf_sql_varchar">,
<cfqueryparam
value="#st_Employee.Password#" cfsqltype="cf_sql_varchar">,
<cfqueryparam
value="#st_Employee.LastName#" cfsqltype="cf_sql_varchar">,
<cfqueryparam
value="#st_Employee.BirthDate#" cfsqltype="cf_sql_date">,
<cfqueryparam
value="#st_Employee.Email#" cfsqltype="cf_sql_varchar">,
<cfqueryparam
value="#st_Employee.Remarks#" cfsqltype="cf_sql_varchar">,
<cfqueryparam
value="#st_Employee.Picture#" cfsqltype="cf_sql_varchar">)
</cfquery>
<!--- Commit the database changes. --->
<cftransaction action="commit"/>
<!--- Get the new ID. --->
<cfquery name="q_returnQuery"
datasource="#APPLICATION.DataSource#">
SELECT @@identity
AS newId
</cfquery>
<!--- Finally, return the new ID. --->
<cfreturn getEmployee(q_returnQuery.newId)>
</cffunction>
<cffunction name="editEmployee" access="remote" output="no"
returntype="struct" displayname="Update employee information"
hint="Updates
employee information in the system">
<cfargument name="EmployeeID" type="numeric" required="true"
hint="The unique object ID for the user">
<cfargument name="Office" type="numeric" required="false"
hint="The
employee's office">
<cfargument name="Login" type="string" required="true" hint="The
user's login">
<cfargument name="Password" type="string" required="true"
hint="The
user's password">
<cfargument name="LastName" type="string" required="false"
hint="The
user's last name">
<cfargument name="BirthDate" type="date" required="false"
hint="The
user's birthdate">
<cfargument name="Email" type="string" required="false"
hint="The
user's email">
<cfargument name="Remarks" type="string" required="false"
hint="Remarks concerning the user">
<cfargument name="Picture" type="string" required="false"
hint="The
user's photo">
<!--- Create a structure for the employee's information. --->
<cfset st_Employee = StructNew()>
<cfset st_Employee.EmployeeID = ARGUMENTS.EmployeeID>
<!--- Prepare the user info text fields for updating the
database
--->
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.Office">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Office#">
</cfinvoke>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.Login">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Login#">
</cfinvoke>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.Password">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Password#">
</cfinvoke>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.LastName">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.LastName#">
</cfinvoke>
<cfinvoke component="common" method="prepareDateField"
returnvariable="st_Employee.BirthDate">
<cfinvokeargument name="DateString"
value="#ARGUMENTS.BirthDate#">
</cfinvoke>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.Email">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Email#">
</cfinvoke>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.Remarks">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Remarks#">
</cfinvoke>
<cfinvoke component="common" method="prepareTextField"
returnvariable="st_Employee.Picture">
<cfinvokeargument name="TextField"
value="#ARGUMENTS.Picture#">
</cfinvoke>
<!--- Update the employee's info --->
<cftransaction action="begin"/>
<cfquery datasource="#APPLICATION.DataSource#">
UPDATE Employees
SET Office = <cfqueryparam
value="#st_Employee.Office#" cfsqltype="cf_sql_integer">,
Login = <cfqueryparam
value="#st_Employee.Login#" cfsqltype="cf_sql_varchar">,
Password = <cfqueryparam
value="#st_Employee.Password#" cfsqltype="cf_sql_varchar">,
LastName = <cfqueryparam
value="#st_Employee.LastName#" cfsqltype="cf_sql_varchar">,
BirthDate = <cfqueryparam
value="#st_Employee.BirthDate#" cfsqltype="cf_sql_date">,
Email = <cfqueryparam
value="#st_Employee.Email#" cfsqltype="cf_sql_varchar">,
Remarks = <cfqueryparam
value="#st_Employee.Remarks#" cfsqltype="cf_sql_varchar">,
Picture = <cfqueryparam
value="#st_Employee.Picture#" cfsqltype="cf_sql_varchar">
WHERE EmployeeID = #st_Employee.EmployeeID#
</cfquery>
<!--- Commit the database changes. --->
<cftransaction action="commit"/>
<!--- Finally, return the userdata. --->
<cfreturn getEmployee(st_Employee.EmployeeID)>
</cffunction>
<cffunction name="deleteEmployee" access="remote" output="no"
returntype="numeric" displayname="Delete employee information"
hint="Deletes
all information for the specified employee">
<cfargument name="EmployeeID" type="numeric" required="true"
hint="Unique object ID specifying the particular employee to delete">
<cftransaction action="begin"/>
<!--- Delete the item from tbEmployees --->
<cfquery datasource="#APPLICATION.DataSource#">
DELETE FROM Employees
WHERE EmployeeID = #ARGUMENTS.EmployeeID#
</cfquery>
<!--- Commit the database changes. --->
<cftransaction action="commit"/>
<!--- Finally, return the deleted employee. --->
<cfreturn ARGUMENTS.EmployeeID>
</cffunction>
</cfcomponent>
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email
to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev'
in the message of the email.
CFCDev is run by CFCZone (www.cfczone.org) and supported
by Mindtool, Corporation (www.mindtool.com).
An archive of the CFCDev list is available at
www.mail-archive.com/[EMAIL PROTECTED]
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email
to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev'
in the message of the email.
CFCDev is run by CFCZone (www.cfczone.org) and supported
by Mindtool, Corporation (www.mindtool.com).
An archive of the CFCDev list is available at www.mail-archive.com/[EMAIL PROTECTED]
