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();


With the above code, I got the following output:

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.

Comments:

Post a Comment:
Comments are closed for this entry.
About

sundararajan

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Bookmarks
Links

No bookmarks in folder

Blogroll

No bookmarks in folder

News

No bookmarks in folder