Java

1. Two interfaces to access the same data

LeanXcale is a SQL database with NoSQL characteristics. As a relational key-value database, LeanXcale provides access to the same data through two independent APIs:

A 2003 ANSI SQL interface that is powerful and easy to use, but can be slow, so is most suitable for complex queries (e.g., multijoin queries).
A proprietary key-value interface, called KiVi, is fast and provides all functionality except for join operations, so it is most suitable for simpler queries where the overhead of SQL is too costly (e.g., updates and inserts).

Both interfaces interact with the same data and can be part of the same transaction.

2. SQL

2.1. Installation

To use the JDBC driver, you simply define the maven dependency as:

<dependency>
  <groupId>com.leanxcale</groupId>
  <artifactId>qe-driver</artifactId>
  <version>1.6.6</version>
  <classifier>jdbc-client</classifier>
</dependency>

Make sure you do not forget to include the LeanXcale public maven repository in your maven configuration :

    <repository>
      <id>maven-releases</id>
      <url>https://nexus.leanxcale.com/repository/maven-releases</url>
    </repository>

If you prefer to include the library in your project manually, then you can download the Java JDBC driver for LeanXcale from the Drivers page. By taking this approach, you must execute this command to include it into your local maven repository :

mvn install:install-file
-Dfile=<path-to-file>
-DgroupId=com.leanxcale
-DartifactId=qe-driver
-Dversion=1.6.6
-Dpackaging=jar
-DgeneratePom=true

Where:
<path-to-file> is the path to the file to load

With this, you can use the maven dependency defined previously.

2.2. Quick Start

This section provides a set of code snippets to connect, read, insert, and update information through the JDBC Java API:

2.2.1. Connecting 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: dbtest
Username: user1
Password: pass4user1

With JDBC, a database is represented as a URL, user, and password:

    Connection conn = DriverManager.getConnection("jdbc:leanxcale://123.45.67.89:1522/dbtest", "user1", "pass4user1");

By default, the connection is configured with 'autocommit', so that each command is sent to the database. This behavior can be changed with:

    conn.setAutoCommit(false);

TTo close the database connection, call the close() method on the Connection:

    conn.close();

Using an external pool

Another way to obtain a connection is to use a connection pool. An example using the hikariCP JDBC connection pool is the following:

    final HikariConfig configPool = new HikariConfig();
    configPool.setMaximumPoolSize(20);
    configPool.setDriverClassName("com.leanxcale.client.Driver");
    configPool.setJdbcUrl("jdbc:leanxcale://172.17.0.2:1522/db");
    configPool.setUsername("app");
    configPool.setPassword("app");
    configPool.setAutoCommit(false);

    final HikariDataSource ds = new HikariDataSource(configPool);
    Connection connection = ds.getConnection();

2.2.2. SQL Queries

Any time you want to query the database with SQL statements, you need to use a Statement or PreparedStatement instance. Once you have a Statement or PreparedStatement to execute a query. This returns a ResultSet instance from which you obtain the complete result.

    try (Statement st = conn.createStatement()) {
      try (ResultSet rs = st.executeQuery("SELECT * FROM person WHERE lastName = 'Doe'")) {
        while (rs.next()) {
          System.out.print("Column 1 returned ");
          System.out.println(rs.getString(1));
        }
      }
    }

The same example, but with PreparedStatement, is the following that requires binding the value in the query:

    try (PreparedStatement pst = conn.prepareStatement("SELECT * FROM person WHERE lastName = ?")) {
      pst.setString(1,"Doe");

      try (ResultSet rs = pst.executeQuery()) {
        while (rs.next()) {
          System.out.print("Column 1 returned ");
          System.out.println(rs.getString(1));
        }
      }
    }

2.2.3. Creating, modifying and dropping

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

Tables

An example of creating a table:

    String createTable= "CREATE TABLE person (" +
        "  id BIGINT NOT NULL" +
        ", name VARCHAR " +
        ", lastName VARCHAR " +
        ", birth_date DATE " +
        ", PRIMARY KEY (id)" +
        ")";

    Statement st = conn.createStatement();
    st.execute(createTable);
    st.close();

Then, to drop the created table:

    Statement st = conn.createStatement();
    st.execute("DROP TABLE person");
    st.close();

Sequences

