ColdFusion 9 ORM

1. ColdFusion 9 ORM Object Relational Mapping Overview Presented By: Denard Springle Northern Virginia ColdFusion Users Group

2. Advantages of ORM • Database Vendor Independence • Caching & Concurrency • Performance Optimization • Faster Application Development • Reduces or Eliminates Database Design Cycles • Inherent Object Oriented Programming Application Development Framework

3. CF~8 vs. CF9 DAO ORM Persistent CFCs Inherent Getters & Setters Inherent Object Relational Mapping Automates database design Automates relationships Reduces Code Reduces Complexity • SQL Statements • <cfquery>, <cfinsert>, <cfupdate> • Tables as Bean CFCs • CRUD (Create, Retrieve, Update, Delete) written for each object as DAO • Complex DAO relationships • Increasingly complex code management

4. Step One: Application.cfc • <cfcomponent><cfset this.name = “MyApplication”><cfset this.ormenabled = “true”><cfset this.datasource = “MyDatasource”></cfcomponent>Minimum required Application.cfc parameters for an ORM enabled application. • <cfset this.ormsettings = [struct]>Defines additional settings that can be used to configure ORM.

5. Step Two: Map your CFC to the Database Table • customer.cfc • <cfcomponent persistent=“true”><cfproperty name=“id” generator=“increment”><cfproperty name=“name”><cfproperty name=“age”><cfproperty name=“email”></cfcomponent> • One <cfproperty> should be created for each column in the table • To specify a column mapping use column=“<column name>” in the <cfproperty> tag, otherwise the name is used as the column name

6. Step 3: Perform CRUD Operations • <cfsetaEx = EntityLoad(“customer”)> Retrieves all records from the customer table. • <cfsetoEx = EntityNew(“customer”)><cfsetoEx.setname(“Jane Gum”)><cfsetoEx.setage(“35”)><cfsetoEx.setemail(“nospam@nospam.com”)><cfsetEntitySave(oEx)><cfsetormflush()> Creates new record and saves it to the customer table. • <cfsetoEx = EntityLoad(“customer”, 1, true)><cfsetoEx.setage(“45”)><cfsetEntitySave(oEx)><cfsetormflush()> Loads a record, sets a new age and saves it back to customer table. • <cfsetEntityDelete(oEx)><cfsetormflush()> Deletes the record from the customer table

7. Step Four : Define Relationships Part One • book.cfc • <cfcomponent persistent=“true”><cfproperty name=“id” generator=“increment”><cfproperty name=“title”><cfproperty name=“author”></cfcomponent>

8. Step Four : Define Relationships Part Two • customer.cfc • <cfproperty name=“book_id” type=“array” fieldtype=“one-to-many” cfc=“book” fkcolumn=“id”> • This defines a one-to-many relationship between a customer (one) and their books (many)

9. Step Four: Customer.cfc Result • customer.cfc • <cfcomponent persistent=“true”><cfproperty name=“id” generator=“increment”><cfproperty name=“name”><cfproperty name=“age”><cfproperty name=“email”><cfproperty name=“book_id” type=“array” fieldtype=“one-to-many” cfc=“book” fkcolumn=“id”></cfcomponent>

10. Loading Objects (Records) • <cfsetaC = EntityLoad(“customer”)> Retrieves an array of all customer objects • <cfsetoC = EntityLoad(“customer”, 27, true)> Retrieves a customer object whose primary key value is 27. • <cfsetaC = EntityLoad(“customer”, {age=“35”})> Retrieves an array of customer objects whose age is 35. • <cfsetaC = EntityLoad(“customer”, {age=“35”}, “name desc”)> Retrieves an array of customer objects whose age is 35 sorted by name in descending order. • <cfsetoC = EntityLoad(“customer”, {name=“Jane Gum”}, true)> Retrieves a customer object for the customer whose name is ‘Jane Gum’.

