MonetDBJavaLite

IMPORTANT This repository will no longer be maintained, since MonetDBe-Java has been released: https://github.com/MonetDBSolutions/MonetDBe-Java MonetDBe is the spiritual successor to MoneDBLite, which resulted from a further remodulation of MonetDB to accommodate embedded connections.

IMPORTANT Before any further reading, remember that this software might crash in JVM-native border crossing, although testing has been made on it :) To be 100% safe you can run MonetDBJavaLite in a sub-process inside the JVM, so if it crashes, your application in the main process will still be up!

After MonetDBLite, MonetDBRLite and MonetDBPythonLite, MonetDBJavaLite is available. This project allows the integration of MonetDB, a column-wise and high-scale OLAP relational database in the JVM. Unlike a traditional socket connection, in an embedded connection, both the client and the server share the same process, which means there is no necessity to serialize and deserialize data, or using an IPC method, making the connection faster.

The existing JDBC driver for MonetDB was extended to accommodate both MAPI (regular socket connection) and Embedded connections, while aiming at simplicity of integrating both connections.

Delivery and Installation

There are two jars distributed: The new MonetDB JDBC driver jar (monetdb-jdbc-new-<version>.jar), and the MonetDBJavaLite jar (monetdb-java-lite-<version>.jar). The former can be used independently if only MAPI JDBC connections are desired. The latter contains the embedded server code. For both Embedded API and Embedded JDBC connections, the second jar is also required in the CLASSPATH.

The current version for monetdb-java-lite is 2.39 and monetdb-jdbc-new is 2.37

IMPORTANT The version of the JDBC driver for MonetDBJavaLite is not synced with the version of the original MonetDB JDBC driver.

monetdb-jdbc-new-<version>.jar is compatible with OpenJDK 7, and is both CPU architecture and Operating System independent. In other hand, monetdb-java-lite-<version>.jar requires OpenJDK 8 to run, thus only supports x86_x64/amd64 architectures on Windows, MacOS X and Linux.

Both jars can be obtained through the download section of our website.

Starting on version 2.30, both jars can be obtained from Maven Central repository. monetdb-java-lite depends on monetdb-jdbc-new, therefore only the former is required to add in the dependencies list.

<dependency>
  <groupId>monetdb</groupId>
  <artifactId>monetdb-jdbc-new</artifactId>
  <version>2.37</version>
</dependency>

<dependency>
  <groupId>monetdb</groupId>
  <artifactId>monetdb-java-lite</artifactId>
  <version>2.39</version>
</dependency>

JNI C code

MonetDBJavaLite is heavily based and dependent on the generic one (i.e MonetDBLite). To interface Java with C it uses JNI. JNI code comes with two complementing parts - Java and native C code. In the Java code it is declared as a function native, which indicates that it is actually implemented in C. Later the implementation is written inside the native library. This is where it's called the embedded C-level interface function from the Java code.

Libraries

Packed in the src/main/resources/libs directory of the monetdb-java-lite-<version>.jar there are several directories, containing the C-library of MonetDB for each supported operating system. The extension of the library should either be the default for a dynamic libraries on the user's OS or the generic (for JNI) .jnilib. For this reason the monetdb-java-lite-<version>.jar size is much larger than the average size of a .jar file. If the user wants to save space, he might delete the unwanted versions of the native library in the src/main/resources/libs directory, although it's not recommended to do so.

