www.ColdFusionORMbook.com

Agenda

  • Introduction to ORM
  • Getting Started
  • Relationships
  • HQL
  • Gotchas
  • Next Steps
  • Resources

What is an ORM?

Object relational mapping (ORM) is a programming framework that allows you to define a mapping between application object model and the relational database.
http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSD628ADC4-A5F7-4079-99E0-FD725BE9B4BD.html

Why should I use an ORM?

  • Reduces development time
  • Overcomes vendor specific SQL differences
  • Promotes effective design patterns
  • Reduces maintenance

Relax!

Potential Downside

Without proper tuning performance can suffer.

Make sure you optimize queries
and use effective caching strategy!

Getting Started

Step 1: Configuring ORM in your Application
	component {
	  // set the unique name of the application
	  this.name = "myCMS";
	  // set the datasource
	  this.datasource = "mydsn";
	  // turn ORM on
	  this.ormenabled = true;
	}

Application.cfc

Step 2: Mapping Tables to Objects
/**
*  I model a blog article
*/
component persistent="true" table="articles" {
  
  // identifier
  property name="id" fieldtype="id" generator="native" column="articleID";
  
  // properties
  property name="title" ormType="string" length="250";
  property name="summary" ormType="string" length="500";
  property name="description" ormType="text";
}

Article.cfc

Additional ORM Settings

	this.ormsettings = {
	      cfclocation = ["model/beans"],
	      dbcreate = "update",
	      dialect = "MySQL",
	      datasource = "ORMds",
	      useDBForMapping = false
	    }

Application.cfc

ORMReload()

	if (structKeyExists(url,"reloadORM")) {      
		ORMReload();     
	}
							

CRUD

Hibernate’s design goal is to relieve the developer from 95% of common data persistence-related programming tasks by eliminating the need for manual, hand-crafted data processing using SQL and JDBC.
Source: http://docs.jboss.org/hibernate/orm/4.1/quickstart/en-US/html/pr01.html

Create

	myCategory = EntityNew("Category");
	myCategory.setTitle("Music");
	EntitySave(myCategory);
			
	myCategory = EntityNew("Category", {title:"Sports"});
	EntitySave(myCategory);
			
	myCategory = new model.Category(title="Family");
	EntitySave(myCategory);
			
	myCategory = EntityNew("Category"); 
	myCategory.title = "Holiday";
	EntitySave(myCategory);
			

Read

	categories = EntityLoad("Category");
	WriteDump(var = categories, top = 2);
			

Update

	myCategory = EntityLoadByPK("Category", 1);
	myCategory.setTitle("New Title");
			

Delete

	myCategory = EntityLoadByPK("Category", 1);
	EntityDelete(myCategory);
			

More Examples

	categories = EntityLoad("Category", {dsp = true});
	categories = EntityLoad("Category", {dsp = true}, "date desc");

More Examples

	myCategory = EntityLoadbyPK("Category",1);
	myCategory = EntityLoad("Category",1);
	myCategory = EntityLoad("Category",1,true);

Convert Object to Query

	myCategory = EntityLoadbyPK("Category",1);
	myCategoryQuery = EntityToQuery(myCategory);				
	categories = EntityLoad("Category");
	categoriesQuery = EntityToQuery(categories);

Relationships

One-to-Many

	component persistent="true" {
		property name="id" fieldtype="id" generator="native";
		property name="firstName" length="50";
		property name="lastName" length="50";
			  
		// one Author can have many Articles
		property name="articles" fieldtype="one-to-many"
		 cfc="Article" fkcolumn="fk_authorid" type="array"
		 singularname="article";
		}

Author.cfc

One-to-Many Methods

  • addArticle()
  • hasArticle()
  • removeArticle()
  • setArticles()
  • getArticles()

One-to-Many Usage

	transaction {
		Author = EntityLoadByPK("Author", 1);

		Article = EntityNew("Article");
		Article.setTitle("ORM Makes My Life Easy!");

		Author.addArticle(Article);

		EntitySave(Article);
		}

Many-to-One

	component persistent="true" {
		property name="id" fieldtype="id" generator="native";
		...

		//many Article entities can have one Author entity
		 property name="Author" fieldtype="many-to-one"
		 cfc="Author" fkcolumn="fk_authorid";
		}

Article.cfc

