Skip to main content
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Damon Gentry
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Damon Gentry ( @Fuseboxer )

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

By on
Tags:

Over the weekend, for funzies, I was taking some of my standard SQL and converting it over to use ColdFusion 9's new ORM functionality. SQL is really easy for me to think about and ORM is very new, so I thought translating some use-cases would help me get adjusted to the new mentality. As it turned out, however, the standard SQL that I had was giving me quite the headache as I start to convert it!

In a particular application that I have, I break out contact information into its own database table such that other entities in my database can join to it if they need contact information:

contact_information

  • id - int - primary key
  • phone - varchar
  • email - varchar

NOTE: In reality, that table is bigger, but for our purposes, I'm keeping it small.

Then, I might have other database tables join to it using a "contact_information_id" foreign key reference.

contact

  • id - int - primary key
  • name - varchar
  • contact_information_id - int - PK in contact_information

In this table, the "contact_information_id" column is a foreign key that points to the "id" field (primary key) in the contact_information data table. With this type of relationship in place, I can easily create a SQL statement to join the two tables into a single "entity:"

SELECT
	c.id,
	c.name,
	ci.phone,
	ci.email
FROM
	contact c
INNER JOIN
	contact_information ci
ON
	c.contact_information_id = ci.id

In addition to a "contact" table, I might have other tables like, "company," that can / will follow the same schema. When I look at this SQL statement, what I think of is a single entity that has been spread across multiple tables. And, as it turns out, ColdFusion 9's new ORM functionality has a mechanism defined for this very use case; it's called a "Join Mapping." A Join Mapping is used to map the properties of a single CFC to several different database tables.

To accomplish this, all you have to do is provide a Table and JoinColumn attribute in the CFProperty tags of those properties that are not persisted in the CFC's corresponding table. So, for example, in my Contact.cfc, my "phone" column would use the table, "contact_information," and it would be joined on the column, "contact_information_id:"

Contact.cfc

<!---
	Enable ORM for this component and persist the data
	in the contact data table.
--->
<cfcomponent
	output="false"
	hint="I am a contact."
	persistent="true"
	table="contact">

	<cfproperty
		name="id"
		type="numeric"
		setter="false"

		persistent="true"
		fieldtype="id"
		column="id"
		ormtype="integer"
		generator="identity"
		length="10"
		notnull="true"
		/>

	<cfproperty
		name="name"
		type="string"

		persistent="true"
		fieldtype="column"
		column="name"
		ormtype="string"
		length="50"
		notnull="true"
		/>

	<!---
		The phone property is going to be spread across the
		contact_information table. It will be joined on the
		column, contact_information_id. Use the Table and
		JoinColumn attributes to define this distributed
		persistence model.
	--->
	<cfproperty
		name="phone"
		type="string"
		default=""

		persistent="true"
		table="contact_information"
		joincolumn="contact_information_id"
		fieldtype="column"
		column="phone"
		length="30"
		notnull="true"
		/>

</cfcomponent>

I am keeping these CFCs short to demonstrate the ORM principles rather than the full breadth of the fields. And, as you can see, the contact's name field will be persisted in the contact data table; but, the phone field, as dictated by the Table and JoinColumn CFProperty attributes, will be persisted in the contact_information table.

This all seems fine so far until we look at how the ORM system builds the correspond database tables in our database:

contact

  • id - int
  • name - varchar

contact_information

  • contact_information_id - int
  • phone - varchar

What happened to our contact_information table? Where is its primary key? It looks like the ORM system got the relationship backwards! The "contact_information_id" column is not supposed to be in contact_information - it's supposed to be in the contact table and be a foreign key to the ID column of our contact_information table.

Before I went into full-on panic mode as to why the tables were all messed up, I figured I would at least complete the translation. Once the code was done and working with the ORM's funky structure, then I could go back and debug the table columns. So, next I created my Company.cfc with the exact same structure:

Company.cfc

<!---
	Enable ORM for this component and persist the data
	in the company data table.
