Crate JDBC Driver

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

Crate 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.

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.

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 Crate, 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 Crate 0.48.1. If the Crate 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.

Crate JDBC properties

Properties can be specified when connecting to Crate using the JDBC driver:

Properties properties = new Properties();
properties.put(<key>, <value>);
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");

Crate JDBC driver supports following properties:

strict:

Default: false.

If this property is set to true the driver will be compliant with the JDBC specification. It will then raise an unsupported operation exception if an unsupported operation is called , like auto-commit or setting a savepoint. The default is false, so the Crate JDBC driver ignores the calls of unsupported operations, which makes it compatible with most 3rd party applications that require transactional features.

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 Crate.

The following features are not supported:

  • java.sql.ParameterMetaData as returned by e.g. java.sql.PreparedStatement
  • DataSource
  • CallableStatement as Crate 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.

Crate

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

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

Below, you can see the compatibility table for the JDBC driver version 2.x.x:

Crate JDBC driver Crate Note
2.x.x 0.56.x The psql.enabled must be set to true. The prepareThreshold property must be set to 0.
2.x.x 0.57.x  

Types

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

Frozen Delights!
Crate 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 getArray
geo_point point JAVA_OBJECT getObject
geo_shape polygon 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 Crate 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");