In an IDE or when Maven runs tests from the command line, the application will use the unpacked library, already present in the src/main/resources/libs dir (since there isn't a .jar yet). When running "in production" - from a .jar, the application will stream copy the library to a temp dir, and load the library from there. This is needed, since one cannot use compressed libraries in a .jar directly.

MonetDB database in the JVM

Due to the MonetDB internal implementation, we allow only one database per JVM process. If the user wants to use several databases, we recommend to create another JVM processes to do so. Due to this limitation, all the actions in MonetDBJavaLite turn around in a single database, which has to be started before any connection.

Note that all the following APIs are NOT thread-safe for performance reasons, and thread-safety not being part of JDBC specification. If the user wants to use a multi-threading environment, we recommend to either create one connection for each thread or use proper synchronization primitives in Java.

Other note is that async API is absent, because no IO operations are performed in an embedded connection. At the same time, asynchronous calls are absent in JDBC and MonetDB uses multiple threads in query execution, making it CPU efficient. However if the user still prefers to use async operations, this API can be embedded easily with the CompletableFuture class available since Java 8.

To shrink the size of the native library, some features of MonetDB were removed in the MonetDBLite version. Those features include: the GeoSpatial/geometry module, merge and remote tables functionality, JSON module, and Data Vaults extension.

Start the database and make connections

The MonetDB Embedded Database has to be loaded in order to perform all the operations. Due to the one database process restriction, the MonetDBEmbeddedDatabase class is a singleton. The MonetDBEmbeddedDatabase will create MonetDB's farm if it's nonexistent in the directory, else it will initialize the existing one. MonetDBLite farm directories are NOT compatible with MonetDB Server ones. To exchange data between the two databases, use SQL import and export statements such as the CSV import and export.

Starting on monetdb-java-lite 2.33 and monetdb-jdbc-new 2.32, it is possible to start the in-memory mode by specifying a null pointer, :memory: or an empty string on the path. In an in-memory connection, data is not persisted on disk. In other hand transactions are held in-memory thus more performance is obtained. To start the database:

Path directoryPath = Files.createTempDirectory("monetdbjavalite");
MonetDBEmbeddedDatabase.startDatabase(directoryPath.toString());
//MonetDBEmbeddedDatabase.startDatabase(null); //in-memory mode

Before exiting the JVM it is VERY important to shutdown the database, otherwise the program will cause many memory leaks. The void MonetDBEmbeddedDatabase.stopDatabase() class method shuts down the embedded database and any pending connections if existing. The class method boolean MonetDBEmbeddedDatabase.isDatabaseRunning() checks if the database is running, int MonetDBEmbeddedDatabase.getNumberOfConnections() retrieves the number of connections in the database and boolean MonetDBEmbeddedDatabase.isDatabaseRunningInMemory() checks if it is running in-memory.

MonetDB to Java Mappings

The Java programming language is a strong typed language, thus an explicit mapping between MonetDB SQL types and Java classes/primitives was made. We favored the usage of Java primitives for the most common MonetDB SQL types, hence making less object allocations. However for the more complex SQL types, we mapped them to Java Classes, while matching the JDBC specification.

One important feature of MonetDB is SQL NULL values are mapped into the system's minimum values (check the table for details). In MonetDBJavaLite, this feature persists for primitive types. However for Java Classes mapping, SQL NULL values are translated into null objects. Down bellow there is a explanation on how to easily check for SQL NULL values in query result sets (NullMappings class).

MonetDB Type	Java Primitive/Class	Null Value
boolean	boolean	System's SCHAR_MIN value
tinyint	byte	Same as boolean
smallint	short	System's SHRT_MIN value
integer	int	System's INT_MIN value
bigint	long	System's LLONG_MIN value
real	float	System's NaN value
double	double	System's NaN value
decimal/numeric	java.math.BigDecimal	Null pointer
char/varchar/clob	java.lang.String	Null pointer
date	java.sql.Date	Null pointer
time (with or without timezone)	java.sql.Time	Null pointer
timestamp (with or without timezone)	java.sql.Timestamp	Null pointer
month interval	int	Same as integer
second interval	long	Same as bigint
blob	byte[] (an object!)	Null pointer

Notice that other more rare data types such as geometry, json, inet, url, uuid and hugeint are missing. These types were removed from MonetDBLite to shrink the size of the library.

Just the Embedded API

Besides regular JDBC interface, MonetDBJavaLite adds another more lightweight API, surpassing some layer of the JDBC specification. The user should use this API instead of JDBC if he intends to obtain more performance without the concern of portability. The javadocs for the Embedded API can be found in our website.

After the database is loaded, connections can be performed to it.

MonetDBEmbeddedConnection connection = MonetDBEmbeddedDatabase.createConnection();
//the session goes...
//Don't forget to close the connection at the end!!
connection.close();

After the connection starts, regular queries can be sent to the embedded database, and later retrieve results. The connection starts on the auto-commit mode by default. The methods void startTransaction(), void commit() and void rollback() are used for transaction management. The methods Savepoint setSavepoint(), Savepoint setSavepoint(String name), void releaseSavepoint(Savepoint savepoint) and void rollback(Savepoint savepoint) handle savepoints within the transaction.

Update Queries

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

connection.startTransaction();
connection.executeUpdate("CREATE TABLE example (words text, counter int, temporal timestamp)");
int numberOfInsertions = connection.executeUpdate("INSERT INTO example VALUES ('monetdb', 1, now()), ('java', 2, now()), (null, null, null)");
connection.commit();

Queries with result sets

For queries with result sets, the method QueryResultSet executeQuery(String query) sends a query to the server, and retrieves results immediately in a QueryResultSet instance.

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

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

A column of values can be retrieved using 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) family of methods. Remember that the input array must be already initialized. 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) methods can be used instead.

