JDBC 中的复杂类型
java.sql 查询的结果存储在 ResultSet 中。如果 ResultSet 包含复杂类型的列，您可以使用以下方法之一检索它：
-
对于 ROW 类型的列，使用
getObject()，它返回一个java.sql.Struct。
类型转换表
java.sql.Array 和 java.sql.Struct 对象都有其自己的用于访问复杂类型数据的 API。在每种情况下，数据都以 java.lang.Object 的形式返回，其类型需要强制转换为 Java 类型。预期的确切 Java 类型取决于复杂类型定义中使用的 Vertica 类型，如下面的类型转换表所示：
ARRAY、SET 和 MAP 列
例如，以下方法运行返回某些 Vertica 类型的 ARRAY 的查询，然后在使用 getArray() 检索时这些类型由 JDBC 驱动程序强制转换为其对应 Java 类型的数组。此特定示例以 ARRAY[INT] 和 ARRAY[FLOAT] 开头，因此其类型分别强制转换为由类型转换表确定的 Long[] 和 Double[]。
-
getArrayResultSetExample()显示了如何将 ARRAY 处理为java.sql.ResultSet。此示例使用getResultSet()将底层数组作为另一个ResultSet返回。您可以使用此底层ResultSet执行以下操作：-
检索父
ResultSet。 -
将其视为
Object数组或ResultSet。
-
-
getArrayObjectExample()展示了如何将 ARRAY 作为原生 Java 数组进行处理。此示例使用getArray()将底层数组作为Object数组（而不是ResultSet数组）返回。它具有以下含义：-
您不能使用底层
Object数组来检索其父数组。 -
所有底层数组都被视为
Object数组（而不是ResultSet）。
-
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() + ")");
}
}
}
}
}
}
getArrayResultSetExample() 的输出显示 Vertica 列类型 ARRAY[INT] 强制转换为 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)
getArrayObjectExample() 的输出显示 Vertica 列类型 ARRAY[FLOAT] 强制转换为 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 列
如果在包含 ROW 类型的列的 java.sql.ResultSet 上调用 getObject()，会将该列检索为 java.sql.Struct，其中包含 Object[]（本身可通过 getAttributes() 检索）。
Object[] 的每个元素代表该结构中的一个属性，每个属性都有对应的 Java 类型，如上面的类型转换表所示。
此示例定义具有以下属性的 ROW：
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() +")");
}
}
}
getRowExample() 的输出显示了每个元素的属性及其对应的 Java 类型：
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)