Julia interface to Java database drivers

View the Project on GitHub JuliaDB/JDBC.jl

JDBC - Julia interface to Java JDBC database drivers

Build Status Build status JDBC JDBC

This package enables the use of Java JDBC drivers to access databases from within Julia. It uses the JavaCall.jl package to call into Java in order to use the JDBC drivers.

The API provided by this package is very similar to the native JDBC API, with the necessary changes to move from an object oriented syntax to a Julia's more functional syntax. So while a Java method is transformed to a Julia function with the same name, the reciever in Java (the object before the dot) becomes the first argument to the Julia function. For example, statement.executeQuery(sql_string) in Java becomes, in Julia: executeQuery(statement, sql_string). Therefore, some familiarity with JDBC is useful for working with this package.

In JDBC, accessing the data frome a SQL call is done by iterating over a ResultSet instance. In Julia therefore, the ResultSet is a regular Julia iterator, and can be iterated in the usual fashion.

There is however, an optional readtable method that is defined when DataFrames is loaded. This converts a JDBC resultset into a Julia DataFrame.

This package is now julia v0.4 and later only. The last released version of this package that works with julia v0.3 is v0.0.3


To start it up, add the database driver jar file to the classpath, and then initialise the JVM.

using JDBC
JDBC.init() # or JavaCall.init()

Basic Usage

As described above, using this package is very similar to using a JDBC driver in Java. Write the Julia code in a way that is very similar to how corresponding Java code would look.

conn = DriverManager.getConnection("jdbc:derby:test/juliatest")
stmt = createStatement(conn)
rs = executeQuery(stmt, "select * from firsttable")
 for r in rs
      println(getInt(r, 1),  getString(r,"NAME"))

To get each row as a julia tuple, iterate over the result set using JDBCRowIterator. Values in the tuple will be of Nullable type if they are declared to be nullable in the database.

for r in JDBCRowIterator(rs)

The following accessor functions are defined. Each of these functions take two arguments: the Resultset, and either a field index or a field name. The result of these accessor functions is always a pure Julia object. All conversions from Java types are done before they are returned from these functions.



While inserts and updates can be done via a fully specified SQL string using the Statement instance above, it is much safer to do so via a PreparedStatement. A PreparedStatement has setter functions defined for different types, corresponding to the getter functions shown above.

ppstmt = prepareStatement(conn, "insert into firsttable values (?, ?)")
setInt(ppstmt, 1,10)
setString(ppstmt, 2,"TEN")

Similary, a CallableStatement can be used to run stored procedures. A CallableStatement can have both input and output parameters, and thus has both getter and setter functions defined.

setString(cstmt, 1, "derby.locks.deadlockTimeout")
setString(cstmt, 2, "10")

Note that as per the JDBC API there are two kinds of execute methods defined on a Statement : executeQuery returns a ResultSet (usually from a select), and executeUpdate returns an Integer which denotes the number of rows effected by a query (usually an update or insert or a DDL). For PreparedStatements and CallableStatements, an additional function execute is defined which returns a boolean which specifies whether a ResultSet has been returned from the query.

Also note that for a Statement, the query itself is specified in the corresponding execute.. call, while for a PreparedStatement and a CallableStatement, the query itself is specified while creating them.

The connections and the statements should be closed via their close(...) functions. commit(connection), rollback(connection) and setAutoCommit(true|false) do the obvious things.


Pass the JResultSet object from executeQuery to getTableMetaData to get an array of (column_name, column_type) tuples.

conn = DriverManager.getConnection("jdbc:derby:test/juliatest")
stmt = createStatement(conn)
rs = executeQuery(stmt, "select * from firsttable")
metadata = getTableMetaData(rs)

DBAPI.jl Interface

DBAPI.jl is implemented in this package. To connect:

conn = connect(JDBCInterface, "jdbc:mysql://",
               props=Dict("user" => "root", "passwd" => ""),

To disconnect:


To execute a query, we first need a cursor, then we run execute! on the cursor:

csr = cursor(conn)
execute!(csr, "insert into pi_table (pi_value) values (3.14);")
execute!(csr, "select * from my_table;")

To iterate over rows call rows on the cursor:

rs = rows(csr)
for row in rs
    # do stuff with row

To close the cursor call close on the cursor instance.