Monday 13 July 2009

Coldfusion, JPA & Hibernate

Coldfusion 9 beta has just been released, and one of its major selling points for me is the out of the box Object Relational Mapping support. Excellent!

Now, two things are really annoying me about this:

  1. ORM has been around for a long time, Hibernate for Java and Transfer for Coldfusion have both been around for well over 10 years. Out of the box support for this in Coldfusion is late. Coldfusion is meant to be a 'rapid application development platform', abstracting the database layer means developers can get on with writing more important code, instead of debugging SQL. It should have been here before now.
  2. The CF9 implementation is tied to hibernate. One of the advantages of ORM is that it's database agnostic. If it's a good thing that your code isn't tied to a specific database, why would you want to be tied to a specific persistance provider?

Java Persistence API

Why haven't Adobe used the JPA? That way you can write your database independant code and implement it with whichever JPA implementation suits you best, and seamlessly change to a new implementation if a better one comes along:

If you install Coldfusion on Glassfish or Geronimo you will already have TopLink or OpenJPA available, why would you want to have Hibernate libraries there aswell?

I've had a go with using the JPA and Hibernate from Coldfusion, and it works quite well. I've made use of my Java Utility Class, so you'll need to get up and running first. Or use another way of adding classes and jars to your Coldfusion classpath.

First I created a bunch of JPA annotated entity classes and a persistence.xml to use the CFARTGALLERY database that comes installed with Coldfusion 8.


<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="" xmlns:xsi="" xsi:schemaLocation="">
  <persistence-unit name="CF_ART_GALLERY_PU" transaction-type="RESOURCE_LOCAL">
      <property name="hibernate.connection.driver_class" value="org.apache.derby.jdbc.EmbeddedDriver"/>
      <property name="hibernate.connection.url" value="jdbc:derby:C:\ColdFusion8\db\artgallery"/>
      <property name="hibernate.connection.username" value=""/>
      <property name="hibernate.connection.password" value=""/>

import java.math.BigDecimal;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Table(schema = "APP", name = "ART")
  @NamedQuery(name = "Art.findAll", query = "SELECT a FROM Art a"),
  @NamedQuery(name = "Art.findByArtId", query = "SELECT a FROM Art a WHERE a.artId = :artId"),
  @NamedQuery(name = "Art.findByArtName", query = "SELECT a FROM Art a WHERE a.artName = :artName"),
  @NamedQuery(name = "Art.findByPrice", query = "SELECT a FROM Art a WHERE a.price = :price"),
  @NamedQuery(name = "Art.findByIsSold", query = "SELECT a FROM Art a WHERE a.isSold = :isSold"),
  @NamedQuery(name = "Art.countAll", query = "SELECT COUNT(a) FROM Art a")
public class Art implements Serializable {

