JDBC Driver

The most obvious way to connect to a data source using the Java programming language is by making use of the JDBC API. MonetDB supplies a 100% pure Java JDBC driver (type 4) which allows to connect and work with a MonetDB database server from a Java program without any other libraries needed.

This document gives a short description how to use the MonetDB JDBC driver in Java applications. Familiarity with the Java JDBC API is required to fully understand this document. Please note that you can find the complete JDBC API on Oracle's web site.

The latest release of the MonetDB JDBC driver has implemented most of the JDBC 4.2 API classes and methods. If you make extensive use of JDBC API and semantics and rely on its features, please report any missing functionality on our Github Java repository.

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

Getting the JDBC driver Jar

You can download the latest MonetDB JDBC driver 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. It also contains a release notes text file with important information on the JDBC driver release and a ChangeLog with information on what has changed since previous release. In the subdirectory archive you find older releases and a complete ChangeLog-Archive.

The download area also contains two other jar files: "jdbcclient.jre8.jar" and "monetdb-mcl-1. * .jre8.jar". These are optional jars. "jdbcclient.jre8.jar" contains the JdbcClient program (a command line program similar (but not equal) to mclient written in Java using the JDBC API), see Jdbc Client for more information. "monetdb-mcl-1. * .jre8.jar" is the MonetDB Communication Layer which allows a Java program to communicate to a MonetDB server using the Mapi protocol. The JDBC driver jar file already includes this mcl library.

Compiling the driver (using ant, optional)

If you prefer to build the driver yourself, make sure you acquire the MonetDB Java repository from monetdb java. The Java sources are built using Apache's Ant tool, a make file and require JDK 1.8 or higher. Simply issuing the command make should be sufficient to build the driver jar-archive in the subdirectory jars. The Java sources currently require at least a Java 8 compatible compiler.

Using the JDBC driver in your Java 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. The previous MonetDB JDBC Driver class name nl.cwi.monetdb.jdbc.MonetDriver has been deprecated, so do not use it anymore.

From Febr2021 release (monetdb-jdbc-3.0.jre8.jar) the MonetDB JDBC Driver only works with Java 8 (or higher) JVMs. For Java 7 you can use previous stable release (monetdb-jdbc-2.29.jre7.jar) available from the archive directory.

Using the MonetDB JDBC driver in your Java program:

import java.sql.*;

// 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");

The MonetDB JDBC Connection URL string format passed to the getConnection() method is defined as:

jdbc:monetdb://<hostname>[:<portnr>]/<databasename>[?<property>=<value>[&<property>=<value>]...]

where elements between < and > are required and elements between [ and ] are optional.

Following optional connection properties are allowed:

user=<login name>
password=<secret value>
debug=true
logfile=<name logfile>
fetchsize=<nr of rows>
so_timeout=<time in milliseconds>
treat_blob_as_binary=false
treat_clob_as_varchar=false
language=mal
hash=<sha512 or sha384>

See for more information: MonetDB Java release notes text file.

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, b
                            WHERE a.var1 = b.id
                              AND 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 query (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) {
            System.out.println(se.getMessage());
        } finally {
            // when done, close all (server) resources
            if (rs != null) rs.close();
            if (st != null) st.close();
            if (con != null) con.close();
        }
    }
}

it is no longer required (or recommended) to include code line: Class.forName("org.monetdb.jdbc.MonetDriver"); as the MonetDriver class registers itself with the JDBC DriverManager automatically when the monetdb-jdbc-3.X.jre8.jar file is loaded.