Blog

7Jul
2009
ORM - Rethinking ColdFusion Database Integration

Database integration is a hallmark of ColdFusion applications. Indeed, the <cfquery> tag was one of the first added to the language, and to this day remains ones of the most used. There is an inherent simplicity and flexibility to being able to create database queries on the fly, so as to be able to refer to column names to work with results. There's also a real downside to this type of database integration. After all, consider what would happen to your code if a table name changed, or if columns were refactored and split, or if whole schemas were updated. Scary, huh? The downside of how most of us integrate databases into our ColdFusion apps is that we tend to write database specific code even at the client level, the code generating output or working with form fields, for example. In addition, we inevitably must write DBMS specific SQL, and that DBMS specific code needs to be managed by our applications.

Before I go any further, I must point out that <cfquery> is not going away, and it remains a very simple and powerful database integration option. For many of us, and for many apps, <cfquery> is, and remains, highly appropriate. But, having said that, in ColdFusion Centaur we're adding support for a newer way to think about database integration, leveraging Object Relational Mapping (ORM). ColdFusion's ORM support is built on Hibernate, the premier ORM implementation in Java development, and in fact, Hibernate is built right into the next version of ColdFusion, and exposed via ColdFusion Components and language elements.

ColdFusion's ORM support requires extensive coverage, which is way beyond the scope of this post, but here are the basics. In ORM, you never write SQL statements, and never really consider tables of rows and columns, at least not when writing client code. Instead, developers using ORM work with objects. For example, a table containing books with columns named title and author and ISBN, would have a corresponding object with the same properties. When using ORM, instead of retrieving a row from a table, you'd retrieve a book object (which is automatically populated by the contents of the table row). To retrieve all rows you'd not use a SELECT * FROM Books, instead you'd use Entity functions to request an array of book objects, populated and ready to use. And then instead of referring to column names in your code, you'd use Get methods in the returned objects. This is actually far less confusing than it sounds, so let's look at an example. First the Application.cfc:

view plain print about
1<cfcomponent>
2    <cfset this.ormenabled=true>
3    <cfset this.datasource="cfbookclub">
4</cfcomponent>
this.datasource is used to define the datasource to be used, and then this.ormenabled is set to TRUE to turn on ORM support.

Next we'll need an object that represents the table to be used, and in ColdFusion objects are implemented as ColdFusion Components (if you are working with multiple tables you'd have multiple CFCs, one for each table). Here's Books.cfc which maps to the Books table in the specified datasource:

view plain print about
1<cfcomponent persistent="true">
2    <cfproperty name="BOOKID" column="BOOKID" datatype="integer" length="10" />
3    <cfproperty name="AUTHORID" column="AUTHORID" datatype="integer" length="10" />
4    <cfproperty name="TITLE" column="TITLE" datatype="string" length="255" />
5    <cfproperty name="BOOKDESCRIPTION" column="BOOKDESCRIPTION" datatype="clob" length="2147483647" />
6    <cfproperty name="BOOKIMAGE" column="BOOKIMAGE" datatype="string" length="50" />
7    <cfproperty name="THUMBNAILIMAGE" column="THUMBNAILIMAGE" datatype="string" length="50" />
8    <cfproperty name="ISSPOTLIGHT" column="ISSPOTLIGHT" datatype="character" length="1" />
9    <cfproperty name="GENRE" column="GENRE" datatype="string" length="50" />
10</cfcomponent>

Notice that there are no methods (functions) in this CFC, there are only <cfproperty> tags that correspond to the table columns. And actually, the <cfproperty> tags are optional, and if omitted ColdFusion will read the database table to implicitly define the properties for you! Also, note that the CFC is named Books.cfc, so it maps to the Books table. If the table name changed, or if you needed to use a different CFC name, you could use the optional <cfcomponent> table attribute to specify the table name to use.

Oh, and it's worth noting that the new ColdFusion IDE (the subject of a future post) comes with wizards to generate these table CFCs for you.

