Using Java To Rename Columns In A ColdFusion Query Object

One of the things that I really love about ColdFusion is how easy it is to work with query objects. The ability to run a query on an existing query object is one of those great features that sets CF apart from other languages.

Query of Query Incurs Overhead

One possible use of the "Query of Query" feature is to rename a query object column when it is not possible to do this in native SQL. (While rare this does occur on occasions such as when using MySQL's 'SHOW' command which does not allow column renaming.) As simple as "Query of Query" makes accomplishing this task, it does incur additional overhead and really is overkill for what should be a simple procedure.

Enter Native Java

Fortunately this brings us to the other thing I love about ColdFusion. Since ColdFusion runs on Java and allows us to use Java code directly in our cfml, we can leverage the power of Java to rename the columns in our query object. This means we do not have to incur the performance hit that comes with starting up the "Query of Query" engine to achieve our goals.

The Java Code

To rename a given column in a query object we utilize two java functions.

• "getColumnNames()" returns an array containing all the existing column names for a given query object.
• "setColumnNames()" sets the column names in the query object using an array of new column names

Below is a simple function I wrote to find and rename a single column in a query object. I have only tested on Adobe CF9 but this code should be compatible with all versions of CF which run on Java.

<cffunction name="renameColumn" access="public" output="false" returntype="query" hint="Uses java to rename a given query object column">
      <cfargument name="queryObj" required="true" type="query">
      <cfargument name="oldColName" required="true" type="string">
      <cfargument name="newColName" required="true" type="string">
      
      <!--- Get an array of the current column names --->
      <cfset var colNameArray = queryObj.getColumnNames()>
      <cfset var i = 0>
      
      <!--- Loop through the name array and try match the current column name with the target col name--->
      <cfif arrayLen(colNameArray)>
            <cfloop from="1" to="#arrayLen(colNameArray)#" index="i">
                  <!--- If we find the target col name change to the new name --->
                  <cfif compareNoCase(colNameArray[i],arguments.oldColName) EQ 0>
                        <cfset colNameArray[i] = arguments.newColName>
                  </cfif>
            </cfloop>
      </cfif>
      
      <!--- Update the column names with the updated name array --->
      <cfset queryObj.setColumnNames(colNameArray)>
      
      <cfreturn queryObj />
</cffunction>

So there you have it a simple, fast way to update the column names of a query object without having to use the "Query of Query" engine.