SQL Zone is brought to you in partnership with:

Bob Silverberg has been developing software for over twenty years, the past ten of which have been devoted to web application development with ColdFusion. He runs a small development company from his home in Toronto, where he divides his time between client work, open source development and taking care of his two young sons. He is the creator of the ValidateThis validation framework and an active contributor to the Model-Glue and MXUnit frameworks. He participates actively on a number of ColdFusion mailing lists and blogs at www.silverwareconsulting.com. Bob has posted 1 posts at DZone. View Full User Profile

An Introduction to ColdFusion Object-Relational Mapping

05.10.2010
| 15415 views |
  • submit to reddit

ColdFusion has always been a platform designed to enable Rapid Application Development (RAD). With the release of ColdFusion 9, another powerful RAD feature has been added to the toolkit; baked in Object Relational Mapping (ORM) powered by the enterprise level Java solution Hibernate.

In this article, co-authored by John Whish, we will explain in a bit more detail what ColdFusion ORM is all about, and will take you through examples of ColdFusion ORM in action including basic configuration, mapping objects, maintaining and displaying data using objects, and defining relationships.

ColdFusion ORM allows you to build object oriented applications, without needing to worry about the problem of mapping your objects' data to a relational database. What does that mean to you? It means that you can focus your time and energy on building a rich business layer instead of writing lots of code to handle the Create, Read, Update and Delete (often referred to as CRUD) operations. ColdFusion ORM will generate your SQL statements for you, and also has the ability to create and update your database structure. This not only saves you time during development but also makes maintenance simpler and less error prone.

Requirements

To run the code samples in this article you will need a copy of ColdFusion 9. There is a free edition for developers which you can download here.  If you have not installed ColdFusion before here is a guide.

Setting up Your Application

ColdFusion uses a special file called Application.cfc to define an application. To use ColdFusion ORM, you need to specify two settings in this file:

this.ormenabled = true;

 

which tells ColdFusion that you want to use the ORM, and:

this.datasource = "cfdocexamples";

 

which tells ColdFusion which datasource will be used for communication between the ORM and a relational database.  For the examples in this article we are going to use an existing database which is installed with ColdFusion when you install the samples. The installation process will also create the datasource (called cfcdocexamples) for you.

Your final Application.cfc should look something like this:

component
{
this.name = "DZone_ORM_Example";
this.ormenabled = true;
this.datasource = "cfdocexamples";
}

 

We've added the this.name setting, which is not required for ORM functionality, but should be included in an Application.cfm file. Throughout this article we're using ColdFusion's script-based syntax, which has been greatly enhanced in ColdFusion 9. If you've used ColdFusion before, and you're more comfortable with the tag-based syntax, by all means use that. Your code will look slightly different than ours, of course.

Mapping an Object to the Database

Objects in ColdFusion are defined in files known as ColdFusion Components, or CFCs for short.  In order to map a ColdFusion object to a database table using the ORM, you must create a CFC for that object. When creating an object that maps to an existing table, you only have to tell the ORM which table to use, and it can automatically create a property for each column in the table. To do this, just give your CFC the same name as the table. For example, for the table called EMPLOYEE, create a CFC called Employee.cfc and add the following code:

Employee.cfc

component persistent="true"
{
}

 

Using the persistent="true" attribute tells ColdFusion that you want the ORM to manage the data for this object. Let's take our Employee object for a test drive and see if it worked.

Create a file called test.cfm and add the following code to it:

test.cfm

<cfscript>
ORMReload();
WriteDump( EntityLoad( "Employee" ) );
</cfscript>

 

The first time your application is accessed a number of things have to happen to load the ORM, and these take a reasonable amount of processing time. Because of this, the ORM is not reloaded on each and every request by default. This means that, when you make a change to your ORM configuration, either in your Application.cfc file or in one of your persistent cfcs, you need to tell the ORM to reload in order to pick up those changes. You do this using the ORMReload function.  As we'll be making a number of changes to our persistent cfcs during the article we've simply included an ORMReload call at the top of the test.cfm file.

Run this script in a browser and you'll see:

The persistent attribute in Employee.cfc tells ColdFusion that this CFC maps to a database table. As we haven't explicitly defined any properties for the Employee object, ColdFusion introspects the database to map the Employee object, creating one property for each column that is found.

While that is pretty RAD, you may not be happy with the column names as they're defined in your table. For example, we'd like to have a property called employeeId, rather than emp_id. We can rename table columns by adding property statements to our CFC. Note that once we define a property in our CFC we must define all properties that we wish our object to have - the ORM will no longer pick them up automatically. This is actually a feature, as it allows us to define properties only for the columns that we care about.

