A. Sundararajan's Weblog

Using Apache DB Derby from JavaScript

Apache DB Derby is co-bundled with Mustang (Java SE 6). And Mozilla Rhino based JSR-223 script engine for JavaScript is also bundled with Mustang. How about accessing Derby database from JavaScript? I tried the following script...

// constructor to create DB object. Accepts user, password and DB name
function DB(user, password, db) {
    var driver = new org.apache.derby.jdbc.EmbeddedDriver();
    var props = new java.util.Properties();
    props.put("user", user);
    props.put("password", password);
    this.conn = java.sql.DriverManager.getConnection("jdbc:derby:" +
                db + ";create=true", props);
}

// wraps the ResultSet as an Iterator
DB.prototype.query = function (str) {
    var stat = this.conn.createStatement();
    var rs = stat.executeQuery(str);

    return new java.util.Iterator() {
            hasNext: function() { 
                return rs.next();
            },
            next: function() {
                return new JSAdapter() {
                    __has__: function (name) {
                       try {
                           rs.findColumn(name);
                           return true;
                       } catch (e) {
                           println(e);
                           return false;
                       }
                    },
                    __get__: function (name) {
                        return rs.getObject(name);
                    }
                };
            },
            remove: function() {
                rs.deleteRow();
            }
        };
}

// execute query and update
DB.prototype.execute = function (str) {
    var stat = this.conn.createStatement();
    return stat.execute(str);
}

DB.prototype.update = function (str) {
    var stat = this.conn.createStatement();
    return this.conn.executeUpdate(str);
}

// dispose the connection
DB.prototype.dispose = function() {
    this.conn.close();
}

// simple "main" function that uses the above db "API"!

function main() {
    var db = new DB("user1", "user1", "derbyDB");
    db.execute("create table derbyDB(num int, addr varchar(40))");  
    db.execute("insert into derbyDB values (1956,'Webster St.')");
    db.execute("insert into derbyDB values (1910,'Union St.')");
    db.execute(
          "update derbyDB set num=180, addr='Grand Ave.' where num=1956");

    var res = db.query(
                    "SELECT num, addr FROM derbyDB ORDER BY num");

    // because of JSAdapter, we can access
    // columns as "fields" of the "row object".
    while (res.hasNext()) {
        var row = res.next();
        println(row.num + "\t" + row.addr);
    }
    db.dispose();
}

main();

jrunscript -cp D:\jdk1.6.0\db\lib\derby.jar -f db.js
180     Grand Ave.
1910    Union St.

Note that I've put derby.jar in the classpath so that Derby classes can be accessed from the scripts.

( Aug 07 2006, 07:58:08 PM IST ) Permalink | | | | |