Java

1. Two interfaces to access the same data

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

A 2003 ANSI SQL interface: It is powerful and easy to use, but can be slow. It is most suitable for complex queries (e.g. multijoin queries)

A proprietary key-value interface called KiVi. It is fast and provides all functionality except for JOINs. It is most suitable for simpler queries where the overhead of SQL would be too costly (e.g. updates or inserts)

Both interfaces interact with the same data, and can even be part of the same transactions.

2. SQL

2.1. Installation

To use the JDBC driver you simply have to define the maven dependency:

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

To use it, don’t 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, you can download the Java JDBC driver for LeanXcale from the Drivers page. If you do it this way, you’ll have to execute this command to include it in your local maven repository:

mvn install:install-file
-Dfile=<path-to-file>
-DgroupId=com.leanxcale
-DartifactId=qe-driver
-Dversion=1.5.4
-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

In this section you will find 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 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

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

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

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

    conn.setAutoCommit(false);

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

    conn.close();

Using an external pool

Another way to get a connection is to use a connection pool.

Example using hikariCP JDBC connection pool:

    final HikariConfig configPool = new HikariConfig();
    configPool.setMaximumPoolSize(20);
    configPool.setDriverClassName("com.leanxcale.client.Driver");
    configPool.setJdbcUrl("jdbc:leanxcale://172.17.0.2:1529/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 launch SQL statements to the database, you require a Statement or PreparedStatement instance. Once you have a Statement or PreparedStatement, you can execute a query. This will return a ResultSet instance, where you get the entire 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, where it’s necessary to bind 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 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.

Tables

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

And drop it: Example to create a table:

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

Sequences

Another examples of object could be Sequences. We can create them with the table and associated 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 one standalone sequence:

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

Indexes

Unlike sequences, we can only create indexes after the table is created:

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

If we want unique indexes to avoid duplicated rows, we can create it with this sentence:

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

2.2.4. Inserting, updating and deleting

To change data (perform an INSERT, UPDATE, or DELETE) we use the executeUpdate() method. This method is similar to the method executeQuery() used to launch a SELECT statement, but it doesn’t return a ResultSet; instead it returns the number of rows affected by the INSERT, UPDATE, or DELETE statement.

Inserting one Record:

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

Updating records:

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

Deleting 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 are going to recover information from many tables launching only one 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 have to define the maven dependency to the kivi-api library, with the desired version.

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

To use it, don’t 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, you can download the Java KiVi Direct API driver from the Drivers page.

3.2. Quick Start

In the following pages you can find 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

Starting a Session Example. Note that when you start a session It will automatically connect, If It is the first session, and the API library will manage connections for you.

In credentials you are setting the user, password and the name of the database ("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 next examples, session creation is not included, but this is needed to start any session. In fact, the import section is commonly shown so you can be aware of the classes that you need to be imported, but then the part of session initialization has been removed.

3.2.2. Inserting, updating and deleting

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

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

Deleting a Record by 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.3. Reading and scanning Data

Getting a Data Row directly by 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);

Simple SCAN with 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 show how to define a set of filters that will be pushed to the KiVi datastore to get a result matching a set of 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 that you want to get 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:

Most common simple aggregations can also be done through the KiVi direct API. You need to define a first array with the fields to be used as the group by 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.4. Transaction Management

The KiVi JAVA API can be configured as transactional or not. If the session is not configured as transactional, the commit operation will force the data to be flushed to the datastores. If the session is configured as transactional, the internal transaction management capabilities will ensure conflict checks, durability, persistence and consistency.

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

4. Other Examples

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

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