node-jdbc
JDBC API Wrapper for node.js
Latest Version
Installation
- Release:
npm i --save jdbc
- Development:
npm i --save jdbc@next
(this will install code from the master branch).
Please visit node-jdbc for information on installing with npm.
Status
Supported Java Versions
node-java has experiemental support for 1.8, and if you are brave you can
compile it with such. All the tests work out of the box on a 1.8 JVM, but I've only wrapped 1.7 functions.
Note that Java 9 is not currently supported. When node-java supports Java 9, I will look into implementing any
new Java 9 API changes (if any).
Major API Refactor
- One Instance to Rule Them All (JVM)
node-java spins up one JVM instance only. Due to this fact, any JVM options
and classpath setup have to happen before the first java call. I've created a
small wrapper (jinst.js) to help out with this. See below for example
usage. I usually add this to every file that may be an entry point. The
unit tests
are setup like this due to the fact that order can't be guaranteed.
var jinst = require('jdbc/lib/jinst');
if (!jinst.isJvmCreated()) {
jinst.addOption("-Xrs");
jinst.setupClasspath(['./drivers/hsqldb.jar',
'./drivers/derby.jar',
'./drivers/derbyclient.jar',
'./drivers/derbytools.jar']);
}
Everyone gets a pool now. By default with no extra configuration, the pool
is created with one connection that can be reserved/released. Currently, the
pool is configured with two options: minpoolsize
and maxpoolsize
. If
minpoolsize
is set, when the pool is initialized, minpoolsize
connections
will be created. If maxpoolsize
is set (the default value is minpoolsize
),
and you try and reserve a connection and there aren't any available, the pool
will be grown. This can happen until maxpoolsize
connections have been
reserved. The pool should be initialized after configuration is set with the
initialize()
function. JDBC connections can then be acquired with the
reserve()
function and returned to the pool with the release()
function.
Below is the unit test for the pool that demonstrates this behavior.
var _ = require('lodash');
var nodeunit = require('nodeunit');
var jinst = require('../lib/jinst');
var Pool = require('../lib/pool');
if (!jinst.isJvmCreated()) {
jinst.addOption("-Xrs");
jinst.setupClasspath(['./drivers/hsqldb.jar',
'./drivers/derby.jar',
'./drivers/derbyclient.jar',
'./drivers/derbytools.jar']);
}
var config = {
url: 'jdbc:hsqldb:hsql://localhost/xdb',
user : 'SA',
password: '',
minpoolsize: 2,
maxpoolsize: 3
};
var testpool = null;
var testconn = null;
module.exports = {
setUp: function(callback) {
if (testpool === null) {
testpool = new Pool(config);
}
callback();
},
testinitialize: function(test) {
testpool.initialize(function(err) {
test.expect(1);
test.equal(null, err);
test.done();
});
},
testreserve: function(test) {
testpool.reserve(function(err, conn) {
test.expect(4);
test.equal(null, err);
test.ok(conn && typeof conn == 'object');
test.equal(testpool._pool.length, 1);
test.equal(testpool._reserved.length, 1);
testconn = conn;
test.done();
});
},
testrelease: function(test) {
testpool.release(testconn, function(err, conn) {
test.expect(3);
test.equal(null, err);
test.equal(testpool._pool.length, 2);
test.equal(testpool._reserved.length, 0);
testconn = null;
test.done();
});
},
testreserverelease: function(test) {
testpool.reserve(function(err, conn) {
if (err) {
console.log(err);
} else {
testpool.release(conn, function(err) {
test.expect(3);
test.equal(null, err);
test.equal(testpool._pool.length, 2);
test.equal(testpool._reserved.length, 0);
test.done();
});
}
});
},
testreservepastmin: function(test) {
var conns = [];
for(i = 0; i < 3; i++) {
testpool.reserve(function(err, conn) {
conns.push(conn);
if (i == 3) {
test.expect(2);
test.equal(testpool._pool.length, 0);
test.equal(testpool._reserved.length, 3);
_.each(conns, function(conn) {
testpool.release(conn, function(err) {});
});
test.done();
}
});
}
},
testovermax: function(test) {
var conns = [];
for(i = 0; i < 4; i++) {
testpool.reserve(function(err, conn) {
if (err) {
if (i == 3) {
test.expect(3);
test.ok(err);
test.equal(testpool._reserved.length, 3);
test.equal(testpool._pool.length, 0);
_.each(conns, function(conn) {
testpool.release(conn, function(err) {});
});
test.done();
} else {
console.log(err);
}
} else {
conns.push(conn);
}
});
}
}
};
- Fully Wrapped Connection API
The Java Connection API has almost been completely wrapped. See
connection.js
for a full list of functions.
conn.setAutoCommit(false, function(err) {
if (err) {
callback(err);
} else {
callback(null);
}
});
- ResultSet processing separated from statement execution
ResultSet processing has been separated from statement execution to allow for
more flexibility. The ResultSet returned from executing a select query can
still be processed into an object array using the toObjArray()
function on the
resultset object.
conn.createStatement(function(err, statement) {
if (err) {
callback(err);
} else {
statement.executeQuery("SELECT * FROM blah;", function(err, resultset) {
if (err) {
callback(err)
} else {
resultset.toObjArray(function(err, results) {
if (results.length > 0) {
console.log("ID: " + results[0].ID);
}
callback(null, resultset);
});
}
});
}
});
- Automatically Closing Idle Connections
If you pass a maxidle property in the config for a new connection pool,
pool.reserve()
will close stale connections, and will return a sufficiently fresh connection, or a new connection. maxidle can be number representing the maximum number of milliseconds since a connection was last used, that a connection is still considered alive (without making an extra call to the database to check that the connection is valid). If maxidle is a falsy value or is absent from the config, this feature does not come into effect. This feature is useful, when connections are automatically closed from the server side after a certain period of time, and when it is not appropriate to use the connection keepalive feature.
Usage
Some mininal examples are given below. I've also created a
node-example-jdbc project with more thorough examples.
Initialize
var JDBC = require('jdbc');
var jinst = require('jdbc/lib/jinst');
if (!jinst.isJvmCreated()) {
jinst.addOption("-Xrs");
jinst.setupClasspath(['./drivers/hsqldb.jar',
'./drivers/derby.jar',
'./drivers/derbyclient.jar',
'./drivers/derbytools.jar']);
}
var config = {
url: 'jdbc:hsqldb:hsql://localhost/xdb',
drivername: 'my.jdbc.DriverName',
minpoolsize: 10,
maxpoolsize: 100,
user: 'SA',
password: '',
properties: {}
};
var hsqldb = new JDBC(config);
hsqldb.initialize(function(err) {
if (err) {
console.log(err);
}
});
Reserve Connection, Execute Queries, Release Connection
var asyncjs = require('async');
hsqldb.reserve(function(err, connObj) {
if (connObj) {
console.log("Using connection: " + connObj.uuid);
var conn = connObj.conn;
asyncjs.series([
function(callback) {
conn.setAutoCommit(false, function(err) {
if (err) {
callback(err);
} else {
callback(null);
}
});
},
function(callback) {
conn.setSchema("test", function(err) {
if (err) {
callback(err);
} else {
callback(null);
}
});
}
], function(err, results) {
});
asyncjs.series([
function(callback) {
conn.createStatement(function(err, statement) {
if (err) {
callback(err);
} else {
statement.executeUpdate("CREATE TABLE blah "
+ "(id int, name varchar(10), date DATE, "
+ " time TIME, timestamp TIMESTAMP);",
function(err, count) {
if (err) {
callback(err);
} else {
callback(null, count);
}
});
}
});
},
function(callback) {
conn.createStatement(function(err, statement) {
if (err) {
callback(err);
} else {
statement.executeUpdate("INSERT INTO blah "
+ "VALUES (1, 'Jason', CURRENT_DATE, "
+ "CURRENT_TIME, CURRENT_TIMESTAMP);",
function(err, count) {
if (err) {
callback(err);
} else {
callback(null, count);
}
});
}
});
},
function(callback) {
conn.createStatement(function(err, statement) {
if (err) {
callback(err);
} else {
statement.executeUpdate("UPDATE blah "
+ "SET id = 2 "
+ "WHERE name = 'Jason';",
function(err, count) {
if (err) {
callback(err);
} else {
callback(null, count);
}
});
}
});
},
function(callback) {
conn.createStatement(function(err, statement) {
if (err) {
callback(err);
} else {
statement.setFetchSize(100, function(err) {
if (err) {
callback(err);
} else {
statement.executeQuery("SELECT * FROM blah;",
function(err, resultset) {
if (err) {
callback(err)
} else {
resultset.toObjArray(function(err, results) {
if (results.length > 0) {
console.log("ID: " + results[0].ID);
}
callback(null, resultset);
});
}
});
}
});
}
});
},
function(callback) {
conn.createStatement(function(err, statement) {
if (err) {
callback(err);
} else {
statement.executeUpdate("DELETE FROM blah "
+ "WHERE id = 2;", function(err, count) {
if (err) {
callback(err);
} else {
callback(null, count);
}
});
}
});
},
function(callback) {
conn.createStatement(function(err, statement) {
if (err) {
callback(err);
} else {
statement.executeUpdate("DROP TABLE blah;", function(err, count) {
if (err) {
callback(err);
} else {
callback(null, count);
}
});
}
});
}
], function(err, results) {
hsqldb.release(connObj, function(err) {
if (err) {
console.log(err.message);
}
});
});
}
});