Another example of an object could be a sequence. We create these with a table and associate to one field:

    String createTable= "CREATE TABLE person (" +
        "  id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY" +
        ", name VARCHAR " +
        ", lastName VARCHAR " +
        ", phone VARCHAR " +
        ", PRIMARY KEY (id)" +
        ")";

    Statement st = conn.createStatement();
    st.execute(createTable);
    st.close();

Or, a single standalone sequence can be created by:

    Statement st = conn.createStatement();
    st.execute("CREATE SEQUENCE IF NOT EXISTS personSeq");
    st.close();

Indexes

Unlike sequences, indexes can only be created after a table exists:

    Statement st = conn.createStatement();
    st.execute("CREATE INDEX phoneIdx ON person (phone)");
    st.close();

If we want unique indexes to avoid duplicated rows, then we create the index with this query:

    st.execute("CREATE UNIQUE INDEX passportIdx ON person (passport)");

2.2.4. Inserting, updating and deleting

To change data (i.e., perform an INSERT, UPDATE, or DELETE query), we use the executeUpdate() method that is similar to executeQuery() used to query a SELECT statement, but does not return a ResultSet. Instead, this update method returns the number of rows affected by the INSERT, UPDATE, or DELETE statement.

To insert one new record:

    String sql = "INSERT INTO person VALUES (100, 'John', 'Doe', '555333695')";
    st.executeUpdate(sql);

To update existing records:

    String sql = "UPDATE person SET passport = '666668888' WHERE id in (100, 101)";
    st.executeUpdate(sql);

To delete records:

    int id = 100;
    PreparedStatement pst = conn.prepareStatement("DELETE FROM Registration WHERE id = ?");
    pst.setInt(1, id);
    int rowsDeleted = pst.executeUpdate();
    System.out.println(rowsDeleted + " rows deleted");

2.2.5. Queries from many tables

In this example, we recover information from many tables by calling a single query:

    Statement stmt = conn.createStatement();
    String query ="SELECT name, lastname, isbn FROM person INNER JOIN books ON person.id = books.authorid";
    ResultSet rs = stmt.executeQuery(query);
    System.out.println("Name  LastName ISBN");

    while (rs.next()) {
      String name = rs.getString("name");
      String lastname = rs.getString("lastname");
      int isbn = rs.getInt("isbn");
      System.out.println(name + "  " + lastname+"   "+isbn);
    }

3. KiVi

3.1. Installation

To use the Direct KiVi API connector, you simply define the maven dependency to the kivi-api library with the desired version.

  <dependency>
    <groupId>com.leanxcale</groupId>
    <artifactId>kivi-api</artifactId>
    <version>1.6.6</version>
  </dependency>

Be sure to include the LeanXcale public maven repository in your maven configuration.

    <repository>
      <id>maven-releases</id>
      <url>https://nexus.leanxcale.com/repository/maven-releases</url>
    </repository>

If you prefer to include the library into your project manually, then you can download the Java KiVi Direct API driver from the Drivers page.

3.2. Quick Start

The following sections include a set of code snippets to connect, read, insert, and update information through the KiVi JAVA direct API. You can read the API here.

3.2.1. Connecting and starting a session

When you start a new session, it automatically connects. If this is the first session, then the API library manages the connections for you.

In the credentials, you set the user, password, and name of the database (e.g., "tpch" in the example ):

import com.leanxcale.kivi.database.Database;
import com.leanxcale.kivi.database.Index;
import com.leanxcale.kivi.database.Table;
import com.leanxcale.kivi.session.Session;
import com.leanxcale.kivi.session.SessionFactory;
import com.leanxcale.kivi.session.Credentials;
import com.leanxcale.kivi.session.Settings;

Settings settings = new Settings()
.credentials(user, pass, "tpch")

Session session = SessionFactory.newSession("kivi:lxis://lxserver:9876", settings);
Database database = session.database();

In the following examples, the session creation is not included but is still needed to start any session. The import section is commonly shown so that you can be aware of the classes you need to import, but then the part of the session initialization is removed.

3.2.2. Enabling security

If your LeanXcale instance is configuring with security, you will have to generate an application certificate in order to allow your application to connect to LeanXcale.

Once this is done, configure the settings to enable security and provide the path to the certificate file.

Security security = new Security("/path/to/certificate/file.kcf");

Settings settings = new Settings()
    .credentials(user, pass, "tpch")
    .setSecurity(security);

Session session = SessionFactory.newSession("kivi:lxis://lxserver:9876", settings);
Database database = session.database();

3.2.3. Inserting, updating and deleting

Insert one Record(Tuple):

