Datasource-less Queries in ColdFusion Using JDBC

Is it possible to execute a query in ColdFusion without defining a datasource in ColdFusion administrator? Yes! The following is one method, I'm sure there are others, for executing a ColdFusion query without explicitly defining a ColdFusion datasource.

What We'll Need

  • ColdFusion Engine
  • Database Driver JAR

I will assume you already have a ColdFusion engine installed and running. I tested this code on Adobe CF 9.

For this example, I used the MySQL Connector/J driver to connect to a local MySQL database. Place the JAR file in your ColdFusion classpath.

Steps

Before we begin, a note of caution. Java is case sensitive, so when instantiating an object using a Java class, case matters!

Create a Datasource

The first step to creating a manual connection to the database is to create a datasource using the com.mysql.jdbc.jdbc2.optional.MysqlDataSource class, which is part of the MySQL Connector/J library.

view plain print about
1<cfset datasource = createObject( "java", "com.mysql.jdbc.jdbc2.optional.MysqlDataSource" )>

Create a Connection

Using the datasource, create a connection object, passing in the identity of user to connect as. The datasource class has a method, getConnection(), that makes creating a connection simple. Use the setter methods on the datasource to provide the login details for the user credentials and the name and port of the server to connect to.

Please, please, please do not use root! Create and use a user account with only the permissions it needs.

view plain print about
1<cfset datasource.setUser( "cfuser" )>
2<cfset datasource.setPassword( "12345" )>
3<cfset datasource.setServerName( "localhost" )>
4<cfset datasource.setPortNumber( 3306 )>
5
6<cfset connection = datasource.getConnection()>

Execute the Query

Now that we have a connection to the database, we can use JDBC to execute queries. First, we'll need to create a JDBC statement, which is a class that defines the query to execute. The connection object has a method that once again makes it easy:

view plain print about
1<cfset statement = connection.createStatement()>

With the statement object in hand, we can now write out the query to execute. The executeQuery() method on the statement class takes a string with the query definition. This method returns a JDBC resultset object.

view plain print about
1<cfset resultset = statement.executeQuery( "SELECT table_name FROM information_schema.tables ORDER BY table_name" )>

Convert to a ColdFusion Query

At this point, the query is represented in a JDBC resultset. Although we could use this object, a ColdFusion query object is much more useful and feature-rich. Converting the resultset to a query object requires using one of the internal classes in ColdFusion, coldfusion.sql.QueryTable, passing the resultset into the constructor:

view plain print about
1<cfset query = createObject( "java", "coldfusion.sql.QueryTable" ).init( resultset )>

Clean Up

The JDBC connection needs to be cleaned up when it is opened. Keeping it open may result in oddness and other memory-related issues.

view plain print about
1<cfset connection.close()>

Using a ColdFusion Datasource in Java

From time to time I have to switch out of ColdFusion into Java to get a task accomplished. The latest was writing a password callback class for a web service integration project using ColdFusion, aka the Axis web service engine, and WSS4J.

[More]