back

Object relational mapping in ColdFusion 9 and ColdFusion Builder

by Brian Rinaldi

We ColdFusion developers have long made the case that ColdFusion offers the fastest way to build web applications — a case that was further strengthened with the release of ColdFusion 9 late last year. Nonetheless, until recently we never had a defense for the counterargument that ColdFusion development was made more difficult for new developers by the lack of a full-featured IDE. Not only did ColdFusion Builder change that discussion by providing everything you'd expect from an IDE, such as component and variable insight, code hinting, step debugging, and integrated help/documentation, but it also exceeded expectations in the areas of extensibility and integration with tools like Adobe Flash Builder.

In this article, I examine some of the many productivity features ColdFusion Builder offers by building a simple sample application that uses ColdFusion ORM. In simple terms, ORM is a feature within ColdFusion 9 that enables you to build complex, data-driven applications without having to worry about all the standard CRUD (create, read, update, and delete) queries and logic that you would typically need to write manually. ORM is already easy and powerful, but by utilizing some of the wizards that come bundled with ColdFusion Builder, creating an ORM-based application is now a breeze.

Getting started

To get started, download and install ColdFusion 9 as well as ColdFusion Builder, which is available both as a standalone IDE and as a plug-in within Eclipse. Once you have both applications installed, make sure that ColdFusion Builder sees your ColdFusion server. Within ColdFusion Builder, choose the Servers tab at the bottom of your screen. Click the Add New Server button or right-click and select Add New Server. Follow the wizard by adding your server's settings to the appropriate form fields. In Figure 1, you can see my settings, but yours may be different depending on how your local ColdFusion instance is installed.

Configuring ColdFusion server settings.

Figure 1. Configuring ColdFusion server settings.

Now that you've added your server, you can associate this server with any of your projects as well as start and stop it from within ColdFusion Builder. In the RDS DataView tab on the right side of your default perspective, you should see localhost listed. If you pull down localhost, you should see all your data sources, and if you expand each of those, you can see the tables and columns within each data source.

Add Apptacular