For NULL values mappings of a column, the methods void getColumnNullMappingsByIndex(int column, boolean[] input) andvoid getNullMappingByName(String columnName, boolean[] input) can be used.

QueryResultSet qrs = connection.executeQuery("SELECT words, counter, temporal FROM example");
int numberOfRows = qrs.getNumberOfRows(), numberOfColumns = qrs.getNumberOfColumns();

String[] columnNames =  new String[numberOfColumns];
//gets ['words', 'counter', 'temporal']
qrs.getColumnNames(columnNames);

//gets 'monetdb'
String singleWord = qrs.getStringByColumnIndexAndRow(1, 1);

int[] counterValues = new int[numberOfRows];
//gets [1, 2, (low negative value because it's a null value)]
qrs.getIntColumnByIndex(2, counterValues);

Timestamp[] temporalValues = new Timestamp[numberOfRows];
//gets an array of java.sql.Timestamp objects
qrs.getTimestampColumnByName("temporal", temporalValues); //got it by name

boolean[] truthNullMappings = new boolean[numberOfRows];
//get the null mappings of a column, in this case: [false, false, true]
qrs.getNullMappingByName("counter", truthNullMappings);

qrs.close(); //don't forget to close in the end!!! ;)

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

If it is desired to iterate row-wise, the methods QueryResultRowSet fetchResultSetRows(int startIndex, int endIndex), QueryResultRowSet fetchFirstNRowValues(int n) and QueryResultRowSet fetchAllRowValues() can be used. However as of now, these methods convert all the values including primitives into Java Objects, which causes slightly more memory allocations, hence is not recommended in low memory devices. The MonetDBRow class instance holds the data of a single retrieved row.

QueryResultSet qrs = connection.executeQuery("SELECT words, counter, temporal FROM example");

QueryResultRowSet rows = qrs.fetchAllRowValues();
MonetDBRow[] arrayRep = rows.getAllRows();
for (MonetDBRow singleRow : arrayRep) {
   System.out.println(singleRow.getColumnByIndex(1) + singleRow.getColumnByIndex(2)
                      + singleRow.getColumnByIndex(3));
}

qrs.close(); //don't forget ;)

Prepared statements

Since version 2.30 is possible to use regular JDBC Prepared Statements in the Embedded API. The API is similar to the JDBC one, but just by taking the most important methods, which are implemented by MonetDB's JDBC driver.

