Ask Ben: Processing A Query In Java

How do you send and process a coldfusion query to a java object created by cfobject (not through cfx)?

I have to admit, as my Car Talk friends would say, this one "Stumped the Chump". I am not a Java expert. From what I have read though, underlying the ColdFusion query object is the Java class:

coldfusion.sql.QueryTable

This implements the Java interfaces for:

javax.sql.RowSet and coldfusion.wddx.RecordSet

When you send ColdFusion objects to Java, ColdFusion generally does a type-cast automatically to the appropriate Java type. I am not sure how to test this here, but you might be able to pass the Query object directly to Java. If not, you can explicitly convert the Query object to the Java QueryTable object:

<cfset jQueryTable = CreateObject(
	"java",
	"coldfusion.sql.QueryTable"
	).Init( qColdFusionQuery )
	/>

... or as CFObject ...

<cfobject
	type="java"
	action="create"
	name="jQueryTable"
	class="coldfusion.sql.QueryTable"
	/>

<!--- Initialize the object. --->
<cfset jQueryTable.Init( qColdFusionQuery ) />

This creates an instance of the Java QueryTable and passes in your ColdFusion query (qColdFusionQuery) as a the constructor argument. Then, once you get this object into your Java arena, you can use the built in methods to manipulate it. I got this list off of the page http://www.activsoftware.com/mx/undocumentation/query.cfm originally posted on Pete Freitag's site:

queryName.first()
queryName.last()
queryName.isFirst()
queryName.isLast()
queryName.next()
queryName.previous()
queryName.findColumn(String name)
sort(int columnID, boolean ascending)
getColumnTypeName(int columnID)
... many more functions here ...

As far as passing this into a function or returning it from a function, you just have to make sure that object type is defined as "coldfusion.sql.QueryTable".

As someone commented on Pete's site (linked above), to get back to a regular ColdFusion query object, you can do:

<cfset qActualColdFusionQuery = jQueryTable.FirstTable() />

When I first started looking into this question, I went about it with the idea of building up a ResultSet from scratch. I couldn't find any information on that at all. Then I thought maybe casting XML to ResultSet somehow? No go on that either. But since the QueryTable class implements the interfaces for RowSet, you should be able to do just about everything you want to do.

NOTE: I learned about this FOR the answer and it may contain misinformation.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/go/151