Complex Types in JDBC
The results of a java.sql
query are stored in a ResultSet
. If the ResultSet
contains a column of complex type, you can retrieve it with one of the following:
- For columns of type ARRAY, SET, or
MAP, use
getArray()
, which returns ajava.sql.Array
. - For columns of type ROW, use
getObject()
, which returns ajava.sql.Struct
.
Type Conversion Table
The objects java.sql.Array
and java.sql.Struct
each have their own API for accessing complex type data. In each case, the data is returned as java.lang.Object
and will need to be type cast to a Java type. The exact Java type to expect depends on the Vertica type used in the complex type definition, as shown in this type conversion table:
java.sql Type | Vertica Type | Java Type |
---|---|---|
BIT
|
BOOL | java.lang.Boolean
|
BIGINT
|
INT | java.lang.Long
|
DOUBLE
|
FLOAT | java.lang.Double
|
CHAR
|
CHAR | java.lang.String
|
VARCHAR
|
VARCHAR | java.lang.String
|
LONGVARCHAR
|
LONGVARCHAR | java.lang.String
|
DATE
|
DATE | java.sql.Date
|
TIME
|
TIME | java.sql.Time
|
TIME
|
TIMETZ | java.sql.Time
|
TIMESTAMP
|
TIMESTAMP | java.sql.Timestamp
|
TIMESTAMP
|
TIMESTAMPTZ | com.vertica.dsi.dataengine.utilities.TimestampTz
|
getIntervalRange(oid, typmod)
|
INTERVAL | com.vertica.jdbc.VerticaDayTimeInterval
|
getIntervalRange(oid, typmod)
|
INTERVALYM | com.vertica.jdbc.VerticaYearMonthInterval
|
BINARY
|
BINARY | byte[]
|
VARBINARY
|
VARBINARY | byte[]
|
LONGVARBINARY
|
LONGVARBINARY | byte[]
|
NUMERIC
|
NUMERIC | java.math.BigDecimal
|
TYPE_SQL_GUID
|
UUID | java.util.UUID
|
ARRAY
|
ARRAY | java.lang.Object[]
|
ARRAY
|
SET | java.lang.Object[]
|
STRUCT
|
ROW | java.sql.Struct
|
ARRAY
|
MAP | java.lang.Object[]
|
ARRAY, SET, and MAP Columns
For example, the following methods run queries that return an ARRAY of some
Vertica type, which is then type cast to an array of its corresponding Java
type by the JDBC driver when retrieved with getArray()
. This particular example starts with ARRAY[INT] and ARRAY[FLOAT], so they are type cast to Long[]
and Double[]
, respectively, as determined by the type conversion table.
getArrayResultSetExample()
shows how the ARRAY can be processed as ajava.sql.ResultSet
. This example usesgetResultSet()
which returns the underlying array as anotherResultSet
. You can use this underlyingResultSet
to:- Retrieve the parent
ResultSet
. - Treat it as an
Object
array orResultSet
.
- Retrieve the parent
getArrayObjectExample()
shows how the ARRAY can be processed as a native Java array. This example usesgetArray()
which returns the underlying array as anObject
array rather than aResultSet
. This has the following implications:- You cannot use an underlying
Object
array to retrieve its parent array. - All underlying arrays are treated as
Object
arrays (rather thanResultSet
s.
- You cannot use an underlying
package com.vertica.jdbc.test.samples; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.sql.ResultSet; import java.sql.Array; import java.sql.Struct; public class ComplexTypesArraySamples { /** * Executes a query and gets a java.sql.Array from the ResultSet. It then uses the Array#getResultSet * method to get a ResultSet containing the contents of the array. * @param conn A Connection to a Vertica database * @throws SQLException */ public static void getArrayResultSetExample (Connection conn) throws SQLException { Statement stmt = conn.createStatement(); final String queryText = "SELECT ARRAY[ARRAY[1,2,3],ARRAY[4,5,6],ARRAY[7,8,9]]::ARRAY[ARRAY[INT]] as array"; final String targetColumnName = "array"; System.out.println ("queryText: " + queryText); ResultSet rs = stmt.executeQuery(queryText); int targetColumnId = rs.findColumn (targetColumnName); while (rs.next ()) { Array currentSqlArray = rs.getArray (targetColumnId); ResultSet level1ResultSet = currentSqlArray.getResultSet(); if (level1ResultSet != null) { while (level1ResultSet.next ()) { // The first column of the result set holds the row index int i = level1ResultSet.getInt(1) - 1; Array level2SqlArray = level1ResultSet.getArray (2); Object level2Object = level2SqlArray.getArray (); // For this ARRAY[INT], the driver returns a Long[] assert (level2Object instanceof Long[]); Long [] level2Array = (Long [])level2Object; System.out.println (" level1Object [" + i + "]: " + level2SqlArray.toString () + " (" + level2SqlArray.getClass() + ")"); for (int j = 0; j < level2Array.length; j++) { System.out.println (" Value [" + i + ", " + j + "]: " + level2Array[j] + " (" + level2Array[j].getClass() + ")"); } } } } } /** * Executes a query and gets a java.sql.Array from the ResultSet. It then uses the Array#getArray * method to get the contents of the array as a Java Object []. * @param conn A Connection to a Vertica database * @throws SQLException */ public static void getArrayObjectExample (Connection conn) throws SQLException { Statement stmt = conn.createStatement(); final String queryText = "SELECT ARRAY[ARRAY[0.0,0.1,0.2],ARRAY[1.0,1.1,1.2],ARRAY[2.0,2.1,2.2]]::ARRAY[ARRAY[FLOAT]] as array"; final String targetColumnName = "array"; System.out.println ("queryText: " + queryText); ResultSet rs = stmt.executeQuery(queryText); int targetColumnId = rs.findColumn (targetColumnName); while (rs.next ()) { // Get the java.sql.Array from the result set Array currentSqlArray = rs.getArray (targetColumnId); // Get the internal Java Object implementing the array Object level1ArrayObject = currentSqlArray.getArray (); if (level1ArrayObject != null) { // All returned instances are Object[] assert (level1ArrayObject instanceof Object[]); Object [] level1Array = (Object [])level1ArrayObject; System.out.println ("Vertica driver returned a: " + level1Array.getClass()); for (int i = 0; i < level1Array.length; i++) { Object level2Object = level1Array[i]; // For this ARRAY[FLOAT], the driver returns a Double[] assert (level2Object instanceof Double[]); Double [] level2Array = (Double [])level2Object; for (int j = 0; j < level2Array.length; j++) { System.out.println (" Value [" + i + ", " + j + "]: " + level2Array[j] + " (" + level2Array[j].getClass() + ")"); } } } } } }
The output of getArrayResultSetExample()
shows that the Vertica column type ARRAY[INT] is type cast to Long[]
:
queryText: SELECT ARRAY[ARRAY[1,2,3],ARRAY[4,5,6],ARRAY[7,8,9]]::ARRAY[ARRAY[INT]] as array level1Object [0]: [1,2,3] (class com.vertica.jdbc.jdbc42.S42Array) Value [0, 0]: 1 (class java.lang.Long) Value [0, 1]: 2 (class java.lang.Long) Value [0, 2]: 3 (class java.lang.Long) level1Object [1]: [4,5,6] (class com.vertica.jdbc.jdbc42.S42Array) Value [1, 0]: 4 (class java.lang.Long) Value [1, 1]: 5 (class java.lang.Long) Value [1, 2]: 6 (class java.lang.Long) level1Object [2]: [7,8,9] (class com.vertica.jdbc.jdbc42.S42Array) Value [2, 0]: 7 (class java.lang.Long) Value [2, 1]: 8 (class java.lang.Long) Value [2, 2]: 9 (class java.lang.Long)
The output of getArrayObjectExample()
shows that the Vertica column type ARRAY[FLOAT] is type cast to Double[]
:
queryText: SELECT ARRAY[ARRAY[0.0,0.1,0.2],ARRAY[1.0,1.1,1.2],ARRAY[2.0,2.1,2.2]]::ARRAY[ARRAY[FLOAT]] as array Vertica driver returned a: class [Ljava.lang.Object; Value [0, 0]: 0.0 (class java.lang.Double) Value [0, 1]: 0.1 (class java.lang.Double) Value [0, 2]: 0.2 (class java.lang.Double) Value [1, 0]: 1.0 (class java.lang.Double) Value [1, 1]: 1.1 (class java.lang.Double) Value [1, 2]: 1.2 (class java.lang.Double) Value [2, 0]: 2.0 (class java.lang.Double) Value [2, 1]: 2.1 (class java.lang.Double) Value [2, 2]: 2.2 (class java.lang.Double)
ROW Columns
Calling getObject()
on a java.sql.ResultSet
that contains a column of type ROW retrieves the column as a java.sql.Struct
which contains an Object[]
(itself retrievable with getAttributes()
).
Each element of the Object[]
represents an attribute from the struct, and each attribute has a corresponding Java type shown in the type conversion table above.
This example defines a ROW with the following attributes:
Name | Value | Vertica Type | Java Type ----------------------------------------------------------- name | Amy | VARCHAR | String date | '07/10/2021' | DATE | java.sql.Date id | 5 | INT | java.lang.Long current | false | BOOLEAN | java.lang.Boolean
package com.vertica.jdbc.test.samples; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.sql.ResultSet; import java.sql.Array; import java.sql.Struct; public class ComplexTypesSamples { /** * Executes a query and gets a java.sql.Struct from the ResultSet. It then uses the Struct#getAttributes * method to get the contents of the struct as a Java Object []. * @param conn A Connection to a Vertica database * @throws SQLException */ public static void getRowExample (Connection conn) throws SQLException { Statement stmt = conn.createStatement(); final String queryText = "SELECT ROW('Amy', '07/10/2021'::Date, 5, false) as rowExample(name, date, id, current)"; final String targetColumnName = "rowExample"; System.out.println ("queryText: " + queryText); ResultSet rs = stmt.executeQuery(queryText); int targetColumnId = rs.findColumn (targetColumnName); while (rs.next ()) { // Get the java.sql.Array from the result set Object currentObject = rs.getObject (targetColumnId); assert (currentObject instanceof Struct); Struct rowStruct = (Struct)currentObject; Object[] attributes = rowStruct.getAttributes(); // attributes.length should be 4 based on the queryText assert (attributes.length == 4); assert (attributes[0] instanceof String); assert (attributes[1] instanceof java.sql.Date); assert (attributes[2] instanceof java.lang.Long); assert (attributes[3] instanceof java.lang.Boolean); System.out.println ("attributes[0]: " + attributes[0] + " (" + attributes[0].getClass().getName() +")"); System.out.println ("attributes[1]: " + attributes[1] + " (" + attributes[1].getClass().getName() +")"); System.out.println ("attributes[2]: " + attributes[2] + " (" + attributes[2].getClass().getName() +")"); System.out.println ("attributes[3]: " + attributes[3] + " (" + attributes[3].getClass().getName() +")"); } } }
The output of getRowExample()
shows the attribute of each element and its corresponding Java type:
queryText: SELECT ROW('Amy', '07/10/2021'::Date, 5, false) as rowExample(name, date, id, current) attributes[0]: Amy (java.lang.String) attributes[1]: 2021-07-10 (java.sql.Date) attributes[2]: 5 (java.lang.Long) attributes[3]: false (java.lang.Boolean)