JDBC Driver

The most obvious way to connect to a MonetDB server using the Java programming language is to use the Java Database Connectivity (JDBC) API. MonetDB supplies a 100% pure Java JDBC driver (type 4) which allows to connect and work with a MonetDB database server from any Java program. MonetDB's JDBC driver is stable, robust and fast. As an extension to the standard JDBC functionality it also supports fast client side CSV data file import and exports with the COPY INTO ... ON CLIENT statement.

This document gives a description how to get and use the MonetDB JDBC driver in Java applications. Some familiarity with the Java JDBC API is recommended.

For an introduction into Java Database Connectivity see JDBC wikipedia and JDBC Introduction.

In order to use the MonetDB JDBC driver in Java applications you need (of course) a running MonetDB/SQL server instance (mserver5) running, preferably using monetdbd.

Getting the JDBC driver

You can download the latest MonetDB JDBC driver as a single jar file from our MonetDB Java Download Area. You will find a jar file called "monetdb-jdbc-3.X.jre8.jar" where X is the minor version number. No other libraries or dependent jar files are needed.

Maven users can get the MonetDB JDBC driver using: Clojars

This jre8.jar file can be used with Java Runtime versions 8 and higher.

The JDBC driver supports all MonetDB servers from version Jul2015 and higher. Note: some functionality, such as JDBC escape syntax requires MonetDB server Jun2023 or higher.

We recommend to always use the latest released JDBC driver. To receive info (notified by email) on new releases, subscribe to the announce-list.

The download area also contains the release notes with information on the JDBC driver release. The ChangeLog contains detailed information on what has changed since previous release. In the subdirectory archive you find older releases and a complete ChangeLog-Archive.

Tip The download area also contains the "jdbcclient.jre8.jar" file. This is the JdbcClient user program, a command line interface program (similar to mclient) written in Java using the JDBC API, see JdbcClient for more information. The JdbcClient program includes the JDBC driver, so it can be used as a standalone java program to quickly test JDBC connectivity to a MonetDB server.

Using the JDBC driver in your programs

To use the MonetDB JDBC driver, the monetdb-jdbc-3.X.jre8.jar java-archive file name has to be in the Java classpath setting. Make sure this is actually the case. The main MonetDB JDBC Driver class name is org.monetdb.jdbc.MonetDriver.

To use the MonetDB JDBC driver in your Java program:

import java.sql.Connection;
import java.sql.DriverManager;  // required to load org.monetdb.jdbc.MonetDriver class

// request a Connection to a MonetDB server running on 'localhost' (with
// default port 50000) for database demo for user and password monetdb
Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/demo", "monetdb", "monetdb");

JDBC Connection URL format

The MonetDB JDBC connection URL formats are:

jdbc:monetdb://[<host>[:<port>]]/<databasename>[?<properties>]
jdbc:monetdbs://[<host>[:<port>]]/<databasename>[?<properties>]

where the <properties> are &-separated: prop1=value1[&prop2=value2[&prop3=value3...]]

The second form (monetdbs) is for creating a TLS-protected connection. TLS (Transport Layer Security) is the security mechanism also used for HTTPS. This second format is supported only since release monetdb-jdbc-3.4 (2025).

When no <host> value is specified, localhost is used as default.

When no <port> value is specified, 50000 is used as default.

Property keys and values support percent-escaped byte sequences. For example, the password chocolate&cookies can be passed as follows: jdbc:monetdb:///demo?user=me&password=chocolate%26cookies.

Note: monetdb-jdbc-3.3 and earlier did not support percent-escapes. If your password contains percent-characters, those must now be encoded as %25.

Supported connection properties are:

Booleans values <bool> can be written as true, false, yes, no, on and off.

Property fetchsize is accepted as an alias of replysize.

Client authentication (Mutual TLS, or MTLS) is not yet supported.

The properties treat_clob_as_varchar and treat_blob_as_binary control which type is returned by ResultSetMetaData.getColumnType(int) for CLOB and BLOB columns. When treat_clob_as_varchar is enabled, Types.VARCHAR is returned instead of Types.CLOB for CLOB columns. When treat_blob_as_binary is enabled, Types.VARBINARY is returned instead of Types.BLOB for BLOB columns. This will cause generic JDBC applications such as SQuirrel SQL and DBeaver to use the more efficient #getString() and #getBytes() methods rather than #getClob() and #getClob(). These properties are enabled by default since monetdb-jdbc-3.0.