--->
<cfcomponent
	output="false"
	hint="I am a company."
	persistent="true"
	table="company">

	<cfproperty
		name="id"
		type="numeric"
		setter="false"

		persistent="true"
		fieldtype="id"
		column="id"
		ormtype="integer"
		generator="identity"
		length="10"
		notnull="true"
		/>

	<cfproperty
		name="name"
		type="string"

		persistent="true"
		fieldtype="column"
		column="name"
		ormtype="string"
		length="50"
		notnull="true"
		/>

	<!---
		The phone property is going to be spread across the
		contact_information table. It will be joined on the
		column, contact_information_id. Use the Table and
		JoinColumn attributes to define this distributed
		persistence model.
	--->
	<cfproperty
		name="phone"
		type="string"
		default=""

		persistent="true"
		table="contact_information"
		joincolumn="contact_information_id"
		fieldtype="column"
		column="phone"
		length="30"
		notnull="true"
		/>

</cfcomponent>

As you can see, the Company.cfc is exactly like the Contact.cfc except for the fact that it is persisted in the company table, not the contact table. And just like the Contact entity, this CFC also has a phone property whose persistence is distributed to the contact_information table.

So far, this is just like our original SQL statement - both the contact and the company entities have "contact information" persisted into a separate table. Despite some odd column naming, this ORM Join Mapping appears to translate well in this use case. But, when I rebuild the database and run a test page that creates and persists a Contact.cfc instance, I get this ColdFusion error:

INSERT on table 'CONTACT_INFORMATION' caused a violation of foreign key constraint 'FK6BC4C78DC453376A' for key (1). The statement has been rolled back.

Very interesting! Inserting a Contact created a foreign key violation in the contact_information table. Now I see why the columns were a bit funky. It appears that this distributed persistence model - the Join Mapping - cannot be shared between different entities. When the ORM system created the contact_information table, it must have added a foreign key constraint to the "contact_information_id" column such that its value must appear in the associated table (ie. contact). This was fine when we had a contact table only; but, when we added both a contact and a company table to the database, the ORM system must have added two foreign key constraints to the contact_information_id column. With two different foreign key constraints in place on the same column, no insert into the contact_information table could ever be valid.

Here's where the conceptual relationship defined in our SQL statement simply doesn't jive with the Object-Relational Mapping (ORM) view of the world. As it turns out, what was a simple distributed persistence model in our SQL is actually a one-to-one entity relationship in the ORM context. And, for this type of relationship to work, our "contact information" needs to be an entity in and of itself:

ContactInformation.cfc

<!---
	Enable ORM for this component and persist the data
	in the contact_information data table.
--->
<cfcomponent
	output="false"
	hint="I am contact information."
	persistent="true"
	table="contact_information">

	<cfproperty
		name="id"
		type="numeric"
		setter="false"

		persistent="true"
		fieldtype="id"
		column="id"
		ormtype="integer"
		generator="identity"
		length="10"
		notnull="true"
		/>

	<cfproperty
		name="phone"
		type="string"

		persistent="true"
		fieldtype="column"
		column="phone"
		ormtype="string"
		length="30"
		notnull="true"
		/>

</cfcomponent>

Once this CFC was in place, I then had to go back and edit my Contact.cfc and Company.cfc to contain a ContactInformation property that mapped to this new ContactInformation.cfc.

Contact.cfc

<!---
	Enable ORM for this component and persist the data
	in the contact data table.
--->
<cfcomponent
	output="false"
	hint="I am a contact."
	persistent="true"
	table="contact">

	<cfproperty
		name="id"
		type="numeric"
		setter="false"

		persistent="true"
		fieldtype="id"
		column="id"
		ormtype="integer"
		generator="identity"
		length="10"
		notnull="true"
		/>

	<cfproperty
		name="name"
		type="string"

		persistent="true"
		fieldtype="column"
		column="name"
		ormtype="string"
		length="50"
		notnull="true"
		/>

	<!---
		This Contact contains a ContactInformation entity in a
		one-to-one relationship. In this case, the foreign key
		column (FKColumn) in THIS table is contact_information_id
		and references the primary key of the contact_information
		table, which is ID.
	--->
	<cfproperty
		name="contactInformation"
		type="any"

		persistent="true"
		fieldtype="one-to-one"
		cfc="ContactInformation"
		fkcolumn="contact_information_id"
		fetch="join"
		notnull="true"
		cascade="all"
		/>