Table people = database.getTable("person");
Tuple person = people.createTuple();

//Fill in tuple fields
person.putLong("id", 1L).
      .putString("name", "John")
      .putString("lastName", "Doe")
      .putString("phone", "555333695")
      .putString("email", "johndoe@nowhere.no")
      .putDate("birthday",Date.valueOf("1970-01-01"))
      .putInt("numChildren", 4);

//Insert tuple
people.insert(person);

//Tuples are sent to the datastore when COMMIT is done
session.commit();

Insert using a Sequence:

Table people = database.getTable("person");
Tuple person = people.createTuple();

long personId = database.getSequence("personId").nextVal();

person.putLong("id", personId)
      .putString("name", "John")
      .putString("lastName", "Doe")
      .putString("phone", "555333695")
      .putString("email", "johndoe@nowhere.no")
      .putDate("birthday",Date.valueOf("1970-01-01"))
      .putInt("numChildren", 4);

people.insert(person);

Insert a Record containing a BLOB that is read from a file:

Table people = database.getTable("person");
Tuple person = people.createTuple();

person.putString("name", "John")
      .putString("lastName", "Doe")
      .putString("phone", "555333695");

OutputStream os = people.createBlob(person, "photo");
Files.copy(Paths.get("/path/to/john.jpeg"), os);

people.insert(person);

Update a Record:

// Tuple person to be updated has to be previosly retrieved through
// a get()
Tuplekey key = table.createTupleKey();
key.putLong("id", 0L);

Tuple person = table.get(key);
person.putString("phone", "anotherPhone")
      .putString("email", "johndoe@somewhere.some")

people.update(person);

Delete a Record by its Key:

Table people = database.getTable("person");
TupleKey johnKey = people.createTupleKey();
johnKey.putLong("id", key);

people.delete(johnKey);

Delete with a Filter:

import static com.leanxcale.kivi.query.aggregation.Aggregations.*;
import static com.leanxcale.kivi.query.expression.Constants.*;
import static com.leanxcale.kivi.query.expression.Expressions.*;
import static com.leanxcale.kivi.query.filter.Filters.*;
import static com.leanxcale.kivi.query.projection.Projections.*;

// Simple filter
people.delete(Filters.eq("name", string("John")));

// Expression usage
people.delete(Filters.gt(
        div( field("weight"),
          pow(field("height"),2)),
          float(26)
      ));

3.2.4. Reading and scanning Data

Retrieving a data row directly by its Key:

import com.leanxcale.kivi.tuple.Tuple;
import com.leanxcale.kivi.tuple.TupleKey;

Tuplekey key = table.createTupleKey();
key.putLong("id", 0L);

Tuple tuple = table.get(key);

Perform a simple SCAN with the basic options:

import static com.leanxcale.kivi.query.aggregation.Aggregations.*;
import static com.leanxcale.kivi.query.expression.Constants.*;
import static com.leanxcale.kivi.query.expression.Expressions.*;
import static com.leanxcale.kivi.query.filter.Filters.*;
import static com.leanxcale.kivi.query.projection.Projections.*;

Table people = database.getTable("person");
TupleKey min = people.createTupleKey();
min.putLong("id", 20);

TupleKey max = people.createTupleKey();
max.putLong("id", 30L);

people.find()
        .min(min)
        .max(max)
        .forEach(tuple->processTuple(tuple));

// Max 20 results
people.find()
        .first(20)
        .forEach(tuple->processTuple(tuple));

Scan a table using a Secondary Index:

Table table people = database.getTable("person");
TupleKey minKey = new TupleKey(new Field("dni", string(10)));
minKey.putString("111111111Q");

people.find()
        .index("dniIdx")
        .indexMin(minKey)
        .forEach(tuple->processTuple(tuple));

Advanced Finding and Filtering:

The following examples demonstrate how to define a set of filters to be pushed to the KiVi datastore to obtain a result that matches specified conditions.

import static com.leanxcale.kivi.query.aggregation.Aggregations.*;
import static com.leanxcale.kivi.query.expression.Constants.*;
import static com.leanxcale.kivi.query.expression.Expressions.*;
import static com.leanxcale.kivi.query.filter.Filters.*;
import static com.leanxcale.kivi.query.projection.Projections.*;

Table people = database.getTable("person");

// Basic comparisons
people.find()
      .filter(gt("numChildren", 4).and(eq("name", string("John"))))
      .forEach(tuple->processTuple(tuple));

