The most obvious way to connect to a MonetDB server using the Java programming
language is by making use of 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.
The latest JDBC driver is stable, robust and fast. Try it.
It also supports fast client side csv data file import and export via MonetDB
COPY ... ON CLIENT ... commands.
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 process running, preferably using monetdbd.
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 latest MonetDB JDBC driver using: Clojars
This jre8.jar file can be used with Java Runtime versions 8 and higher (11, 14, 17, 21, 22, 23, etc.).
The JDBC driver supports all MonetDB servers from version Jul2015 and higher. Note that some functionality, such as JDBC escape syntax requires MonetDB server Jun2023 or higher.
We recommend to always use the latest available JDBC driver. To get notified by email on new releases, subscribe for 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 This 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 Jdbc Client 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.
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 and will be removed in a future release, 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.Connection;
import java.sql.DriverManager;
// 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
autocommit=false
language=mal
hash=<sha512 or sha384>
For more information see: MonetDB Java release notes text file.
The MonetDB JDBC driver implementation complies to the JDBC 4.2 definition, see JDBC 4.2 API. It 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 useable/supported:
java.sql.Connection
The following features/methods are NOT useable/supported:
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 specifc 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 useable/supported:
The following methods will add an SQLWarning:
java.sql.PreparedStatement
The following methods are NOT useable/supported:
java.sql.ParameterMetaData
java.sql.CallableStatement
The following methods are NOT useable/supported:
java.sql.ResultSet
The following methods are NOT useable/supported:
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 useable/supported:
java.sql.Clob
A simple implementation using a StringBuilder to store the whole CLOB. The following methods are NOT useable/supported:
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 useable/supported:
The following java.sql.* interfaces are NOT implemented:
JDBC 4.2 API is part of JDK 1.8 (Java 8).
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
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) { }
}
}
}
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.
If you prefer to build the JDBC driver yourself, make sure you acquire the
MonetDB Java repository from 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.