</cfcomponent>

As you can see here, rather than having the property, "phone," be part of a distributed persistence JOIN Mapping in the Contact.cfc definition, we had to move it into a one-to-one
ContactInformation property relationship. In this ContactInformation CFProperty tag, the FKColumn tells the ORM system which column in the contact table will contain a foreign key (FK) reference to the contact_information table's primary key (id). The Fetch attribute determines if lazy loading will be available on this relationship; since we are using a JOIN to pull back the contact_information record, it will be loaded immediately.

In the ContactInformation property, I have turned on "cascade" for all persistence actions. Since I want ContactInformation to be a sort-of distributed persistence portion of Contact (or Company, or whatever other entity might have contact information), I want the embedded ContactInformation.cfc instance to be saved when the Contact is saved and deleted when the Contact is deleted; I don't ever want a ContactInformation.cfc instance to exist beyond the life of its associated entity record.

The Company.cfc looks exactly the same as our new Contact.cfc, so I won't go into details; but, the code that uses and manipulates the Contact instance now has to go through the ContactInformation property to get and set the phone number:

<!--- Create a new contact instance. --->
<cfset contact = new com.Contact() />

<!--- Create a new contact information instance. --->
<cfset contactInfo = new com.ContactInformation() />

<!--- Store the contact values. --->
<cfset contact.setName( "Tricia" ) />

<!--- Set the contact information. --->
<cfset contactInfo.setPhone( "212-555-7718" ) />

<!--- Store the contact information in the contact. --->
<cfset contact.setContactInformation( contactInfo ) />

<!--- Save the contact. --->
<cfset entitySave( contact ) />

As you can see above, in order to store the phone number into the new contact, we had to first create a ContactInformation.cfc instance. The phone number was then stored into the ContactInformation.cfc which was, itself, then stored in the ContactInformation property of our Contact.cfc.

Getting this all to work took me no less than 3 hours. Afterwards, it might seem like a rather simple example; but, the shift in mentality between the SQL-dictated relationship and the ORM-dictated relationship was not obvious at all. ORM is definitely powerful; but, it requires not only a change in the way we think about objects but a change in how we think about relationships.

Want to use code from this post? Check out the license.

Reader Comments

15,640 Comments

@Brian,

From what it looked like in the docs, I can't use embedded objects without manually altering the Hibernate configuration files. I hope to avoid any touching of the config files (especially as a new comer to ORM).

15,640 Comments

@Brian,

Hmm, maybe I am just not understand the documentation (not a stretch of the imagination). I'm looking at the following page:

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WS027D3772-2E98-4d5b-8800-054A62EBF8D9.html#WS28445A09-C914-45c5-95EA-7F0BC4B18F76

In fact, now that I look at it again, here are my concerns:

1. Important: The embedded object cannot be a persistent object. This feature is supported only when the hibernate mapping is explicitly defined in the hibernate mapping file (.hbm.xml files).

My Understanding: I guess it cannot be persistent because its persistence is handled by the parent object?? I am not clear on this.

2. In the database, both these objects are persisted in the Employee table as a single row.

My Understanding: In this case, I don't think that both my Company and my Contact objects can share the same ContactInformation table.

3. Name object (embedded object) itself does not have its own identity.

My Understanding: This goes with the one above.

Also, the documentation is not very clear on how to reference the CFC from within the CFProperty tags. For example, their embedded CFC is called "name.cfc", but the embedded property is "EmployeeName". It looks like this relationship is only defined in the actual Hibernate config XML file.

I know the docs are sometimes very confusing, so if you can shed some light on this, I would much appreciate it. This stuff is a bit overwhelming for me!!

113 Comments

@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

15,640 Comments

@Justice,

While I can respect the fact that Hibernate is a seasoned product, I am not sure how your solution solves my problem. In my existing database, I have both Contact and Company tables sharing a ContactInformation table. If the ID of the contact information table is also a foreign key, how will it know which table it is pointing to (contact vs. company). As I hypothesized in the blog, I think that's it was throwing the foreign key constraint upon insert.