  private static final long serialVersionUID = 1L;
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "ARTID")
  private Integer artId;
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "ARTISTID")
  private Artist artist;
  @Column(name = "ARTNAME")
  private String artName;
  @Column(name = "DESCRIPTION")
  private String description;
  @Column(name = "PRICE")
  private BigDecimal price;
  @Column(name = "LARGEIMAGE")
  private String largeImage;
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "MEDIAID")
  private Media media;
  @Column(name = "ISSOLD")
  private Boolean isSold;

  public Art() {

  public Art(Integer artId) {
    this.artId = artId;

  public Integer getArtId() {
    return artId;

  public void setArtId(Integer artId) {
    this.artId = artId;

  public Artist getArtist() {
    return artist;

  public void setArtist(Artist artist) {
    this.artist = artist;

  public String getArtName() {
    return artName;

  public void setArtName(String artName) {
    this.artName = artName;

  public String getDescription() {
    return description;

  public void setDescription(String description) {
    this.description = description;

  public BigDecimal getPrice() {
    return price;

  public void setPrice(BigDecimal price) {
    this.price = price;

  public String getLargeImage() {
    return largeImage;

  public void setLargeImage(String largeImage) {
    this.largeImage = largeImage;

  public Media getMedia() {
    return media;

  public void setMedia(Media media) { = media;

  public Boolean isIsSold() {
    return isSold;

  public void setIsSold(Boolean isSold) {
    this.isSold = isSold;

If you don't want to hand code Java entity classes, a Netbeans wizard can help you generate them and a persistence.xml.

Next I created a CFC to manage the creation of an entity manager, and wrap common functions.


<cfcomponent displayname="JpaController" output="false">
 <cffunction access="public" returntype="JpaController" name="init" output="false">
   var LOCAL = {};  
   LOCAL.srcPath = expandPath("java/src");
   LOCAL.destPath = expandPath("java/bin");
   LOCAL.confPath = expandPath("java/conf");
   LOCAL.libPath = expandPath("java/lib");  
   LOCAL.classPath = [LOCAL.libPath, LOCAL.destPath, LOCAL.confPath];
   LOCAL.persistenceUnit = "CF_ART_GALLERY_PU";  
   VARIABLES.cfjavautil = createObject("java", "").init(LOCAL.srcPath, LOCAL.destPath, LOCAL.classPath);
   LOCAL.persistence = VARIABLES.cfjavautil.createObject("javax.persistence.Persistence");
     LOCAL.emf = LOCAL.persistence.createEntityManagerFactory(LOCAL.persistenceUnit);

   VARIABLES.em = LOCAL.emf.createEntityManager();
   return this;

 <cffunction access="public" returntype="Any" name="createEntity" output="false">
   <cfargument type="String" name="entity" required="true">  
      return VARIABLES.cfjavautil.createObject(entity);

 <cffunction access="public" returntype="void" name="persist" output="false">
   <cfargument type="String" name="entity" required="true">

  <cffunction access="public" returntype="Any" name="merge" output="false">
    <cfargument type="String" name="entity" required="true">
      mergedEntity = VARIABLES.em.merge(entity);
      return mergedEntity;

  <cffunction access="public" returntype="void" name="remove" output="false">
    <cfargument type="String" name="entity" required="true">
 <cffunction access="public" returntype="Any" name="createNamedQuery" output="false">  
   <cfargument type="String" name="namedQuery" required="true">
    return VARIABLES.em.createNamedQuery("#namedQuery#");

 <cffunction access="public" returntype="void" name="close" output="false">

I use an Application.cfm to create a single instance of the CFC and store it in application scope.


<cfapplication name="ColdfusionJPA" sessionmanagement="true">
  if(NOT isDefined("APPLICATION.jpaController")) {
    APPLICATION.jpaController = createObject("component", "JpaController").init();

Then finally, a page to use the controller CFC to execute a named query to retrieve a list of Art entities and page through them.


<cfparam name="FORM.firstResult" default="0">
<cfparam name="FORM.maxResult" default="10">

<cfif isDefined("FORM.prev")>
  <cfset FORM.firstResult = FORM.firstResult - FORM.maxResult>
<cfelseif isDefined("")>
  <cfset FORM.firstResult = FORM.firstResult + FORM.maxResult>

  artCount = APPLICATION.jpaController.createNamedQuery("Art.countAll").getSingleResult();
  artList = APPLICATION.jpaController.createNamedQuery("Art.findAll").setFirstResult(FORM.firstResult).setMaxResults(FORM.maxResult).getResultList();

  <cfloop index="art" array="#artList#">
    #art.getArtId()#) #art.getArtName()#

  <form action="index.cfm" method="post">
    <cfif FORM.firstResult GT 0>
      <input type="submit" id="prev" name="prev" value="<-">
    <cfif (FORM.firstResult + FORM.maxResult) LT artCount>
      <input type="submit" id="next" name="next" value="->">
    <input type="hidden" id="firstResult" name="firstResult" value="#FORM.firstResult#">
    <input type="hidden" id="maxResult" name="maxResult" value="#FORM.maxResult#">

To have a go with this download the zip below and extract it to your C:\ColdFusion8\wwwroot directory. If you are using my Java Utility Class, then entity classes will be compiled automatically and the JPA and Hibernate JARs will be available from the project lib directory.

Source Code