JavaScript and SQL using Rhino and SQLite

Jul 3, 2009 by Peter Newhook    2 Comments    Posted under: Geek

My previous posts have revolved around running JavaScript in environments outside of the browser. After the disappointment of Microsoft dropping plans for Managed JScript, I’ve taken some time to work with Mozilla Rhino.

As an aside, it’s been quite nice to work with a strictly open source product. My company is a heavy user of Microsoft products so I’m very much exposed to the .Net ecosystem. I think it’s very strong, but I do yearn for a little more open community.

Recently, I’ve become interested with SQLite, and I thought it would be a great exercise to use SQLite from Rhino. I could find all kinds of articles that cover similar topics, but none that covered exactly this, so I get to feel like I’m getting first tracks, even if down a smaller slope.

SQLite is kept small and nimble by only providing an API to C/C++ and Tcl. So you’ll need a way to bind Java to SQLite. I used SQLite JDBC That project appears to be under relatively active development, works on all platforms, and is available as a compiled jar. The jar contains all the code you need so you don’t even have to install SQLite separately.

Next start the Rhino shell including the SQLite jar on your classpath. I’m running Windows and use a batch file to do this, do my batch file looks like this:

@echo off
echo Starting Rhino
cd c:\Rhino1_7r2
java -cp ".;C:\rhino1_7r2\jar\sqlitejdbc-v056.jar;;C:\rhino1_7r2\js.jar" org.mozilla.javascript.tools.shell.Main

If you’re typing this in the interactive shell, I’ve added come some of the return statements in comments.

//import the Java SQL package
importPackage(java.sql);
//load the SQLite driver
//importPackage does not work here
java.lang.Class.forName("org.sqlite.JDBC");
// returns:class org.sqlite.JDBC
//Create connection to test.db
//That database is created if it doesn’t exist.
//Normally, you could supply two more arguments here with
//username and password, but SQLite doesn’t support this
var conn = DriverManager.getConnection("jdbc:sqlite:test.db");

//Use the connection to create a Statement object
var stat = conn.createStatement();
stat.executeUpdate("drop table if exists people ;");
//returns: 0
stat.executeUpdate("create table people (name, occupation);");
//returns: 0
//Create a prepared statement object
var prep = conn.prepareStatement("insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "Computers");
prep.addBatch();
conn.setAutoCommit(false);
//Execute the prepared statements
prep.executeBatch();
//returns: [I@6e1408
conn.setAutoCommit(true);
var resultSet = stat.executeQuery("select * from people;");
while (resultSet.next()){
print(resultSet.getString("name") + “ – “ + resultSet.getString("occupation"));
}
//returns
// Gandhi - politics
// Turing – Computers
//cleanup
//While SQLite really isn’t the tool to use if
//data needs to be shared across multiple applications
// or users, be sure to close resultSet otherwise the
//db will stay locked
resultSet.close();
stat.close();
conn.close();

You can then launch your SQLite inspector of choice, I use the fantastic SQLiteman. Select * from people and you will see what you’ve just added. Satisfying isn’t it?

Notice that I had to specify java.lang.Class.forName whereas most examples you see connecting to a SQL database will simply call Class.forName. This is because the lang package isn’t imported by default into Rhino, where is normally is in Java.