In this article, I will introduce key ORM concepts and show you how to create and use Java Hashmaps using HQL, ColdFusion, and Lucee.

Table of Contents

What is HQL?

HQL is an abbreviation of Hibernate Query Language. This platform-independent language is inspired by the Structured Query Language (SQL). It allows you to query the properties of Java Object Relational Mapping (ORM) entities. These queries do not work against the database directly; however, they are translated into conventional SQL queries before retrieving data from a database.

HQL is object-oriented and supports inheritance and polymorphism. It is also quite performant and uses extensive caching features and lazy loading to retrieve the necessary data. Using ORM and Hibernate provides database vendor independence and is nearly as fast as querying the database directly using SQL.

I have never encountered a situation where I could not use ORM and HQL to replicate functionality that I would typically perform using SQL. However, ORM adds overhead and a layer of complexity, and traditional SQL is often easier to use. SQL may be more appropriate for applications that do not require database vendor independence or sophisticated reporting.

While ORM and HQL allow the developer to write traditional SQL, this is not recommended as the queries would be database vendor-specific and lose portability. Using both HQL and conventional SQL adds unnecessary complexity unless it is unavoidable. 

ColdFusion/Lucee Prerequisites

ColdFusion and Lucee support ORM, which may be enabled using the ColdFusion or Lucee package manager. With Lucee, I use the Ortus ORM extension, which provides nearly identical functionality to ColdFusion ORM. Unfortunately, Adobe has introduced unexpected changes and several ORM-related bugs in the latest ColdFusion 2023 release. However, I have used the Ortus extension successfully for nearly a year, and it works flawlessly.

Once ORM is installed, you must configure your application and database to support it. If you're new to ORM, I strongly recommend purchasing the ColdFusion ORM book by John Whish. It covers some of the material in this article and is an essential ColdFusion ORM resource.

ColdFusion/Lucee Hibernate Support

Once ORM is configured, both ColdFusion and Lucee support HQL using ORMExecuteQuery and cfquery. Both methods offer the same functionality; however, the cfquery method provides several advantages and is preferred. 

Using the Cfquery Tag

In this article, we will use the cfquery tag to query the database. Even if you advocate using cfscript, the cfquery tag offers several advantages. First, you will not have to concatenate SQL statements manually and is much cleaner.  We can also use the cfqueryparam tags, which we will cover later in this article. Finally, we can output our statements for debugging and analyze performance metrics, which we can't do when using cfscript and ORMExecuteQuery. 

Using the cfquery tag is nearly identical to using SQL. The only difference is that we specify the dbtype of ORM and omit the datasource.

<cfquery name="Data" dbtype="hql"> 
	FROM Post 
	WHERE PostId = <cfqueryparam value="1"> 
	ORDER BY DatePosted 
</cfquery>

Case Sensitivity

Even when using case-insensitive languages like Lucee or ColdFusion, all column and table name properties are case-sensitive with HQL. However, HQL keywords, such as FROM, AS, JOIN, etc., are not case-sensitive. 

Aliasing Tables, Columns, and Joins

If you need to reference other tables or columns, you must alias the column and table names. You may also use aliases to change the case of table and column properties.

To alias a column or table, you may use an optional AS keyword to make the query more readable. You can omit the AS keyword and specify the alias after the entity. Both of these queries will produce the same result:

SELECT FirstName as FirstName FROM Users as Users

SELECT FirstName FirstName FROM Users Users

HQL supports inner and outer joins. Inner joins can be written simply with a join keyword; however, outer joins must use the outer keyword. All joins may be aliased and follow the same rules when aliasing column or table entities.

The HQL FROM Keyword

The simplest form of a query is something like 'FROM Post,' which will retrieve the Post entity along with the other associated entities. For example, 'FROM Post' will retrieve the Post entity and all related entities, such as the Comment and RelatedPost entities. If you intend to display records, this is an inefficient query, as it returns a collection of entity objects instead of individual properties. It is similar to loading an entity using the loadEntity statement in ColdFusion or Lucee. 

Selecting Entity Properties

Instead of selecting objects using the FROM keyword, you can select individual properties using the SELECT keyword. Hibernate calls this dynamic instantiation and wraps the results in a type-safe object rather than returning entity-based objects. ColdFusion and Lucee takes this Java wrapper object and returns an array of arrays. This is similar to a SELECT statement using SQL.

Wrapping the Results in a HashMap

Use the map keyword in a query in ColdFusion or Lucee to return the result as a Java HashMap. The map keyword is not case-sensitive. Typically, Java HQL will return a Java list of maps; however, ColdFusion and Lucee seem to transform these objects into a Java HashMap automatically.

The benefit of returning the results as a Java HashMap is that these objects are an incredibly efficient way of storing large amounts of data. No matter how large the HashMap resultset is, accessing or modifying the underlying values takes place at a near-constant time. ColdFusion/Lucee will treat this hashmap as an array of structures, and some of the original HashMap efficiency is lost, however, as we need to loop through the ColdFusion array object to output or modify the underlying values. 

Another benefit is that it is similar to the approach ColdFusion or Lucee developers use with cfquery. To use a Java HashMap, we will use the map keyword, surround the column properties with parentheses, and alias all of the columns.

<cfquery name="Data" dbtype="hql">
	SELECT
	new Map (
		PostId as PostId,
		Title as Title,
		DatePosted as DatePosted
	)
	FROM Post
</cfquery>

HQL Keywords

Many of the keywords used in SQL are available to HQL. For example, you can use the DISTINCT keyword like so:

SELECT DISTINCT
	new Map (DatePosted as DatePosted)
FROM Post

The following aggregates are also supported: avg, count, max, min, and sum. You can also mix the distinct keyword with the aggregates:

SELECT DISTINCT
	new Map (count(distinct PostId as NumPosts))
FROM Post

HQL Associations and Joins

Using proper joins is one of the most challenging aspects of ORM. Since ORM is object-oriented, many of the joins are automatically created when you create the ORM model, and specifying additional joins explicitly in your code is inefficient.

ORM creates JOINS differently depending upon the mapping association. A join is not typically needed in your query when using a one-to-one relationship. However, in a one-to-many relationship, ORM creates a lazy-loaded join, and the child data is not typically fetched. You may need to create an explicit join if you want to extract the data on child objects. 

When running into problems creating HQL queries, using cfdump is your friend. When writing HQL joins, I dump the object using the entityLoad method for inspection. The dump object helps identify how to traverse the ORM object and find the join property. You may have to use trial and error to solve thorny JOIN issues. 

That said, be careful when using cfdump with Lucee! The cfdump will consume a lot of memory when using Lucee and ORM, and I had to restart the web server. I have not noticed this when using ColdFusion's cfdump and ORM.

WHERE Clause

Using the WHERE clause in HQL is nearly identical to the WHERE clause in SQL. The vast majority of the WHERE operators and expressions in SQL are available. The only exception is that HQL may not support some database vendor operators, such as the != operator in MariaDB. However, HQL supports the <>, which does the same thing. 

Cfqueryparam and Named Parameters

When using the cfquery tag, we can use cfqueryparam tags that query the database using HQL-named parameters. The cfqueryparam tag, along with HQL-named parameters, prevents SQL injection and uses database-specific bind variables, which improves query performance.

However, one thing that drives me nuts when using cfqueryparam is that ColdFusion throws an error when using a cfqueryparam against the primary key of a foreign table. To get around this I have to specify the where clause value directly and omit the cfqueryparam. I am unsure if this is a ColdFusion bug, but cfqueryparam can't use a primitive value to load a foreign entity. If you omit using the cfqueryparam tag, you should validate the value in the WHERE clause to avoid potential injection attacks. 

