Scala

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. Scala JDBC

Scala has Java interoperability and the standard Scala backend is a JVM. So, all Scala classes are Java classes, even though there are some limitations in a few cases.

This means that we can use the LeanXcale JDBC driver.

2.2. Installation

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

The driver can be installed and configured as a dependency for your Scala application.

2.2.1. Dependency management

You have two installation options for the LeanXcale JDBC dependency, A) manual installation, or B) using the dependency manager.

Option A: Manual install

To manually install the LeanXcale JDBC connector:

Download the connector from the Drivers page.
Add the qe-driver-1.5.4-jar-with-dependencies.jar to your project lib directory.

Option B: Scala Dependency Manager

You can use the Scala dependency manager sbt to install the LeanXcale JDBC driver from the public Maven repository just by adding the dependency in the build.sbt file using the following format:

libraryDependencies += groupId % artifactId % version

An example of build.sbt file with the Maven repository configured and the dependency added would be:

name := "scalaJDBC"

version := "0.1"

scalaVersion := "2.12.6"

libraryDependencies += "com.leanxcale" % "qe-driver" % "1.5.4"

resolvers += "LeanXcale Maven Repository" at "https://nexus.leanxcale.com/repository/maven-public/"

Local Maven

As a third option, you can also install the JDBC jar dependency manually in your local Maven repository and then add that repository in your sbt configuration so that the driver can be found. This method assumes you already have downloaded the dependency manually using option A.

In a terminal, run:

mvn install:install-file -Dfile=qe-driver-1.5.4-jdbc-client.jar -
  DgroupId=com.leanxcale -DartifactId=qe-driver -Dversion=1.5.4 -Dpackaging=jar

Add the local repository to the build.sbt file:

resolvers += "Local Maven Repository" at "file://<pathToYourLocalRepo>"

The dependency can now be found by sbt and you will be able to use the JDBC driver in your project.

2.3. Quick Start

In this section you will find a set of code snippets for some common operations using the LeanXcale JDBC driver from a Scala application.

2.3.1. Connecting

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 standard URL, user and password:

    val driver = "com.leanxcale.client.Driver"
    val url = "jdbc:leanxcale://123.45.67.89:1529/dbtest"
    val username = "user1"
    val password = "pass4user1"
    var connection:Connection = null
    Class.forName(driver)
    connection = DriverManager.getConnection(url, username, password)

The autocommit property true by default. If you are executing massive loads, and want to change it to false, use the setAutoCommit() method:

    connection.setAutoCommit(false)

2.3.2. Closing

To close the database connection, call the close() method:

    connection.close()

2.3.3. SQL Queries

Using the JDBC driver, you need to launch a query to the database with either a Statement or a PreparedStatement.

A sample Statement:

    try{
      val st = connection.createStatement()
      try{
        val rs = st.executeQuery("SELECT * FROM person WHERE lastName = 'Doe'")
        while(rs.next()){
          println("Column 1 returned")
          println(rs.getString(1))
        }
      }
    }

When using a PreparedStatement, you need to bind the value to the query:

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

      try{
        val rs = pst.executeQuery()
        while(rs.next()){
          println("Column 1 returned")
          println(rs.getString(1))
        }
      }
    }

Both statement types, using the executeQuery() method, return a ResultSet which contains the result of the query.

2.3.4. Modifying data

To change data with operations like INSERT, UPDATE or DELETE, you use the executeUpdate() method.

The executeUpdate() method the number of rows affected by the INSERT, UPDATE or DELETE statement, not a ResultSet.

Inserting one record:

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

Updating records:

    val sqlUpdate = "UPDATE person SET passport = '666668888' WHERE id in (100, 101)"
    st.executeUpdate(sqlUpdate)

Deleting records:

    val id = 100
    val pst = connection.prepareStatement("DELETE FROM Registration WHERE id = ?")
    pst.setInt(1, id)
    val rowsDeleted = pst.executeUpdate()
    println(rowsDeleted + " rows deleted")

2.3.5. 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:

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

    val stCreate = connection.createStatement()
    stCreate.execute(createTable)
    stCreate.close()

And to drop it:

    val stDrop = connection.createStatement()
    stDrop.execute("DROP TABLE person")
    stDrop.close()

Sequences

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

    val createTableSeq = "CREATE TABLE person (" +
      "  id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY" +
      ", name VARCHAR " +
      ", lastName VARCHAR " +
      ", phone VARCHAR " +
      ", PRIMARY KEY (id)" +
      ")"
    val stTableSeq = connection.createStatement()
    stTableSeq.execute(createTableSeq)
    stTableSeq.close()

