Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2008 (Washington, D.C.) with:

Using ColdFusion Query Column's Java Methods To Find Values And Test Existence

By Ben Nadel on
Tags: ColdFusion

More fun Java exploration in ColdFusion! Have you ever tried to find the a value in a query column? Or just wanted to know that it existed at all (but not really need to know where it is)? If you use straight up ColdFusion to do this, there are two methods that pop into my mind: Query of queries and Query loop. I am here to tell you that it's easier than that. You can use the ColdFusion query-column object's underlying Java methods to find values and test for existence.

Before we get into that though, let's take a look at some old-school methods for finding a value. First, let's build a query to test:

  • <!--- Create a query of body parts. --->
  • <cfset qParts = QueryNew(
  • "id, name, turn_on",
  • "INTEGER, VARCHAR, VARCHAR"
  • ) />
  •  
  • <!--- Add rows to the query. --->
  • <cfset QueryAddRow( qParts, 7 ) />
  •  
  • <!--- Set row data. --->
  • <cfset qParts[ "id" ][ 1 ] = 1 />
  • <cfset qParts[ "name" ][ 1 ] = "Feet" />
  • <cfset qParts[ "turn_on" ][ 1 ] = "No" />
  •  
  • <cfset qParts[ "id" ][ 2 ] = 2 />
  • <cfset qParts[ "name" ][ 2 ] = "Calves" />
  • <cfset qParts[ "turn_on" ][ 2 ] = "Yes" />
  •  
  • <cfset qParts[ "id" ][ 3 ] = 3 />
  • <cfset qParts[ "name" ][ 3 ] = "Thighs" />
  • <cfset qParts[ "turn_on" ][ 3 ] = "Yes" />
  •  
  • <cfset qParts[ "id" ][ 4 ] = 4 />
  • <cfset qParts[ "name" ][ 4 ] = "Butt" />
  • <cfset qParts[ "turn_on" ][ 4 ] = "Very Much" />
  •  
  • <cfset qParts[ "id" ][ 5 ] = 5 />
  • <cfset qParts[ "name" ][ 5 ] = "Hands" />
  • <cfset qParts[ "turn_on" ][ 5 ] = "Yes" />
  •  
  • <cfset qParts[ "id" ][ 6 ] = 6 />
  • <cfset qParts[ "name" ][ 6 ] = "Smile" />
  • <cfset qParts[ "turn_on" ][ 6 ] = "Yes" />
  •  
  • <cfset qParts[ "id" ][ 7 ] = 7 />
  • <cfset qParts[ "name" ][ 7 ] = "Hair" />
  • <cfset qParts[ "turn_on" ][ 7 ] = "No" />

