ColdFusion, HQL and Named Parameters with Collections

I am currently working on a app that has a fairly simple database model, and we are using ColdFusion 9 and Hibernate to map that model. The model is actually a local cache of a Sales Force CRM database. I have an Account, that can have a list of postal codes assigned to it. Since we have no need for any metadata other than the postal code, I am mapping using something like the following:

component output="false" entityname="Account" {
//...
  property name="ZipCodes" fieldtype="collection" type="array"
  table="sf_territory_zip" fkcolumn="sf_territory_id"
  elementcolumn="zip_code" elementtype="string";
//...
}

I am using the collection field type, which treats the zip codes as an array. Unfortunately, something in ColdFusion and HQL doesn't play nicely when you have to write a query to find an Account that has a certain Zip code. There are many examples out on the net on how to do this, and they usually look like:

From Account a where :zip in elements(a.ZipCodes)

In ColdFusion, you would write this as:

ORMExecuteQuery('From Account a where :zip in elements(a.ZipCodes)',{zip='12345'});

However, when doing this, at least on my instance of ColdFusion 9.01, NullPointerException's are thrown from deep inside of Hibernate. Hard coding the zipcode in the query (From Account a where '12345' in elements(a.ZipCodes)) works fine. Best I can tell from the stacktrace, Hibernate/ColdFusion does not know either the type of the named parameter, or the type of the column that it is trying to compare against. I also tried using array and non-named parameter syntax, that too did not work. Ultimately, I ended up using straight up Hibernate, bypasing the ORMExecuteQuery methodd.

local.session = ORMgetsession('mySession');
local.query = local.session.createQuery('From Account a where :zip in elements(a.ZipCodes)');
local.query.setString('zip',arguments.zipcode);
local.matches = local.query.list();

1 thought on “ColdFusion, HQL and Named Parameters with Collections”

  1. Dan,
    Thanks for this post.  I was having the exact same problem you were having and your code helped me get past it.  Seems a lot slower than the ORMExecuteQuery() code but for now it'll suffice.
    Crazy – no matter what I do, I cannot get the traditional HQL to work.

Leave a Reply to Ron WestCancel reply