Arguably the most impressive feature in ColdFusion Builder is its extensibility. You can create extensions using pure CFML that run within the Eclipse and ColdFusion Builder environment. This enables anyone with basic ColdFusion development skills to customize the ColdFusion Builder environment – no Java or Eclipse SDK experience needed. In addition, you can share these plug-ins within your workplace or with the community in general. In fact, more than 30 free and open source ColdFusion extensions are currently available at RIAForge.org. Apptacular is an example of one of those free extensions. It was created by Adobe ColdFusion evangelist Terrance Ryan, and it will soon be released on Adobe Labs (you can download a preview release via Terry's article in the Adobe Developer Connection).

The goal of Apptacular is to use the database introspection features within ColdFusion Builder to generate a fully functional application based on the database metadata. Essentially what this does is create scaffolds — a term borrowed from the Ruby on Rails framework — which are essentially default CRUD form and list views. Apptacular also manages the data portion of the application via ColdFusion 9 ORM.

Generating your application with Apptacular

Let's imagine a common workplace situation. Your manager comes to you wanting to be able to manage the employees and departments data in the database. Before ORM and ColdFusion Builder, you probably would have created some components for CRUD data access (DAOs), the build form, and list and detail views. While that may not stretch the limits of your skill set, it's certainly time-consuming and often tedious.

Now using ColdFusion 9 ORM, ColdFusion Builder, and the Apptacular extension, you can create the entire CRUD application for your manager in about five minutes and take the rest of the afternoon off.

For this example, we are going to add some steps to the process to build the project in ColdFusion Builder and create the data source. My database is a simple Apache Derby embedded database containing two simple tables for employees and departments. To create a Derby embedded database in ColdFusion:

  1. Go to your ColdFusion Administrator, and under Data & Services, choose Data Sources.
  2. Enter the name of your data source (in this example, I'm calling my data source ormExample), select the Apache Derby Embedded option, and click Add.
  3. In the form, choose a destination and folder name for your database file, select the Create Database option, and click Submit.

To create your simple data schema, simply run the following code snippet in your application. It will create employees and departments tables and populate both with some sample data:

<cfquery name="createDeps" datasource="ormExample">
	  CREATE TABLE DEPARTMENTS
	  (DEPARTMENTID CHAR(35) NOT NULL
	  CONSTRAINT DEPARTMENTS_PK PRIMARY KEY,
	  TITLE VARCHAR(255))
  </cfquery>
  <cfquery name="createEmps" datasource="ormExample">
	  CREATE TABLE EMPLOYEES
	  (EMPLOYEEID CHAR(35) NOT NULL
	  CONSTRAINT EMPLOYEES_PK PRIMARY KEY,
	  FIRSTNAME VARCHAR(50),
	  LASTNAME VARCHAR(50),
	  EMAIL VARCHAR(100),
	  DEPARTMENTID CHAR(35),
	  CONSTRAINT DEPS_FK
	  FOREIGN KEY (DEPARTMENTID)
	  REFERENCES DEPARTMENTS (DEPARTMENTID))
  </cfquery>
	  
  <!--- add some fake data --->
  <cfset dep1 = createUUID() />
  <cfquery name="insertDepartments"
	  datasource="ormExample">
	  INSERT INTO DEPARTMENTS(DEPARTMENTID,TITLE)
	  VALUES ('#dep1#','Department of Redundancy Department')
  </cfquery>
  <cfset dep2 = createUUID() />
  <cfquery name="insertDepartments"
	  datasource="ormExample">
	  INSERT INTO DEPARTMENTS(DEPARTMENTID,TITLE)
	  VALUES ('#dep2#','Department of Humor Analysis')
  </cfquery>
  <cfset dep3 = createUUID() />
  <cfquery name="insertDepartments"
	  datasource="ormExample">
	  INSERT INTO DEPARTMENTS(DEPARTMENTID,TITLE)
	  VALUES ('#dep3#','Complaints Department')
  </cfquery>
	  
  <cfquery name="insertDepartments"
	  datasource="ormExample">
	  INSERT INTO
	  EMPLOYEES(EMPLOYEEID,FIRSTNAME,LASTNAME,EMAIL,DEPARTMENTID)
	  VALUES
	  ('#createUUID()#','Brian','Rinaldi','brian.rinaldi@gmail.com','#dep1#')
  </cfquery>
  <cfquery name="insertDepartments"
	  datasource="ormExample">
	  INSERT INTO
	  EMPLOYEES(EMPLOYEEID,FIRSTNAME,LASTNAME,EMAIL,DEPARTMENTID)
	  VALUES
	  ('#createUUID()#','Peter','Parker','spidey@dailybugle.com','#dep2#')
  </cfquery>
  <cfquery name="insertDepartments" datasource="ormExample">
	  INSERT INTO
	  EMPLOYEES(EMPLOYEEID,FIRSTNAME,LASTNAME,EMAIL,DEPARTMENTID)
	  VALUES
	  ('#createUUID()#','Bruce','Wayne','bruce@wayneent.com','#dep3#')
  </cfquery>
  <cfquery name="insertDepartments"
	  datasource="ormExample">
	  INSERT INTO EMPLOYEES(EMPLOYEEID,FIRSTNAME,LASTNAME,EMAIL,DEPARTMENTID)
	  VALUES
	  ('#createUUID()#','Clark','Kent','kalel@dailyplanet.com','#dep1#')
  </cfquery>

Now it's time to create your ColdFusion project. In ColdFusion Builder, choose File > New > ColdFusion Project. Give the project a name (mine is Edge Article Orm), keep the default workspace location, and click Next. On the following screen, select the localhost server option you added earlier and click Next.

The next screen enables you to add linked folders associated with your project. This is a nice feature in ColdFusion Builder because it allows you to separate your project workspace from the associated folders as well as add folders from any location. For example, you can add a mapped folder of components. In this case, click the Add button and add the folder where your example files will live in your web root. Then click Finish.

Now that you've gotten the requirements out of the way, it's time to complete the tough task of creating list, detail, and form views for editing employees and departments:

  1. Open the RDS DataView tab (this is typically in the tab bar on the far right of your default ColdFusion Builder perspective).
  2. Expand your ormExample DSN under localhost. Right-click any table in the datasource, and under the Apptacular menu item, choose Generate Application.
  3. For the destination, choose the linked folder you just added in the ormExample project and then click OK.
  4. Since your data schema does not automatically generate IDs, you need to open the employees.cfc and departments.cfc within the CFC folder and change the generator attribute of the employeeID and departmentID from Native to uuid.

Now go to Adobe TV, watch some videos, and learn new skills because that's it; your application is finished. If you view your application (mine is at http://localhost/ormExample), you'll see menu items for Employees and Departments. If you click those, you will see the sample departments and employees that were added by the setup script. If you edit an employee, for example, you will see that Apptacular understood the association between employees and departments (via the foreign key in the database), and each employee object instance is associated with the proper department object instance, which is represented by a select input in the employee form. In fact, if you view the department list, you will see that it even tells you how many employees are in each department, and if you click Read on a department, you will see a list of employees within that department in addition to the department record detail. The list pages are also completely sortable, and if they had more data, they would even be automatically paged.

Managing your database with ORM

One of the amazing things about the Apptacular extension is that it enables you to easily manage all underlying code generation settings and data mappings after generating the application, so you can regenerate your application code as needed. To find these features, right-click the folder containing Apptacular and expand the Apptacular menu item. You should see a list of available actions that will open several dialog boxes to modify the underlying metadata and rules Apptacular uses to generate code.

While this is powerful, ORM itself has the ability to manage your database for you as well. In fact, since Apptacular assumes you already have a schema defined, we built the data schema first. However, ColdFusion ORM could have created the entire data schema for you based on our persistent components. To see how that works, let's tweak some of the Apptacular generated code to enable ORM to add a Title field to our employee data.

ORM's ability to create and manage your data schema is configured via the dbcreate property of ormsettings that is defined in your Application.cfc file. Open the Application.cfc file that Apptacular created for you, and add the following line beneath the This.ormsettings.logSQL property:

This.ormsettings.dbcreate = "update";

Now open the generated /cfc/employees.cfc file and add a TITLE property beneath the EMAIL property line at the top of the file:

property name="TITLE"
	  column="TITLE" ormtype="string" type="string"
	  length="100";

Now browse the application but add the reset_app URL variable to the URL that tells the Apptacular generated code to reload the ORM settings. For example, my URL looks like this: http://localhost/ormExample/?reset_app.

If you refresh your RDS settings in the RDS DataView panel, you should see a new TITLE field within your EMPLOYEES table. ORM was smart enough to recognize the change in your data model and adjust the schema as needed automatically. Of course, the Apptacular generated forms and lists have no idea that this new property exists, so you need to simply regenerate the application. Right-click the Apptacular application root and choose Apptacular > Regenerate Application. When you reload your application again using ?reset_app, you should see the new property added to all the Employee form and list views.

Wrapping up

While our application might need some minor code tweaks and styling, it is a completely functional application that could easily be deployed to production for managing database records. Amazingly however, using ColdFusion 9 ORM, ColdFusion Builder, and Apptacular, we built this application without writing a single line of code. Clearly, if your application has more complex requirements, such as those associated with a real back-end business application, you need to modify and extend the generated components and templates. Nonetheless, in a matter of minutes you definitely have a substantial head start.

‹ Back


Brian Rinaldi is as a Content and Community Manager for the Adobe Developer Center team, where he helps drive content strategy for HTML5 and JavaScript developer content. Brian blogs regularly at http://remotesynthesis.com and is a unreformed twitter addict @remotesynth.