CrateDB JDBC Driver

JDBC is a core API of Java 1.1 and later. It provides a standard set of interfaces to SQL-compliant databases.

CrateDB provides a type 4 JDBC driver. The driver is written in pure Java, and communicates with the database using the PostgreSQL Wire Protocol.

Note

Differences to version 1.x.x:
  • The connection string changed to the same format as PostgreSQL JDBC, which means that setting the default schema with the connection string is not supported. Use setSchema() instead or provide a schema name in a SQL statement.
  • Support for the experimental showsubcolumns parameter has been dropped.

Table of Contents

Download and Setup

The crate-jdbc jar files are hosted on Bintray and available via JCenter.

In addition to crate-jdbc there is also a standalone version named crate-jdbc-standalone which already includes its dependencies.

Note

crate-jdbc-standalone should not be used in Maven projects, but might want to be used e.g. as a driver for the Squirrel SQL client.

You can download the latest standalone version directly from the Bintray Repository.

If you want to use crate-jdbc with your Maven project you need to add the Bintray repository to your pom.xml:

...
<repositories>
    ...
    <repository>
        <snapshots>
            <enabled>false</enabled>
        </snapshots>
        <id>central</id>
        <name>bintray</name>
        <url>http://dl.bintray.com/crate/crate</url>
    </repository>
</repositories>
...
<dependencies>
    ...
    <dependency>
        <groupId>io.crate</groupId>
        <artifactId>crate-jdbc</artifactId>
        <version>...</version>
    </dependency>
</dependencies>
...

Using Gradle:

repositories {
    ...
    jcenter()
}

dependencies {
    compile 'io.crate:crate-jdbc:...'
    ...
}

Alternatively you can follow the instructions on the Bintray repository overview page by clicking the “Set me up!” button.

Connecting to CrateDB server

CrateDB >= 2.1 requires to connect using a valid database user. If you’re using the standard edition of CrateDB, you can only use the predefined crate user. If you’re using the enterprise edition, see User Management on information how to add and remove users.

In JDBC, the database user can be passed via the user key JDBC URL or CrateDB JDBC properties.

JDBC Driver Class

A connection can be established using DriverManager.getConnection() method, e.g.:

Connection conn = DriverManager.getConnection("crate://localhost:5432/");

The driver class is io.crate.client.jdbc.CrateDriver.

JDBC URL Format

With JDBC, a database is represented by a URL (Uniform Resource Locator). With CrateDB, this takes the following form:

