Skip to main content

MonetDBLite for Java

Following the footsteps of MonetDBLite for R and MonetDBLite for Python, we now have MonetDBJavaLite which deploys MonetDBLite in a JVM with JDBC support. It has been tested on Linux, Mac and Windows. In the "lite" versions of MonetDB, both client and server run within the same process, saving eventual inter-process communication such as a socket connection. Although the code is still somewhat experimental, it’s worth trying out, and report your stories and complains.

MonetDBJavaLite connections

We provide two APIs for MonetDBJavaLite: an Embedded API (non-standard) and the standard JDBC API. The former API has been introduced for certain scenarios where performance is critical, such as when dealing with large result sets. But the better performance comes at the cost of less portability. The Javadoc of this API can be found in our website.

A MonetDBLite JDBC connection is very similar to a regular connection with a MonetDB server. Differences between the two connections are:

  • Only one database is allowed per JVM process. However multiple connections to the same database is allowed within the same process.
  • MonetDBLite is a compacted version of MonetDB with some features removed in order to reduce  the size of the library, e.g. the GEOM  module, MERGE TABLE  and REMOTE TABLE, the JSON module, and the Data Vaults extension.
  • The authentication scheme is absent from MonetDBLite.
  • MonetDBJavaLite native code uses a separate heap from the JVM, which means that connections and result sets descriptions must be explitily closed to avoid memory leaks.
  • No two concurrent MonetDBJavaLite processes can use the same database farm simultaneously.

Embedded API

Start a database

A database must be started before any connection can be made. When starting the database, one can specify a path to the database farm. If no such path is supplied, an in-memory connection will be automatically established instead. In an in-memory connection data is not persisted on disk. In other hand transactions are held in-memory thus more performance is obtained.

Path directoryPath = Files.createTempDirectory("testdb");
//start the database with the silent flag set (no debugging output) and disable the sequential pipeline.
MonetDBEmbeddedDatabase.startDatabase(directoryPath.toString(), true, false);
MonetDBEmbeddedConnection connection = MonetDBEmbeddedDatabase.createConnection();
connection.executeUpdate("CREATE TABLE example (words text)");
//...
connection.close();
MonetDBEmbeddedDatabase.stopDatabase(); //Don’t forget to shutdown at the end :)

Transactions

After a connection has been established, one can send  queries to the embedded database and retrieve the results. The connection starts on the auto-commit mode by default. The methods void startTransaction(), void commit() and void rollback() can be used for transaction management. The methods Savepoint setSavepoint(), Savepoint setSavepoint(String name), void releaseSavepoint(Savepoint savepoint) and void rollback(Savepoint savepoint) handle savepoints in transactions.

Update queries

For update queries (e.g. INSERT, UPDATE and DELETE), the method int executeUpdate(String query) is used to send update queries to the server and get the number of rows affected.

connection.startTransaction();
int numberOfInsertions = connection.executeUpdate("INSERT INTO example VALUES ('monetdb'), ('java'), (null)");
connection.commit();

Queries with result sets

For queries with result sets, one can use the method QueryResultSet executeQuery(String query) to send a query to the server, and retrieve the results using a QueryResultSet instance.

The result set metadata can be retrieved with the methods int getNumberOfRows(), int getNumberOfColumns(), void getColumnNames(String[] input) and void getColumnTypes(String[] input).

There are several ways to retrieve the results of a query. The family of methods T get#TYPE#ByColumnIndexAndRow(int column, int row) and
T get#TYPE#ByColumnNameAndRow(String columnName, int row) retrieve a single value from the result set. The column and row indexes for these methods (and the other methods in this family) start from 1, same as  in JDBC.

A column of values can be retrieved using the family of methods void get#TYPE#ColumnByIndex(int column, T[] input, int offset, int length) and void get#TYPE#ColumnByName(String name, T[] input, int offset, int length). Note  that the input array must be initialized beforehand. If there is no desire to provide the offset and length parameters, the methods void get#Type#ColumnByIndex(int column, T[] input) and get#Type#ColumnByName(String columnName, T[] input) can be used instead.

QueryResultSet qrs = connection.executeQuery("SELECT words FROM example");
int numberOfRows = qrs.getNumberOfRows();
int numberOfColumns = qrs.getNumberOfColumns();
String[] columnNames = new String[numberOfColumns];
qrs.getColumnNames(columnNames); //returns ['words']

String singleWord = qrs.getStringByColumnIndexAndRow(1, 1); //gets 'monetdb'
String[] wordsValues = new int[numberOfRows];
qrs.getStringColumnByIndex(1, wordsValues); //returns ['words', 'java', null]
qrs.close(); //Don’t forget :)

