Using queries

ColdFusion lets you use HQL (Hibernate Query Language) to run queries directly on the database. If you are familiar with HQL, you can use it for running complex queries.

In general, use HQL in the following scenarios:

  • The query is not specific to a particular object but only to some fields in the object.

  • To retrieve some fields of the object without loading the object.

  • When you use table joins.

  • When you use aggregate functions like min, max, avg, and count.

  • To retrieve entities by specifying a filter that needs to use operators other than AND.

For more information on HQL, see

www.hibernate.org/hib_docs/reference/en/html/queryhql.html

Adobe recommends

Have a tutorial you would like to share?

Using ColdFusion ORM and HQL

Brian Kotek
In this five-part series, Brian Kotek explains how to use ColdFusion and HQL in real-world application development.

The HQL methods return a single or multi-dimensional array of values or entities, based on what the HQL query returns.

If you are sure that only one record exists that matches this filter criteria, specify unique=true so that a single entity is returned instead of an array. You can use unique=true to suppress the duplicate records from the query result.

Note: entityname and properties used in HQL are case sensitive.

The following HQL methods are available:

ORMExecuteQuery(hql, [params] [,unique])

ORMExecuteQuery(hql, [,unique] [, queryoptions])

ORMExecuteQuery(hql, params [,unique] [,queryOptions])

ORMExecuteQuery (hql, params, boolean unique, Map queryOptions)

ORMExecuteQuery(hql, [,unique] [, queryoptions])

Runs the HQL on the default data source specified for the application. You can specify several options to control the behavior of retrieval using queryoptions:

  • maxResults: Specifies the maximum number of objects to be retrieved.

  • offset: Specifies the start index of the resultset from where it has to start the retrieval.

  • cacheable: Whether the result of this query is to be cached in the secondary cache. Default is false.

  • cachename: Name of the cache in secondary cache.

  • timeout: Specifies the timeout value (in seconds) for the query

Maxresults and timeout are used for pagination.

Note: If the query returns an object or an array of objects, the init method of the persistent CFC is called (if available).

Examples

To retrieve an array of artwork objects from the ART table:

<cfset art = ORMExecuteQuery("from ART")>

To retrieve an array of artwork objects that have a price greater than 400 dollars:

<cfset art = ORMExecuteQuery("from ART where price > 400")>

To retrieve an array of artwork objects that have a priceid 100:

<cfset artObj = ORMExecuteQuery("from ART where priceid = 100>

To retrieve an array of objects that contain the first name of artists:

<cfset firstNameArray = ORMExecuteQuery("select FirstName from Artist")>

To retrieve the number of artwork objects:

<cfset numberOfArts = ORMExecuteQuery("select count(*) from Art")>

To retrieve an array of objects that have an artistid 1:

<cfset firstName = ORMExecuteQuery("select FirstName from Artist where ARTISTID = 1", true)>

To retrieve an array of ten artist objects starting from the fifth row in the query result:

<cfset artists = ORMExecuteQuery("from Artist", false, {offset=5, maxresults=10, timeout=5})>

ORMExecuteQuery(hql, params [,unique] [,queryOptions])

This type of ORMExecuteQuery lets you pass unnamed parameters to the query. Use '?' (question mark) as the place-holder for the parameters. The values to the parameters should be passed as an array to params.

Examples: unnamed parameters

To retrieve an array of artist objects with artistid equal to 40:

<cfset artists = ORMExecuteQuery("from ARTIST where artistid > ?", [40])>

To retrieve an array of artwork objects with a priceid equal to 1:

<cfset artObj = ORMExecuteQuery("from ART where priceid=?", [1], true)>

To retrieve an array of objects with a price id equal to 40, and price lesser than 80 dollars:

<cfset artists = ORMExecuteQuery("from ART where priceid > ? and price < ?", [40, 80])>
Note: In case of more than one parameter, values are picked up based on the parameter sequence, for example, the first parameter will be replaced by first value and second parameter will be replaced by second value.

Examples: named parameters

This type of ORMExecuteQuery lets you pass named parameters to the query. The placeholder for the parameter should be a name and should start with ":" as in ":age" or ":id". The values to the names should be passed as key-value pairs.

For example, to retrieve artist details of all artists whose reside in USA and are also citizens of USA, your code should look like this:

<cfset USArtists = ORMExecuteQuery("from ARTIST where country=:country and citizenship=:country", {country='USA'})> 
<cfset orderDetail = ORMExecuteQuery("from Orders where OrderID=:orderid and ProductID=:productid", {orderid=1, productid=901}, true)>
Note: Parameters are not case-sensitive.

Examples: group by

This type of ORMExecuteQuery lets you retrieve aggregate or grouped values for the query.

For example, to retrieve the first name and last name along with the status of the artwork being sold or not, you can write a query similar to the following:

<cfset artist = ORMExecuteQuery( 
            "SELECT art.Artist.Firstname, art.Artist.Lastname, SUM(art.Price) as Sold FROM  Art as art WHERE art.IsSold=1 GROUP BY art.Artist.Firstname, art.Artist.Lastname")> 
    <cfloop array="#artist#" index="artistItem"> 
    <cfoutput> 
    #artistItem[1]# #artistItem[2]# #artistItem[3]#<br> 
    </cfoutput> 
</cfloop>
Note: Built-in functions to obtain the data such as getFirstName() or getLastName() cannot be used if you are using select queries with specific column names. The result will be returned as an array object and values can be retrieved using array index.

Example: order by

This type of ORMExecuteQuery lets you retrieve sorted data from a data source using the order by clause. For example, to sort the data from the Artist table by firstname, use the following code:

<cfset artist = ORMExecuteQuery('FROM Artist ORDER BY firstname ASC', false, {maxresults=5} )> 
<cfloop array="#artist#" index="artistObj"> 
    <cfoutput>Name = #artistObj.getFirstName()# 
#artistObj.getLastName()#<br></cfoutput> 
<br> 
</cfloop>

Example: aggregate functions

This type of ORMExecuteQuery lets you retrieve data when using aggregate functions such as sum, count, avg. 

<cfset artist = ORMExecuteQuery( 
            "SELECT COUNT(*) FROM Art as art WHERE art.Artist.ArtistID=:ArtistID AND art.IsSold=:Sold", { ArtistID=1, Sold=True }, True )> 
<cfoutput> 
    #artist# 
</cfoutput><br>

Example: expressions

This type of ORMExecuteQuery lets you retrieve data using expressions such as mathematical operators, logical operators, binary comparisons, and many others.

For example, the following code is used to retrieve the price of an artwork, which is greater than or equal to 10000 along with the name and description of the artwork.

<cfset artArr = ORMExecuteQuery("from Art where price>=10000")> 
<cfloop array="#artArr#" index="artObj"> 
    <cfoutput> 
        Art Name = #artObj.getArtName()#<br> 
        Description = #artObj.getDescription()#<br> 
        Price = #artObj.getPrice()#<br> 
    </cfoutput> 
<br> 
</cfloop>