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.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 |
Insert one record:
val sql = "INSERT INTO person VALUES (100, 'John', 'Doe', '555333695')"
st.executeUpdate(sql)
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: