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.