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

Posted September 11, 2006 at 5:11 PM by Ben Nadel

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:

<cfset qParts = QueryNew(
"id, name, turn_on",
"INTEGER, VARCHAR, VARCHAR"
) />
<cfset QueryAddRow( qParts, 7 ) />
<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:

<cfquery name="qTestExistence" dbtype="query">
SELECT
id
FROM
qParts
WHERE
name = <cfqueryparam value="Butt" cfsqltype="CF_SQL_VARCHAR" />
</cfquery>
<cfif qTestExistence.RecordCount>
Yes... and it is good :)
<cfelse>
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:

<cfset intIndex = 0 />
<cfloop query="qParts">
<cfif NOT Compare( qParts.name, "Butt" )>
<cfset intIndex = qParts.CurrentRow />
<cfbreak />
</cfif>
</cfloop>
<cfif intIndex>
Yes at Index #intIndex#... and it is good :)
<cfelse>
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():

<cfif (qParts[ "name" ].IndexOf( JavaCast( "string", "Butt" ) ) GTE 0)>
Yes at Index #intIndex#... and it is good :)
<cfelse>
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:

<cfset intIterations = 1000 />
<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 />
<cftimer label="Query of Queries" type="outline">
<cfloop index="intI" from="1" to="#intIterations#" step="1">
<cfset strPart = arrParts[
RandRange( 1, ArrayLen( arrParts) )
] />
<cfif blnNoMatch>
<cfset strPart = "{NULL}" />
</cfif>
<cfquery name="qTestExistence" dbtype="query">
SELECT
id
FROM
qParts
WHERE
name =
<cfqueryparam value="#strPart#" cfsqltype="CF_SQL_VARCHAR" />
</cfquery>
<cfif qTestExistence.RecordCount>
Yes... and it is good :)
<cfelse>
No, sadly there was nothing :(
</cfif>
</cfloop>
</cftimer>
<cftimer label="Query Loop" type="outline">
<cfloop index="intI" from="1" to="#intIterations#" step="1">
<cfset strPart = arrParts[
RandRange( 1, ArrayLen( arrParts) )
] />
<cfif blnNoMatch>
<cfset strPart = "{NULL}" />
</cfif>
<cfset intIndex = 0 />
<cfloop query="qParts">
<cfif NOT Compare( qParts.name, strPart )>
<cfset intIndex = qParts.CurrentRow />
<cfbreak />
</cfif>
</cfloop>
<cfif intIndex>
Yes... and it is good :)
<cfelse>
No, sadly there was nothing :(
</cfif>
</cfloop>
</cftimer>
<cftimer label="Query Column Java" type="outline">
<cfloop index="intI" from="1" to="#intIterations#" step="1">
<cfset strPart = arrParts[
RandRange( 1, ArrayLen( arrParts) )
] />
<cfif blnNoMatch>
<cfset strPart = "{NULL}" />
</cfif>
<cfif (qParts[ "name" ].IndexOf( JavaCast( "string", strPart ) ) GTE 0)>
Yes... and it is good :)
<cfelse>
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:

<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:

Reader Comments

Lossed

Mar 25, 2007 at 10:53 PM // reply »

2 Comments

1 Points

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

Lossed

Mar 25, 2007 at 10:54 PM // reply »

2 Comments

1 Points

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

Ben Nadel

Mar 26, 2007 at 7:17 AM // reply »

11,238 Comments

75,337 Points

When you do:

#(myquery['mycolumnname'][10] EQ 120)#

does it output true?

lossed

Mar 27, 2007 at 4:11 AM // reply »

1 Comments

1 Points

Yes, it finds it, returns true.
A dump of the query shows it there too.
Here's a blow by blow account:
[code]

<cfquery name="q" datasource="#request.dsn#" username="#request.dsu#" password="#request.dsp#">select intcount from workoutint limit 1</cfquery>

<cfoutput>
#q['intcount'][1] EQ 10# 
#q['intcount'].indexOf(10)#
#q['intcount'].indexOf(javacast('string','10'))#

<cfset myArray=q['intcount'].toArray()>
#variables['myArray']contains(10)#

<cfset qMyQuery=querynew('intcount')>
<cfset temp=queryaddrow(qMyQuery)>
<cfset temp=QuerySetCell(qMyQuery,'intcount',10,1)>
<cfdump var="#qMyQuery#">

#qMyQuery['intcount'].indexOf(10)#
</cfoutput>
[/code]

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

lossed

Mar 27, 2007 at 7:45 PM // reply »

1 Comments

1 Points

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.

Ben Nadel

Mar 29, 2007 at 8:59 AM // reply »

11,238 Comments

75,337 Points

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.

Marco Antonio C Santos

May 21, 2007 at 3:25 PM // reply »

15 Comments

58 Points

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?

Ben Nadel

May 21, 2007 at 4:42 PM // reply »

11,238 Comments

75,337 Points

@Marco,

To use the LIKE operator, you could just perform a ColdFusion query of queries.

bisrat gebreegziabher

Jun 10, 2009 at 10:31 AM // reply »

1 Comments

10 Points

I requested you to dissplay the java program that shows [4][10] 2d array using the given information.

absolethe

Jun 24, 2009 at 1:20 PM // reply »

1 Comments

10 Points

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):

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:

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*

Travis Woolworth

Jun 8, 2010 at 3:09 PM // reply »

2 Comments

23 Points

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.

Ben Nadel

Jun 9, 2010 at 9:56 AM // reply »

11,238 Comments

75,337 Points

@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.

Matt Casey

Nov 2, 2010 at 1:34 PM // reply »

2 Comments

20 Points

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?

Ben Nadel

Nov 3, 2010 at 10:37 AM // reply »

11,238 Comments

75,337 Points

@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.

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong> <em>italic</em> <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment

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

1,000 Iterations That Will Match

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

What Other People Are Searching For

Reader Comments

Post A Comment