A sample Java JDBC program

import java.sql.*;

/*
 * This example assumes there exist tables a and b filled with some data.
 * On these tables some queries are executed and the JDBC driver is tested
 * on it's accuracy and robustness against 'users'.
 *
 * @author Fabian Groffen
 */
public class MJDBCTest {
    public static void main(String[] args) throws Exception {

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            String con_url = "jdbc:monetdb://localhost:50000/mydb?so_timeout=10000";

            // make a connection to the MonetDB server using JDBC URL starting with: jdbc:monetdb://
            con = DriverManager.getConnection(con_url, "monetdb", "monetdb");

            // make a statement object
            st = con.createStatement();

            // execute SQL query which returns a ResultSet object
            String qry = "SELECT a.var1, COUNT(b.id) AS total" +
                         "  FROM a JOIN b ON a.var1 = b.id" +
                         " WHERE a.var1 = 'andb'" +
                         " GROUP BY a.var1" +
                         " ORDER BY a.var1, total";
            rs = st.executeQuery(qry);

            // get meta data and print column names with their type
            ResultSetMetaData md = rs.getMetaData();
            final int colCount = md.getColumnCount();
            for (int i = 1; i <= colCount; i++) {
                System.out.print(md.getColumnName(i) + ":" + md.getColumnTypeName(i) + "\t");
            }
            System.out.println("");

            // now print the data: only the first 5 rows, while there probably are
            // a lot more. This shouldn't cause any problems afterwards since the
            // result should get properly discarded when we close it
            for (int i = 0; rs.next() && i < 5; i++) {
                for (int j = 1; j <= colCount; j++) {
                    System.out.print(rs.getString(j) + "\t");
                }
                System.out.println("");
            }

            // close (server) resource as soon as we are done processing resultset data
            rs.close();
            rs = null;

            // tell the driver to only return 5 rows for the next execution
            // it can optimize on this value, and will not fetch any more than 5 rows.
            st.setMaxRows(5);

            // we ask the database for 22 rows, while we set the JDBC driver to
            // 5 rows, this shouldn't be a problem at all...
            rs = st.executeQuery("select * from a limit 22");
            int var1_cnr = rs.findColumn("var1");
            int var2_cnr = rs.findColumn("var2");
            int var3_cnr = rs.findColumn("var3");
            int var4_cnr = rs.findColumn("var4");

            // read till the driver says there are no rows left
            for (int i = 0; rs.next(); i++) {
                System.out.println(
                    "[" + rs.getString(var1_cnr) + "]" +
                    "[" + rs.getString(var2_cnr) + "]" +
                    "[" + rs.getInt(var3_cnr) + "]" +
                    "[" + rs.getString(var4_cnr) + "]" );
            }

            // close (server) resource as soon as we are done processing resultset data
            rs.close();
            rs = null;

            // unset the row limit; 0 means as much as the database sends us
            st.setMaxRows(0);

            // we only ask 10 rows
            rs = st.executeQuery("select * from b limit 10;");
            int rowid_cnr = rs.findColumn("rowid");
            int id_cnr = rs.findColumn("id");
            var1_cnr = rs.findColumn("var1");
            var2_cnr = rs.findColumn("var2");
            var3_cnr = rs.findColumn("var3");
            var4_cnr = rs.findColumn("var4");

            // and simply print them
            while (rs.next()) {
                System.out.println(
                    rs.getInt(rowid_cnr) + ", " +
                    rs.getString(id_cnr) + ", " +
                    rs.getInt(var1_cnr) + ", " +
                    rs.getInt(var2_cnr) + ", " +
                    rs.getString(var3_cnr) + ", " +
                    rs.getString(var4_cnr) );
            }

            // close (server) resource as soon as we are done processing resultset data
            rs.close();
            rs = null;

            // perform a ResultSet-less statement (with no trailing ; since that
            // should be possible as well and is JDBC standard)
            int updCount = st.executeUpdate("delete from a where var1 = 'zzzz'");
            System.out.println("executeUpdate() returned: " + updCount);

        } catch (SQLException se) {
            while (se != null) {
                System.out.println(se.getSQLState() + ": " + se.getMessage());
                se = se.getNextException();
            }
        } finally {
            // when done, close all (server) resources
            if (rs != null)  try { rs.close(); } catch (Exception e) { }
            if (st != null)  try { st.close(); } catch (Exception e) { }
            if (con != null) try { con.close(); } catch (Exception e) { }
        }
    }
}

