Step 3: Connect to the Database Using the Java Driver Manager

Now that we have the database driver loaded we can create a connection to the target database using a "java.sql.drivermanager" object. We know from the getDrivers() cfdump that the correct connection string for the MySQL5 JDBC connection is in the format
"http://[host]:[port]/[dbname]?user=[username]&password=[password]".

<!--- Create a driver manager instance --->
<cfset driverManager = createObject("java","java.sql.DriverManager")>

<!--- Create the connection to the DB --->
<cfset connection = driverManager.getConnection(
   "jdbc:mysql://localhost:3306/demodb?user=demouser&password=demopass")>

Step 4: Run a Query

Now that we have established a connection to the database we can run a test query. It is worth noting that as we bypassed coldfusions datasources we have also bypassed the <cfquery> tag, so to run a query we need to build a sql string and pass it to the java "ExecuteQuery" method. This of course means that <cfqueryparam> will not work either.

<!---Create a connection statement and run our query string --->
<cfset statement = connection.createStatement()>      
<cfset resultSet = statement.ExecuteQuery("SELECT * FROM testtable")>

Step 5: Convert to a Coldfusion Query Object

In addition to not being able to use <cfquery> and <cfqueryparam>, we must also convert the result set returned from the ExecuteQuery() method into a Coldfusion query object.

<!---Convert the resultset into a coldfusion query object --->
<cfset queryObj = createObject("java", "coldfusion.sql.QueryTable").init(resultSet)>