And finally, here's how to retrieve the Books data. First we'll retrieve all books (this is equivalent to a SELECT *, but instead of returning a query, an array of Books objects is returned):

view plain print about
1<!--- Get data --->
2<cfset data=EntityLoad("Books")>
3<!--- Display titles --->
4<cfoutput>
5<cfloop array="#data#" index="book">
6    #book.GetTitle()#<br>
7</cfloop>
8</cfoutput>

Of course, sorting and filtering and more are all supported. For example, to retrieve books with an author id of 10 (equivalent to a SQL WHERE clause) you could use the following:

view plain print about
1<!--- Get data --->
2<cfset data=EntityLoad("Books", {authorid=10})>

Notice that to access the Title property, a GetTitle() method is used. This is a getter, and it is automatically created by ColdFusion. There are also setters, used to set properties within an object. To save a new or updated object you'd simple do the following:

view plain print about
1<cfset EntitySave(data)>

This would work for an insert or an update, just save the object and ColdFusion and Hibernate figure out whether to update an existing row or insert a new one.

And we've even made it easier to bridge between using ORM and working with queries (for example, to be able to use <cfoutput query="">, and more), just use the EntityToQuery() function to convert basic arrays of entity objects to familiar ColdFusion queries.

There's a lot more to ORM and ColdFusion's Hibernate integration. And ColdFusion developers get access to all of Hibernate, be it support for relational tables, lazy loading, caching, query optimization (by tweaking HQL directly), controlling all ORM settings, and much more, too. The next ColdFusion gives you the power of Hibernate with the productivity that is uniquely ColdFusion.

Related Blog Entries