To check if a boolean value is NULL, one can use the method boolean checkBooleanIsNull(int column, int row) of the class QueryResultSet. For all other data types, one can use the methods boolean Check#Type#IsNull(T value) of the class NullMappings.

Append data to a table

To append new data to a table, one can use the method int appendColumns(Object[] data) from the class MonetDBTable. The data should come as an array of columns, where each column has the same number of rows, and each array class corresponds to the mapping defined above. To insert null values, use the constant T get#Type#NullConstant() from the class NullMappings.

connection.executeUpdate("CREATE TABLE interactWithMe (dutchGoodies text, justNumbers int)");
MonetDBTable interactWithMe = connection.getMonetDBTable("interactWithMe");
String[] goodies = new String[]{"eerlijk", "lekker", "smullen", "smaak", NullMappings.getObjectNullConstant<String>()};
int[] numbers = new int[]{2, 3, NullMappings.getIntNullConstant(), -1122100, -23123};
Object[] appends = new Object[]{goodies, numbers};
interactWithMe.appendColumns(appends);

Data type mapping

The Java programming language is a strong typed language, thus the mapping between MonetDB SQL types and Java classes/primitives must be explicit. The usage of Java primitives is favored for the most common MonetDB SQL types, hence making less object allocations. However for the more complex SQL types, such as Strings and Dates, the map is made to Java Classes, while matching the JDBC specification.

One important feature of MonetDB is that the SQL NULL values are mapped into the system's minimum values. In MonetDBJavaLite, this feature persists for primitive types. NB: for the Java Classes mapping, SQL NULL values are translated into null objects! Other more rare data types such as geometry, json, inet, url, uuid and hugeint are missing. These types were removed from MonetDBLite to reduce the size of the library. Please check the GitHub documentation for details on data type mapping.

Other methods provided in this API include Prepared Statements which are detailed in the GitHub documentation and the Javadocs.

JDBC API

To start a JDBC embedded connection, one must provide a JDBC URL in the format: jdbc:monetdb:embedded:[<directory>], where directory is the location of the database. To connect to an in-memory database the directory must be :memory: or not present.

When starting a JDBC Embedded connection, it checks if there is a database instance running in the provided directory, otherwise an exception is thrown. While closing, if it's the last connection, the database will shut down automatically.

//Connection con = DriverManager.getConnection("jdbc:monetdb:embedded:/home/user/myfarm"); //POSIX
//Connection con = DriverManager.getConnection("jdbc:monetdb:embedded:C:\\user\\myfarm"); //Windows
//Connection con = DriverManager.getConnection("jdbc:monetdb:embedded::memory:"); //in-memory mode
Statement st = con.createStatement();
st.executeUpdate("CREATE TABLE jdbcTest (justAnInteger int, justAString varchar(32))");
st.executeUpdate("INSERT INTO jdbcTest VALUES (1, 'testing'), (2, 'jdbc')");
ResultSet rs = st.executeQuery("SELECT justAnInteger, justAString from test1;");
while (rs.next()) {
    int justAnInteger = rs.getInt(1);
    String justAString = rs.getString(2);
    System.out.println(justAnInteger + " " + justAString);
}
rs.close(); //Don't forget! :)
st.close();
con.close();

Please check the GitHub documentation for differences between MonetDB’s JDBC socket and embedded connections.

Distribution

The project is hosted and maintained on Github: https://github.com/hannesmuehleisen/MonetDBLite-Java

Two JAR files are distributed: monetdb-java-lite (~6.4Mb) and monetdb-jdbc-new (~150Kb). The former depends on the later and contains MonetDBLite library adapted for the JVM. It is compatible with JVM 8 onwards only. This JAR also provides native libraries for 64-bit Linux, MacOS X and Windows. The later is a fork of MonetDB’s JDBC driver and is used for JDBC connections.

Both JARs are hosted on Maven Central repository.

  • Apache Maven:
<dependency>
    <groupId>monetdb</groupId>
    <artifactId>monetdb-java-lite</artifactId>
    <version>2.33</version>
</dependency>
  • Gradle: compile 'monetdb:monetdb-java-lite:2.33'

Otherwise is possible to download the JARs from our website and add them to the CLASSPATH.

Developer and support

MonetDBJavaLite is being supported by Pedro Ferreira, a software developer at MonetDB Solutions. Feel free to create an issue on Bugzilla or GitHub, create a pull request or just send an email. You can also create a question on Stack Overflow with the tag “monetdblite”.