Data types

Table of contents

Type mapping

JDBC maps SQL types to Plain Old Java Objects (POJOs).

While this mapping is straightforward for most CrateDB types, for some it is not.

The following table shows how the CrateDB types are mapped to JDBC types and what method can be used to fetch them from a ResultSet instance:

CrateDB Type

JDBC Type

ResultSet Method

boolean

BOOLEAN

getBoolean

byte

TINYINT

getByte

short

SMALLINT

getShort

integer

INTEGER

getInteger

long

BIGINT

getLong

float

REAL

getFloat

double

DOUBLE

getDouble

string

VARCHAR

getString

ip

VARCHAR

getString

timestamp

TIMESTAMP

getDate, getTime, or getTimestamp

geo_point

ARRAY

getArray

geo_shape

JAVA_OBJECT

getObject

object

JAVA_OBJECT

getObject

array

ARRAY

getArray

Array types

Array types map to java.sql.Array instances.

Use the .getArray() to get the underlying array (it is usually safe to cast it to Object[]) if you prefer. Otherwise, to have JDBC handle type mapping, you can use .getResultSet() and use the related ResultSetMetaData 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 many rows as there are elements in the array.

Here’s one example:

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(
        "SELECT array_field FROM my_table"
);
resultSet.first();
Array arrayField = resultSet.getArray("array_field");
Object[] arrayFieldValue = (Object[]) arrayFieldValue.getArray();

When inserting arrays using a prepared statement, you must convert your 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.

For example:

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

You can fetch them using ResultSet.getObject() and cast the result to Map<String, Object>. This Map will contain all nested columns defined in the object.

Here’s an example:

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(
    "SELECT object_field FROM my_table"
);
resultSet.first();

Map<String, Object> objectValue = (Map<String, Object>) resultSet.getObject("object_field");
Object objectField = objectValue.get("nested_field");

Caution

Objects can be null.