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 100% pure Java JDBC driver (type 4) which allows use of the MonetDB database in a Java alike way.

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://docs.oracle.com/javase/7/docs/technotes/guides/jdbc/index.html.

The latest release of the MonetDB JDBC driver has implemented most of the essential JDBC API classes and methods (only CallableStatement is not yet implemented). If you make extensive use of JDBC API and semantics and rely on its features, please report any missing functionality on our bugzilla.

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 JDBC driver Jar

The easiest way to acquire the driver is to download it from our MonetDB Java Download Area. You will find a jar file called monetdb-jdbc-X.Y.jar where X and Y are major and minor version numbers. The other two listed jar files (jdbcclient.jar and monetdb-mcl-*.jar) are optional utility jars. jdbcclient.jar contains a java command line program similar to mclient, see below.

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 7 compatible compiler.

Using the JDBC 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. Note: as of Jul2015 release (monetdb-jdbc-2.17.jar) the MonetDB JDBC Driver only works with Java 7 (or higher) JVMs.

Using the MonetDB JDBC driver in your Java program:

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

	so_timeout=<time in milliseconds>
	language=mal
	language=sql
	treat_blob_as_binary=true
	debug=true

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 {
            // make a connection to the MonetDB server using JDBC URL starting with: jdbc:monetdb:// 
            con = DriverManager.getConnection("jdbc:monetdb://localhost:50000/mydb?so_timeout=10000", "monetdb", "monetdb");
            // make a statement object
            st = con.createStatement();
            // make a executeand SQL query which returns a ResultSet object
            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 column names 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("");
            // 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 <= md.getColumnCount(); 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");
            // 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") + "]");
            }
            // 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;");
            // 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"));
            }
            // 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();
        }
    }
}

Note: it is no longer required (or recommended) to include code line:

  Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");

as the MonetDriver registers itself with the JDBC DriverManager automatically when the monetdb-jdbc-X.Y.jar file is loaded.

 

Using the JdbcClient utility program

We have created an example Java SQL command line program similar to (but not compatible with) mclient program. The jdbcclient.jar program can be downloaded from the MonetDB Java Download Area. It includes and uses the MonetDB JDBC driver internally. As it already includes the JDBC driver classes it is very easy to start it (assuming you have an MonetDB/SQL server running) via:

% java -jar jdbcclient.jar -p50000 -ddemo -umonetdb
password:

Welcome to the MonetDB interactive JDBC terminal!
Database Server: MonetDB v11.23.14
JDBC Driver: MonetDB Native Driver v2.23 (Liberica 20161117 based on MCL v1.13)
Current Schema: sys
Type \q to quit, \h for a list of available commands
auto commit mode: on
sql>

From this sql> prompt you can enter an SQL query (include an ; as end-of-statement) and execute it by using the enter-key.

If the connection fails, observe the error messages from JdbcClient (and/or the merovingian logs) for clues.

Use \q to quit the program.

To see all jdbcclient startup options just run:

% java -jar jdbcclient.jar --help

Tip: if you do not want to enter the password each time, use a .monetdb file (which contains the user and password settings) similar as for mclient