And also, they can be created standalone:

    val stSeq = connection.createStatement()
    stSeq.execute("CREATE SEQUENCE IF NOT EXISTS personSeq")
    stSeq.close()

Indexes

Indexes needs to be created after the table is created, unlike sequences:

    val stIndex = connection.createStatement()
    stIndex.execute("CREATE INDEX lastNameIdx ON person (lastName)")
    stIndex.close()

If you would like to have unique indexes to avoid duplicate values:

    val stIndexUnique = connection.createStatement()
    stIndexUnique.execute("CREATE UNIQUE INDEX lastNameIdxUnique ON person (lastName)")
    stIndexUnique.close()

2.3.6. 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:

    val stmt = connection.createStatement()
    val query ="SELECT name, lastname, isbn FROM person INNER JOIN books ON person.id = books" +
      ".authorid"
    val rs = stmt.executeQuery(query)
    println("Name  LastName ISBN")

    while (rs.next()) {
      val name = rs.getString("name")
      val lastname = rs.getString("lastname")
      val isbn = rs.getInt("isbn")
      println(name + "  " + lastname+"   "+isbn)
    }

3. KiVi

3.1. Installation

In order to use the KiVi Direct API connector there are two options: A) manual installation, or B) using the dependency manager.

Option A: Manual install

To manually install the KiVi Direct API connector:

Download the connector from the Drivers page.
Add the kivi-api-1.5.4-jar-with-dependencies.jar to your project lib directory.

Option B: Scala Dependency Manager

You can use the Scala dependency manager sbt to install the KiVi Direct API from the LeanXcale Maven public repository and add the dependency in the build.sbt file using this format:

libraryDependencies += "com.leanxcale" % "kivi-api" % "1.5.4"

An example of a sbt file adding the repository and the required dependency would be the following:

name := "scalaJDBC"

version := "0.1"

scalaVersion := "2.12.6"

libraryDependencies += "com.leanxcale" % "kivi-api" % "1.5.4"

resolvers += "LeanXcale Maven Repository" at "https://nexus.leanxcale.com/repository/maven-public/"

Local Maven

As a third option, you can install the KiVi Direct API dependency in your local Maven repository and then add that repository in your sbt configuration so that the driver can be found. Notice that for this option you need to download the KiVi Direct API manually following the steps on option A.

In a terminal, run:

mvn install:install-file -Dfile=kivi-api-1.5.4-direct-client.jar -
DgroupId=com.leanxcale -DartifactId=kivi-api -Dversion=1.5.4 -Dpackaging=jar

Add the local repository to the build.sbt file:

resolvers += "Local Maven Repository" at "file://<pathToYourLocalRepo>"

The dependency can now be found by sbt and you will be able to use the KiVi Direct API driver in your project.

3.2. Quick start

In this section you will find a set of code snippets for some common operations using the KiVi JAVA direct API from a Scala application.

3.2.1. Connecting and starting a session

The first session will automatically connect and the API library will manage connections for you.

In credentials you are setting the user, password and the name of the database (dbtest) .

    import java.util.Arrays

    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;

    val pass: Array[Char] = Array('P','A','S','S','4','U','S','S','E','R','1')
    val credentials: Credentials = new Credentials().setUser("user1").setPass(pass).setDatabase
    ("dbtest")
    val settings = new Settings()
      .credentials(credentials)

    val session = SessionFactory.newSession("kivi:zk//zkserver:2181", settings);
    val database = session.database();

Session creation is needed to be able to connect to LeanXcale.

For brevity, in the examples below, session creation has not been included in the sample code.

The import section shows the classes you need to import, but session initialization is not included.

3.2.2. Inserting, updating and deleting

Inserting one Record(Tuple):

    val people = database.getTable("person");
    val 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"))
      .putInteger("numChildren", 4);

    //Insert tuple
    people.insert(person);

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

Insert using a Sequence:

    val people = database.getTable("person");
    val person = people.createTuple();

    val 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"))
      .putInteger("numChildren", 4);

    people.insert(person);

Insert a Record containing a BLOB read from a file:

    val people = database.getTable("person");
    val 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()
    val key = table.createTupleKey();
    key.putLong("id", 0L);

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

    people.update(person);

Deleting a Record by Key:

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

    people.delete(johnKey);

