Complex types in JDBC

The results of a java.sql query are stored in a ResultSet.

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 a java.sql.Array.

  • For columns of type ROW, use getObject(), which returns a java.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 a java.sql.ResultSet. This example uses getResultSet() which returns the underlying array as another ResultSet. You can use this underlying ResultSet to:

    • Retrieve the parent ResultSet.

    • Treat it as an Object array or ResultSet.

  • getArrayObjectExample() shows how the ARRAY can be processed as a native Java array. This example uses getArray() which returns the underlying array as an Object array rather than a ResultSet. 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 than ResultSets.

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)