JDBC driver behavior and programming tips

• After creating a Connection object check for SQLWarnings via conn.getWarnings();

• Close JDBC ResultSet, Statement, PreparedStatement, CallableStatement and Connection objects immediately (via close()) when they are no longer needed, in order to release resources and memory on the server and client side. Especially ResultSets can occupy large amounts of memory on the server and client side.

• By default the ResultSets created by methods in DatabaseMetaData which return a ResultSet (such as dbmd.getColumns(...)) are TYPE_SCROLL_INSENSITIVE, so they cache their ResultSet data to allow absolute, relative and random access to data rows and fields. To free heap memory and server resources, close those ResultSets immediately when no longer needed.

• By default the ResultSets created by stmt.executeQuery(...) or stmt.execute(...) are TYPE_FORWARD_ONLY, to reduce the potentially large amount of client memory needed to cache the whole ResultSet data.

• When you need to execute many SQL queries sequentially reuse the Statement object instead of creating a new Statement for each single SQL query. Alternatively you can execute the SQL queries as one script (each SQL query must be separated by a ; character) string via stmt.execute(script), stmt.getResultSet() and stmt.getMoreResults(). Or you can use the batch execution functionality, see stmt.addBatch() and stmt.executeBatch() methods.

• The fastest way to retrieve data from a MonetDB ResultSet is via the getString(int columnIndex) method, because internally all data values (of all types) are stored as Strings, so no conversions are needed.

• Avoid using rs.getObject() as it will need to construct a new Object for each value, even for primitive types such as int, long, boolean.

• Avoid using rs.getClob(). Instead use getString() for all CLOB columns, which is much faster and uses much (3 times) less memory.

• Avoid using rs.getBlob(). Instead use getBytes() to get a byte array or use getString() to get a string containing hex pairs, for all BLOB columns. These methods are much faster and use much less memory. The getString() is the fastest way as no conversions are done at all. The getBytes() will need to convert the hex char string into a new bytes[].

• Try to avoid calling "rs.get...(String columnLabel)" methods inside the while(rs.next()) {...} loop. Instead resolve the columnLabels to column numbers before the loop via method "int findColumn(String columnLabel)" and use the int variables with the rs.get...(int columnIndex) methods. This eliminates the call to findColumn(String columnLabel) for each value of every column for every row in the ResultSet.

ON CLIENT extension

MonetDB provides the COPY INTO statement to perform fast bulk loading of csv file data and exports, see the sections on load csv file and write csv file. By default, COPY INTO accesses files on the server but it also has a mode to access text files on the client. This is supported by various clients including the command line tools mclient(1), JdbcClient and the JDBC driver since release 3.2 (monetdb-jdbc-3.2.jre8.jar).

If you execute, for example,

COPY INTO mytable FROM 'data.csv' ON CLIENT;

the server will send a message to the JDBC driver asking for the contents of file 'data.csv'. By default, the JDBC driver will refuse with an error message: No file upload handler has been registered with the JDBC driver. This is for security reasons. However, you can register a callback to handle these requests from the server:

Connection conn = DriverManager.getConnection(dbUrl, userName, password);
MyUploader handler = new MyUploadHandler();
conn.unwrap(MonetConnection.class).setUploadHandler(handler);

Here, MyUploadHandler is an implementation of the interface MonetConnection.UploadHandler defined as follows:

public interface UploadHandler {
    /**
     * Called if the server sends a request to read file data.
     *
     * Use the given handle to receive data or send errors to the server.
     *
     * @param handle Handle to communicate with the server
     * @param name Name of the file the server would like to read. Make sure
     *             to validate this before reading from the file system
     * @param textMode Whether to open the file as text or binary data.
     * @param linesToSkip In text mode, number of initial lines to skip.
     *                    0 means upload everything, 1 means skip the first line, etc.
     *                    Note: this is different from the OFFSET option of the COPY INTO,
     *                    where both 0 and 1 mean 'upload everything'
     * @throws IOException when I/O problem occurs
     */
    void handleUpload(Upload handle, String name, boolean textMode, long linesToSkip) throws IOException;

