Learning ColdFusion 9: From SQL To ORM - A Conceptual Shift In Relationships

Ben, you can also do this with an embedded object instead of a one to one mapping. The docs have full details.

I don't think so Ben, all you should need to do is specify "embedded=true" in the cfcomponent tag.

@Ben,

The contact details scenario you describe, with contact having column contact_information_id, is, to Hibernate, 'contact has a property contact_information'.

What you want, 'contact_information is (an optional) part of contact', is to be achieved by reversing the relationship: contact_information should have a column id, which is both the table's primary key as well as a foreign key to the contact table's primary key. Do this, and you can easily use the join-table mapping.

Hibernate implements many of the best or most correct patterns of database architecture. Hibernate is the long-term, mature product of the best people working on the object-relational mapping problem. It is advisable to heed Hibernate's warning: if a certain case is difficult to map to your table structure, then it may be an indication that your table structure is off in some way. In this case, Hibernate is warning you that the concept you want to implement, 'contact_information being an embedded part (join-table) of contact', should be implemented relationally in a certain way, while the concept you did actually implement, 'contact_information is a property of contact', is not the one you want.

The recommended table structure you should be using, per Hibernate's advice and per the way you would like to be using your CFC objects, is as follows:

contact
+ id int pkey
+ name varchar

contact_information
+ id int pkey fkey(contact.id)
+ phone varchar
+ email varchar

The resulting query would be something like:

select c.id, c.name, ci.phone, ci.email
from contact c
join contact_information ci on ci.id = c.id

Hey Ben. Sorry if I am misunderstanding your model design, but it seems like you have your join directions reversed to begin with. I am assuming that what you mean to express is a "one Contact to many Contact Info" relationship, but what you have is a "One Contact Info to many Contacts", which seems odd. Again, I might be misunderstanding your model, but in a general contact manager, you'd define one Contact and then have multiple "Contact Info" records (say their primary contact info, secondary, etc) that point to that contact. Then what you would have is:

Contact Info (id, phone, email, contact_id_fk)
Contact(id, name)

Maybe this is why you are seeing the reverse of what you are expecting in ORM? Just a thought.

-Brian

@Ben, I come from .NET with NHibernate (the .NET port of Hibernate), so I've gotten a chance to learn a number of its norms and kinks.

Given your requirements, I'm afraid you may not be able to use the Hibernate join-table mapping. So you will basically need to use the reference mapping: 'contact has property contact_information', because given your table structure, that's what makes sense to Hibernate. And that is in fact what you've gone and done very nicely.

The reason is Hibernate has trouble is this. Your table structure suggests that 'contact_information' is an important entity in its own right, even apart from any relationship with 'contact'. The way your table structure suggests this is that 'contact_information' has its own ID.

So there is a clash between your table structure, which treats 'contact_information' as an entity and not simply as a join-table part of 'contact', and your ideal object-model, which only knows about 'contact' entities and has no concept at all of 'contact_information' entities and treats the 'contact' and 'contact_information' tables as two parts to making up a single whole entity. Your database says: 'treat contact_information as an important entity that exists in and of itself', while your ideal object-model says: 'there's no such thing as a distinct contact_information; contact_information is simply a part of contact'. Hibernate has trouble with that sort of thing.

You can take a look at the relevant Hibernate documentation here: http://docs.jboss.org/hibernate/stable/core/reference/en/html/mapping.html#mapping-declaration-join
In particular, take a look at the example, which shows how to accomplish a join-mapping with Java persistent classes.

Ben, you're right that when you embed an object into another entity, Hibernate essentially merges the embedded object into the parent. So this is useful where you have common properties and behavior that you want to apply to numerous objects. But it doesn't create a separate object and as a result it isn't useful when you want to share the same object across different entities (as you do). So in your case, I think you're on the right track to create the separate ContactInformation entity. I didn't mean to confuse you but just wanted to point out another way you can fill a similar need.

Where I think you're off course is in the mapping. You don't want a one to one here, since that is just that: one to one. What you probably want is a Many to One (many Contacts/Companies can share one ContactInformation) that uses a link table to relate the Customer to the ContactInformation.

In that case, what you end up with is a table for Contacts and a table for ContactInformation, and a table generated by Hibernate that maps a Contact's ID to the ContactInformation ID. You'd do the same with Company. In this way, ContactInformation isn't tied to either one, the relationship is defined on Contact/Company and Hibernate goes through the link table to find the associated ContactInformation. So something like:

