Handling null db values with ORM
I had an email from someone who was trying to use ORM with an existing database and was having errors because of null values in the database. When you're using SQL with cfquery, then ColdFusion will automatically convert null database values to a empty string. However, with ColdFusion ORM (which is based on the Java Hibernate framework), null values are preserved.
If you use cfquery then null values are returned as empty strings. You can replicate this by using the IsNull function to check for nulls and then return a blank string if it is. Something like:
component persistent="true"
{
property name="id" fieldtype="id" generator="native";
property name="forename"; // might be null
property name="surname"; // might be null
// override the automatic get methods to handle nulls
function getForename()
{
if ( IsNull( variables.forename ) )
{
return "";
}
return variables.forename;
}
function getSurname()
{
if ( IsNull( variables.surname ) )
{
return "";
}
return variables.surname;
}
}
As you can see this involves writing a lot of extra code and is pretty messy - there must be a better way!
With ColdFusion ORM there is a feature called event handling, which fires specific methods when certain things happen. One of those events is postLoad which is called after the entity has been populated from the database. This is a good time to clean up the data and reduces the extra code we have to write.
component persistent="true"
{
property name="id" fieldtype="id" generator="native";
property name="forename"; // might be null
property name="surname"; // might be null
void function postLoad()
{
param value="variables.forename" default="";
param value="variables.surname" default="";
}
}
To enable ORM event handling in your application, you'll need to add this to your Application.cfc
this.ormsettings.eventHandling = "true";
This is much better than the original version, but you will need to remember to add each property to the postLoad method. Wouldn't it be nice if the postLoad method automatically handled all properties?
component persistent="true"
{
property name="id" fieldtype="id" generator="native";
property name="forename" type="string"; // might be null
property name="surname" type="string"; // might be null
void function postLoad()
{
var properties = getMetaData( this ).properties;
var key = "";
// using a "for in" loop gives error
for ( var i=1; i<=ArrayLen( properties ); i++ )
{
key = properties[ i ];
if ( !StructKeyExists( variables, key.name ) )
{
// null value so set as an empty string
if ( key.type eq "string" )
{
variables[ key.name ] = "";
}
}
}
}
}
Now the postLoad method is using metadata to get the properties and then iterates through them. It's worth noting that I've had to add the ormtype attribute to each property as I don't want to set blank strings for numeric / boolean etc datatypes.
- Posted in:
- ColdFusion
- Hibernate
Also if you had a list of, lets say, 100 entities that each entity that gets updated will then have a sql update statement called against the db to set null values to empty strings.
Comment by Greg Moser – January 21, 2012
I ended up doing something similar.
The postLoad() is in a component that's extended by all my models. It also has a more extensive defaults section based on types and reflects the default attribute near the end, which CF doesn't always seem to honor by default:
if structKeyExists(strProp, 'default'){
variables[sKey] = strProp.default;
}
Comment by Michael Zock – February 03, 2012
You could use the preInsert and preUpdate, to do the opposite of what I've got going on in the postLoad method by detecting blank stings and replacing with null.
Comment by John Whish – February 03, 2012
Comment by Michael Zock – February 03, 2012
The only problem with using preUpdate() to switch the values back to null, is while the integrity of the DB will be in tact... Hibernate will still be calling update commands for data that really hasn't changed. Unfortunately once you are inside of the preUpdate() method, there is no way to cancel the SQL update command that Hibernate is about to run.
While this may not seem like a big deal, on a list of 100 entities you will have 100 update commands called, all of which will do absolutely nothing to the data in the database.
Another option to solve this problem is to just store blank values in your database by setting a default on the property, or using the method that you provide in your example. I am not a fan of this methodology because I actually like the "null" aspects of values, to me null and blank are two different things that can be used in concert to provide more control. However not everyone feels this way, and most people are totally fine with blanks in the DB over nulls.
Comment by Greg Moser – February 03, 2012
@Greg, great point. I personally am happy for a blank string to go into a database if the user has submitted a form and not provided a value in the form field. To me that means they have submitted a blank string. I tend to use null when no value has been set at all and I don't have a default.
Comment by John Whish – February 06, 2012
That is a completely valid and fair point. I guess in that case, I would just opt to set a default value on the property or do this inside of the init()... but to each their own.
Thanks for all of your contributions to the ColdFusion community, and especially you work with ORM... It is much appreciated!
-Greg
Comment by Greg Moser – February 06, 2012