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.
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:
MERGE TABLE and REMOTE TABLE, the JSON module, and the Data Vaults extension.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 :)
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.
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();
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.
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);
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.
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.
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.
<dependency>
<groupId>monetdb</groupId>
<artifactId>monetdb-java-lite</artifactId>
<version>2.33</version>
</dependency>
Otherwise is possible to download the JARs
from our website and add them to the CLASSPATH.
MonetDBJavaLite is being supported by Pedro Ferreira, a software developer at MonetDB Solutions.