Database integration is a hallmark of ColdFusion applications. Indeed, the
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
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:
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:
Notice that there are no methods (functions) in this CFC, there are only
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):
#book.GetTitle()#
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:
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:
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
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.
ORM – Rethinking ColdFusion Database Integration
26 responses to “ORM – Rethinking ColdFusion Database Integration”
-
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#". -
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 -
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 😀 -
Mark, duh, thanks for pointing that out! I’ve updated the example to use your far superior code snippet, thanks! 🙂
— Ben -
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? -
Pretty cool stuff
-
And how about remote access of ORM objects? Will that be possible, or is that just a bad idea?
-
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. 😀
-
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.
-
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 -
@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 togathe -
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.
-
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.
-
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.
-
@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 -
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.
-
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! -
On Coder’s Revolution (http://www.codersrevolution.com/index.cfm/2008/11/21/New-Centuar-Features-Demoed-ORM-Bolt-IDE-Code-Generation-CFScript) 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! -
@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 -
Hi Ben,
Quick question, are we be able to update Hibernate framework?
BTW, ORM feature look promising. -
"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. -
-
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… -
It supports stored procedures through named queries (see http://docs.jboss.org/hibernate/stable/core/reference/en/html_single/#sp_query), 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.
-
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. -
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.
Leave a Reply