connection.executeUpdate("CREATE TABLE testPrepared (oneValue int, information clob);");
MonetDBEmbeddedPreparedStatement statement1 = connection.prepareStatement("INSERT INTO testPrepared VALUES (?, ?);");
statement1.setInt(1, 12);
statement1.setString(2, "lekker");
int numberOfRowsAffected = statement1.executeUpdate();
//... do something with numberOfRowsAffected
statement1.close(); //don't forget ;)

MonetDBEmbeddedPreparedStatement statement2 = connection.prepareStatement("SELECT oneValue, information FROM testPrepared WHERE oneValue=?;");
statement2.setInt(1, 12);
QueryResultSet qrs = statement2.executeQuery();
//... do something with the result set
qrs.close();
statement2.close(); //don't forget ;)

If you prefer to ignore the result of execution of the prepared statement you can use the void executeAndIgnore() method, which ignores the results (but eventual Exceptions) by not allocating resources for them.

Utilities methods

In MonetDBEmbeddedConnection class there are other utility methods that can be used to manage the current connection.

• boolean getAutoCommit() - Checks if autocommit mode is turned on or not.
• void setAutoCommit(boolean autoCommit) - Sets autocommit mode on and off.
• String getSchema() - Returns the current schema name.
• void setSchema(String newSchema) - Sets the current schema.
• QueryResultSet listTables(boolean listSystemTables) - Lists existing tables details in the SQL catalog.
• boolean checkIfTableExists(String schemaName, String tableName) - Self explanatory :)
• void removeTable(String schemaName, String tableName) - Self explanatory :)
• boolean isClosed() - Is the connection closed? :)

Interacting with Tables

Another important feature of MonetDBLite is the ability to interact with database tables easily. This featured is also present in MonetDBJavaLite. A single table's data can be retrieved using the methods MonetDBTable getMonetDBTable(String schemaName, String tableName) and MonetDBTable getMonetDBTable(String tableName).

Much alike QueryResultSet class, tables metadata information can be retrieved with the same above methods (both QueryResultSet and MonetDBTable share the same base class).

Iterate a table

To iterate a table, (e.g. for exporting), the method int iterateTable(IMonetDBTableCursor cursor) can be used. The IMonetDBTableCursor interface instance must implement the methods int getFirstRowToIterate(), where the first row in the table is specified (starting from 1), int getLastRowToIterate() the last one, and void processNextRow(RowIterator rowIterator), containing the business logic of the iteration. The rowIterator has information about the iteration itself, as well the current row.

connection.executeUpdate("CREATE TABLE iterateMe (oneValue short, information char(10), justADate date)");
connection.executeUpdate("INSERT INTO iterateMe VALUES (1, 'iterate', now()), (2, 'a', '2014-10-02'), (3, 'table', '1950-12-12')");

MonetDBTable iterateMe = connection.getMonetDBTable("iterateMe");
iterateMe.iterateTable(new IMonetDBTableCursor() {
    @Override
    public void processNextRow(RowIterator rowIterator) {
        System.out.println(rowIterator.getColumnByIndex(1, Short.class)
                           + " " + rowIterator.getColumnByIndex(2, String.class)
                           + " " + rowIterator.getColumnByIndex(3, Date.class));
    }

    @Override
    public int getFirstRowToIterate() {
        return 1;
    }

    @Override
    public int getLastRowToIterate() {
        return iterateMe.getNumberOfRows();
    }
});

Append data to a table

To append new data to a table, the method int appendColumns(Object[] data) is used. data is 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 T get#Type#NullConstant() constant in the NullMappings class. Due to limitations of representation of booleans in Java, to append to a boolean column, a byte array should be used instead, as shown in the example. For all other types, there are no changes.

For decimals, a rounding mode must be set before appending. The method void setRoundingMode(int roundingMode) should be used accordingly click here for details.

