Node.js

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

3. Quick Start

In this section you will fin a set of code snippets to perform several operations such as connect, read, insert and update using LeanXcale JDBC driver from NodeJS.

3.1. Connecting 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 for 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 as when using JDBC from Java, it is represented by an 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 get the connection, you must reserve it and then you will be able to operate with it. After your work is done, you have to 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 in order to have a connection.

By default, the autocommit property is set to false, if you want to set it to true you will have to:

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

To close the connection:

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

3.2. SQL Queries

As you are using the JDBC driver, it works the same way than when used from Java. So, in order to launch a query to the database you need a Statement or a PreparedStatement. This returns a ResultSet which 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 can get the same behavior using PreparedStatement. In this case it is necessary to bind the value to the query.

    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. Inserting, updating and deleting

To perform operations like INSERT, UPDATE or DELETE, that change data, you use 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(function (err, statement) {
      if (err) {
        console.error(err);
      } else {
        statement.executeUpdate("INSERT INTO person VALUES (100, 'John', 'Doe', '555333695')",
            function (err, count) {
              if (err) {
                console.error(err);
              } else {
                console.log(count);
              }
            });
      }
    });

3.3.2. Updating records:

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

3.3.3. Deleting records:

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

3.4. Creating, modifying and dropping

To create, modify or drop a database object like a table or sequence, you use the execute() method. This method is similar to the method executeQuery(), but it doesn’t return an object Result.

3.4.1. Tables

Example to create 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)
          }
        })
      }
    });

And to drop it:

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

And also, they can be created standalone:

    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 needs to be created after the table is created, 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 would like to have unique indexes to avoid duplicate values:

    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 many tables using a single query you will have to join those tables in the following way:

    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.