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.