Many-to-One Methods

  • hasAuthor()
  • setAuthor()
  • getAuthor()

Many-to-Many

	component persistent="true" {
		property name="id" fieldtype="id" generator="native";
		...

		property name="articles" fieldtype="many-to-many"
	 	 cfc="Articles" type="array" singularname="article"
	 	 linktable="ArticlesCategories";
		}

Category.cfc

Many-to-Many Methods

  • addArticle()
  • getArticles()
  • hasArticle()
  • removeArticle()
  • setArticles()

Relationship Power

The U.S. women's gymnastics team. Photo: Jason Lavengood/USA Gymnastics

The Inverse Attribute

component persistent="true" {
	...
		  
	// one Author can have many Articles
	property name="articles" fieldtype="one-to-many"
	 cfc="Article" fkcolumn="fk_authorid" type="array"
	 singularname="article" 
	 inverse="true" cascade="delete";
	}

Author.cfc

Cascading Strategies

  • none
  • delete
  • save-update
  • all
  • delete-orphan
  • all-delete-orphan

Sort Order

component persistent="true" {
	...
		  
	// one Author can have many Articles
	property name="articles" fieldtype="one-to-many"
	 cfc="Article" fkcolumn="fk_authorid" type="array"
	 singularname="article" 
	 inverse="true" cascade="delete"
	 orderby="datepublished desc";
	}

Author.cfc

Where Filter

component persistent="true" {
	...
		  
	// one Author can have many Articles
	property name="publishedarticles" fieldtype="one-to-many"
	 cfc="Article" fkcolumn="fk_authorid"
	 orderby="datepublished desc"
	 where="ispublished = 1";
	}

Author.cfc

Formulas

component persistent="true" {
	... 

	// get count of active articles
	property name="publishedarticlecount" setter="false"
	formula="select count(*)
		     from Articles a
		     where author_pk = a.fk_authorid
	  		   and a.ispublished = 1";
	}

Author.cfc

HQL

HQL

	EntityLoad("User");

Same as:

	ORMExecuteQuery("from User");

SQL Injection Prevention

	userid = 123;
	EntityLoadByPK("User", id);

Same as:

	ORMExecuteQuery("from User where id = #userid#", true);

Better:

	ORMExecuteQuery("from User where id = ?", [userid], true);

Or:

	ORMExecuteQuery("from User where id = :id", {id=userid}, true);

HQL Examples

ORMExecuteQuery(hql, params [,unique] [,queryOptions])
	q = ORMExecuteQuery(
	 "from User where username like :prefix", {prefix = "J%"}
	);
				
	q = ORMExecuteQuery(
	 "from Art where price > :lowprice", {lowprice = 400}
	);
				
	q = ORMExecuteQuery(
	 "from Artist", false, {offset=5, maxresults=10, timeout=5}
	);
				

Relationships HQL

Get articles by Author ID:

	Author = EntityLoadByPK("Author", 2);
	articles = Author.getArticles();

The HQL way:

	from Article
	where Author.id = :authorID

Relationships HQL

Get published articles by Author ID:

	from Article
	where Author.id = :authorID
	 and ispublished = :isPublished

Get unique authors:

	select distinct p.Author
	from Article a
	where a.ispublished = :isPublished

Hash Maps

	select new map(id as ID, title as TITLE)
	from Article
	order by title

From Love to Hate?

Image Source: http://www.easyrock.com.ph

Errors

Error during DDL export
Cannot simultaneously fetch multiple bags
Failed to lazily initialize a collection of role, no session or session was closed
Illegal attempt to dereference collection

Incomplete Information?

Logging

	this.ormsettings = {
	      ...
	      logSQL = true
	    }

{cfinstall}\cfusion\log4j.properties

log4j.logger.org.hibernate.type=DEBUG

Hibernate Session Management

ORM session starts when the first CRUD method is called and is closed when the request ends.
	this.ormsettings = {
		...
		//switch to false and use transactions
		automanageSession = false,
		flushatrequestend = false
	    }

Performance Improvements

  • Paging
  • Immediate Fetching
  • Lazy Fetching
  • Eager Fetching
  • Batch Fetching

There is more!

  • Second-Level Caching
  • Convenience Methods
  • Versioning
  • Validation
  • ORM Search
  • Advanced Mapping
  • Event Handlers

Online Resources

Publications

Thank You!