connection.executeUpdate("CREATE TABLE interactWithMe (dutchGoodies text, justNumbers int, truth boolean, huge blob)");
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};
byte[] truths = new byte[]{NullMappings.getBooleanNullConstant(), 1, 1, 0, 0};
byte[][] justBlobs = new byte[][]{new byte[]{1,2,5,7}, NullMappings.getObjectNullConstant<byte[]>(),
                                  new byte[]{-1,-2,-3,-4,-5,-6}, new byte[]{127}, new byte[]{0,0,0,0,0}};
Object[] appends = new Object[]{goodies, numbers, truths, justBlobs};
interactWithMe.appendColumns(appends);

QueryResultSet qrs = connection.executeQuery("SELECT * FROM interactWithMe");
//checking values....

JDBC

The existing MonetDB JDBC driver was extended to support both MAPI and Embedded connections. At the same time, the MAPI connection was improved with ByteBuffers in the lower layers of the driver for memory saving, thus less garbage collection is now performed.

JDBC Embedded connection

There are several tutorials about the JDBC on the Internet, hence here will be explained just how to start an embedded connection. In the JDBC specification, an URL is provided to the DriverManager identifying the driver's vendor and the most important properties of the connection.

To start a JDBC Embedded connection the JDBC URL must provided in the format jdbc:monetdb:embedded:[<directory>], where directory is the location of the database. The following example shows how it can be done. In contrast, a JDBC MAPI connection URL has the format jdbc:monetdb://<hostname>[:<portnr>]/<databasename>[?<property>=<value>[;<property>=<value>]].

Starting on monetdb-java-lite 2.33 and monetdb-jdbc-new 2.32 it is possible to start the in-memory mode with :memory: or an empty string in the directory path.

//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

//just a JDBC statement and result set
Statement st = con.createStatement();
st.executeUpdate("CREATE TABLE jdbcTest (justAnInteger int, justAString varchar(32))");
st.executeUpdate("INSERT INTO jdbcTest VALUES (1, 'testing')");
ResultSet rs = st.executeQuery("SELECT justAnInteger, justAString from jdbcTest");
while (rs.next()) {
    int justAnInteger = rs.getInt(1);
    String justAString = rs.getString(2);
    System.out.println(justAnInteger + " " + justAString);
}
rs.close(); //Don't forget! :)
st.executeUpdate("DROP TABLE jdbcTest");
st.close();
con.close();

As seen in the example, the MonetDBEmbeddedDatabase calls were not required for better portability of the JDBC Embedded connection. What really happens is when starting a JDBC Embedded connection, it checks if there is a MonetDBEmbeddedDatabase instance running in the provided directory, otherwise an exception is thrown. While closing, if it's the last connection, the MonetDBEmbeddedDatabase will shut down.

It is made possible to use the the Embedded API in the JDBC Embedded connection, although it is completely separated from the JDBC specification.

//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

MonetDBEmbeddedConnection cast = ((EmbeddedConnection)con).getAsMonetDBEmbeddedConnection();
cast.executeUpdate("SELECT something FROM somewhere WHERE field=1");
//do as a MonetDBEmbeddedConnection...
con.close(); //The connection close statement should be called from the JDBC connection instance

Differences between the JDBC MAPI and Embedded connections

In MonetDBLite, less important features of MonetDB were turned off in order to shrink its size. This also means features of the MonetDB JDBC driver won't be available in a JDBC Embedded connection.

• As mentioned before, the authentication scheme is nonexistent in the Embedded connection.
• In the JDBC specification a Fetch Size attribute allows to fetch a result set in blocks. This feature is favorable in a socket connection (MAPI) where the client and the server might not be in the same machine, thus fetching the results incrementally in blocks. However in the Embedded connection, this feature is less favorable as both the client and the server are in the same machine. Therefore the result set is always retrieved with a single block, making the void setFetchSize(int rows) and int getFetchSize() methods depreciated in a Embedded connection (they do nothing).
• The methods void setNetworkTimeout(Executor executor, int millis) and int getNetworkTimeout() are insignificant as there is no network involved in the Embedded connection.
• As mentioned before, some MonetDB data types are not featured in MonetDBLite.
• In the JDBC specification BLOBs and CLOBs have wrapper interfaces for incremental processing. In a MAPI connection, MonetBlob and MonetClob classes provide this implementation, however in the Embedded connection, these wrappers are not used, so only Strings and byte[] are used in favor for more performance.

