Node.js

1. NodeJS JDBC

When using NodeJS we can load JDBC drivers using the node-jdbc library. This library acts as a JDBC API wrapper for NodeJS.

This means we can used the LeanXcale JDBC driver.

2. Installation Guide

There are two alternatives to use LeanXcale JDBC driver: downloading the driver by hand or using Maven.

2.1. Install the JDBC driver manually

You can download the LeanXcale JDBC driver from the Drivers page.

You will be using the jdbc library to make use of LeanXcale JDBC driver, you will need to run the following command to install the release version of the jdbc library:

npm i --save jdbc

Using the --save you will save the dependency in the package.json file, so it will be installed everytime you install all dependencies for the project.

The following step is to configure the jdbc node library to use LeanXcale driver. You must place the LeanXcale driver you downloaded from the Drivers page in a directory under your project, for example, a lib folder.

At last, you just need to configure the classpath of the jdbc library to include LeanXcale driver in the classpath of the jdbc library.

var JDBC = require('jdbc');
var jinst = require('jdbc/lib/jinst');
var lx;
var asyncjs = require('async');

if (!jinst.isJvmCreated()){
  jinst.addOption("-Xrs");
  jinst.setupClasspath(['lib/qe-driver-1.5.4-jdbc-client.jar']);
}

2.2. Install the JDBC driver using Maven

Also, you can download the LeanXcale JDBC driver from our public Maven repository. The Maven dependency can be downloaded by using the node-java-maven module, which can be installed alongside with the java module using npm:

npm i --save node-java-maven
npm i --save java

Using node-java-maven, Maven dependencies and repositories can be specified in the package.json file. To add LeanXcale dependency you would need to add the public repository and the required version for our JDBC driver. In your package.json add:

    "java": {
      "repositories": [
        {
          "id": "maven-releases",
          "name": "maven-releases",
          "url": "https://nexus.leanxcale.com/repository/maven-public/"
        }
      ],
      "dependencies": [
        {
          "groupId": "com.leanxcale",
          "artifactId": "qe-driver",
          "version": {minor-version}
        }
      ]
    }

Now run node-java-maven. In this way, you will be installing the LeanXcale JDBC driver into your local Maven repository.

./node_modules/.bin/node-java-maven

Once Maven has downloaded the driver into your local repository, you will use the jdbc library to use it from your node application.

To install the jdbc library run:

npm i --save jdbc

Using the --save you will save the dependency in the package.json file, so it will be installed everytime you install all dependencies for the project.

At last, you just need to configure the classpath of the jdbc library to include LeanXcale driver in the classpath of the jdbc library. In this case, as you downloaded the driver using Maven, it will be located in your Maven local repository under the com/leanxcale-driver path.

var JDBC = require('jdbc');
var jinst = require('jdbc/lib/jinst');
var lx;
var asyncjs = require('async');

