Skip to main content

JDBC Driver

MonetDB JDBC Driver

The most obvious way to connect to a data source using the Java programming language is by making use of the in Java defined JDBC framework. MonetDB has a native Java JDBC driver type 4 which allows use of the MonetDB database in a Java alike way.

It is quite difficult to have a fully complete JDBC implementation. Also this driver isn't complete in that sense. However, it is believed that the most prominent parts of the JDBC interface are implemented, and in such a way that they adhere to the specifications. If you make extensive use of JDBC semantics and rely on many of its features, please report any missing functionality on our bugzilla.

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 http://java.oracle.com/.

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

Getting the driver Jar

The easiest way to acquire the driver is to download it from our download area. You will find a jar file called monetdb-jdbc-X.Y.jar where X and Y are major and minor version numbers.

Compiling the driver (using ant, optional)

If you prefer to build the driver yourself, make sure you acquire the MonetDB Java repository, e.g. as part of the source downloads. The Java sources are built using Apache's Ant tool. Simply issuing the command ant distjdbc should be sufficient to build the driver jar-archive in the subdirectory jars. See the ant web site for more documentation on the ant build-tool: http://ant.apache.org/. The Java sources currently require at least a Java 6 compatible compiler. The JDBC driver, cannot be compiled any more with a Java 1.5 or earlier due to JDBC4.

Testing the driver using the JdbcClient utility

Before you start developing your programs which use the MonetDB JDBC driver it is generally a good idea to check if the driver actually works in your environment. JdbcClient is a no longer distributed, but when compling from sources, it is still built and put in the jars directory. Follow the steps below to assure your setup is complete:

  1. start monetdbd
  2. create a database using monetdb create mytest
  3. run the JdbcClient utility using java -jar {path/to/jdbcclient.jar} -dmytest -umonetdb (with password monetdb)

The last step should give you something like this:

% java -jar jars/jdbcclient.jar -umonetdb
password:

Welcome to the MonetDB interactive JDBC terminal!
Database: MonetDB 5.0.0
Driver: MonetDB Native Driver 1.5 (Steadfast_pre4 20061124)
Type \q to quit, \h for a list of available commands
auto commit mode: on
monetdb-> 

From here you can execute a simple query to assure yourself everything is setup to work correctly. If the connection fails, observe the error messages from JdbcClient and the merovingian logs for clues.

Using the driver in your Java programs

To use the MonetDB JDBC driver, the monetdb-jdbc-X.Y.jar  jar-archive has to be in the Java classpath. Make sure this is actually the case.

Loading the driver in your Java program requires two lines of code:

// make sure the ClassLoader has the MonetDB JDBC driver loaded
Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");
// request a Connection to a MonetDB server running on 'localhost'
Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/database", "monetdb", "monetdb");

The first line makes sure the Java ClassLoader has initialised (and loaded) the Driver class of the MonetDB JDBC package, so it is registered with the DriverManager. The second line requests a Connection object from the DriverManager which is suitable for MonetDB.

The string passed to the "getConnection()"method is defined as "jdbc:monetdb://<host>[:<port>]/<database>" where elements between "<" and ">" are required and elements between "[" and "]" are optional.

A sample Java 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 {
        // make sure the driver is loaded
        Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");
        Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/database", "monetdb", "monetdb");
        Statement st = con.createStatement();
        ResultSet rs;

        rs = st.executeQuery("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;");
        // get meta data and print columns with their type
        ResultSetMetaData md = rs.getMetaData();
        for (int i = 1; i <= md.getColumnCount(); i++) {
            System.out.print(md.getColumnName(i) + ":" +
                md.getColumnTypeName(i) + "\t");
        }
        System.out.println("");
        // 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 on the next query
        for (int i = 0; rs.next() && i < 5; i++) {
            for (int j = 1; j <= md.getColumnCount(); j++) {
                System.out.print(rs.getString(j) + "\t");
            }
            System.out.println("");
        }

        // tell the driver to only return 5 rows, 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");
        // read till the driver says there are no rows left
        for (int i = 0; rs.next(); i++) {
            System.out.print("[" + rs.getString("var1") + "]");
            System.out.print("[" + rs.getString("var2") + "]");
            System.out.print("[" + rs.getInt("var3") + "]");
            System.out.println("[" + rs.getString("var4") + "]");
        }

        // this close is not needed, should be done by next execute(Query) call
        // however if there can be some time between this point and the next
        // execute call, it is from a resource perspective better to close it.
        //rs.close();

        // 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;");
        // and simply print them
        while (rs.next()) {
            System.out.print(rs.getInt("rowid") + ", ");
            System.out.print(rs.getString("id") + ", ");
            System.out.print(rs.getInt("var1") + ", ");
            System.out.print(rs.getInt("var2") + ", ");
            System.out.print(rs.getString("var3") + ", ");
            System.out.println(rs.getString("var4"));
        }

        // this close is not needed, as the Statement will close the last
        // ResultSet around when it's closed
        // again, if that can take some time, it's nicer to close immediately
        // the reason why these closes are commented out here, is to test if
        // the driver really cleans up it's mess like it should
        //rs.close();

        // perform a ResultSet-less query (with no trailing ; since that should
        // be possible as well and is JDBC standard)
        // Note that this method should return the number of updated rows. This
        // method however always returns -1, since Monet currently doesn't
        // support returning the affected rows.
        st.executeUpdate("delete from a where var1 = 'zzzz'");

        // closing the connection should take care of closing all generated
        // statements from it...
        // don't forget to do it yourself if the connection is reused or much
        // longer alive, since the Statement object contains a lot of things
        // you probably want to reclaim if you don't need them anymore.
        //st.close();
        con.close();
    }
}