11. <cfquery> INSERT vs. ORM <cfquery> INSERT ORM <cfset oEx = EntityNew(“customer”)><cfset oEx.setname(“Jane Gum”)><cfset oEx.setage(“35”)><cfset oEx.setemail(“nospam@nospam.com”)><cfset EntitySave(oEx)><cfset ormflush()> <cfscript>oEx = EntityNew(“customer”);oEx.setName(“Jane Gum”);oEx.setAge(“35”);oEx.setemail(“nospam@nospam.com”);EntitySave(oEx);ormflush();</cfscript> • <cfquery name=“qPutCustomer” datasource=“MyDatasource”>INSERT INTO customer ( name, age, email ) VALUES (<cfqueryparam value="Jane Gum" cfsqltype="cf_sql_varchar“>,<cfqueryparam value="35" cfsqltype="cf_sql_integer“>,<cfqueryparam value="nospam@nospam.com" cfsqltype="cf_sql_varchar"> </cfquery>

12. <cfquery> SELECT vs. ORM <cfquery> SELECT ORM <cfset aCustomers = EntityLoad(“customer”)> <cfset oCustomer = EntityLoad(“customer”, {name=“Jane Gum”}, true)> • <cfquery name=“qGetCustomers” datasource=“MyDatasource”>SELECT * FROM customer</cfquery> • <cfquery name=“qGetCustomer” datasource=“MyDatasource”>SELECT * FROM customerWHERE name = ‘Jane Gum’</cfquery>

13. <cfquery> UPDATE vs. ORM <cfquery> UPDATE ORM <cfset oCustomer.setage(“45”)><cfset EntitySave(oCustomer)><cfset ormflush()> • <cfquery name=“qUpdCustomer” datasource=“MyDatasource”>UPDATE customer SETage = 45WHERE id = #qGetCustomer.id#</cfquery>

14. <cfquery> DELETE vs. ORM <cfquery> DELETE ORM <cfset EntityDelete(oCustomer)><cfset ormflush()> • <cfquery name=“qDelCustomer” datasource=“MyDatasource”>DELETE FROM customer WHERE id = #qGetCustomer.id#</cfquery>

15. <cfquery> vs. ORM Output <cfquery> Output ORM Output <cfoutput>#oCustomer.getname()#<br />#oCustomer.getage()#<br />#oCustomer.getemail()#</cfoutput> <cfset oCustomer.setage(“45”)> <cfoutput>#oCustomer.getname()#<br />#oCustomer.getage()#<br />#oCustomer.getemail()#</cfoutput> <cfset EntitySave(oCustomer)><cfset ormflush()> • <cfoutput>#qGetCustomer.name#<br />#qGetCustomer.age#<br />#qGetCustomer.email#</cfoutput> • <cfset qGetCustomer.age = 45> • <cfoutput>#qGetCustomer.name#<br />#qGetCustomer.age#<br />#qGetCustomer.email#</cfoutput> • <cfquery name=“qUpdCustomer” datasource=“MyDatasource”>UPDATE customer SETage = #qGetCustomer.age#WHERE id = #qGetCustomer.id#</cfquery>

16. Convert Object To Query • <cfset aCustomers = EntityLoad(“customer”)><cfset qCustomers = EntityToQuery(aCustomers)>Loads the customer object array and converts it to a query object. • <cfset aCustomers = EntityLoad(“customer”)><cfset qEmail = EntityToQuery(aCustomers, “email”)>Loads the customer object array and converts only the ‘email’ column to a query object.

17. Hibernate Query Language (HQL) • <cfsetaCustomers = ORMExecuteQuery(“from customer”)> • <cfsetaCustomer = ORMExecuteQuery(“from customer where id = 27”, true> • <cfsetaCustomer = ORMExecuteQuery(“from customer where name=:name”, {name=“Jane Gum”}, true> • <cfsetaCustomers = ORMExecuteQuery(“from customer where age=:age and email=:email”, {age=35, email=“nospam@nospam.com”})> • Built-in functions to obtain data such as getage() and getname() cannot be used if you are using select queries with specific column names. The result will be returned as an array object and values can be retrieved using an array index.

18. Additional Resources • Chapter 8: Coldfusion ORM in the ‘Developing ColdFusion 9 Applications’ reference. • Adobe’s tutorials (available from the CF9 product page) • Ben Forta’s, Ray Camden’s, Dan Wilson’s, Ben Nadel’s (and others) blogs are always excellent references for all things ColdFusion • NVCFUG Adobe Groups Website