if (!jinst.isJvmCreated()){
  jinst.addOption("-Xrs");
  jinst.setupClasspath(['path_to_m2_repository/.m2/repository/com/leanxcale
  /qe-driver-1.5.4-jdbc-client.jar']);
}

3. Quick Start

This section includes code snippets to perform several operations to get you started, such as connect, read, insert, and update using the LeanXcale JDBC driver from within NodeJS.

3.1. Connect to LeanXcale

Connecting to the SQL interface of requires some basic information:

If you are using LeanXcale as a Cloud Service, you can find the IP or hostname for your instance in the LeanXcale Cloud Console.
If you’re using LeanXcale on-premise, you can use the information from your installation.

In our examples we will use these parameters:

IP / hostname: 123.45.67.89
Port: 1529
DB name: dbtest
Username: user1
Password: pass4user1

The connection details are the same when using JDBC from Java, as it is represented by a URL, user, and password.

  var conf = {
    url:'jdbc:leanxcale://123.45.67.89:1529/dbtest',
    drivername: 'com.leanxcale.client.Driver',
    properties: {
      user:'user1',
      password: 'pass4user1'
    }
  };
  lx = new JDBC(conf);
  lx.initialize(function(err) {
    if (err) {
      console.log(err);
    } else{
      console.log('Connection initialized without error')
    }
  });

To obtain a connection, you must first reserve it, and then you can operate with it. After your work is complete, then you must release the connection.

  lx.reserve(function(err, connObj){
    let conn = connObj.conn;
    lx.release(connObj, function(err){
      if (err) {
        console.error(err.message)
      }
    });
  });

All operations must be performed inside a reserve block to maintain the connection.

By default, the autocommit property is set to false, So, if you want to set it to true, then you must call:

    conn.setAutoCommit(false, function(err) {
      if (err) {
        console.error(err);
      } else {
        console.log('Autocommit Disabled');
      }
    });

To close the connection, run:

    conn.close( function(err) {
      if (err) {
        console.error(err);
      } else {
        console.log('Connection closed');
      }
    });

3.2. SQL Queries

Using the JDBC driver in this context is identical to when used from Java. So, to launch a query to the database, you need a Statement or a PreparedStatement, which returns a ResultSet that contains the result of the query.

    conn.createStatement(function (err, statement) {
      if (err) {
        console.error(err);
      } else {
        statement.executeQuery("SELECT * FROM person WHERE lastName = 'Doe'", function (err, resultset) {
          if (err) {
            console.error(err);
          } else {
            resultset.toObjArray(function(err, results) {
              if (results.length > 0) {
                console.log(results);
              }
            });
          }
        });
      }
    });

You obtain the same behavior with PreparedStatement, but, in this case, binding the value to the query is necessary.

    conn.prepareStatement("SELECT * FROM person WHERE lastName = ?",function (err, statement) {
      if (err) {
        console.error(err);
      } else {
        statement.setString(1,"Doe", function(err){
          if(err){
            console.log(err);
          }
        });
        statement.executeQuery(function (err, resultset) {
          if (err) {
            console.error(err);
          } else {
            resultset.toObjArray(function(err, results) {
              if (results.length > 0) {
                console.log(results);
              }
            });
          }
        });
      }
    });

3.3. Insert, update and delete

To perform standard database operations that change data, such as INSERT, UPDATE, or DELETE, you call the executeUpdate() method. Unlike the method executeQuery() used previously in SELECT statements, the executeUpdate() method does not return a ResultSet, but the number of rows affected by the INSERT, UPDATE, or DELETE statement.

3.3.1. Insert one record

    conn.prepareStatement("INSERT INTO person VALUES (100, 'John', 'Doe',"
        + " '555333695')", function (err, statement) {
      if (err) {
        console.error(err);
      } else {
        statement.executeUpdate(
            function (err, count) {
              if (err) {
                console.error(err);
              } else {
                console.log(count);
              }
            });
      }
    });

3.3.2. Update records

    conn.prepareStatement("UPDATE person SET passport = '666668888' WHERE id in (100, 101)",
        function (err, statement) {
      if (err) {
        console.error(err);
      } else {
        statement.executeUpdate(
            function (err, count) {
              if (err) {
                console.error(err);
              } else {
                console.log(count);
              }
            });
      }
    });

3.3.3. Delete records

    conn.prepareStatement("DELETE FROM Registration WHERE id = 1",
        function (err, statement) {
      if (err) {
        console.error(err);
      } else {
        statement.executeUpdate( function (err, count) {
          if (err) {
            console.error(err);
          } else {
            console.log(count);
          }
        });
      }
    });

3.4. Create, modify and drop

To create, modify, or drop a database object, such as a table or sequence, you call the execute() method, which is similar to executeQuery(), but does not return an object Result.

3.4.1. Tables

An example of creating a table:

    let createTable= "CREATE TABLE person (" +
        "  id BIGINT NOT NULL" +
        ", name VARCHAR " +
        ", lastName VARCHAR " +
        ", birth_date DATE " +
        ", PRIMARY KEY (id)" +
        ")";
    conn.createStatement(function(err, statement){
      if (err) {
        console.error(err);
      } else {
        statement.execute(createTable, function(err, result){
          if (err) {
            console.error(err);
          } else {
            console.log(result)
          }
        })
      }
    });

Then, to drop the table:

    conn.createStatement(function(err, statement){
      if (err) {
        console.error(err);
      } else {
        statement.execute("DROP TABLE person", function(err, result){
          if (err) {
            console.error(err);
          } else {
            console.log(result);
          }
        })
      }
    });

3.4.2. Sequences

Sequences can be created and associated with a field or a table in the following way:

    let createTableSequence = "CREATE TABLE person (" +
        "  id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY" +
        ", name VARCHAR " +
        ", lastName VARCHAR " +
        ", phone VARCHAR " +
        ", PRIMARY KEY (id)" +
        ")";
    conn.createStatement(function(err, statement){
      if (err) {
        console.error(err);
      } else {
        statement.execute(createTableSequence, function(err, result){
          if (err) {
            console.error(err);
          } else {
            console.log(result)
          }
        })
      }
    });

Also, sequences can be created as a standalone object:

    let createSequence = "CREATE SEQUENCE IF NOT EXISTS personSeq";
    conn.createStatement(function(err, statement){
      if (err) {
        console.error(err);
      } else {
        statement.execute(createSequence, function(err, result){
          if (err) {
            console.error(err);
          } else {
            console.log(result);
          }
        })
      }
    });

3.4.3. Indexes

Indexes must be created after a table is created, which is unlike sequences:

    let createIndex = "CREATE INDEX phoneIdx ON person (phone)";
    conn.createStatement(function(err, statement){
      if (err) {
        console.error(err);
      } else {
        statement.execute(createIndex, function(err, result){
          if (err) {
            console.error(err);
          } else {
            console.log(result);
          }
        })
      }
    });

If you want unique indexes included to avoid duplicate values, then run the following:

    let createUniqueIndex = "CREATE UNIQUE INDEX passportIdx ON person (passport)";
    conn.createStatement(function(err, statement){
      if (err) {
        console.error(err);
      } else {
        statement.execute(createUniqueIndex, function(err, result){
          if (err) {
            console.error(err);
          } else {
            console.log(result);
          }
        })
      }
    });

3.5. Queries from many tables

If you want to retrieve information from multiple tables using a single query, then you must join these tables with the following:

    let joinQuery = "SELECT name, lastname, isbn FROM person INNER JOIN books ON person.id = books.authorid";
    conn.createStatement(function (err, statement) {
      if (err) {
        console.error(err);
      } else {
        statement.executeQuery(joinQuery, function (err, resultset) {
          if (err) {
            console.error(err);
          } else {
            resultset.toObjArray(function(err, results) {
              if (results.length > 0) {
                console.log(results);
              }
            });
          }
        });
      }
    });

4. Other Examples

We have more detailed examples and real-world scenarios available on our blog.

From Mean To Lean. Read how you can transform your web application from MEAN to the LEAN framework.