In part one I dipped my toes into the world of ORM in ColdFusion 9. Today I'm diving in...and I know there's far more to this than what I'm about to try, so there may be a Part 3 yet to come. As with part one, I'm writing this as I go...mistakes and all.

Today's challenge: get the rest of the spreadsheet imported into a more relational format.

The source data has a set of columns for payment information, which is great if you only want to keep the most recent payment information. Likewise, there's one column called "comments" that looks like it'll grow to be kinda nasty over time.

Ideally there's a one-to-many relationship between a person and payments and a person and comments. So how do we do that in an ORM configuration?

Step one is to create a CFC for a comment, as that is somewhat more simple.

Once again we've defined the CFC as "persistent" so Hibernate knows about it. As before we set the primary key field by defining the fieldtype and generator attributes on it.

The second property is the fun one. Usually when we're building a database, we'd reflexively put "personID" as a foreign key on the table. Remember, though, that what we're trying to capture here is the relationship between two objects. A comment "belongs to" a person.

Deconstructing the attributes:

• cfc: the actual cfc object that this property references (person is in the /chiomega/mms folder)
• fkcolumn: I like to use the PK of the referenced object as the FK column name in the database.
• fieldtype: truly magical - there are "many" comments for "one" person.
• lazy: don't get the data for the person until I ask for it

The options for Lazy Loading are many...and extremely worth the read. Any code where I can include an attribute lazy="extra" is beyond awesome to me! Quite honestly, though, how many times have you written a very large SQL statement with many join conditions, knowing full well you won't use most of the data but prefer to use one "getter" statement? Too many, right? No more. Let Hibernate take care of getting the data for you at the appropriate time. I could write a blog post purely on the subject of lazy loading and how great this is.

Back to the topic at hand: now that we've got a "comment" object with a relationship to "person" defined, let's look at the "person" and set the inverse relationship.

That last property says that each person has a property called "comments" that is an array of "comment" entities of type "chiomega.mms.comment".

Whoa. Break it down:

• singularName: in a "one-to-many" scenario, what we're referencing is plural...but there's also a need (which I'll clarify momentarily) to reference the items individually.
• type: valid types are array and struct. An array makes sense for this example.
• fieldtype: the reverse of what we had on the "comment" entity. Each person can have 0 or more "comment" objects.
• cfc: the type you're referencing, in this case the cfc we just created
• fkcolumn: the column in the "child" table that will represent this "person"; recall that we set fkColumn in the "comment" entity to "personID"
• cascade: When I save a "person", save any child comment records that exist (insert or update) on the person. Also goes for delete operations.

singularName is incredibly useful: this is used to define the auto-generated add/remove methods for the related objects. Read more on what's created for you in the ColdFusion 9 docs.

The "cascade" attribute was a trial-by-error discovery. There's a cascade options section in the docs on your choices here - basically it defines how the relationship behaves on save/update operations. If you get a object references an unsaved transient instance - save the transient instance before flushing error, this is why -- go back and set a cascade on your parent entity so that child operations work properly.

That should do it for configuration. Let's tie it together and import people and comments from the spreadsheet again.

"obj" is my person. If we want to import a comment, we'll create a new entity of type "comment". Recall that these entities have getters and setters automagically created for us, so I populate the note and createDate properties of the comment with values from the spreadsheet.

Once the comment is populated, I "addComment" to the person - the addComment is one of those automatically generated methods. Something I learned since the last post is that calling ormFlush() isn't necessary - at the end of the request it's called implicitly.

That was easy! Following the same pattern, I added an entity for payments:

...and configured my 'person' to reference them...

Then tweaked the importer to add those as well:

And, best of all, I haven't written a single line of SQL nor have I gone in and created tables/columns/relationships/constraints in the database, yet I have this ready and waiting for use:

Up next: building an actual application using what we've just created. I'm pumped...this is working out great so far!