// Between
Date minDate = Date.valueOf("1900-01-01");
Date maxDate = Date.valueOf("2000-01-01");

people.find()
      .filter(between("birthday", date(minDate), date(maxDate))
      .forEach(tuple->processTuple(tuple));

// Using a Expression with operators
people.find()
      .filter(gt("numChildren",sub(field("numRooms"),int(1))))
      .forEach(tuple->processTuple(tuple));

Project:

The concept of the projection is the set of fields from the table you want to obtain from the table. The project may also include operations over the fields to be retrieved.

import static com.leanxcale.kivi.query.aggregation.Aggregations.*;
import static com.leanxcale.kivi.query.expression.Constants.*;
import static com.leanxcale.kivi.query.expression.Expressions.*;
import static com.leanxcale.kivi.query.filter.Filters.*;
import static com.leanxcale.kivi.query.projection.Projections.*;

Table people = database.getTable("person");

// Basic inclusion
people.find()
      .project(include(asList("name", "lastName", "birthdate")))
      .forEach(tuple->{
            String name = tuple.getString("name");
            String lastname = tuple.getString("lastName");
            Date date = tuple.getDate("birthdate");
      });

// SELECT name, Lastname, weight/height^2 AS imc FROM person
// Alias and expression usage
people.find()
      .project(compose(Arrays.asList(
        alias("name"),
        alias("lastName"),
        alias("imc",div(field("weight"),pow(field("height"),2)))
        )))
      .forEach(tuple->{
        String name = tuple.getString("name");
        String lastName = tuple.getString("lastName");
        float imc = getFloat("imc");
});

Aggregations:

The most common simple aggregations can also be performed through the KiVi direct API. You must define an initial array with the fields to be used as the group by the key (if any), and then a list of aggregation expressions over some fields.

import static com.leanxcale.kivi.query.aggregation.Aggregations.*;
import static com.leanxcale.kivi.query.expression.Constants.*;
import static com.leanxcale.kivi.query.expression.Expressions.*;
import static com.leanxcale.kivi.query.filter.Filters.*;
import static com.leanxcale.kivi.query.projection.Projections.*;

// Simple aggregation
int numPeople =
    people.find()
      .aggregate(emptyList(), count("numPeople"))
      .iterator.next().getLong("numPeople");

// Group By aggregation
people.find()
      .aggregate(.asList("name"),.asList(
        count("numPeople"),
        avg("averageHeight", field("height"))
        avg("averageIMC",div(field("weight"),pow(field("height"),2)))
        ))
      .forEach(tuple->{
        String name = tuple.getString("name");
        long numPeople = tuple.getLong("numPeople");
        float avgHeight = tuple.getFloat("averageHeight");
        float avgIMC = tuple.getFloat("averageIMC");
    });

// Filtering before aggregate
Date date = Date.valueOf("1970-01-01");

people.find()
      .filter(gt("birthdate", date(date)))
      .aggregate(.asList("name"),.asList(
        count("numPeople"),
        avg("averageHeight", field("height"))
        avg("averageIMC",div(field("weight"),pow(field("height),2)))
        ))
      .forEach(tuple->{
        String name = tuple.getString("name");
        long numPeople = tuple.getLong("numPeople");
        float avgHeight = tuple.getFloat("averageHeight");
        float avgIMC = tuple.getFloat("averageIMC");
    });

//Filtering after aggregate
people.find()
      .aggregate(.asList("name"),.asList(
        count("numPeople"),
        avg("averageHeight", field("height"))
        avg("averageIMC",div(field("weight"),pow(field("height),2)))
        ))
      .filter(gt("averageIMC",float(26)).or(ge("numPeople",100)))
      .forEach(tuple->{
        String name = tuple.getString("name");
        long numPeople = tuple.getLong("numPeople");
        float avgHeight = tuple.getFloat("averageHeight");
        float avgIMC = tuple.getFloat("averageIMC");
    });

3.2.5. Transaction Management

The KiVi Java API is configurable to be transactional or not. If the session is not configured as transactional, then the commit operation forces data to be flushed into the datastores. If the session is configured as transactional, then the internal transaction management capabilities ensure conflict checks, durability, persistence, and consistency.

Settings settings = new Settings(new Credentials(user, pass, "tpch")));
settings.transactional();

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:

Streaming Twitter data into a LeanXcale database - Ingest huge amounts of data with KiVi and query that data using standard SQL.