Let's add property statements to our CFC for some of the columns:

Employee.cfc

component persistent="true"
{
property name="employeeId" column="emp_id" fieldtype="id" type="numeric" generator="increment";
property name="firstName";
property name="lastName";
property name="salary";
property name="contract";
}

 

In addition to renaming the emp_id column we've also told the ORM that is it the primary key to our table by specifying the fieldtype attribute to have a value of "id". The ORM needs to know this to be able to retrieve individual records and to update and delete records. We specified that the column has a numeric datatype and that the generator attribute has a value of increment, which tells the ORM that it should automatically generate a primary key for us for new records. There are a number of generators available, all of which are covered in the ColdFusion documentation.

Running our code will now yield the following:

Notice that the emp_id property is now called employeeId, and that we no longer see properties for contract_file, project_docs or startdate.

Loading data

We have already used the ORM to load some data, in our test.cfm file. Let's explain what we did.

Test.cfm uses the EntityLoad function to ask the ORM to return all records from the Employee table:

WriteDump( EntityLoad( "Employee" ) );

 

EntityLoad can do more that simply return all records in a table, and we'll look at that in a moment. For now you should know that what it actually returns is an array of objects, Employee objects to be exact. We tell it that we want an array of Employee objects by passing the argument "Employee" into the function. We are using what is referred to as the entityName of the Employee object. By default the entityName of an object is the same as the name of the CFC, so Employee.cfc has an entityName of "Employee".  We can change the entityName of an object by specifying the entityName attribute on the component tag, like so:

Employee.cfc

component persistent="true" entityName="myEmployee"
{
...
}

 

That would yield an object with an entityName of "myEmployee". Each persistent object in your application must have a unique entityName, so if you need to have two CFCs with the same filename, you can resolve the issue by giving one of them a specific entityName via the entityName attribute.

Displaying data

Although our test.cfm file is displaying the data retrieved from the database,  we haven't outputted it to the browser in a particularly friendly way. Now would be a good time to change that, but before we do that we'll quickly revisit the output from test.cfm.

The bit we are interested in are the methods. As you can see ColdFusion has created two methods  for each property. One is prefixed with get, the other is prefixed with set. The get methods allow us to get the value for that property. Let's take a look at that now.

list.cfm

<cfscript>
ORMReload();
employees = EntityLoad( 'Employee' );
</cfscript>

<cfoutput>
<table>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Salary</th>
</tr>
<cfloop array="#employees#" index="employee">
<tr>
<td>#employee.getEmployeeID()#</td>
<td>#employee.getFirstName()#</td>
<td>#employee.getLastName()#</td>
<td>#employee.getSalary()#</td>
</tr>
</cfloop>
</table>
</cfoutput>

 

We are simply looping through the array of employees that EntityLoad returns and calling the appropriate get method (also referred to as a getter) for each property. So to show the first name, we use getFirstName.

Inserting Data

ColdFusion has two functions that we need to use in order to insert data into our database; EntityNew and EntitySave. Let's take a look at how we would use them to add an Employee to the database.

add.cfm

<cfscript>
employee = EntityNew( "Employee" );

employee.setFirstName( "Sam" );
employee.setLastName( "Smith" );
employee.setSalary( 55000 );
employee.setContract( "Y" );

EntitySave( employee );
</cfscript>

 

As you might guess, EntityNew creates a new blank Employee object. Once again we are passing the entityName of our object into the function. We can then use the set methods (also referred to as setters) that ColdFusion automatically creates for us to populate the object. Finally we call EntitySave to persist our new Employee and save our changes to the database. Note that we didn't have to call a setter for employeeId because we specified a generator of increment, which tells the ORM to generate the primary key for us. If we now run list.cfm again, we'll see a new employee called "Sam Smith".

Updating Data

To update an existing object we need to be able to first get a ColdFusion object that represents the record in the database. The easiest way to do that is to use another new ColdFusion function: EntityLoadByPK. This function allows us to specify the primary key of a record and will then return an object to us with that record's data. Once we have that object we can call setters to change the data:

edit.cfm

<cfscript>
employee = EntityLoadByPK( "Employee", 1 );

// change the salary
employee.setSalary( 125000 );
</cfscript>

You may wonder why there isn't an EntitySave after the call to setSalary. When you load an existing object from the database, then any changes you make to that object will be persisted to the database at the end of the request, whether you call EntitySave or not. This is the default behaviour of ColdFusion ORM, and can be changed, but that topic is beyond the scope of this article.

Deleting Data

We've covered Create, Read and Update, so that just leaves Delete. Deleting data from the database is a simple task using EntityLoadByPK and a new function called EntityDelete.

delete.cfm

