Coldfusion 9 ORM, Caching and Autocommit

One more from the archives of the company dev blog, this time from August 2011. We have been gradually moving off of ColdFusion over the last several years, but maybe there is something in here that might be useful for someone.

We have been using ColdFusion 9 for a few months now. With all new code that is developed, we have been abandoning <CFQUERY> in favor of CF9?s ORM, which is based on Hibernate. This is a great technology that lets us focus more on work that matters, instead of coding lots of repetitive SQL queries. But, as with any new technology, there are some gotchas that developers have to be aware of. Two such gotchas have come up recently on projects I have worked on, and I am going to share those with you today.

Setup

For this simple example, we are going to use a very basic table.

create table dbo.person (
id int IDENTITY(1,1) NOT NULL,
first_name varchar(128) NOT NULL,
last_name varchar(128) NOT NULL,
);

In traditional ColdFusion, if you wanted to update a person, you would perform a with an update statement, like so:

update person set first_name=<cfqueryparam value="#firstname#">
where id = <cfqueryparam value="#id#">

In ColdFusion 9 with ORM, you have an object that represents a person, as defined in Person.cfc. Note that I will be using 100% CfScript for the rest of this article.

component persistent="true" table="person" {
property name="id" fieldtype="id" generator="native";
property name="firstName" column="first_name";
property name="lastName" column="last_name";
}

To update the person, you would write something like the following:

local.person = EntityLoadByPk('Person',arguments.personId);
local.person.setFirstName(arguments.newFirstName);
EntitySave(local.person);

So far, this is all basic ColdFusion ORM. Let's look at some things you need to be aware of as your application grows in complexity.

Gotcha 1 : Caching

Consider the following MxUnit code:

public void function testFunction1() {
  local.person = EntityLoad('Person',{firstName='John',lastName='Doe'},true);
  local.person.setFirstName('Jane');
  otherFunction1();
}
private void function otherFunction1() {
  local.person = EntityLoad('Person',{firstName='John',lastName='Doe'},false);
  assertEquals(1,local.person.size());
  assertEquals('Jane',local.person[1].getFirstName());
}

In function otherFunction1, we have asked Hibernate to give us John Doe a second time. One would expect this to return a fresh copy of John Doe, but actually it has returned a reference to the same person as inside testFunction1. The query is indeed performed on 'John Doe', but in the scope of the current request, that maps to an object already in memory, one that has a new first name. You can read a little more about this form of caching in the CF docs.

It is important to also see what happens when you use EntityReload. Since both variables point to the same instance from the cache, an EntityReload on will will reload the other. Consider the following:

public void function testFunction2() {
  local.person = EntityLoad('Person',{firstName='John',lastName='Doe'},true);
  local.person.setFirstName('Jane');
  otherFunction2();
  assertEquals('John',local.person.getFirstName());
}
private void function otherFunction2() {
  local.person = EntityLoad('Person',{firstName='John',lastName='Doe'},true);
  assertEquals('Jane',local.person.getFirstName());
  EntityReload(local.person);
  assertEquals('John',local.person.getFirstName());
}

Gotcha 2: Autocommit

By default, Hibernate is configured to commit any changed objects. In our above example, the EntitySave(local.person) is completely optional - at the end of the request, the changes to the person will be persisted to the database. What's bad about this is that in the pre-ORM world, developers in ColdFusion are used to explicitly updating the database via____ or some other framework. You would think that the equivalent would be calling EntitySave.

This recently bit me when I was validating some data before writing it back to the database. We have a form that is used to update contact information, and allows for our users to add new types of contacts to a person (such as billing e-mail, toll-free number, etc). There are some rules involved, one being that certain people must have one and only one billing e-mail address. Instead of validating the form and the database separately, the code I was working on converted the new contact field from the form into a new database object (via EntityNew), added it to the list of existing contact fields, and then ran the validation on the objects. If validation failed, the idea was to present the user with an error so they could correct it, and new or updated information would not make it to the datbase. Instead, Hibernate went ahead and saved all changes to the database, and allowed the error message to be displayed.

To work around this, there are a few things you can do:

1. Don't mess with objects unless you want your changes to always be committed
2. Turn off autocommit by adding <property name="connection.autocommit">true</property> to your hibernate.xml file.
3. If you want to back out any changes you have made to an object, call EntityReload(local.myEntity) to discard changes
4. Use transactions

public void function testFunction3() {
  transaction {
    local.person = EntityLoad('Person',{firstName='John',lastName='Doe'},true);
    local.person.setFirstName('Jane');
    transactionrollback();
    assertEquals('Jane',local.person.getFirstName());
  }
  otherFunction3();
  assertEquals('Jane',local.person.getFirstName());
}

private void function otherFunction3() {
  transaction {
    local.person = EntityLoad('Person',{firstName='John',lastName='Doe'},false);
    assertEquals(1,local.person.size());
    assertEquals('John',local.person[1].getFirstName());
  }
}

Note that transactionRollback() does not rollback the change to the object, but it does appear to invalidate the cache. The second method gets a fresh copy from the database.

These gotchas can be very good things, but if you develop your application without being aware of them, you will be very surprised when data starts appearing in your database that you thought you had thrown away.