<!--- Note: the following query does not work with cfqueryparam. I get an invalid integer message no matter what I do (and this is typical of some ORM queries). It is not a security issue here as I am checking to see if it is a siple numeric value --->			
<cfquery name="Data" dbtype="hql" ormoptions="#{maxresults=1}#">		
	SELECT new Map (
		...
	)
	FROM 
		AnonymousUser as tblAnonymousUser
	WHERE 0=0
	-- The IPAddressId is the primary key of the foreign table.
	<cfif len(ipAddressId) and isNumeric(ipAddressId)>
		AND IpAddressRef = #ipAddressId#
	</cfif>
</cfquery>

ORDER BY, GROUP and Subqueries

Like SQL, HQL supports the ORDER BY, GROUP, and subqueries. The HAVING keyword and correlated subqueries are allowed. However, mathematical expressions may not be used in the ORDER BY statement. That said, depending upon the database vendor, you may be able to use the expression value in a column alias and use it in the ORDER BY statement.

Limiting the Number of Records and Query Pagination

In either ColdFusin or Lucee, you can not limit the results using database vendor-specific keywords such as TOP or LIMIT. Instead, you may limit and offset the records using the ormoptions maxresults and offset arguments in the cfquery tag. 

The maxresults argument specifies the number of records the query returns, and the offset will skip the specified number of records. I use the following logic to paginate through posts in Galaxie Blog:

<cfquery name="Data" dbtype="hql" ormoptions="#{maxresults=numRows, offset=offset}#">
	SELECT new Map (
		Post.PostId as PostId,
		...
	)
	FROM Post as Post 
</cfquery>

Outputting Records in the HQL Query

Like looping through a cfquery object, Java Hashmaps created using HQL can be outputted to the page by looping through an array of structs. The following code uses a for loop to loop through the records in the getPosts HQL query object and output the PostId and Title. The key objects are case-sensitive, so make sure they match the case used in the persistent CFCs and columns in the database.

Important note: Before ColdFusion 2021, you used to be able to output the data without surrounding the key with square brackets and quotes using familiar structure syntax like getPost[i].PostId. However, since CF2021, this no longer works and has caused an error. I annotated a bug with Adobe since this syntax is mentioned in their documentation, but it was promptly deleted, and I never heard back from them. Instead, bracket the key like so: queryName[recordNumber]['key'] like the example below.

<cfloop from="1" to="#arrayLen(getPost)#" index="i">
  <cfoutput>postId: #getPost[i]['PostId']# title:#getPost[i]['Title']#</cfoutput>
</cfloop>

Dealing with Null Values 

ColdFusion and Lucee will raise an error If one of the columns contains a null value. To overcome this, loop through the HQL object and inspect each column value using the structKeyExists function. If the key does not exist, set the value of the column to an empty string like so: 

<cfquery name="Data" dbtype="hql">
	SELECT new Map (
		User.Biography as Biography
	)
	FROM 
		Users as User
</cfquery>

<!--- Sanitize the data by replacing null values with an empty sting --->
<cfif arrayLen(Data)>
	<!--- Loop through the data again to set the vars if they are null. Note: this should only have one row --->		
	<cfloop from="1" to="#arrayLen(Data)#" index="i">

		<!--- Set the values in the structure. --->
		<cfset userRow = Data[i]>

		<!--- These variables are optional and may not be present. --->
		<cfif not structKeyExists(userRow, "Biography")>
			<cfset Data[i]["Biography"] = "">	
		</cfif>
	</cfloop>
</cfif><!---<cfif arrayLen(Data)>--->

Final Thoughts

If you want to see ORM in action, even if you don't intend to use a blog, consider downloading and installing Galaxie Blog. ORM drives Galaxie Blog, which is free and open source. This project is one of the largest open-source code bases in the ColdFusion community, with around 50 database tables. The design is also understandable since it does not involve overly sophisticated data concepts.