<cfscript>
// load an entity by id
employee = EntityLoadByPK( "Employee", 1 );

// delete the entity
EntityDelete( employee );
</cfscript>

 

If you run list.cfm you will see that the employee with an employeeId of 1 has been removed.

Relationships

Thus far we've been looking at the employee entity in isolation, but when you start working with objects you'll quickly discover that objects have relationships with each other.  We're going to look at the three basic types of relationships, which will cover the majority of use cases you will encounter: many-to-one, one-to-many and many-to-many.

Many-to-One Relationship

Each Employee belongs to a particular Department. We describe this as a many-to-one relationship from the perspective of the Employee, because many Employees belong to one Department. Did you notice how we said that? Because we're describing the relationship from the point of view of the Employee we say the word "Employee" first, and we end up with many Employees belong to one Department. If we wanted to describe the relationship from the perspective of the Department, we'd say that one Department has many Employees, so, from the point of view of the Department it would be a one-to-many relationship.

Because we are defining the Employee entity, and adding the relationship to it, we have a many-to-one on our hands.

We add the relationship to the Employee entity by adding a new department property, like so:

Employee.cfc

component persistent="true"
{
property name="employeeId" column="emp_id" fieldtype="id" type="numeric" generator="increment";
property name="firstName";
property name="lastName";
property name="salary";
property name="contract";

property name="department" fieldtype="many-to-one" cfc="Department" fkcolumn="dept_id";
}

 

We use the fieldtype attribute to tell the ORM that this property represents a many-to-one relationship, and, because a relationship is about connecting two objects to each other, we also have to tell the ORM where to find the definition of the other object, which is what the cfc attribute is for. We also tell the ORM the name of the database column that will be used as the foreign key (via the fkcolumn attribute). This fkcolumn attribute is optional with some databases. We've said that the department property will contain an object which is defined in Department.cfc, so we need to create that next.

Department.cfc

component persistent="true" table="departmt"
{
property name="id" column="dept_id" fieldtype="id" type="numeric" generator="increment";
property name="name" column="dept_name";
property name="location";
}

 

Notice that we've used the table attribute when defining the component. This allows us to name our cfc Department.cfc even though the table in the database is called departmt. We've also created friendlier property names for a couple of the columns in the table.

If we now browse to our test.cfm page, the dump should look like this:

Notice that we can now see a Department object contained within the department property of each Employee. If we want to change an employee's Department, we can call the setter that is automatically created for us:

edit.cfm

<cfscript>
employee = EntityLoadByPK( "Employee", 2 );

// load the Department to assign
department = EntityLoadByPK( "Department", 3 );

// change the location
employee.setDepartment( department );
</cfscript>

 

One-to-Many Relationship

An Employee can have one or more of phone numbers. For example an Employee may have a home phone number, a work phone number and a mobile phone number.  From the perspective of the Employee, one Employees can have many phone numbers, so we have a one-to-many relationship on our hands.

Once again, we add the relationship to the Employee entity by adding a new phoneNumbers property:

Employee.cfc

component persistent="true"
{
property name="employeeId" column="emp_id" fieldtype="id" type="numeric" generator="increment";
property name="firstName";
property name="lastName";
property name="salary";
property name="contract";
property name="department" fieldtype="many-to-one" cfc="Department" fkcolumn="dept_id";

property name="phoneNumbers" fieldtype="one-to-many" cfc="PhoneNumber" fkcolumn="emp_id" type="array" singularname="phoneNumber";
}

 

This time we use the fieldtype attribute to tell the ORM that this property represents a one-to-many relationship, and, once again, we use the cfc attribute to point to the definition of the object that will be contained in this property. Because an Employee can have more than one PhoneNumber, we name this property phoneNumbers (notice the plural name). This property will contain a collection of PhoneNumbers, and we can choose to store that collection as an array or as a structure. For the most part it is simpler to use an array, so that's what we've chosen here by specifying "array" for the type attribute. When ColdFusion automatically creates methods for maintaining this collection it will use the property name to name the methods. We want our methods called addPhoneNumber and removePhoneNumber (rather than addPhoneNumbers), so we specify "phoneNumber" for the singularname attribute.

We now need a PhoneNumber.cfc to define our PhoneNumber entity. The table for this does not already exist in the sample database, but never fear. We can ask the ORM to create the table for us automatically. In order to do that we must add a line to our Application.cfc file:

Application.cfc

component
{
this.applicationname = "DZone_ORM_Example";
this.ormenabled = true;
this.datasource = "cfdocexamples";
this.ormsettings = { dbcreate="update" };
}

 