I understand that it suggests best practices, but I think there will be times when it needs to be implemented on top of an existing applications... of course, maybe that is something that should only be done be a true ORM expert who is comfortable going in and updating the mapping configuration files.

I, good sir, am NOT that expert :)

15,640 Comments

@Justice,

Also, it looks like you know about embedded objects. If you wouldn't mind, can you look at my concerns in my Embedded response to Brian and maybe expand on the documentation a bit? I find it to be very confusing.

Your expert guidance is appreciated.

14 Comments

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

15,640 Comments

@Brian,

The model is just a one-to-one relationship. One contact record can only have one contact_info record. If it looks reversed in my first example, it's probably because I am just learning all this ORM stuff and get confused on which columns are specified how.

Even though, in the first example, had I changed the JoinColumn attribute to "contact_id" I suppose that would have pointed out even more quickly that my SQL concepts did not fit into the "JOIN Mapping" concept.

113 Comments

@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.

116 Comments

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.

113 Comments

@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.

15,640 Comments

@Justice,

Thanks for the insight. This stuff is all very new to me, so I'm quite shaky. My database structure probably doesn't make the most sense as it is. I was heavily influenced in the way that I structured this DB and looking back now, I can't really see a great reason to have factored out contact information out into its own table, especially seeing as its a one-to-one relationship.

@Brian,

Yeah, that database design is a bit funky. The factoring out of the contact info does imply a one-to-many, but in practice the application uses it as a one-to-one... this was designed several years ago :)

113 Comments

@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.

68 Comments

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

15,640 Comments

@Justice,

Agreed. I'm not *truly* building this on top of an existing app; I am merely using an existing app as a case study for learning mapped CFCs in the ORM features.

As I was going through this, the pain that it was causing made me think more about the underlying data structure than it did about the way ORM was working. At the end of the day, I have no problems thinking about ContactInformation as a proprety rather than a "subset" of Contact fields. This would also lead more naturally to a one-to-many collection, as most people seem to think I was trying to do.

So far, it all seems very cool.

@Rick,

Yeah, it's a bit of an old app. Perhaps not the best database structure. If I had to tell you why it was built the way it was, I would not be able to give you any great arguments.

45 Comments

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?

15,640 Comments

@David,

The CFProperty tag has an "index" attribute, but I have not used it yet. The documentation states:

"Specifies the name of an index that is created using the mapped column."

55 Comments

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.

113 Comments

@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.

44 Comments

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.

116 Comments

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).

44 Comments

@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). :\

116 Comments

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.

15,640 Comments

@Peter,

You make a great point. When I started out looking into the ORM functionality, I was trying very much NOT to think about the database at all. In fact, that's why I started out with a clean Derby database for testing - I wanted the ORM to create all the tables for me.

With this specific example, I happen to see the JOIN Mapping in the documentation and thought to myself that that would nicely align with the way that I handle some of my relationship.

But, I agree 100% with what you and Justice are saying - think in terms of objects.

@Jake,

As @Brian points out, I have a unique coding style, so there's not nearly as many lines of code as there might appear to be. Also, because I am still learning this stuff, I am putting in optional attributes so that I can drill it into my head.

For example, all CFProperty tags default to "persisted=true". However, I still like to put them in so that I can get my mind in the right context. When I've gotten this more down pat, I'll probably start removing some of the unnecessary attributes.

If you look at other people's blog examples, you will see that their property tags are MUCH MUCH smaller than mine :) What can I say - I'm a sucker for verbosity.

53 Comments

@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.

15,640 Comments

@Andrew,

If I could go back and redesign the database (this was based on an existing app of a few years), I would probably go do it this way.

57 Comments

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?

57 Comments

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.

113 Comments

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

57 Comments

@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.

113 Comments

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

53 Comments

@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.

57 Comments

@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.

116 Comments

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).

113 Comments

@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

15,640 Comments

I don't think I saw this in any of the comments, but I believe you can also use the function:

ORMExecuteQuery( hql )

... to do bulk deletes in a more object-oriented way. Of course, I have not tried that - just my guess.

53 Comments

@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.

57 Comments

@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.

2 Comments

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>
2 Comments

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.

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel