Create & Connect to databases (mysql, SQLite etc) on the fly with ColdFusion

Working with databases in ColdFusion is very easy - you have to set up your datasources in the CFAdmin (or by code using the CFAdmin API) and you can start writing SQL using CFQUERY. But what if you want to connect to a database on the fly without creating a datasource?
In this case it comes in handy that ColdFusion is a Java-based product, so you can use all the power JDBC is offering. At the online music hub tunesBag I recently had to create SQLite databases on the fly - it's quite easy in fact as you can see below:

1) Add the JDBC Driver (or use an existing one)
You can use any JDBC driver, e.g. mysql, MS-SQL etc
In my case: Download the SQLite JDBC Driver from zentus.com and place it in wwwroot/WEB-INF/lib (sqlitejdbc-v053.jar)

a_sqlite = createObject( 'java', 'org.sqlite.JDBC' )
a_prop = createObject( 'java', 'java.util.Properties' )
db_filename = '/tmp/sqlite_' & CreateUUID() & '.db'

2) Write the code (this is an example for sqlite)

a_conn = a_sqlite.connect( 'jdbc:sqlite:' & a_db_filename, a_prop.init() )
a_statement = a_conn.createStatement()
a_res = a_statement.execute( 'BEGIN;')

Create the table
a_res = a_statement.execute( 'CREATE TABLE test (id INTEGER PRIMARY KEY, firstname TEXT, surname TEXT);');

Insert data
prep = a_conn.prepareStatement( 'INSERT INTO test (firstname,surname) VALUES (?, ?);' );

prep.setString( 1, 'John' );
prep.setString( 2, 'Doe' );

// you can add multiple inserts ...
prep.addBatch();

prep.setString( 1, 'Max' );
prep.setString( 2, 'Mustermann' );
prep.addBatch();

// insert!
prep.executeBatch();

// save the changes
a_res = a_statement.executeUpdate( 'END;');

// quit
a_conn.close();
a_conn = 0;

Where to go from here
For more details, check out the official JDBC documentation provided by SUN!