Introducing ColdFusion ORM

In previous ColdFusion releases, database access was achieved by:

  • Managing relational data using tags such as cfquery, cfinsert, and cfupdate, which handle SQL statements.

  • Managing objects using ColdFusion components (CFCs), and object lifecycle using the application itself

  • Writing SQL queries for each CFC, even for basic CRUD (Create, Retrieve, Update, and Delete) operations.

The complexity of managing these tasks increases as your application grows.

ColdFusion ORM automates most of these tasks, which:

  • Makes application code cleaner and more manageable

  • Enhances your productivity and lets you develop database applications faster

  • Creates applications that can run faster because of built-in ORM optimizations

  • Minimizes the amount of code you write

Apart from providing a framework for mapping the object model with the relational database, ColdFusionORM provides data query and retrieval facilities.

For more information, see www.hibernate.org.

ColdFusion ORM example

ColdFusion ORM manages persistence through objects, which are also called entities in the ORM context. In ColdFusion, persistence is managed through CFCs and their properties. Each persistent CFC in ColdFusion application maps to a table in the database. Each property in the persistent CFC maps to a column in the table.

The following example explains these concepts by building a simple application, which would enable you to jumpstart with ColdFusion ORM. The example uses the cfartgallery data source that is shipped as part of ColdFusion 9 documentation option in the installer. The cfartgallery data source has Artists and Art tables. Artists has a one-to-many relationship with the Art table.

Step 1:

Specify the ORM settings in the Application.cfc file.

The minimum required settings are mentioned in the following sample code snippet:

Application.cfc

<cfset this.name = "ArtGalleryApp"> 
<cfset this.ormenabled = "true"> 
<cfset this.datasource = "cfartgallery"> 

Apart from these, there are other settings that you can use to configure ORM. For details, see ORM settings.

Important: Define these setting only in Application.cfc and not in Application.cfm.

Step 2:

Map the ARTISTS.cfc to the database table.

  1. Create the ARTISTS.cfc.

  2. Flag it as a persistent CFC and map it to the ARTISTS table.

    To make the ARTISTS.cfc persistent, the persistent attribute should be set to true in the cfcomponent tag. The table attribute should be set to the table name. If table attribute is not specified, then the CFC name is taken as the table name.

    Each CFC can be given an entity name. Entity name is the name used by the ORM related functions to work with the persistent CFC. It can be specified by using the entityname attribute in cfcomponent. If entityname is not specified, then the CFC name is taken as the entityname.

  3. Now, create properties in ARTISTS.cfc and map them to the columns in the table. One property should be created for each column in the table. To map the property to the column, the column attribute should be set to the corresponding column name. If the column attribute is not specified, then the name of the property is taken as the column name.

For details on setting the ORM-specific attributes, see Define ORM mapping.

The ARTISTS.cfc is defined as follows:

<cfcomponent persistent="true"> 
    <cfproperty name="id" column = "ARTISTID" generator="increment"> 
    <cfproperty name="FIRSTNAME"> 
    <cfproperty name="LASTNAME"> 
    <cfproperty name="ADDRESS"> 
    <cfproperty name="CITY"> 
    <cfproperty name="STATE"> 
    <cfproperty name="POSTALCODE"> 
    <cfproperty name="EMAIL"> 
    <cfproperty name="PHONE"> 
    <cfproperty name="FAX"> 
    <cfproperty name="thepassword"> 
</cfcomponent>

Step 3:

Perform CRUD operations.

To retrieve data from the ARTISTS table, use EntityLoad():

    ARTISTS = EntityLoad("ARTISTS")

All the records from the ARTISTS table are retrieved as an object array.

To add a new artist, create a new artist object and call EntitySave() for this object.

<cfscript> 
try { 
    newArtistObj = EntityNew("artists"); 
    newArtistObj.setfirstname("John"); 
    newArtistObj.setlastname("Smith"); 
    newArtistObj.setaddress("5 Newport lane"); 
    newArtistObj.setcity("San Francisco"); 
    newArtistObj.setstate("CA"); 
    newArtistObj.setPostalCode("90012"); 
    newArtistObj.setphone("612-832-2343"); 
    newArtistObj.setfax("612-832-2344"); 
    newArtistObj.setemail("jsmith@company.com"); 
    newArtistObj.setThePassword("jsmith"); 
    EntitySave(newartistobj); 
    ormflush(); 
} catch(Exception ex) { 
    WriteOutput("<p>#ex.message#</p>"); 
} 
</cfscript>

To update an existing record, load that object and make changes to it. ColdFusion automatically detects that the row for this object needs to be updated and it will get updated when ORMFlush() is called.

Note: ORMFlush() is called at the end of the request by default.

In the following code, the newArtistObj is already managed by ORM, so it does not need to be loaded again.

newArtistObj.setphone("612-832-1111"); 
ormflush();

To delete a record, EntityDelete() is used.

EntityDelete(newArtistObj); 
ormflush();

Step 4:

Define Relationships

First define the mapping for the ART table to define a relationship between artwork and artists.

The ART.cfc is defined as follows:

<cfcomponent persistent="true"> 
    <cfproperty name="artid" generator="increment"> 
    <cfproperty name="artname"> 
    <cfproperty name="price"> 
    <cfproperty name="largeimage"> 
    <cfproperty name="mediaid"> 
    <cfproperty name="issold"> 
</cfcomponent>

In cfartgallery, the table ARTISTS has a one-to-many relationship with ART table, which are joined using the foreign key column ARTISTID. This means that each artist has created multiple artwork pieces and many artworks are created by one artist. To represent this in the object model, each ARTISTS object would contain an array of ART objects. Each ART object will contain a reference to its ARTISTS object. This is an example of a bidirectional relationship.

To achieve this, you need to add an extra property to the ARTISTS.cfc object that contains the array of ART objects for that ARTIST.

<cfproperty name="art" type="array" fieldtype="one-to-many" cfc="Art" fkcolumn="ARTISTID">

fieldtype="one-to-many" specifies the type of relation.

CFC="Art" is used to convey that the relationship is with "ART" cfc.

fkcolumn="artistid" specifies the foreign key.

ART forms a many-to-one relationship with ARTISTS table because each piece of artwork is created by an artist and many other pieces of artwork are created by the same artist. To define this relationship, add a property in ART.cfc to define the relationship with ARTISTS.cfc.

<cfproperty name="artists" fieldtype="many-to-one" fkcolumn="artistid" cfc="Artists" lazy="true">

fieldtype="many-to-one" specifies the type of relation.

CFC="ARTISTS" is used to convey that the relationship is with "ARTISTS" cfc.

fkcolumn="ARTISTID" specifies the foreign key.

Step 5:

Retrieve records in relationship

<cfscript> 
    artist = EntityLoad("Artists", 1, true); 
    arts = artist.getArts(); 
    WriteOutput("<b>" & artist.getid() & " " & artist.getfirstname() & " " & 
    artist.getlastname() & "</b> has " & ArrayLen(arts) & " arts:<br>"); 
        if (ArrayLen(arts) > 0) 
        { 
            for(j = 1; j <= ArrayLen(arts); j ++) 
            { 
                art = arts[j]; 
                WriteOutput(art.getartname() & "<br>"); 
            } 
        } 
</cfscript>