[jdbc:]crate://<host>:<psql-port>[,<host>:<psql-port> , ...]/
    [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

Note

The pair or list of <host>:<psql-port> pairs must always end with trailing slash.

The jdbc: prefix is optional. For example. To connect to a single server the following two formats are both allowed:

crate://localhost:5432/
jdbc:crate://localhost:5432/

In order to connect to multiple servers multiple <host>:<psql-port> pairs can be specified by delimit them using a comma:

crate://host1.example.com:5432,host2.example.com:5432/

To specify a schema, the setSchema method must be explicitly called on the connection. If no schema is provided, the doc default schema will be used:

Connection conn = DriverManager.getConnection("crate://localhost:5432/");
conn.setSchema("foo");

Note

Default schema support requires at least CrateDB 0.48.1. If the CrateDB server that is used has a version that is lower than 0.48.1 the specified schema will be ignored and the default doc schema will be used instead.

CrateDB JDBC properties

Properties can be specified when connecting to CrateDB using the JDBC driver. Here’s an example that illustrates the use of Properties to establish an SSL connection with the database superuser crate.

Properties properties = new Properties();
properties.put("user", "crate");
properties.put("ssl", "true");
Connection conn = DriverManager.getConnection("crate://localhost:5432/", properties);

In addition connection properties can be passed via the JDBC URL:

Connection conn = DriverManager.getConnection("crate://localhost:5432/?property1=value1&property2=value2");

CrateDB JDBC driver supports following properties:

strict:

Default: false.

By default the CrateDB JDBC driver ignores the calls of unsupported operations, which makes it compatible with most 3rd party applications that require transactional features.

If this property is set to true it will behave strictly according to CrateDBs capabilities and the JDBC specification. In case an unsupported operation is called, the corresponding exception will be raised.

Unsupported operations include setting auto-commit or savepoints. The strict drivers reports that the database doesn’t support transactions, the only supported transaction isolation level is TRANSACTION_NONE. Read-only connections aren’t supported either.

Compatibility

JDBC

This JDBC driver follows the JDBC 4.1 standard as it uses PGSQL driver as the underlying connector, however there are certain features that are not fully compatible with CrateDB.

The following features are not supported:

  • java.sql.ParameterMetaData as returned by e.g. java.sql.PreparedStatement
  • DataSource
  • CallableStatement as CrateDB does not support stored procedures

For further details about compatibility with all possible JDBC features, see the ResultSet, ResultSetMetaData and DatabaseMetaData implementations.

Though only an optional feature, it is still worth mentioning:

  • the ResultSet is read only (TYPE_FORWARD_ONLY, CONCUR_READ_ONLY), so changes to a ResultSet are not supported. Though DDL and DML statements are supported using the Statement and PreparedStatement interfaces.

CrateDB

All versions of the JDBC driver >= 2.0.x require CrateDB 0.56.0 or newer. Previous versions of the JDBC driver (1.x) are only compatible with older versions of CrateDB.

The support for versions of the JDBC driver >= 2.0.x with CrateDB version 0.56.x is experimental. The binding between versions of the JDBC driver >= 2.0.x and CrateDB will be stable when 0.57.x of CrateDB is stable.

Below, you can see the compatibility table for the JDBC driver:

CrateDB JDBC driver CrateDB Version Notes
1.x <= 0.55.x Uses transport protocol, default port 4300.
2.x 0.56.x The psql.enabled must be set to true. The prepareThreshold property must be set to 0.
2.x >= 0.57.x  
2.x >= 2.1.x Client needs to connect with a valid database user to get access to CrateDB.

Types

JDBC maps SQL types to POJOs. While this mapping is straightforward for some CrateDB types, for some it is not. This table shows how the CrateDB types are mapped by JDBC and how they should be fetched from a ResultSet instance:

Types
CrateDB type PostgreSQL Type JDBC type ResultSet method used to get a value
boolean bool BOOLEAN getBoolean
byte   TINYINT getByte
short int2 SMALLINT getShort
integer int4 INTEGER getInteger
long int8 BIGINT getLong
float float4 REAL getFloat
double float8 DOUBLE getDouble
string varchar VARCHAR getString
timestamp timestamp TIMESTAMP getDate, getTime, getTimestamp
ip varchar VARCHAR getString
array array ARRAY getArray
geo_point array ARRAY getArray
geo_shape json JAVA_OBJECT getObject
object json JAVA_OBJECT getObject

Array Types

Array types map to java.sql.Array instances. Use the .getArray() to get the underlying java array (it is usually safe to cast it to Object[]) if you prefer. Otherwise, to have JDBC handle the type mapping stuff for you, you can use .getResultSet() and use the related ResultSetMetaData or another way to get the array values converted to Java POJOs. The ResultSet will have one column with the inner array type and the name of the array field (Array.getBaseType()) and as much rows as there are elements in the array.

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select array_field from my_table");
resultSet.first();
Array arrayFieldArray = resultSet.getArray("array_field");
Object[] arrayFieldValue = arrayFieldValue.getArray();

ResultSet arrayFieldResultSet = arrayFieldArray.getResultSet();
arrayFieldResultSet.first();
String firstValue = arrayFieldResultSet.getString("array_field");

When inserting arrays using a prepared statement, you must convert your java array to a java.sql.Array by the use of createArrayOf(). This function takes as its first argument a CrateDB type as described above and as its second the array you want to convert. You can then use setArray() to set this converted array using a prepared statement’s argument.

PreparedStatement preparedStatement = connection.prepareStatement("insert into my_table " +
  "(string_array) values (?)");
preparedStatement.setArray(1, connection.createArrayOf("string", new String[]{"a", "b"}));
preparedStatement.execute();

Object Types

Object columns map to a java.util.Map<String, Object>. You can fetch them using ResultSet.getObject() and cast the result to Map<String, Object>. Be aware that it can be null. This map will contain all the nested columns defined in that object:

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select object_field from my_table");
resultSet.first();
Map<String, Object> objValue = (Map<String, Object>)resultSet.getObject("object_field");
Object nestedValue = objValue.get("nested_field");