select c.id, c.name, ci.phone, ci.email from contact c join contact_contact_information cci on c.id = cci.contact_id join contact_information ci on cci.contact_information_id = ci.id

It may take some experimenting to get that working since I haven't tried it within CF9, however, I do things like this when using Hibernate in Java applications all the time.

If you really want to understand the full spectrum of Hibernate's capabilities, I'd recommend reading Java Persistence with Hibernate. It's pretty big, but so is Hibernate, and it's basically The Bible for Hibernate usage.

@Brian, I haven't even downloaded CF9 yet, let alone attempted to work with it and figure it out, so I am going to have to look at the CF9 documentation on this one.

It looks like the 'embedded' attribute corresponds to the '<component>' mapping. The component-mapping works exactly the opposite to the join-mapping.

Join-mapping: one persistent class (cfc) split across multiple database tables.
Component-mapping: multiple classes (cfc), one of which is persistent and the remainder of which are embedded, all persisted to the same database table.

The component-mapping won't help Ben Nadel here, unfortunately.

Note: the mapping is component-mapping, while the cfc attribute is embedded (as opposed to persistent). Just to keep the terms straight for all of us.

@Ben, apologies if my first comment seemed brash. I was under the mistaken impression that you were reworking application for new installations using the ideal domain model (cfcs), were using Hibernate to map the domain model to the database schema, and had the liberty to alter the database schema as required by the domain model.

You will find that Hibernate is built to support many kinds of mappings, including many kinds of legacy (ie, non-optimal or non-natural) mappings - far more than many other ORM tools and certainly far more than ORMs such as Django's or Rails'. Hibernate simply can't support everything found "in the wild" (that is, currently in existence). Depending on your preferred style of building applications (enterprisey, or the "Rails way"), you will find either that Hibernate suits you wonderfully or that Hibernate is far too complicated and has far too many options. I happen to think it's the best thing since sliced bread, but I also think that Rails' ActiveRecord style of development is also the best thing since sliced bread - each in its own context. But I tend to think a number of things are the best thing since sliced bread.

Ben -

I had the same initial reservations as Justice -- I thought your mapping was backwards. After your explanation, I see now what you're trying to do with it ... but I again have to agree with Justice that it's a bit counterintuitive.

Beyond that, good explanation of features, and I'm enjoying the series!

-R

With Hibernate and CF9, is there a way to specify the database indexes that you want created if you elect to have Hibernate create the entities?

Hey Ben,

I think part of the problem you are having here is that you're taking SQL and trying to turn it into ORM. While that is part of the process, ORM will make a lot more sense if you start with a clean object model. Have a look at your db schema (the data and where you are coming from), look at what the app is trying to achieve, and come up with a clean object model. Then model that in hibernate and as a final step look at any differences between the schema hibernate suggests by default to persist that object model and your legacy schema and then decide whether to change your hibernate config or whether to change your schema.

Even though Hibernate does replace the SQL in your app, it isn't designed to replace SQL - it's designed to seamlessly handle persistence of objects. It ends up being a leaky abstraction (you *do* have to think about relational concerns for most non-trivial apps), but it'll still make a lot more sense if you start by coming up with an object model consistent with your use cases and *then* look at the db schemas.

@Peter, that's definitely the recommended way to go about it.

It is certainly true, and it is certainly 100% the point, that Hibernate tries to make it so that your application is only working with objects. Hibernate tries to shove all of that database work completely under the hood so that you the Java (CF) programmer don't have to deal at all with any database concepts or nuances. All you have to do is ask Hibernate for objects, and it gives them to you. You just update those objects, and Hibernate will magically and seamlessly ensure that all updates are then propagated to the database. When you enter the Hibernate Zen, the entire universe is just a world of objects, plus the Hibernate Session which ties all the objects together, but no database anywhere to be found. But you do have to understand Hibernate, the way it works, and its expectations of your domain model and your schema, in order for the experience to become truly seamless.

