Object-Relational Mapping (ORM) allows you to work with objects and have them saved to the database automatically. It can greatly simplify create-read-update-delete (CRUD) operations and make your code more object-oriented. Under the hood, ColdFusion uses the industry leading ORM framework called Hibernate.
Two settings are needed in the Application.cfc file to make an application use ORM: a data source and ormEnabled set to true.
The cfartgallery data source, which uses Derby DB, is installed with the developer version of ColdFusion. ORM will work with any modern database.
Other settings for ORM are set in a structure called ormSettings. In the example below we are setting logsql to true. When set to true, the SQL created by Hibernate will display in the ColdFusion debugging information. There are settings for cfclocation to specify where the application’s persistent cfc's are placed, and dbcreate will automatically keep changes made to your model and the database.
The final setting, invokeImplicitAccessor, will be discussed later.
component {
this.datasource = "cfartgallery";
this.ormEnabled = true;
this.ormSettings = { logsql : true };
this.invokeImplicitAccessor = true;
}
An entity is a class that (in basic operations) maps to a single database table. In this first example, we create an entity that will be referenced as "art" in our code and will map to the art table in the artgallery database. The properties map to the columns in the table and can be a subset of all columns as well as having other properties that are not persistent and marked with the attribute persistent=false.
Each persistent cfc must have a primary key; this is denoted with fieldtype="id" (for composite primary keys use multiple properties with fieldtype="id"). Most of the time, the primary key will have a database auto-generated primary key; in this case, we are using the Hibernate powered generator.
Each property will have getters and setters automatically generated. Both getters and setters can be overwritten. The client’s preference is that the art name always be in uppercase, rather than adjusting it everywhere it is displayed; the example below overwrites the getter to do so. Other functions can be added to the entity as well. The example below has a getProfit function that when a piece is sold, it calculates the amount of profit.
The property name maps to a database column via the column attribute. When the column attribute is not provided, ColdFusion uses the value in the name as the column.
component persistent="true" {
property name="id" column="artid" fieldtype="id" generator="increment";
property name="name" column="artName" ormtype="string";
property name="description" ormtype="text";
property name="price" ormtype="double";
property name="isSold" ormtype="boolean";
property name="artist" fieldtype="many-to-one" cfc="artist";
function getName() {
return uCase( variables.name );
}
function getProfit() {
if ( getIsSold() ) {
return getPrice() * 0.2;
} else {
return 0;
}
}
}
The final property establishes a relationship between art and artist, in this example, a many-to-one. It is similar to a foreign-key relationship in a database between two tables, except now it’s between two objects. The artist entity also has a one-to-many relationship to art. Other relationship types include one-to-one and many-to-many, although it’s generally better to use two one-to-many relationships than a many-to-many.
ColdFusion automatically adds functions to objects for controlling relationships. For the art many-to-one these are getArtist(), hasArtist(), and setArtist(). For other relationships, replace "artist" with the relationship name. getArtist will return an array of objects of the artist, hasArtist is Boolean in determining whether there is a artist associated with the art record, and setArtist takes an object and sets it to the piece of art.
The artist one-to-many will create addArt(), getArt(), hasArt(), removeArt(), setArt(). The two new functions are due to a one-to-many having the options for many objects. addArt takes an art object and adds it to the relationship; likewise, removeArt will remove an art object from the relationship.
The art relationship below also has a cascade property with the value of "delete". When an artist is deleted, it will find all art entities related to it and delete them. While useful, this can cause unintended consequences, so use carefully, look at the other options for cascade, and don't forget you can leave it off altogether.
For artist, we specify the table with the table attribute. One advantage of ORM is the ability to change database tables and column names to more programmer friendly terms.
component persistent="true" table="ARTISTS" {
property name="id" column="ARTISTID" fieldtype="id" generator="increment";
property name="firstname" ormtype="string";
property name="lastname" ormtype="string";
property name="art" fieldtype="one-to-many" cfc="art" fkcolumn="ARTISTID" cascade="delete";
}
To get data, there are two functions we can use: entityLoad and ormExecuteQuery. First, let’s use entityLoad(), which has many signatures. In the example here, the first argument is the entity, the second a structure of values to match exactly (to get all pass an empty structure), the third what to order data by.
<cfset artists = entityLoad( "artist", { firstname: "Jeff" }, "lastname" )>
This will return an array of objects where the artists first name is Jeff.
<cfoutput><cfloop array="#artists#" index="artist"> <h4>#artist.firstName# #artist.lastname# #artist.id#</h4> <cfif artist.hasArt()> <ul> <cfloop array="#artist.getArt()#" index="art"> <li>#art.name# #dollarFormat( art.price )#</li> </cfloop> </ul> </cfif> </cfloop></cfoutput>
Within the loop, artist represents a single object. We can reference the data using the automatic getters in either the getProperty() format or, when invokeImplicitAccessor is set to true in Application.cfc, by just the property. The same concept will be used for setters later.
The output will look like this:
Bowl of Flowers $11,800.00 60 Vibe $25,000.00 Naked $30,000.00 Sky $15,000.00 Slices of Life $20,000.00 sda $10.00
Hibernate Query Language (HQL) is a language similar to SQL used for more complex data retrieval methods. In the following example, we use "like" to get all records in which the first name begins with A. ormExecuteQuery will return an array of objects the same way as entityLoad.
<cfset artists = ormExecuteQuery( "FROM artist WHERE firstname like :firstname ORDER BY lastname", { firstname: "A%"})>
The serializeJSON function can serialize entities in JavaScript Object Notation (JSON) format.
serializeJSON( artists )
will produce:
[{"firstname":"Jeff","id":4,"lastname":"Baclawski"}]
To add a record, use the entityNew function with the first argument being the entity name and an optional second argument being a structure (a map in other languages) of data. After adding data, call the entitySave function.
As we set up a relationship between artist and art, keep in mind that when adding an artist, you need to set their art as well. To do so, use the addXXX() function. In the example below, this is achieved with the addArt function.
transaction {
art = entityNew( "art", { name : "Painting of TV", price : 200, isSold : false } );
entitySave( art );
artist = entityNew( "artist", { firstname : "John", lastname : "Doe" } );
artist.addArt( art );
entitySave( artist );
}
To update a record, retrieve a single object. To do so, use a different signature with the third argument as a boolean which needs to be true to retrieve a single record. Once we have a single record, values can be changed with the implicit setters. Any value changed will be saved to the database at the end of the transaction. If you want to retrieve a value saved in a transaction, do so after the transaction has ended. To rollback any changes, use transactionRollback().
transaction {
artist = entityLoad( "artist", 100, true );
artist.firstname = "Fred";
}
To delete a record, retrieve a single object and then pass it to the entityDelete function.
transaction {
artist = entityLoad( "artist", 100, true );
entityDelete( artist );
}
After any changes to entity configuration (adding/removing a property, etc.), it is necessary to call ormReload() for it to take affect. During development, it’s common to place this in the onRequestStart function of Application.cfc. Depending on the size of application, it can take 100 milliseconds to 5 seconds to run.
There are times when ColdFusion cannot return an entity; instead, the value assigned will be null. Consider the following code:
art = entityLoad( "art", 9999999, true );
It is asking for one entity where the id is ridiculously high. Instead of erroring, it will set the art variable to null. The isNull variable can test for this:
art = entityLoad( "art", 9999999, true );
if ( isNull( art ) ) {
// no art entity provided. write code to deal with it.
}
When using writedump, ColdFusion will try and display the data from relationships. Sometimes this can be an attempt to display way more data than desired. To prevent this, provide some of the other attributes:
writedump( var=artists, top=2, showudfs=false );