Delete with a Filter:

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

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

    // Expression usage
    people.delete(Filters.gt(
      Expressions.div( Expressions.field("weight"),
        Expressions.pow(Expressions.field("height"),Constants.integer(2))),
      Constants.flt(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;

    val key = person.createTupleKey();
    key.putLong("id", 0L);

    val tuple = person.get(key);

Simple SCAN with basic options:

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

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

    val 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:

    val people = database.getTable("person");
    val minKey = new TupleKey(new Field("dni", Type.STRING);
    minKey.putString("dni","111111111Q");

    val dniIdx = database.getIndex("person", "dniIdx")
    people.find()
      .index(dniIdx)
      .indexMin(minKey)
      .forEach(tuple=>processTuple(tuple));

3.2.4. Finding and Filtering

To get a result matching a set of conditions, define a set of filters to be pushed to the KiVi datastore:

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

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

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

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

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

    // Using a Expression with operators
    people.find()
      .filter(Filters.gt("numChildren",Expressions.sub(Expressions.field("numRooms"),
        Constants.integer(1))))
      .foreach(tuple->processTuple(tuple));

3.2.5. Projections

A projection is the set of fields to be retrieved from the table.

The projection can also perform operations on the fields:

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

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

    // Basic inclusion
    people.find()
      .project(Projections.include(Arrays.asList("name", "lastName", "birthdate")))
      .foreach(tuple->{
        val name = tuple.getString("name");
        val lastname = tuple.getString("lastName");
        val date = tuple.getDate("birthdate");
      });

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

3.2.6. Aggregations

Most common simple aggregations can be done through the Spark API.

Define an array with the fields to be used as the "group by" key (if any), followed by a list of aggregation expressions:

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

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

    // Group By aggregation
    people.find()
      .aggregate(Arrays.asList("name"),Arrays.asList(
        Aggregations.count("numPeople"),
        Aggregations.avg("averageHeight", Expressions.field("height")),
        Aggregations.avg("averageIMC",Expressions.div(Expressions.field("weight"),
          Expressions.pow(Expressions.field("height"),Constants.integer(2)))
        )))
      .forEach(tuple=>{
        val name = tuple.getString("name");
        val numPeople = tuple.getLong("numPeople");
        val avgHeight = tuple.getFloat("averageHeight");
        val avgIMC = tuple.getFloat("averageIMC");
      });

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

    people.find()
      .filter(Filters.gt("birthdate", date))
      .aggregate(Arrays.asList("name"),Arrays.asList(
        Aggregations.count("numPeople"),
        Aggregations.avg("averageHeight", Expressions.field("height")),
        Aggregations.avg("averageIMC",Expressions.div(Expressions.field("weight"),
          Expressions.pow(Expressions.field("height"),Constants.integer(2))))))
      .forEach(tuple=>{
        val name = tuple.getString("name");
        val numPeople = tuple.getLong("numPeople");
        val avgHeight = tuple.getFloat("averageHeight");
        val avgIMC = tuple.getFloat("averageIMC");
      });

    //Filtering after aggregate
    people.find()
      .aggregate(Arrays.asList("name"),Arrays.asList(
        Aggregations.count("numPeople"),
        Aggregations.avg("averageHeight", Expressions.field("height")),
        Aggregations.avg("averageIMC",Expressions.div(Expressions.field("weight"),
          Expressions.pow(Expressions.field("height"), Constants.integer(2))))))
      .filter(Filters.gt("averageIMC",Constants.flt(26)).or(Filters.ge("numPeople",
        Constants.flt(100))))
      .forEach(tuple=>{
        val name = tuple.getString("name");
        val numPeople = tuple.getLong("numPeople");
        val avgHeight = tuple.getFloat("averageHeight");
        val avgIMC = tuple.getFloat("averageIMC");
      });

3.2.7. Transaction Management

The KiVi JAVA API session can be configured as not transactional or as transactional.

Not Transactional: The commit operation will force the data to be flushed to the datastores.

Transactional: Conflict checks, durability, persistence and consistency will be managed by transaction management capabilites.

    val pass: Array[Char] = Array('P','A','S','S','4','U','S','S','E','R','1')
    val credentials: Credentials = new Credentials().setUser("user1").setPass(pass).setDatabase
    ("dbtest")
    val settings = new Settings()
      .credentials(credentials)
    settings.transactional();

4. Other Examples

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

To find out how you can connect your Scala application to LeanXcale using both JDBC and the direct KiVi API, see:

Connecting LeanXcale with Scala.