So far I have found that I don't necessarily have to think about the SQL-ness aspect of a Hibernate-persisted domain model. What I do have to think about is the deferred-loading strategy, what the default is or should be for a given class and when to override the default in certain queries. But almost everything else can be taken care of by designing your ideal domain model and then choosing the best schema and the best mapping, so long as you keep the schema normalized. But this kind of issue comes into play when dealing with any kind of remote object source, including for example consuming a REST API exposing an object model: you will need to be aware of which collections have deferred-loading in which situations and when and how to specify that you want or don't want deferred-loading in any given situation.

One thing that strikes is how you went from a 13 line SQL statement (adding 2 lines for the cfquery tags) to 118 lines of CFC code. Granted, some of your readers have pointed out that you may have taken the wrong direction with your ORM implementation, so maybe it could have been done in a few less lines of code (or maybe their solution would be more code).

Isn't ORM supposed to simplify database interactions (or at least, that's one of the goals I've heard touted)? Is the problem how Hibernate was implemented in ColdFusion? Because if using CF's ORM stuff means that I will continually see 10 times more code than just plain cfquery tags...I'm not sure I want to jump on this bandwagon.

Jake, I'm assuming that Ben already had CFCs for his Contact, Company, and ContactInformation. So all that is really different is the CFProperty tags. In any application that uses CFCs, leveraging an ORM will actually reduce the amount of SQL, and thus the total amount of code, by a huge amount compared to what you have to add (a few CFProperty tags).

@Brain,

I can see your point. I use CFCs for my DB interactions as well, but reviewing Ben's example code again, it still appears that the amount of code was increased. One problem with my "118 lines of CFC code" statement is that it included the CFC for the company, which was not in the original SQL statement. That said, to add the company relationship to the SQL query might add an extra 5-10 lines of code. So we'd be talking about one cfquery that's maybe about 20 lines of code.