Comments (25)



  • Russ S.

    If ORM is going to be embedded in CF9, why is it so much uglier to loop over ORM data than it is to loop over a query with cfoutput? Why not offer <cfoutput array="data"> ?

    Better yet would be an option in EntityLoad() to return data as a query, so we don't have to use the getters if we don't want to and Adobe wouldn't have to make any changes to cfoutput.

    While ORM may be more object-oriented, the example you've provided is just not elegant.
    Writing "#data[i].GetColumnName()#" to output table data in a loop is just not as smooth as how it looks with cfquery + cfoutput: simply "#ColumnName#".

    #1Posted by Russ S. | Jul 7, 2009, 09:05 PM
  • Ben Forta

    Russ, you will indeed be able to do a <cfoutput query="#EntityToQuery(data)#"> ... </cfoutput>. But yes, I agree with you, this still needs some tweaking, and is definitely a work in progress.

    --- Ben

    #2Posted by Ben Forta | Jul 7, 2009, 09:08 PM
  • Mark Mandel

    Actually, it's not that ugly to loop over ORM based objects at all, it's just that Ben didn't use the best <cfloop> construct (Sorry Ben!) ;o)

    He could have done it like this:

    <cfset data=EntityLoad("Books")>
    <!--- Display titles --->
    <cfoutput>
    <cfloop array="#data#" index="book">
    #book.GetTitle()#<br>
    </cfloop>
    </cfoutput>

    And it is not that much different from <cfoutput query="#data#">, but with some extra ()'s, and an outside <cfoutput>.

    See! Much neater :D

  • Ben Forta

    Mark, duh, thanks for pointing that out! I've updated the example to use your far superior code snippet, thanks! :-)

    --- Ben

    #4Posted by Ben Forta | Jul 7, 2009, 09:32 PM
  • Russ S.

    Phew! I'm so glad there will be a way to handle it elegantly.
    I'll probably be using <cfoutput query="#EntityToQuery(data)#"> a lot in the future.

    So Ben, I'm guessing that we'll be able to explicitly define the getters within our ORM CFCs? Seems like thats how you'd handle changes in DB schema?

    Also, how would one use ORM for two different databases in a single application?

    #5Posted by Russ S. | Jul 7, 2009, 10:27 PM
  • Sam Farmer

    Pretty cool stuff

  • Russ S.

    And how about remote access of ORM objects? Will that be possible, or is that just a bad idea?

    #7Posted by Russ S. | Jul 7, 2009, 10:34 PM
  • Joshua Curtiss

    The real power of that example loop is when you start using your custom methods in the object that do more than just pull a field from the record. This is going to be fun; so far it looks like you're putting ORM in ColdFusion with the simplicity that ColdFusion is known for. I'm lovin' it. :-D

  • Akbarsait

    ORM Integration is really awesome feature and looking forward to learn more stuff on that. On CF 9 UG tour at Chennai CFUG last month Manju Kiran from ColdFusion Team gave us a great in-depth coverage on ORM features and it was really helpful for us to understand many advance features which are coming with Centaur and ORM integration.

    #9Posted by Akbarsait | Jul 8, 2009, 02:55 AM
  • Neil Moncur

    New to Hibernate, so please excuse the rookie question. Would joins be possible? For instance, say I wanted to grab the "Books" from the example above, left-joined against a hypothetical "authors" table? Would I need to do "get" operation to get the author for every book? Is there some way to say, "get the books, and while you are at it, left join against the authors table", or the equivalent?
    Thanks,
    -Neil

  • Barny

    @Neil Moncur
    yes, it certainly is possible in Hibernate! In Ben's example above, all the properties of the object are simple values, but Hibernate supports linking to other objects. You tell it what the keys or join tables are that link objects togather and it takes care of writing the joins. It means that once it's set up, you can write book.getAuthor().getAddress().getState() and the joining is taken care of. You can also tweak Hibernate to tell it what to get back imediately and what to bring back only when needed (lazy loading). It takes a bit of learning, but it's really remarkably powerful

    Barny

    #11Posted by Barny | Jul 8, 2009, 01:08 PM
  • JD

    How's the overhead(cpu/memory) when dealing with databases with a large number of tables(more than 50)? or a large number of records? I realize Hibernate is where it's @ in terms of ORM, but I'm curious how CF will handle the potentially monsterous CFC loads.

    #12Posted by JD | Jul 8, 2009, 01:26 PM
  • Matthew Lesko

    In light of getting back arrays of CFCs from the ORM, is CF9 improving object creation performance? This was a bottleneck in some scenarios for me to the point I actually stopped using CFCs and rewrote the functionality using structures or Java objects instead.

  • Josh Nathanson

    The big question is, will the object instantiation speed be sufficient to allow for numbers of objects (say 2000 or more)? Ben, any word on that? Otherwise, very exciting stuff.

    #14Posted by Josh Nathanson | Jul 8, 2009, 01:32 PM
  • Neil Moncur

    @Barny
    Thanks, Barny. Great to know. Lack of the feature you described would have made this a non-starter. Or at least would have made it much less useful.
    -Neil

  • Matthew Williams

    Call me uneducated... but, this stuff looks quite a bit like what the FarCry framework already does and has done for a long time. Or am I wrong? Yes, I sound like a broken record on the FarCry stuff ;). The same concept of obfuscation and database manipulation by cfproperties in a CFC is one of the main reasons I really enjoy working with it.

  • Michiel Bakker

    The "lazy loading" feature still has me puzzled. The way I understand it (after having done a little research on hibernate.org and google) is that the CFC's created are loaded when they are requested by the code, and not at the start of each request. Is that true?

    Furthermore, in database development and usage it's generally a very bad idea to use "SELECT *", as this will give you quite some overhead most of the time. Smart indexes and carefully created SELECT-statements give for better stability and less serverload, which is a must especially in applications using a large number of tables (say, 25+) and data (say, 500.000+ records).
    So, my question is: what happens in the background when I only need one or two columns from a table with, say, twenty columns? Will it load all columns into the array and return the appropriate columns after calling their Get...() methods (1) or will it only load the columns I really ask for (2)?

    If statement 1 is true then I'm wondering where the real advantage is. Ofcourse it takes down development time, but it then also produces overhead in applications using large tables. (This doesn't go if you only have a handfull of tables with a couple of rows ofcourse.)

    If statement 2 is true then I'm sure my colleagues and I will start using and loving it!

  • Michiel Bakker

    On Coder's Revolution (http://www.codersrevolution.com/index.cfm/2008/11/...) I read that the columns required are only fetched. I like that, but it has me wondering.
    If you need columns two and four, it only returns these columns. But what happens in the background? Are two queries being executed (one for each column) or only one (containing both columns)? If the latter is the case, CF would have to know in advance which columns are being requested in the code that follows.

    Quite interesting stuff!

  • Brian Kotek

    @Matt - FarCry might do some of this, but what Hibernate offers is mindbogglingly vast and highly refined.

    @Michiel - Lazy-loading is a way to minimize database traffic by only populating the associations you need. So if a User has a collection of Roles, by default, Hibernate won't get all the Roles when you get a User instance. But if any code calls user.roles, THEN it will query for the roles and populate them so that you can use them. You can specify what is lazy and what is not in the association, or in an HQL query, as necessary.

    Hibernate creates and manages indexes and foregin keys automatically, and you can specify additional indexes as you need to (you usually don't).

    You can use HQL to query for individual properties, but in practice I don't see much use for that, since you don't get back an object, which means the real point of having an object in the first place (its behavior) is lost.

  • Syed

    Hi Ben,
    Quick question, are we be able to update Hibernate framework?

    BTW, ORM feature look promising.

    #20Posted by Syed | Jul 9, 2009, 11:37 PM
  • Travis

    "The downside of how most of us integrate databases into our ColdFusion apps is that we tend to write database specific code..."

    The above sample still looks pretty specific so I'm not sure where the benefit is in doing this. I understand this is a basic example, but if your column name changes won't you update the "column" attribute to match the new schema?

    The only example I can see this ORM being relevant is if we changed DB software from, say MS outlook to MYSQL. Can someone please explain to me why I'd want to make CF hard to use? Thanks.

    #21Posted by Travis | Jul 13, 2009, 10:09 AM
  • John Baker

    How does the ORM work with stored procedures and SQL injection? Our database has 900 hundred tables, a thousand procs, multiple schemas and tables with hundreds of millions of records. From a security standpoint, our company will only use stored procs for database access and I am wondering if CF's ORM can make my life easier by integrating with stored procedures to do CRUD operations?
    Does it handle optional input parameters, multiple result sets, return codes, xml input datatypes, etc...

    #22Posted by John Baker | Jul 20, 2009, 10:25 PM
  • Brian Kotek

    It supports stored procedures through named queries (see http://docs.jboss.org/hibernate/stable/core/refere...), but if you already have all of your logic in stored procs I'm not sure you'd gain a lot by using Hibernate in place of a standard stored procedure call.

  • Raghuram Reddy

    I am a java coldfusion guy. The ORM concept implement in coldfusion really a good idea and its appreciated.
    The sessionFactory object is expensive,heavy weight.
    (sessionFactory is a heavyweight object that should ideally be created only once(since it is an expensive and slow operation) and made available to the application code that needs to perfor persistence operations.)
    And also suggested not to use when u use for simple db operations(db with less records).
    so Ben!
    what exactly the sessionFactory object in your example and any recommendations to use with respect to performance.
    pls suggest for the better usage of it.

  • Viral

    Is there a way to create a 3 way joining relationship with ORM.
    e.g. Contacts Table(ID, Name, Age)
    Videos Table(ID, VideoName, URL)
    videoContact(ID, ContactID, videoID, TimeStamp)

    here the 3rd table have 2 foreign keys both from 1st and 2nd table.

    #25Posted by Viral | Dec 22, 2014, 06:32 AM