    /**
     * Called when the upload is cancelled halfway by the server.
     *
     * The default implementation does nothing.
     */
    default void uploadCancelled() {}
}

In your implementation of handleUpload(), you can use the handle object to communicate with the server, for example:

• PrintStream handle.getStream() to obtain a stream object to which you can write. This is useful if you want to generate the data on the fly.

• void uploadFrom(InputStream stream) to have the JDBC driver read data from the stream and send it to the server as-is. For text mode uploads this means the text must be UTF-8 encoded.

• void handle.uploadFrom(Reader reader) to have the JDBC driver read text from the given Reader and upload it.

• void handle.uploadFrom(BufferedReader reader, long linesToSkip) to have the JDBC driver read from the given BufferedReader and upload the text, skipping the first linesToSkip lines. Typically you would use the value passed to handleUpload in parameter linesToSkip.

• void handle.sendError(String errorMessage) to refuse the upload.

If you use sendError() to refuse the upload, the COPY INTO statement will fail but the connection will remain usable. On the other hand, if your implementation of handleUpload throws an IOException, the connection will be closed because the network protocol currently does not provide a way to signal errors to the server once the transfer has begun.

The interface for DownloadHandler is:

public interface DownloadHandler {
    /**
     * Called if the server sends a request to write a file.
     *
     * Use the given handle to send data or errors to the server.
     *
     * @param handle Handle to communicate with the server
     * @param name Name of the file the server would like to write. Make sure
     *             to validate this before writing to the file system
     * @param textMode Whether this is text or binary data.
     * @throws IOException when I/O problem occurs
     */
    void handleDownload(Download handle, String name, boolean textMode) throws IOException;
}

Class org.monetdb.util.FileTransferHandler provides a default implementation of both MonetConnection.UploadHandler and MonetConnection.DownloadHandler which reads and writes files on the local file system. Its constructors takes a Path or directory name and a Charset that specifies the encoding used text files.

public class FileTransferHandler implements MonetConnection.UploadHandler, MonetConnection.DownloadHandler {
    private final Path root;
    private final Charset encoding;

    /**
     * Create a new FileTransferHandler which serves the given directory.
     *
     * @param dir directory Path to read and write files from
     * @param encoding the specified characterSet encoding is used for all data files in the directory
     *                 when null the Charset.defaultCharset() is used.
     */
    public FileTransferHandler(Path dir, Charset encoding) {
        this.root = dir.toAbsolutePath().normalize();
        this.encoding = encoding != null ? encoding: Charset.defaultCharset();
    }

    /**
     * Create a new FileTransferHandler which serves the given directory.
     *
     * @param dir directory String to read and write files from
     * @param encoding the specified characterSet encoding is used for all data files in the directory
     *                 when null the Charset.defaultCharset() is used.
     */
    public FileTransferHandler(String dir, Charset encoding) {
        this(FileSystems.getDefault().getPath(dir), encoding);
    }

    /**
     * Read the data from the specified file (in the root directory) and upload it to the server.
     */
    public void handleUpload(MonetConnection.Upload handle, String name, boolean textMode, long linesToSkip) throws IOException { ... }

    /**
     * Download the data from the server and write it to a new created file in the root directory.
     * When a file with the same name already exists the download request will send an error and NOT overwrite the existing file.
     */
    public void handleDownload(MonetConnection.Download handle, String name, boolean textMode) throws IOException { ... }
}

The JdbcClient application has also been extended to support COPY INTO ... ON CLIENT functionality. However for security reasons you must provide an explicit new startup argument

--csvdir "/path/to/csvdatafiles"

or on MS Windows

--csvdir "C:\\path\\to\\csvdatafiles"

in order to allow the JdbcClient to access local files.

JDBC API compliance

The MonetDB JDBC driver implementation complies to the JDBC 4.2 definition, see JDBC 4.2 API. JDBC 4.2 API is part of JDK 1.8 (Java 8).

The MonetDB JDBC driver is a 100% pure Java implementation (aka as a type 4 driver) and does not depend on or requires any external java library.

It implements the following JDBC 4.2 interfaces:

• java.sql.Driver

  The following method is NOT usable/supported:

  • getParentLogger

• java.sql.Connection

  The following methods/options are NOT usable/supported:

  • createArrayOf
  • createNClob
  • createStruct
  • createSQLXML
  • prepareStatement with array of column indices or column names
  • setHoldability (close/hold cursors over commit is not configurable)

  NOTE: be sure to check for warnings after setting concurrencies or isolation levels; MonetDB server currently does not support anything else but "fully serializable" transactions.