As you said, if you are already using CFCs for your DB work...there will still be lines of code for the CFC parts. But looking at all of those property tags that Ben had to add...they seem to add up to a lot more than 20 lines of code. In fact, I just went back and counted...and just the cfproperty tags for his final examples add up to 50-60 lines of code (which doesn't count the cfc for the company which is not shown in his "fixed" examples). :\

Well first, I wouldn't say "all those property tags" add up to 60 lines of code. Ben puts every attribute on a separate line. He added 8 property tags. That's about 60 seconds of work.

We also haven't seen the template of CFC that contained the original queries, which now don't need to exist. But more importantly, managing these with Hibernate eliminates far more code than just this simple JOIN query.

The ORM lets you select a list, select a filtered list, create, update, and delete. And it does this not only for the aggregated set of objects, in any combination allowed by the relationships that are defined, but also for the individual objects (getting only the ContactInformation, for example). In other words, you're getting either individual or aggregated (and cascading) list, create, update and delete operations on all of these objects, plus filtering and two levels of caching, for the price of 8 property tags. That's a tradeoff I would make every time. And I can guarantee that even for this simple case, the amount of boilerplate code that is eliminated will be far beyond 8 property tags.

Basically, I'm just saying: think about the full set of things you're getting by incorporating the ORM features, because they go much further than the small experiment that Ben is testing out here.

You also benefit from the database agnostic capabilities of an ORM.

@Ben,

I think your issue comes from denormalizing contact_information and company_contact_information into a single table. Technically they are two different entities; and this is a bit more obvious if you add contact_name to the company_contact_information table, which is redundant in the contact_information table.

I can understand the desire to lessen the number of tables in a database by merging these two tables into one; however is it really helpful?

Also, I realize this is just an example, but can I make the suggestion of having company link to contact with a many-to-many link table of company_contacts? This will allow you to separate your contact information across multiple tables and allow a company to have multiple points of contact.

I'm still confused. Which is pretty normal for me.
Let me give a better example. I have 2 tables. 1 for volunteers and 1 for organizations that use volunteers. I want to do a join so that I get a list of volunteers and which organizations they volunteer with. Currently I have a table that has 2 columns, volunteer_id and organization_id. Using this I can go back and forth to get either all volunteers with an organization or all organizations for a volunteer.

How do I do this ORM?

Hmmmm no answer for that one? Or are you just ignoring the pesky buzzing sound?

Okay. Still trying to learn this so I go for a simple problem, delete all records in a table. hmmmmmm.
With cfquery that is easy. But maybe not so much with ORM.

What I need, and am searching for, is a list of all functions I can call.

Don,

With an ORM like Hibernate, you must think in terms of objects, not in terms of records.

If you want to think in terms of records, Hibernate sometimes provides convenience methods (including a way to delete all records in a given table). But most of the time, if you want to think in terms of records, you need to use an ActiveRecord-type ORM or use straight queries.

OrmGetSession()
.connection()
.prepareStatement("truncate table users")
.executeUpdate();

Cheers,
Justice

@Justice,

Hmmmmm so to shift back to coldfusion and orm, would that then just be like truncate(table users) ?

It doesn't matter if you talk objects or records here since a table being an object holds records or data. So I see table methods of set, update, and delete but they only deal with 1 record at a time. Thus my question.

Don,

Objects vs records makes a very big difference.

ORM is the thing that lets you work with objects, and then have those objects be persisted to the database behind the scenes.

Different ORMs work differently. An ActiveRecord-type ORM expects you to treat your objects merely as glorified records - you are working with records masquerading as objects. Hibernate expects you to work with objects from a purely object-oriented programming perspective, and expects you to ignoring the fact that these objects are persisted to a database behind the scenes - you are working with objects, and Hibernate takes care of the rest.

If you need to use Hibernate and also, from time to time, work with records more directly, then you have two options. First option: you can see if Hibernate offers some convenience methods. Second option: you can bypass Hibernate and work with records directly by issuing SQL commands.

I pasted above an example of using Hibernate to issue a SQL command (in ColdFusion, you might just use cfquery instead of going through Hibernate).

TLDR. If you just need to delete all the records in a given table, Hibernate may provide a convenience method, but you might need to work with SQL/cfquery directly.

Cheers,
Justice

@Don,

I think you are confusing what the definition of an object is in regards to OOP (Object-Oriented Programming).

An object, in OOP, is a bunch of methods (functions) and members (variables) encapsulated in a class to describe the actions and state of a real-world entity. For example, a customer at a bookstore has a name, address, phone number, customer identification, purchased books, etc; they can purchase a book, change their address/phone number, etc.

By encapsulating the methods and members into a class; when you instantiate the class into a variable and fill it with some initial members (in essence creating a single object), you can then get the object to do actions on it's own members, for example have a customer object change it's own address.

Now what ORM (Object-Relational Mapping) does, is allow you to save the current state of the object (the values in it's members) into a database structure. Because an object's members can span different related subjects (for example customer, customer's address, customer's purchased books) an ORM needs to know where to save each member; which is where the mapping comes in.

So in OOP you work with single entities per object; or a single set of rows in a database.

If you want to stick with the OOP approach, I would suggest creating an object that is a collection of objects, or a collection inside another object; for example a collection of customer objects or a collection of books sold inside a customer object. With either of these you can have a method that loops through each object in the collection, calling the delete method for each object.

Personally, I think this is where OOP's benefits fall down. It is very inefficient to loop over objects calling delete for each one, as there are overheads with sending requests to a database, and with every extra request you multiple the overhead.

The approach that I would take personally, is to create a collection object that every other collection object inherits off of. Inside that object I would create a method that outputs the primary key of each object in the collection via an array or structure. Then when I want to do a query on more then one object, I would pass the whole collection that the objects were in, into a separate function that would handle the query.

By passing the whole collection, if you do a delete or update on the objects, you can get the function to update each object's members in the collection before returning it. Also, as the value method for the collection is inherited, the update/delete function can assume that every collection has it.

@Andrew Bauer,

Nah. I'm not confusing anything about objects. I was there when the whole thing was conceived for toasters. :)

Yes, oop and java were originally for toasters.

Anyway, you hit my point exactly on the head I guess. I was hoping there was an easy way of clearing a table but it looks like good old sql is the best way.

As I get into the whole ORM thing more and more I'm finding it is just a big pain.

It is actually faster and easier to stick with sql. Why should I make my life harder?

To make this work so it makes my life easier there should be things like (and maybe there is and I don't know it) direct forms to table like I can do kinda with cfinsert. I mean a form is already a structure so it would be great if instead of having to do my own structure to insert the data, I could just say

tableObj.insert(form);

Understanding of course that the fields in the form must align with the columns.

For highly complex sql I use T-SQL or PL/SQL. For simple crud operations this whole ORM thing is just overkill and overly complex.

Think about it. I have to build a cfc defining the table. Then I have to create structures to insert data. Vice 1 query.

Just because it is OOP does not make it better. I am rapidly seeing that in this case, it is worse.

Deleting all objects of a given type is not a common OO use case. Things that deal with bulk manipulation of data, like reporting queries, are typically best done in SQL. This is not what OO or an ORM are meant to do.

On the same level, if all you are doing is taking form data and pushing it into a database, an ORM may not be the best option either. OO was not created to deal with data-centric systems; it was meant to deal with behavior and business logic. If you have no behavior or business logic, and are just moving data values directly from forms to a database, then the benefits of OO are lower.

Still, even in a data-centric application I would still use the ORM features, simply for the amount of work it removes in terms of CRUD queries.

That said, I don't write data-centric apps (I find them very boring to deal with). I also have only come across a handful of truly data-centric apps (things like web-based database administration tools).

@Andrew,

A note: in Hibernate, objects do not have delete methods - the Hibernate Session object has a delete method, and is smart enough to batch together all the delete statements that it would generate and execute them all at once in a single database roundtrip.

@Don,

Hibernate is a Java ORM. If you look at the Rails ORM, you can easily do:

User.create(params['user'])

in the "action" page for a web form (like a registration form).

ORM brings extraordinary benefits when your data model is complex or your feature set is complex because it becomes extremely complex to try to figure out and even generate the right SQL commands all the time. Additionally, SQL yields a flat tabular result-set, while many ORMs will let you work with trees of objects (a User, the user's posts, and all the posts' comments) without any extra work. A simplistic Rails-like example:

user = User.find(params['id'])
print user.name
foreach(post in user.posts) print post.title

You can do it similarly in ColdFusion with Hibernate, without having to descend into SQL.

Cheers,
Justice

@Don,

I like to do as much data manipulation as possible in the database, and I also find it much easier to think in sets instead of objects. However I agree with Brian Kotek, in that OOP does have it's advantages when dealing with single entities.

But in saying that, why does it have to be all or nothing with programming methodologies? Why not use OOP for single instances and then use SQL for set based work.

@Justice,

I wasn't commenting directly on Hibernate, but more on OOP and ORM in general; and I understand that Hibernate has some extra features to handle some of the issues that generic ORM doesn't support.

It is also true that OOP handles trees well for a single object. However, I can produce a result-set with a tree structure in SQL via XML for a whole set of entities (especially with the XML features in MS-SQL 2005+).

Don't get me wrong, I am not attacking OOP or ORM. As I said previously, I believe they have their place in programming; just not a one size fits all.

I also believe that as a developer we need to ask what methods are available and which one is appropriate for the current situation.

I do have a bias towards set based manipulation, and I have been proven wrong plenty of times, with a loop over single entities turning out to be more efficient; but again this comes down to the situation.

@Ben, I absolutely agree. I do a lot of OOP via CFC's. That is where I put all my db manipulation etc. Right now I have a login piece that uses ORM and it works fine.

OOP is good for some things but when it comes to hardcore (get your mind out of the gutter) data manipulation, just gotta get your hands dirty.

And like I said, everything I can do right now with ORM I already have in my cfcs. So I don't see the point of it really other than mental games.

Ooooops. Sorry that was to you Andrew. lol Silly me. I think it should be Friday already.

Hi Ben,

Great post. This helped me a lot figuring out many-to-one relationships. This example is exactly what I was looking for. This by far is the most common table relationship in a database - where userids are practically on every row of record in a database as a modifier or creator - but no one makes examples of it. At least this is the only one I've seen on the net.

However I'm wondering how to set an empty component contact_information if the contact_information_id is empty.

Currently if the contact_information_id is null in the database, it will produce a Variable Undefined error.

Im trying to avoid having to wrap in a try catch or isDefined function

<h1>Phone List</h1>
<cfloop array="#contacts#" index="contact">
#contact.getName()#
<cftry>#contact.getContactInformation().getPhone()<cfcatch></cfcatch></cftry>
</cfloop>

I just figured it out. Added to contact.cfc

public any function getContactInformation() {
	if(isnull(variables.modifier)) {
			contactinfo = new ContactInformation();
			contactinfo.init();
			return contactinfo;
	} else {
			return variables.contactInformation;
	}
}

On another note. After adding the contactinformation property it suggested that I add insert="false" update="false" to my contact_information_id property declaration.

Does this mean I cannot update this field through EntitySave()? Is it because Im using fkcolumn?

Thanks in advance.