Changelog

• 2.39
  • Fixed overflow bug on 'appendColumns' method.
• 2.38
  • Fixed buffer in for batch execution in embedded JDBC connection.
  • Merged with MonetDB Aug2018 release.
• 2.37
  • Created Cmake file for compilation. Merged with MonetDB Mar2018 release.
• 2.36
  • Parse embedded JDBC connection properties (silent and sequential flags).
• 2.35
  • Fixed bug in the JDBC embedded connection, while checking if the connection is valid.
• 2.34
  • Added batch processing in the JDBC connection.
  • Added simplified MonetDBEmbedded.startDatabase(String dbDirectory) method.
  • Added runtime addShutdownHook to stop the database when the JVM exits if it is still running.
• 2.33
  • Merged with latest changes on MonetDB original JDBC driver.
• 2.32
  • Added in-memory mode.
• 2.31
  • Merged with MonetDB Jul2017 release.
• 2.30
  • Added JDBC-like prepared statements in the Embedded API.
  • Deployed on Maven Central Repository.
• 2.25
  • Bugfix release.
• 2.24
  • Experimental release based on MonetDB Dec2016 release.

FAQs

1. I am getting the MonetDBEmbeddedException: "The MonetDB Embedded database is still running!"

Due to the internal representation of MonetDB, we allow only ONE embedded database per process. Check if you are calling the class method boolean StartDatabase(String dbDirectory) more than once in your code without any void StopDatabase() call in between. If you still need more than one database in your program, we recommend to create a sub JVM process. It can be easily achieved with the exec family of methods in the Runtime class.

2. I am having race conditions, can you tell me why?

As said before, this API is not thread-safe for performance reasons. If you want to keep data integrity, implement a synchronization mechanism, or a pool of connections much alike Java EE does with JDBC.

3. Despite no IO, I still want to run this API asynchronously, can you do that?

That can be easily achieved with CompletableFuture in Java 8. For older versions of Java you can use Future instead. An example to run a query asynchronously:

CompletableFuture<QueryResultSet> asyncFetch = CompletableFuture.supplyAsync(() -> {
    try {
        return connection.executeQuery("SELECT something FROM exampleTable");
    } catch (MonetDBEmbeddedException ex) {
        //log the exception...
        return null;
    }
});
// later...
QueryResultSet resultSet = asyncFetch.join();

Note For better transparency of using Java Checked Exceptions with Functional Interfaces check here. Then you can use the CompletableFuture<T> exceptionally(Function<Throwable,? extends T> fn) method to handle the exceptions.

4. Floating-point values are not correctly retrieved from queries!

