Java plain JDBC using Javascript
Modeling a JDBC backend with JavaScript
For this epic, we’ll examine the database access component of an application — connecting to the database using Java’s plain JDBC api and Nashorn with JDK 10, which is Java’s implementation of ES5 JavaScript. There are numerous choices of database technologies and they all go about doing their work in uniquely different ways. We will start with a relational database and explore other technologies in other epics. Let’s get to it!
Let’s model the backend for a simple Todo application. For this epic, we’ll use plain JDBC to handle the persistence. The only domain object we will use here will be:
model Todo {
task: String! unique,
completed: boolean
}The domain entity here is represented using a simple json-like syntax for its ubiquity and instant clarity
Now let’s create the functions required for the database access. We will use the following for this epic:
- H2 database. This is a tiny and fast relational database written in Java.
- apache commons’ BasicDataSource which is used to connect to the H2 DB
Start by creating a project folder, and call it my-app. In this folder, create a src and lib folder. Download the required dependencies and save them in the lib folder. These dependencies are:
Create a directory in the root folder and name it src. This is where our source files will reside.
Create a file appdb.js in the src folder. Let’s define a skeletal structure in this file to help to help us with isolating our functionality in a module
let dao = {};
(function(dao, load){ //...code goes here
})(dao, true);
Ideally in a Nodejs environment, we would make use of module.exports to encapsulate our functions, but since we are using Nashorn, we don’t have that luxury yet.
Let’s create a configuration for the datasource, and get to work
let DataSource = Java.type('org.apache.commons.dbcp2.BasicDataSource');
var DB = function(params){
this.config = {
"jdbc.driverClass": params && params['driverClass'] || "org.h2.Driver",
"jdbc.url": params && params['url'] || "jdbc:h2:./data/todos.js_db;DB_CLOSE_DELAY=-1",
"jdbc.username": params && params['username'] || "sa",
"jdbc.password": params && params['password'] || "sa"
};
this.ds = undefined;
};The DB function is a prototypical way of creating a class in JavaScript. This is what we will use in Nashorn to avoid conflict with the class keyword used in Java. Note that recent ECMAScript editions have also introduced the class keyword in JavaScript which is a more recognizable way of defining classes.
The DB class configuration has four parameters you could pass through the params argument. The properties are
- driverClass — this is the JDBC driver class which connects to the underlaying database
- url — this is the JDBC-centric string that identifies the database, the connection protocol, database location, database name, and other parameters required to successfully connect to the database. Different databases have uniquely different connection url’s.
- username — for most databases, this is the name of the active user connecting to the database
- password — if the active user has configured a password, this is the plain password required to authenticate a user with the database
With this boiler-plate code out of the way, now let’s beef up this class. Let’s add a method to initialize the data-source
DB.prototype.initDS = function(){
var dataSource = new DataSource();
dataSource.setDriverClassName(this.config["jdbc.driverClass"]);
dataSource.setUrl(this.config["jdbc.url"]);
dataSource.setUsername(this.config["jdbc.username"]);
dataSource.setPassword(this.config["jdbc.password"]);
this.ds = dataSource;
};
DB.prototype.closeDS = function(){
this.ds.close();
};The initDS method will create a datasource object and read the necessary parameters from the config object. The closeDS method on the other hand will destroy the datasource object and release any opened resources.
Let’s create a function which will create the database table required for the backend
DB.prototype.createTable = function(query, onSuccess, onError){
var con, stmt;
try{
con = this.ds.getConnection();
stmt = con.createStatement();
var result = stmt.execute(query);
if (result) {
onSuccess("createTable was successful");
}
}
catch(error){
onError(error);
}
finally{
if(stmt) stmt.close();
if(con) con.close();
}
};This is pretty straight-forward. The function takes DDL query, and two callbacks; success and failure. This lends itself to an asynchronous style of programming which will be discussed more in other epics later. The action to proceed after the database operation is deferred to the caller of this operation
Next, let’s create a function to allow batch insertion of tasks, if the need to arises. We will use this function later on in this epic demonstrate its immense advantage when performing a batch operation.
DB.prototype.insertBatch = function(tasks, onSuccess, onError) {
var con, stmt;
try {
con = this.ds.getConnection();
stmt = con.createStatement();
for(var i= 0; i < tasks.length; i++){
stmt.addBatch(tasks[i]);
}
var result = stmt.executeBatch();
onSuccess(result, "batch insert was successful");
} catch (error) {
onError(error);
}finally{
if(stmt) stmt.close();
if(con) con.close();
}
};Again, this is pretty straight-forward also. The function takes a list of tasks, and two callbacks; success and failure. The connection object takes advantage of the addBatch and executeBatch methods in JDBC to perform the batch insert operation
And for good measure, let’s create a function to clear the database records. This is only useful for testing purposes. Ideally, such a function should be protected and only be used with utmost care.
DB.prototype.truncateTable = function (onSuccess, onError) {
var query = "TRUNCATE table tbl_todos";
var con, stmt;
try {
con = this.ds.getConnection();
stmt = con.createStatement();
var result = stmt.executeUpdate(query);
onSuccess(result, "Table data truncated");
} catch (error) {
onError(error);
} finally {
if (stmt) stmt.close();
if (con) con.close();
}
};The TRUNCATE function removes all rows from a table. Unlike DELETE FROM without where clause, this command can not be rolled back. This command is faster than DELETE without where clause. Only regular data tables without foreign key constraints can be truncated. For testing purposes, it is fine. For practical purposes, records should be deleted using a different strategy.
Now we get to the level of finer details. Let’s create a function to create a Todo task
DB.prototype.createTask = function(task, onSuccess, onError) {
var query = "INSERT INTO tbl_todos (task) values (?)";
var con, pst;
try {
con = this.ds.getConnection();
pst = con.prepareStatement(query);
pst.setString(1, task);
var result = pst.executeUpdate();
onSuccess(result, "createTask was successful");
} catch (error) {
onError(error);
}finally{
if(pst) pst.close();
if(con) con.close();
}
};The insert operation take one parameter, which is the task title, and saves it to the database table. The completed value is naturally set to false
Next, let’s create a method to update the status of a single Todo task
DB.prototype.updateDone = function(task, done, onSuccess, onError) {
var query = "UPDATE tbl_todos set completed=? where task = ?";
var con, pst;
try {
con = this.ds.getConnection();
pst = con.prepareStatement(query);
pst.setBoolean(1, done);
pst.setString(2, task);
var result = pst.executeUpdate();
onSuccess(result, "updated complete status");
} catch (error) {
onError(error);
}finally{
if(pst) pst.close();
if(con) con.close();
}
};The update operation take two parameters besides the callbacks — the task title and the completed status — and update the completed status in the database table.
Next, let’s create a method to update the title of a single Todo task
DB.prototype.updateName = function(task, newName, onSuccess, onError) {
var query = "UPDATE tbl_todos set task=? where task = ?";
var con, pst;
try {
con = this.ds.getConnection();
pst = con.prepareStatement(query);
pst.setString(1, newName);
pst.setString(2, task);
var result = pst.executeUpdate();
onSuccess(result, "updateName was successful");
} catch (error) {
onError(error);
}finally{
if(pst) pst.close();
if(con) con.close();
}
};The update operation take two parameters besides the callbacks — the task title and the new title — and update the task title in the database table.
Next, let’s create a method to remove a single Todo task from the table
DB.prototype.deleteTask = function(task, onSuccess, onError) {
var query = "DELETE from tbl_todos where task = ?";
var con, pst;
try {
con = this.ds.getConnection();
pst = con.prepareStatement(query);
pst.setString(1, task);
var result = pst.executeUpdate();
onSuccess(result, "deleteTask was successful");
} catch (error) {
onError(error);
}finally{
if(pst) pst.close();
if(con) con.close();
}
};The delete operation take a single parameter besides the callbacks — the task title — and removes the task from database table.
Next, let’s create a method to retrieve a single Todo task from the table
DB.prototype.retrieveTask = function(name, onSuccess, onError) {
var query = "SELECT * from tbl_todos where task = ?";
var con, pst;
try {
con = this.ds.getConnection();
pst = con.prepareStatement(query);
pst.setString(1, name);
var rs = pst.executeQuery();
if (rs.next()) {
var task = {};
task.completed = rs.getBoolean("completed");
task.name = rs.getString("task");
task.created = rs.getDate("date_created");
onSuccess(task, "retrieveTask was successful");
} else {
onSuccess({}, "no task found");
}
} catch (error) {
onError(error);
}finally{
if(pst) pst.close();
if(con) con.close();
}
};The retrieve operation take a single parameter besides the callbacks — the task title — and fetches the task from database table.
Next, let’s create a method to retrieve a group of Todo tasks from the table using a range — the start position and the number of items to retreieve from that point. This method is useful when you need to perform pagination over the table records
DB.prototype.retrieveByRange = function(start, size, onSuccess, onError) {
var query = "SELECT * from tbl_todos limit ? offset ?";
var con, pst;
try {
con = this.ds.getConnection();
pst = con.prepareStatement(query);
pst.setInt(1, size);
pst.setInt(2, start);
var rs = pst.executeQuery();
var result = [];
while (rs.next()) {
var task = {};
task.completed = rs.getBoolean("completed");
task.name = rs.getString("task");
task.created = rs.getDate("date_created");
result.push(task);
}
onSuccess(result, "retrieveByRange was successful");
} catch (error) {
onError(error);
}finally{
if(pst) pst.close();
if(con) con.close();
}
};The retrieve by range operation take two parameters besides the callbacks — the start and size — and fetches the range of tasks from database table.
Lastly, let’s create a method to retrieve completed Todo tasks from the table.
DB.prototype.retrieveByDone = function(completed, onSuccess, onError) {
var query = "SELECT * from tbl_todos where completed = ?";
var con, pst;
try {
con = this.ds.getConnection();
pst = con.prepareStatement(query);
pst.setBoolean(1, completed);
var rs = pst.executeQuery();
var result = [];
while (rs.next()) {
var task = {};
task.completed = rs.getBoolean("completed");
task.name = rs.getString("task");
task.created = rs.getDate("date_created");
result.push(task);
}
onSuccess(result, "retrieveByDone was successful");
} catch (error) {
onError(error);
}finally{
if(pst) pst.close();
if(con) con.close();
}
};The retrieve tasks by completed status takes a single parameter besides the callbacks — the completed flag — and returns a list of matched items.
Now we have in place a variety of methods we can use to handle the database legwork. Let’s now put this to work.
Let’s export the DB object to make it available outside this script
//export DB through 'dao'
dao.DB = DB;In the closure we have created, we are passing two arguments — dao and true. The second argument is used to determine if initial data should be loaded. In this case, we are going to insert initial data. To do this, add the following section
if(load){
function onCreate(msg){
print(msg);
}
//init database and insert data
var db = new dao.DB();
db.initDS(); var data = [
"merge into tbl_todos (task, completed) key(task) values ('buy milk', false);",
"merge into tbl_todos (task, completed) key(task) values ('work out', true);",
"merge into tbl_todos (task, completed) key(task) values ('watch game', false);",
"merge into tbl_todos (task, completed) key(task) values ('hit gym', false);",
"merge into tbl_todos (task, completed) key(task) values ('go to meeting', true);"
];
db.createTable([
"CREATE TABLE IF NOT EXISTS tbl_todos (",
" task varchar(25) UNIQUE NOT NULL,",
" completed boolean DEFAULT false,",
" date_created datetime default current_timestamp,",
" PRIMARY KEY (task)",
")"
].join(""), onCreate, onCreate); db.insertBatch(data, (res, msg) => {
db.closeDS();
print("res=" + res + ", msg=" + msg);
}, (error) => print(error)); let Date = Java.type('java.util.Date');
print("data loaded".concat(" @ ").concat(new Date().toString()));
}
With the data loading section completed, you can optionally add at the beginning the appdb.js a call to load(‘jvm-npm.js’). Download this file and save it the lib folder.
load('jvm-npm.js');The application is now ready to run. To invoke the script, make sure to add the required dependencies on the classpath. (Note that the ‘\ ‘ characters in the classpath are used to break the command into multiple lines, so just remove them and any spaces in the classpath if they present problems)
jjs --language=es6 -ot -scripting -J-Djava.class.path=./lib/h2-1.4.197.jar:\ ./lib/commons-dbcp2-2.5.0.jar:./lib/commons-pool2-2.6.0.jar:./lib/commons-logging-1.2.jar appdb.js
JUnit-testing the JDBC database access
To begin with, we will use Java’s own Junit library to test the appdb.js data access script we just created for persistance. In the subsequent epic, we will use a Javascript testing library to test the same functionality.
Download the junit library and the hamcrest dependency, and place the jars in the ./lib folder.
Next, create a folder __tests__ and add a new test file appdb-test.js. Now let’s get to work!
load('src/appdb.js');
//********************************************//
// Initialize db for testing
//********************************************//
var assert = org.junit.Assert;In the appdb-test.js file, we first import the appdb.js file with our backend functionality. With unit testing, we get to verify the functional correctness of the individual functions we have. To do this, we also import junit’s Assert class which has the methods we will use
Let’s create the database access configuration object, and use it to create the data access object. The main difference with the default settings is that the database name is changed. While doing so, let’s also create a utility function for generating a random number between a given range.
var math = Java.type('java.lang.Math');
function random(min, max){
return (math.random() * ( max - min )) + min;
}
var config = {
"driverClass": "org.h2.Driver",
"url": "jdbc:h2:./data/todos.js_db_test;DB_CLOSE_DELAY=-1",
"username": "sa",
"password": "sa"
};
let db = new dao.DB(config);
db.initDS();With the data access object initialized, we should create the database and table necessary to test the database access functionality
In this next step, let create a database table and populate it with the test data
db.createTable([
"CREATE TABLE IF NOT EXISTS tbl_todos (",
" task varchar(25) NOT NULL,",
" completed boolean DEFAULT false,",
" date_created datetime default current_timestamp,",
" PRIMARY KEY (task)",
")"
].join(""), (res,msg)=>print(res,msg), (err)=>print(err));var data = [
"merge into tbl_todos (task, completed) key(task) values ('buy milk', false);",
"merge into tbl_todos (task, completed) key(task) values ('work out', true);",
"merge into tbl_todos (task, completed) key(task) values ('watch game', false);",
"merge into tbl_todos (task, completed) key(task) values ('hit gym', false);",
"merge into tbl_todos (task, completed) key(task) values ('go to meeting', true);"
];//clear database records before commencing tests
db.truncateTable((res, msg)=>{
print(res, msg);
//insert new batch of test data
db.insertBatch(data, (res, msg)=>print(res, msg), (error)=>print(error));
}, (error)=>print(error));
In the createTable function, we only create the table if it does not already exist. Similarly, we use the merge syntax to insert the table records if they do not exist, or update the columns if the row exists.
With the database table in place, let’s now start testing the functionality. To start with, let test the Retrieve Task operation.
//********************************************//
// Test methods in db API
//********************************************//
function testRetrieveTask(test, name, onSuccess, onError){
if(test) db.retrieveTask(name, onSuccess, onError);
}
testRetrieveTask(true, 'buy milk',
function(task, msg){
assert.assertEquals("Expecting 'buy milk'", 'buy milk', task.name);
print('name='+task.name+', completed='+task.completed);
},
function(msg){
print(msg);
assert.fail(msg);
}
);To execute the tests, we will need to add the junit and hamcrest libraries to the classpath, and execute the same command as before. This time however, the target file will be the appdb-junit.js file in the __tests__ folder.
On a windows machine, use the semi-colon instead of the colon to separate the classpath jars
jjs --language=es6 -ot -scripting\
-J-Djava.class.path=./lib/h2-1.4.197.jar:\ ./lib/commons-dbcp2-2.5.0.jar:./lib/commons-pool2-2.6.0.jar:./lib/commons-logging-1.2.jar:./lib/junit-4.12.jar:./lib/hamcrest-core-1.3.jar\
__tests__/appdb-junit.jsNext, let’s test the create task function
function testCreateTask(test, name, onSuccess, onError){
if(test) db.createTask(name, onSuccess, onError);
}
let title = "task at " + random(0, 100);
testCreateTask(false, title,
function(task, msg){
assert.assertEquals('Expecting \'' + title + '\'', title, task.task);
print(msg);
},
function(msg){
print(msg);
assert.fail(msg);
}
);The task name is concatenated with a random number using the function we created at the beginning of the file. This serves to differentiate it for testing purposes.
Next, let’s create a test to update the watch game task that was created during initialization
function testUpdateDone(test, name, done, onSuccess, onError){
if(test) db.updateDone(name, done, onSuccess, onError);
}
testUpdateDone(true, 'watch game', true,
function(res, msg){
assert.assertEquals('Expecting \'1\'', '1', res.toString());
print(msg);
},
function(msg){
print(msg);
assert.fail(msg);
}
);Upon successful completion, the update completed status function returns the number of rows updated. In this case, we are only updating one row, so the expected result is 1
Next, let’s create a test to update the task name for a record created during initialization.
function testUpdateName(test, name, newname, onSuccess, onError){
if(test) db.updateName(name, newname, onSuccess, onError);
}
testUpdateName(true, 'watch game', 'watch soccer',
function(res, msg){
assertEquals('Expecting \'1\'', '1', res.toString());
print(msg);
},
function(msg){
print(msg);
assert.fail(msg);
}
);Upon successful completion, the update task name function returns the number of rows updated. In this case once again, we are only updating one row, so the expected result is 1
Next, let’s create a test for the delete task function.
function testDeleteTask(test, name, onSuccess, onError){
if(test) db.deleteTask(name, onSuccess, onError);
}
testDeleteTask(true, 'buy milk',
function(res, msg){
assert.assertEquals('Expecting \'1\'', '1', res.toString());
print(msg);
},
function(msg){
print(msg);
assert.fail(msg);
}
);Upon successful completion, the delete task function returns the number of rows deleted. In this case once again, we are only removed one row, so the expected result is 1
Next, let’s create a test for the function to retrieve a range of tasks.
function testRetrieveByRange(test, start, end, onSuccess, onError){
if(test) db.retrieveByRange(start, end, onSuccess, onError);
}testRetrieveByRange(true, 0, 10,
function(tasks, msg){
assert.assertEquals("Expecting 4", "4", tasks.length.toString());
print(msg);
tasks.forEach(task => print('name='+task.name+', completed='+task.completed));
},
function(msg){
print(msg);
assert.fail(msg);
}
);
Upon successful completion, the retrieve tasks in range function returns the number of records available with the range.
Last and definitely not least, let’s create a test for the function to retrieve completed tasks.
function testRetrieveByDone(test, completed, onSuccess, onError){
if(test) db.retrieveByDone(completed, onSuccess, onError);
}testRetrieveByDone(true, true,
function(tasks, msg){
print(tasks.length, msg);
tasks.forEach (task => {
print('name='+task.name+', completed='+task.completed);
assert.assertEquals('Expecting \'done\'', true, task.completed);
});
},
function(msg){
print(msg);
assert.fail(msg);
}
);
For even more entertaining epics, visit https://practicaldime.org
