Node.js

1. NodeJS JDBC

When using NodeJS, we load JDBC drivers using the node-jdbc library, which acts as a JDBC API wrapper for NodeJS. This capability means we can then use the LeanXcale JDBC driver.

2. Installation Guide

Two alternatives are available to use the LeanXcale JDBC driver, including downloading the driver manually or through Maven.

2.1. Install the JDBC driver manually

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

Using the jdbc library is required to make use of the LeanXcale JDBC driver, so you must run the following command to install the release version of the jdbc library:

npm i --save jdbc

Appending with --save saves this as a dependency in the package.json file, so it will be installed each time you install all the dependencies for the project.

The following step configures the jdbc node library to use the LeanXcale driver. You must first store the LeanXcale driver you downloaded from the Drivers page into a directory under your project, such as a lib folder.

Finally, you configure the classpath of the jdbc library to include the LeanXcale driver.

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-2.0.0-jdbc-client.jar']);
}

2.2. Install the JDBC driver using Maven

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

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

By using node-java-maven, the Maven dependencies and repositories are specified in the package.json file. To add the LeanXcale dependency, you include the public repository and the required version for our JDBC driver into package.json:

    "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}
      }
    ]
  }

Next, run node-java-maven to install the LeanXcale JDBC driver into your local Maven repository:

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

After Maven downloads the driver into your local repository, you use the jdbc library to incorporate it into your node application.

To install the jdbc library, run:

npm i --save jdbc

Appending the --save saves the dependency in the package.json file, so it will be installed each time you install all the dependencies for the project.

Finally, you configure the classpath of the jdbc library to include the LeanXcale driver. In this case, because 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-2.0.0-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 requires some basic information:

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

In our examples, we include these parameters:

IP / hostname: 123.45.67.89
Port: 1529
DB name: db
Username: APP
Password: APP

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:1522/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')
    }
  });

If you are connecting to a LeanXcale instance using Security, add the following parameter to your URL:

url:'jdbc:leanxcale://123.45.67.89:1522/dbtest;secure=true'

Note: You must have previously download your TLS certificate and added it to yor Java Truststore.

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 feature additional detailed examples and real-world scenarios on our blog. To learn more about how you can connect your Scala application to LeanXcale using JDBC and the direct KiVi API, see:

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