Scala

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

Scala features Java interoperability with a JVM as its standard backend. So, all Scala classes are Java classes. However, some limitations exist in a few cases, so we need to use the LeanXcale JDBC driver.

:sourcedir: {examplesdir}/scala

2.2. Installation

You can download the LeanXcale JDBC driver from the Drivers page, which can be installed and configured as a dependency for your Scala application.

2.2.1. Dependency management

Two installation options are available for the LeanXcale JDBC dependency, including a manual installation or using the dependency manager.

Option A: Manual install

Manually install the LeanXcale JDBC connector:

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

Option B: Scala Dependency Manager

The Scala dependency manager sbt installs the LeanXcale JDBC driver from the public Maven repository simply by you adding the following dependency into the build.sbt file with the format:

libraryDependencies += groupId % artifactId % version

An example of a build.sbt file with the Maven repository configured and the dependency added looks like:

name := "scalaJDBC"

version := "0.1"

scalaVersion := "2.12.6"

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

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

Local Maven

As a third option, you can install the JDBC jar dependency manually in your local Maven repository. Then, adding this repository into the sbt configuration enables the driver to be found. This alternate method assumes you previously downloaded the dependency manually, as in Option A.

In a terminal, run the following:

mvn install:install-file -Dfile=qe-driver-1.9.9-jdbc-client.jar -
  DgroupId=com.leanxcale -DartifactId=qe-driver -Dversion=1.9.9 -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 can use the JDBC driver in your project.

2.3. Quick Start

This section includes a set of code snippets for common operations when using the LeanXcale JDBC driver with a Scala application.

2.3.1. Connect

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

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

In case you are connecting a database with Security, you must add the parameter to the JDBC URL specifying that you want to use a secure connection.

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

The AutoCommit property is set to true by default. If you execute massive loads and want to change this to false, then call the setAutoCommit() method:

    connection.setAutoCommit(false)

2.3.2. Close

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

    connection.close()

2.3.3. SQL Queries

When using the JDBC driver, you must query the database with either a Statement or a PreparedStatement.

The following is 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))
        }
      }
    }

With a PreparedStatement, you must 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))
        }
      }
    }

For each of these statement types, calling the executeQuery() method return a ResultSet that contains the returned result of the query.

2.3.4. Modify data

To change data with operations such as INSERT, UPDATE, or DELETE, call the executeUpdate() method.

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

Insert one record:

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

Update records:

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

Delete 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. Create, modify and drop

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

Tables

The following example creates 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()

Then, to drop the table:

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

Sequences

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

    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()

Alternatively, sequences can be created as standalone objects:

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

Indexes

Indexes must 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 want unique indexes to avoid duplicate values, then apply the following query:

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

2.3.6. Queries from many tables

To retrieve information from many tables using a single query, you must join these tables, as in the following:

    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

The KiVi Direct API connector can be initialized through the two options of a manual installation or using the dependency manager.

Option A: Manual install

Manually install the KiVi Direct API connector:

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

Option B: Scala Dependency Manager

The Scala dependency manager sbt installs the KiVi Direct API from the LeanXcale Maven public repository simply by you adding the dependency into the build.sbt file using the format:

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

The following is an example of a sbt file with the added repository and required dependency:

name := "scalaJDBC"

version := "0.1"

scalaVersion := "2.12.6"

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

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

Local Maven

As a third option, the KiVi Direct API dependency can be installed in your local Maven repository. Then, adding this repository into your sbt configuration enables the driver to be found. You must first download the KiVi Direct API manually, as in Option A.

In a terminal, run the following:

mvn install:install-file -Dfile=kivi-api-1.9.9-direct-client.jar -
DgroupId=com.leanxcale -DartifactId=kivi-api -Dversion=1.9.9 -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 so that you can use the KiVi Direct API driver in your project.

3.2. Quick start

This section includes a set of code snippets for common operations using the KiVi JAVA direct API from within a Scala application.

3.2.1. Connect and start a session

The first session automatically connects, and the API library manages the connections for you.

In the credentials, you set 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.Settings;

    val settings = Settings.parse("lx://lxserver:9876/db@" + user);

    val session = SessionFactory.newSession(settings);
    val database = session.database();

In case you are connecting to a LeanXcale database with Security, you must provide the connection with a valid path to an application certificate.

val settings = Settings.parse("lx://lxserver:9876/db@" + user + ";cert=/path/to/certificate/file.kcf");

Learn how to generate your certificate here.

Session creation is required to connect to LeanXcale.

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

The import section shows the classes you must first import, but the session initialization is not included

3.2.2. Insert, update and delete

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

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

Delete 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. Read and scan Data

Retrieve a data row directly by a 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);

A 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 with 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. Find and Filter

To retrieve a result that matches a set of defined conditions, include a set of filters that are 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, which can also perform operations on the fields as in the following:

    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 performed through the Spark API.

First, define an array with the fields to be used as the "Group By" key (if any). Then, follow this with a list of aggregation expressions, as in:

    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 being either transactional or not transactional.

Not transactional: the commit operation forces the data to be flushed to the datastores.

Transactional: conflict checks, durability, persistence, and consistency are managed by the transaction management capabilities.

    val pass: Array[Char] = Array('P','A','S','S','4','U','S','S','E','R','1')
    val settings = new Settings();
    settings.setUser("user1");
    settings.setDatabase("dbtest");
    
    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:

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.