Now that we have a simple query, let's explore how you would test for the existence of a value. To start with, let's look at the query of queries solution:

  • <!--- Check to see if "Butt" is in the query. --->
  • <cfquery name="qTestExistence" dbtype="query">
  • SELECT
  • id
  • FROM
  • qParts
  • WHERE
  • name = <cfqueryparam value="Butt" cfsqltype="CF_SQL_VARCHAR" />
  • </cfquery>
  •  
  • <cfif qTestExistence.RecordCount>
  •  
  • <!--- Butt exists. --->
  • Yes... and it is good :)
  •  
  • <cfelse>
  •  
  • <!--- No butt exists. --->
  • No, sadly there was nothing :(
  •  
  • </cfif>

In this, we run a query against the query object and check each value against the given value. Then, we check to see if any records were returned. Since a query of query much check every value, our best and worst case scenario is N operations. Next, let's take a look at the query loop solution:

  • <!--- Check using loop. Start by setting flag value. --->
  • <cfset intIndex = 0 />
  •  
  • <!--- Loop over query. --->
  • <cfloop query="qParts">
  •  
  • <cfif NOT Compare( qParts.name, "Butt" )>
  •  
  • <!--- Set flag. --->
  • <cfset intIndex = qParts.CurrentRow />
  •  
  • <!--- Break out of loop. --->
  • <cfbreak />
  •  
  • </cfif>
  •  
  • </cfloop>
  •  
  • <!--- Check for flag. --->
  • <cfif intIndex>
  •  
  • <!--- Butt exists. --->
  • Yes at Index #intIndex#... and it is good :)
  •  
  • <cfelse>
  •  
  • <!--- No butt exists. --->
  • No, sadly there was nothing :(
  •  
  • </cfif>

In this case, we loop through each row and check the cell value against our given value. Once we find it, we can try to optimize by breaking out of the loop. Therefore, our best case scenario is 1 operation (if the first row is match) or N operations if there is no match.

Both of the above examples are good. They each have some pros and cons. The query of queries can be slow since the SQL has so much overhead and the number of operations is always large. It can however, return multiple IDs in one go. The query loop is good and can be a small number of operations. The issue I have with both of these methods is that they are wordy. Each has a lot of code associated with them.

Now, let's take a look at what this can look like if we leverage the ColdFusion query-column's underlying Java methods. In this case we are going to use the method IndexOf():

  • <!--- Check using query column Java methods. --->
  • <cfif (qParts[ "name" ].IndexOf( JavaCast( "string", "Butt" ) ) GTE 0)>
  •  
  • <!--- Butt exists. --->
  • Yes at Index #intIndex#... and it is good :)
  •  
  • <cfelse>
  •  
  • <!--- No butt exists. --->
  • No, sadly there was nothing :(
  •  
  • </cfif>

If you blinked, you may have missed it since that solution has sooo little code. What it basically does it check the given value against the cell values and returns the index of the first matching row. Now, as always, when using Java, TAKE CAUTION that it is indexed starting at ZERO, and will return -1 on no-match. That is why we explicitly check for GTE 0.

Now, I don't know what is going on under the surface exactly, but I can assume it works similar to the query loop. But, short code is one thing, speed is another. Let's take a look at how the three methods compare to each other. To test this, I ran 1,000 iterations of finding a random value in the query column. I checked two things: a positive match scenario and a no-match scenario:

  • <!--- Set the interation test count. --->
  • <cfset intIterations = 1000 />
  •  
  • <!--- Set the list of parts. --->
  • <cfset arrParts = ListToArray(
  • "Feet,Calves,Thighs,Hands,Smile,Hair"
  • ) />
  •  
  • <!---
  • Set flag for NO match. This value will determine
  • if we find a match or do not find a match.
  • --->
  • <cfset blnNoMatch = false />
  •  
  •  
  • <!--- Test the query of queries method. --->
  • <cftimer label="Query of Queries" type="outline">
  •  
  • <cfloop index="intI" from="1" to="#intIterations#" step="1">
  •  
  • <!--- Get target part. --->
  • <cfset strPart = arrParts[
  • RandRange( 1, ArrayLen( arrParts) )
  • ] />
  •  
  • <!--- See if we need to force a non-matching result. --->
  • <cfif blnNoMatch>
  • <cfset strPart = "{NULL}" />
  • </cfif>
  •  
  • <!--- Check to see if "Butt" is in the query. --->
  • <cfquery name="qTestExistence" dbtype="query">
  • SELECT
  • id
  • FROM
  • qParts
  • WHERE
  • name =
  • <cfqueryparam value="#strPart#" cfsqltype="CF_SQL_VARCHAR" />
  • </cfquery>
  •  
  • <cfif qTestExistence.RecordCount>
  •  
  • <!--- Butt exists. --->
  • Yes... and it is good :)
  •  
  • <cfelse>
  •  
  • <!--- No butt exists. --->
  • No, sadly there was nothing :(
  •  
  • </cfif>
  •  
  • </cfloop>
  •  
  • </cftimer>
  •  
  •  
  • <!--- Test the query loop method. --->
  • <cftimer label="Query Loop" type="outline">
  •  
  • <cfloop index="intI" from="1" to="#intIterations#" step="1">
  •  
  • <!--- Get target part. --->
  • <cfset strPart = arrParts[
  • RandRange( 1, ArrayLen( arrParts) )
  • ] />
  •  
  • <!--- See if we need to force a non-matching result. --->
  • <cfif blnNoMatch>
  • <cfset strPart = "{NULL}" />
  • </cfif>
  •  
  • <!--- Set default flag value. --->
  • <cfset intIndex = 0 />
  •  
  • <!--- Loop over query. --->
  • <cfloop query="qParts">
  •  
  • <cfif NOT Compare( qParts.name, strPart )>
  •  
  • <!--- Set flag. --->
  • <cfset intIndex = qParts.CurrentRow />
  •  
  • <!--- Break out of loop. --->
  • <cfbreak />
  •  
  • </cfif>
  •  
  • </cfloop>
  •  
  • <!--- Check for flag. --->
  • <cfif intIndex>
  •  
  • <!--- Butt exists. --->
  • Yes... and it is good :)
  •  
  • <cfelse>
  •  
  • <!--- No butt exists. --->
  • No, sadly there was nothing :(
  •  
  • </cfif>
  •  
  • </cfloop>
  •  
  • </cftimer>
  •  
  •  
  •  
  • <!--- Test the query column Java method. --->
  • <cftimer label="Query Column Java" type="outline">
  •  
  • <cfloop index="intI" from="1" to="#intIterations#" step="1">
  •  
  • <!--- Get target part. --->
  • <cfset strPart = arrParts[
  • RandRange( 1, ArrayLen( arrParts) )
  • ] />
  •  
  • <!--- See if we need to force a non-matching result. --->
  • <cfif blnNoMatch>
  • <cfset strPart = "{NULL}" />
  • </cfif>
  •  
  • <!--- Check to see if "Butt" is in the query. --->
  • <cfif (qParts[ "name" ].IndexOf( JavaCast( "string", strPart ) ) GTE 0)>
  •  
  • <!--- Butt exists. --->
  • Yes... and it is good :)
  •  
  • <cfelse>
  •  
  • <!--- No butt exists. --->
  • No, sadly there was nothing :(
  •  
  • </cfif>
  •  
  • </cfloop>
  •  
  • </cftimer>

As it turns out, the Java method is faster. And by a good deal at that. Here are the stats:

1,000 Iterations That Will Match

Query of Queries: 10,202ms - 11,187 ms
Query Loop: 62ms - 78ms
Query Column Java: 16ms - 32ms

1,000 Iterations That Will NOT Match (worst case scenario)

Query of Queries: 10,202 - 11,530ms
Query Loop: 94ms - 110ms
Query Column Java: 15ms - 31ms

As you can see, the query of queries method is horribly slow, but perhaps the most flexible. The Java method is just fast. Not only that, it performs consistently fast whether there is a matching or non-matching scenario.

That's some really cool stuff. And there's more to it (check out the CFDump below of the Java methods of the query column). The one thing that really needs to be understood though, is that we are matching the entire value, not a substring. That is the main disadvantage of any of the Java methods.

Now, as we have covered before, you can run Array methods on the query column. There are some weird caveats to that. But, you can also just convert the query column directly to an array so you never have to worry about it:

  • <!--- Array of values based on the column. --->
  • <cfset arrParts = qParts[ "name" ].ToArray() />

Now, you never have to worry about method notation since you know you have an array.

Here is the Java method CFDump:


 
 
 

 
CFDump - Java Methods on ColdFusion Query Object  
 
 
 


Reader Comments

Thanks for this info.
Unfortunately I can't seem to find an integer value even though I know it's present in the column.
eg:

myquery['mycolumnname'].indexOf(120) returns -1,when I know it's sitting at the java index of 9 (row 10 in cfm).
I tried different casting:
indexOf(javacast("string","0"))
indexOf(javacast("int","0"))
but to no avail

Any ideas where I'm going wrong?
TIA
Lossed

Reply to this Comment

Thanks for this info.
Unfortunately I can't seem to find an integer value even though I know it's present in the column.
eg:

myquery['mycolumnname'].indexOf(120) returns -1,when I know it's sitting at the java index of 9 (row 10 in cfm).
I tried different casting:
indexOf(javacast("string","120"))
indexOf(javacast("int","120"))
but to no avail

Any ideas where I'm going wrong?
TIA
Lossed

Reply to this Comment

Yes, it finds it, returns true.
A dump of the query shows it there too.
Here's a blow by blow account:
[code]
<!---build a quick query--->
<cfquery name="q" datasource="#request.dsn#" username="#request.dsu#" password="#request.dsp#">select intcount from workoutint limit 1</cfquery>
<!---it returns:
intcount
10
--->
<cfoutput>
#q['intcount'][1] EQ 10# <!---returns "yes"--->
#q['intcount'].indexOf(10)#<!---returns "-1" :(--->
#q['intcount'].indexOf(javacast('string','10'))#<!---returns "-1" :(--->
<!---try if explicitly converting to an array via toArray() has any effect--->
<cfset myArray=q['intcount'].toArray()>
#variables['myArray']contains(10)#<!---returns "NO" :(--->
<!---bang head against keyboard, try building query manually--->
<cfset qMyQuery=querynew('intcount')>
<cfset temp=queryaddrow(qMyQuery)>
<cfset temp=QuerySetCell(qMyQuery,'intcount',10,1)>
<cfdump var="#qMyQuery#">
<!---check indexOf() on manual query--->
#qMyQuery['intcount'].indexOf(10)#<!---returns "0" - so why here and not the original queryindexOF()?--->
</cfoutput>
[/code]

It's probably something simple but I am beyound seeing the wood or the trees anymore :/

Reply to this Comment

Sorry for the hassle. It turned out to be a casting issue.
I wound up going through every java type I could think of and found casting the integer as 'long' helped java find the index.
There seems a mismatch between data types of coldfusion and java, maybe the latter is stricter? Anyway, sorted now. Thanks for highlighting these underlying methods.

Reply to this Comment

That is very strange casting issue. You think the underlying Java would cast the value when it was doing the comparison. I figure the Equals() methods (I assume this is what is getting called behind the scenes) would know that an int could equal a long or vice versa. That is lame that you have to cast manually.

Reply to this Comment

Hi Ben. Good point. Using this Java method searches for case sensitive text inside query, ya? Do you know a method to search using Java to search like SQL LIKE?

Reply to this Comment

I must be missing something fundamental about using Java methods in Coldfusion. I've used a couple before, nicked from this blog -- like RemoveRows() and IndexOf() -- successfully.

But I want a subList and though it's listed, I can't get it to work like I think it should.

It's listed here as a query column method, right? So using it like (where qSearch is a query):

<cfset qSearch_ids = qSearch["personid"].subList(JavaCast("int", #StartRow#), JavaCast("int", #EndRow#))>

Seems like it should work just like your examples above...?

Using this and some slightly different versions doesn't work; it gives an anonymous error--without any error text aside from "Error Occurred While Processing Request" and
"java.lang.UnsupportedOperationException
at coldfusion.sql.QueryColumn.subList(QueryColumn.java:374)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)"

I've also tried it on qSearch["personid"].ToArray() ValueList(qSearch.personid) similarly to above and I get the error:

"Either there are no methods with the specified method name and argument types, or the subList method is overloaded with argument types that ColdFusion cannot decipher reliably. ColdFusion found 0 methods that matched the provided arguments. If this is a Java object and you verified that the method exists, you may need to use the javacast function to reduce ambiguity."

I've tried a few other permutations, including the examples provided below and I get the same error.

1. subList example in the comments on - http://www.bennadel.com/blog/241-Finding-Values-In-A-ColdFusion-Array-Using-Java-And-Other-Cool-Stuff.htm

2. The UDF here http://www.cflib.org/udf/arraySlice2

The ONLY way I can get this to work, after much irritation is:

<cfset i = 0>
<cfset qSearch_array = ArrayNew(1)>
<cfloop query="qSearch">
<cfset i = i + 1>
<cfset qSearch_array[i] = personid>
</cfloop>

<cfset qSearch_ids = qSearch_array.subList(JavaCast("int", #StartRow#), JavaCast("int", #EndRow#))>

And if I have to loop the query into an array, I may as well not bother using SubList, but rather only loop from StartRow to EndRow to build qSearch_ids. *sigh*

Reply to this Comment

As I do agree that the Java method is a very cool way to find the existence of a value, I would have to disagree with your first statement of "Since a query of query much check every value, our best and worst case scenario is N operations." While this statement is true, it's only because you've given it an unfair disadvantage that you've given to both the Java and Query Loop method.

Both the Query Loop and Java methods are testing for the existence of a value and when found are breaking there loops and returning the placement of the item, your Query RecordCount method is checking for ALL items that match "Butt", adding maxRows="1" to your query OR "LIMIT 1" within live queries would make the game "fair". In essence meaning the Query RecordCount method has become "Therefore, our best case scenario is 1 operation (if the first row is match) or N operations if there is no match.".

Just thought I'd add my two cents. Thanks again for sharing all the information you do.

Reply to this Comment

@Travis,

Good point with the LIMIT 1. I am not sure if MaxRows will have the same effect, though; I think that happens on the data transfer side, not on the database side. Not completely sure.

Reply to this Comment

Talking of LIMIT...
I've been using mySQL's LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to page result sets.
Wouldn't it be nice to be able to "poke" the found_rows() value back into the CF query metadata somehow so that as well as myQuery.recordcount we could have myQuery.totalRecords.
Anyone have any idea how that might be done?

Reply to this Comment

@Matt,

I'd be curious on that as well. Pagination is one of those tasks always seem to be more complicated than you'd think it *should* be.

Reply to this Comment

I really love the "query column Java method" ! This really works for me in a tiered cfif statement.
The problem I am having it that the "query column Java method" is case sensitive. is there a easy way to search the results of my query without being case sensitive?

<cfquery datasource="mysql" name="getmodel">
select DISTINCT model from inventory where 'make = 'Ford'
</cfquery>

<!--- The user USER_VAR case changes as well as case in column model results --->

<cfif (getmodel[ "model" ].Indexof( JavaCast( "string","#USER_VAR#" ) ) GTE 0)>
Yes we have Ford #USER_VAR#
<cfelse>
No We do not have Ford #USER_VAR#
</cfif>

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.