You can further configure the ORM by creating an ormsettings structure in the this scope and specifying a number of keys in it. These are all covered in the documentation, so for now we'll just look at the one we need to enable table creation. The dbcreate setting tells the ORM whether we want it to make changes to our database structure based on our cfc entity definitions. The default value is none, which tells the ORM to not make any changes. That is the setting that you would want to use in a production environment. The update setting tells the ORM to go ahead and make changes to the structure of the database based on our cfcs, so we've enabled it here. A third option is dropcreate which tells the ORM to drop and then recreate all tables in the database which have been defined via cfcs. Both update and dropcreate can be very useful in a development environment.

Now that we've told the ORM to make changes to our database for us, we can simply define the new PhoneNumber entity:

PhoneNumber.cfc

component persistent="true"
{
property name="phoneNumberId" fieldtype="id" type="numeric" generator="increment";
property name="countryCode";
property name="areaCode";
property name="phoneNumber";
}

 

The next time the ORM is reloaded it will create the PhoneNumber table for us if it doesn't already exist. We already have an ORMReload statement at the top of the test.cfm file, so simply browsing to that will create the PhoneNumber table for us.

To add a PhoneNumber to an Employee, we make use of the addPhoneNumber method that is created for us:

edit.cfm

<cfscript>
employee = EntityLoadByPK( "Employee", 2 );

// create a new PhoneNumber
phoneNumber = EntityNew( "PhoneNumber" );
phoneNumber.setCountryCode( "1" );
phoneNumber.setAreaCode( "416" );
phoneNumber.setPhoneNumber( "555-1212" );

EntitySave( phoneNumber );

// add the number to the Employee
employee.addPhoneNumber( phoneNumber );
</cfscript>

 

Now that we've added the phone number to Employee 2, if we run test.cfm again we'll see the following:



Many-to-Many Relationship

An Employee can speak one or more Languages, and a Language can have one or more speakers. In this case the relationship looks like a one-to-many from both sides (one Employee speaks many Languages and one Language is spoken by many Employees) , and that's exactly what a many-to-many relationship is.

Let's add this relationship to the Employee entity by adding a new property:

Employee.cfc

component persistent="true"
{
property name="employeeId" column="emp_id" fieldtype="id" type="numeric" generator="increment";
property name="firstName";
property name="lastName";
property name="salary";
property name="contract";
property name="department" fieldtype="many-to-one" cfc="Department" fkcolumn="dept_id";
property name="phoneNumbers" fieldtype="one-to-many" cfc="PhoneNumber" fkcolumn="emp_id" type="array" singularname="phoneNumber";

property name="languages" fieldtype="many-to-many" cfc="Language" fkcolumn="emp_id" type="array" singularname="language" inversejoincolumn="languageId" linktable="Employee_Language";
}

 

The many-to-many property is very similar to the one-to-many, the only differences being that we must specify "many-to-many" for the fieldtype attribute, and that we must provide a value for the inversejoincolumn attribute, which is the name of the primary key of the other entity in the relationship, and the linktable attribute which is the name of the table to use to store this relationship (as a many-to-many always requires an intermediary table).

Now we just need a Language.cfc to define a Language entity:

Language.cfc

component persistent="true"
{
property name="languageId" fieldtype="id" type="numeric" generator="increment";
property name="name";
}

 

As with the PhoneNumber table, the next time the ORM is reloaded it will create the Language table for us if it doesn't already exist.

The code that we use to add a Language to an Employee is identical to that which we used to add a PhoneNumber, we simply use the addLanguage method. The following example creates two new Languages and adds them to an Employee:

edit.cfm

<cfscript>
employee = EntityLoadByPK( "Employee", 2 );

// new Language
english = EntityNew( "Language" );
english.setName( "English" );
EntitySave( english );
// add it to the Employee
employee.addLanguage( english );

// load another Language
french = EntityNew( "Language" );
french.setName( "French" );
EntitySave( french );
// add it to the Employee
employee.addLanguage( french );
</cfscript>

 

After running edit.cfm, we can check to see if the two Languages have been added to our Employee. The output from test.cfm should look like:

Summary

You have now seen how to do basic CRUD operations with the ORM, as well as how to map objects and relationships. We hope that this introduction to ColdFusion 9 ORM has piqued your interest and that you will continue to learn about it on your own. An excellent place to start is the ColdFusion ORM chapter in the Developing ColdFusion 9 Applications documentation available here.

 

AttachmentSize
Figure1.png99.68 KB
Figure2.png57.41 KB
Figure3.png95.87 KB
Figure4.png58.31 KB
Figure5.png59.7 KB
Figure6.png48.24 KB
Figure7.png78.15 KB
JohnWhish.jpg164.07 KB
Published at DZone with permission of its author, Bob Silverberg.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)