I found out that on Linux Debian distributions, the locales setting may not be set properly :( Just set them to en_US.UTF-8 and you will be fine. Click here for details.

5. I am getting very low negative numbers and/or NullPointer exceptions in the QueryResultSet!

You are getting SQL NULL values in your query result sets. As explained above, for the primitive MonetDB SQL types we map them to the JVM minimum values. For the more complex MonetDB SQL types such as CHAR and DATE we map to Java Objects, and thus in SQL NULL values are represented with null Java Objects. The NullMappings class contains static methods to check if a value is null or not. At the same time, the SQL standard has the COALESCE function to return a default value when a value is null in the result set.

6. While starting a JDBC connection, I am getting the SQLException: "Unable to connect (localhost:50000): Connection refused"!

The new MonetDB JDBC driver creates a MAPI connection by default, as the most common use case of it. To start an embedded connection you MUST provide the embedded connection JDBC URL with the farm's directory. Check the example above on how it can be done.

7. Why I have to pass a column array as an input in the QueryResultSet? It would be easier to return it in the method instead.

That's a very good question indeed. The reason of this implementation has to do with the representation of Arrays in the JVM. Whenever you create an Array in the JVM, it gets auto-initialized with 0s, which might cause a slight overhead in large result sets. You could try to create them without initialization, but that currently it's not possible in the JVM (check here for details). With the current implementation you can re-use the same arrays in multiple QueryResultSets, thus getting the auto-initialization overhead much less often.

In overall you can see that the QueryResultSet API is similar to the ByteBuffers implementation, where both intend to re-use Arrays.

Regarding this question, it would be better to approach to create an Array pointing directly to the result column, thus avoiding any copy likewise happens in MonetDBRLite and MonetDBPythonLite. However there are several issues regarding this approach in the JVM:

• The internal representation of Arrays vary between JVMs. In some JVMs the arrays might not be contiguous in memory!
• We have to allocate the result set in the Java's Heap instead of the MonetDB's heap. We could try that with an array, but even in the native API the arrays are always auto-initialized, which is already a copy. We could do some crazy hacking by pointing the array to the MonetDB's heap, but the JVM's Garbage Collector might move memory areas, which we have to be very careful with.
• We could try direct ByteBuffers, which are allocated outside of the Java's heap. However to access their data in the Java code in a bulk way to an array, a copy is made.

8. Is there a way to know if a JDBC connection is MAPI or Embedded after it has started?

Yes there is! In JDBC specification you can call the String getClientInfo(String name) method to get a provided property at the beginning of the connection. You can do:

String embeddedString = connection.getClientInfo("embedded");
boolean isEmbedded = (embeddedString != null && embeddedString.equals("true"));

9. I don't like Java that much, can I use this for another programming languages for the JVM?

Yes you can! You can easily import Java libraries for other JVM programming languages such as Scala. The following example creates a JDBC Embedded connection in Scala:

var connection:Connection = null
try {
    connection = DriverManager.getConnection("jdbc:monetdb:embedded:/home/user/myfarm")
    val statement = connection.createStatement()
    statement.executeUpdate("CREATE TABLE example (counter int, justAString varchar(32), floatingPoint real)")
    statement.executeUpdate("INSERT INTO example VALUES (1, 'Scala', 3.223)")
    statement.executeUpdate("INSERT INTO example VALUES (2, 'is', -1000)")
    statement.executeUpdate("INSERT INTO example VALUES (3, 'cool', -743.858)")
    val resultSet = statement.executeQuery("SELECT counter, justAString, floatingPoint FROM example")
    while (resultSet.next()) {
        val counterValue = resultSet.getInt(1)
        val stringValue = resultSet.getString(2)
        val floatingPointValue = resultSet.getFloat(3)
        //process the results...
    }
    resultSet.close() //Don't forget! ;)
    statement.executeUpdate("DROP TABLE example")
    statement.close()
} catch {
    case e: Throwable => e.printStackTrace()
}
connection.close() //Don't forget! ;)

10. Any tips for additional performance of MonetDBJavaLite regarding the JVM?

We haven't dug into the settings of the JVM with MonetDBJavaLite yet, although we can do that in JNI. However we must remember that the best setting may vary with the underlying JVM, and MonetDBJavaLite will be just a part of the running application. One possible optimization is to run the JVM in server mode instead of client mode, although it should be benchmarked as it might not provide better performance results in some applications. You can check the Stack Overflow question here. Don't forget to check the options to JVM such as the garbage collection algorithm and the size of the heap here.

License

This Source Code Form is subject to the terms of the Mozilla Public License, v. 2.0. If a copy of the MPL was not distributed with this file, You can obtain one at https://mozilla.org/MPL/2.0/.

Copyright 1997 - July 2008 CWI, August 2008 - 2018 MonetDB B.V.

Developer and support

MonetDBJavaLite is being supported by Pedro Ferreira, a developer at MonetDBSolutions. 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.

As you could see I like emoticons! :) Just one more :)