I use a Filter object to pass into a Gateway object's readQuery
method. Below is some sample code for a User Account Filter object
and the method call w/in the Gateway. Note, the Filter inherits from a
BaseFilter that provides some standard filter items (insertedBy,
updatedBy, activeFlag) all filters can use.
<cfcomponent name="UserAccountsFilter"
extends="com.attendeeinteractive.projectmngr.v110.model.BaseFilter"
hint="I store filter options used when searching a contact
object">
<cffunction name="init" access="public"
returntype="com.attendeeinteractive.projectmngr.v110.model.users.UserAccountsFilter"
output="false"
hint="I initialize this filter object.">
<cfset super.init()>
<cfset clearMe()>
<cfreturn this>
</cffunction>
<cffunction name="clearMe" access="public" returntype="void"
output="false"
hint="I clear all private variables.">
<cfset super.clearMe()>
<cfset _me.name = "">
</cffunction>
<cffunction name="getName" access="public" returntype="string"
output="false">
<cfreturn _me.name>
</cffunction>
<cffunction name="setName" access="public" returntype="void"
output="false">
<cfargument name="sValue" type="string" required="true">
<cfset _me.name = arguments.sValue>
</cffunction>
</cfcomponent>
<!--- Gateway Method call --->
<cffunction name="readQuery" access="private" returntype="query"
output="false"
hint="I execute a query to return one or more UserAccount
records. Any errors are rethrown.">
<cfargument name="aFilter"
type="com.attendeeinteractive.projectmngr.v110.model.users.UserAccountsFilter"
required="true">
<cfargument name="dbMgr"
type="com.attendeeinteractive.projectmngr.v110.model.DBManager"
required="true">
<cfset var retVal = queryNew("")>
<cfset var local = structNew()>
<cftry>
<cfquery name="local.read_UserAccounts"
datasource="#arguments.dbMgr.getDSN()#"
password="#arguments.dbMgr.getPWD()#"
username="#arguments.dbMgr.getUID()#">
SELECT A.user_account_id AS userAccountID,
A.user_contact_id AS userContactID, B.con_name_first AS firstName,
B.con_name_last AS lastName, B.con_email1 AS email1
FROM user_accounts AS A INNER JOIN contacts AS B ON
A.user_contact_id = B.contact_id
WHERE
<cfif len(arguments.aFilter.getName()) gt 0>
(B.con_name_first LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" maxlength="50"
value="%#arguments.aFilter.getName()#%"> OR
B.con_name_last LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" maxlength="50"
value="%#arguments.aFilter.getName()#%">) AND
</cfif>
<cfif len(arguments.aFilter.getUpdatedBy()) gt 0>
(A.name_updated = <cfqueryparam
cfsqltype="cf_sql_varchar" maxlength="12"
value="#arguments.aFilter.getUpdatedBy()#">) AND
</cfif>
<cfif len(arguments.aFilter.getInsertedBy()) gt 0>
(A.name_inserted = <cfqueryparam
cfsqltype="cf_sql_varchar" maxlength="12"
value="#arguments.aFilter.getInsertedBy()#">) AND
</cfif>
<cfif arguments.aFilter.getActiveFlag() gte 0>
A.fl_active = <cfqueryparam cfsqltype="cf_sql_bit"
value="#arguments.aFilter.getActiveFlag()#">
<cfelse>
A.fl_active IN (0,1)
</cfif>
</cfquery>
<cfset retVal = local.read_UserAccounts>
<cfcatch type="any">
<!--- Log error and rethrow. --->
<cfset arguments.dbMgr.getLogger().logError(this.class() & "
- readQuery: " & cfcatch.detail)>
<cfset retVal = queryNew("")>
<cfrethrow>
</cfcatch>
</cftry>
<cfreturn retVal>
</cffunction>
I personally do not like letting db calls pass in SQL directly as I feel
that breaks my goal of encapsulating the db from the model even if the
db and model are 1 to 1. It might be overkill but I prefer to keep them
separate. If there is an item, such as an order by clause that could
benefit from direct field names I still don't do it. The filter object
stores valid options (not necessarily the field names) and then the
Gateway method sets the correct db level field values. E.g. Filter's
'get/set OrderBy()' allows only the following:
lastname|firstname|company and then the Gateway method calls converts to
the db field such as
<cfif getOrderBy() eq 'firstname'> B.con_name_first<cfelseif
getOrderBy() eq 'lastname'> B.con_name_last<cfelse> B.con_company</cfif>
If I allowed SQL to be passed directly, the caller would need to know
exactly how the query works, i.e. does it join on another table, so it
could pass in a valid value, such as 'B.con_company' instead of
'con_company'. That's just not a 'clean' way to code in my opinion. Not
to mention it is a security hole that could lead to SQL Injection attacks.
Hope this helps.
-Jason
Daniel Roberts wrote:
I recall reading a while back that an ORM framework or code generator
provided basic methods in the Gateway object and then also a
getEntityByCriteria or similarly named method. It would accept a
string that would then be parsed into SQL or it might have just been
the SQL. The latter does feel right and the former could be limiting.
Does anyone have an examples or recommendations for such a method? Thanks
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to
[email protected] with the words 'unsubscribe cfcdev' as the subject
of the email.
CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting
(www.cfxhosting.com).
An archive of the CFCDev list is available at
www.mail-archive.com/[email protected]
--
Jason Daiger
URL: www.jdaiger.com
EML: [EMAIL PROTECTED]
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to
[email protected] with the words 'unsubscribe cfcdev' as the subject of the
email.
CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting
(www.cfxhosting.com).
An archive of the CFCDev list is available at
www.mail-archive.com/[email protected]