• java.sql.DatabaseMetaData

  NOTE: the column SPECIFIC_NAME as returned by getProcedures, getProcedureColumns, getFunctions and getFunctionColumns contains the internal id of the procedure or function. Use it for overloaded procedure and function names to match the proper columns info as returned by getProcedureColumns or getFunctionColumns to a specific procedure or function name as returned by getProcedures or getFunctions. For example, getProcedures(null, "sys", "analyze") will return 4 rows as there exists 4 overloaded system procedures called analyze, with different (from 0 to 3) parameters. When calling getProcedureColumns(null, "sys", "analyze", "%") you will get all the 6 (0+1+2+3) parameters of the 4 system procedures combined. So you will need to use the value of column SPECIFIC_NAME to properly match the right parameters to a specific procedure.

• java.sql.Statement

  The following methods/options are NOT usable/supported:

  • cancel (query execution cannot be terminated, once started) see also: logged issue
  • execute with column indices or names
  • executeUpdate with column indices or names
  • setMaxFieldSize
  • setCursorName

  The following methods will add an SQLWarning:

  • setEscapeProcessing(true) for servers Sep2022 (11.45) and older
  • setEscapeProcessing(false) for servers Jun2023 (11.47) and newer

• java.sql.PreparedStatement

  The following methods are NOT usable/supported:

  • setArray
  • setAsciiStream
  • setBinaryStream
  • setBlob
  • setNClob
  • setRef
  • setRowId
  • setSQLXML
  • setUnicodeStream (note: this method is Deprecated in JDBC 4.2 API)

• java.sql.ParameterMetaData

• java.sql.CallableStatement

  The following methods are NOT usable/supported:

  • setArray
  • setAsciiStream
  • setBinaryStream
  • setBlob
  • setNClob
  • setRef
  • setRowId
  • setSQLXML
  • setUnicodeStream (note: this method is Deprecated in JDBC 4.2 API)
  • All getXyz(parameterIndex/parameterName, ...) methods because output parameters in stored procedures are not supported by MonetDB
  • All registerOutParameter(parameterIndex/parameterName, int sqlType, ...) methods because output parameters in stored procedures are not supported by MonetDB
  • wasNull() method because output parameters in stored procedures are not supported by MonetDB

• java.sql.ResultSet

  The following methods are NOT usable/supported:

  • getArray
  • getAsciiStream
  • getNClob
  • getRef
  • getRowId
  • getSQLXML
  • getUnicodeStream
  • moveToCurrentRow
  • moveToInsertRow
  • All methods related to updateable result sets: updateArray ... updateTimestamp cancelRowUpdates deleteRow insertRow refreshRow

• java.sql.ResultSetMetaData

• java.sql.SavePoint

• java.sql.Wrapper

• java.sql.Blob

  A simple implementation using a byte[] to store the whole BLOB. The following method is NOT usable/supported:

  • setBinaryStream

• java.sql.Clob

  A simple implementation using a StringBuilder to store the whole CLOB. The following methods are NOT usable/supported:

  • setAsciiStream
  • setCharacterStream

• java.sql.SQLData

  implemented by classes: org.monetdb.jdbc.types.INET and org.monetdb.jdbc.types.URL

• javax.sql.DataSource

  The following method is NOT usable/supported:

  • getParentLogger

The following java.sql.* interfaces have NOT been implemented:

• java.sql.Array
• java.sql.DriverAction
• java.sql.NClob
• java.sql.Ref
• java.sql.Rowid
• java.sql.SQLInput
• java.sql.SQLOutput
• java.sql.SQLType
• java.sql.SQLXML
• java.sql.Struct

If you encountered an issue/bug or feel some important features are missing, please let us know by reporting it at our Github issues tracker: MonetDB/monetdb-java/issues

Compiling the driver (optional)

If you prefer to build the JDBC driver yourself, make sure you clone the MonetDB Java repository from hg/monetdb-java or git/monetdb-java. The Java sources are built using Apache's Ant tool and require JDK 1.8 or higher. For convenience there is also a Makefile. Simply issuing the command make should be sufficient to compile and build the jar files in the subdirectory called jars. There are no dependencies on any external jars or packages.