The JDBC driver transparently converts most Vertica data types to the appropriate Java data type. In a few cases, a Vertica data type cannot be directly translated to a Java data type; these exceptions are explained in this section.
This is the multi-page printable view of this section. Click here to print.
JDBC data types
- 1: The VerticaTypes class
- 2: Numeric data alias conversion
- 3: Using intervals with JDBC
- 4: UUID values
- 5: Complex types in JDBC
- 6: Date types in JDBC
1 - The VerticaTypes class
JDBC does not support all of the data types that Vertica supports. The Vertica JDBC client driver contains an additional class named VerticaTypes
that helps you handle identifying these Vertica-specific data types. It contains constants that you can use in your code to specify Vertica data types. This class defines two different categories of data types:
-
Vertica's 13 types of interval values. This class contains constant properties for each of these types. You can use these constants to select a specific interval type when instantiating members of the
VerticaDayTimeInterval
andVerticaYearMonthInterval
classes:// Create a day to second interval. VerticaDayTimeInterval dayInt = new VerticaDayTimeInterval( VerticaTypes.INTERVAL_DAY_TO_SECOND, 10, 0, 5, 40, 0, 0, false); // Create a year to month interval. VerticaYearMonthInterval monthInt = new VerticaYearMonthInterval( VerticaTypes.INTERVAL_YEAR_TO_MONTH, 10, 6, false);
-
Vertica UUID data type. One way you can use the
VerticaTypes.UUID
is to query a table's metadata to see if a column is a UUID. See UUID values for an example.
See the JDBC documentation for more information on this class.
2 - Numeric data alias conversion
The Vertica server supports data type aliases for integer, float and numeric types. The JDBC driver reports these as its basic data types (BIGINT, DOUBLE PRECISION, and NUMERIC), as follows:
Vertica Server Types and Aliases | Vertica JDBC Type |
---|---|
INTEGER INT INT8 BIGINT SMALLINT TINYINT |
BIGINT |
DOUBLE PRECISION FLOAT5 FLOAT8 REAL |
DOUBLE PRECISION |
DECIMAL NUMERIC NUMBER MONEY |
NUMERIC |
If a client application retrieves the values into smaller data types, Vertica JDBC driver does not check for overflows. The following example demonstrates the results of this overflow.
import java.sql.*;
import java.util.Properties;
public class JDBCDataTypes {
public static void main(String[] args) {
// If running under a Java 5 JVM, use you need to load the JDBC driver
// using Class.forname here
Properties myProp = new Properties();
myProp.put("user", "ExampleUser");
myProp.put("password", "password123");
Connection conn;
try {
conn = DriverManager.getConnection(
"jdbc:vertica://VerticaHost:5433/VMart",
myProp);
Statement statement = conn.createStatement();
// Create a table that will hold a row of different types of
// numeric data.
statement.executeUpdate(
"DROP TABLE IF EXISTS test_all_types cascade");
statement.executeUpdate("CREATE TABLE test_all_types ("
+ "c0 INTEGER, c1 TINYINT, c2 DECIMAL, "
+ "c3 MONEY, c4 DOUBLE PRECISION, c5 REAL)");
// Add a row of values to it.
statement.executeUpdate("INSERT INTO test_all_types VALUES("
+ "111111111111, 444, 55555555555.5555, "
+ "77777777.77, 88888888888888888.88, "
+ "10101010.10101010101010)");
// Query the new table to get the row back as a result set.
ResultSet rs = statement
.executeQuery("SELECT * FROM test_all_types");
// Get the metadata about the row, including its data type.
ResultSetMetaData md = rs.getMetaData();
// Loop should only run once...
while (rs.next()) {
// Print out the data type used to defined the column, followed
// by the values retrieved using several different retrieval
// methods.
String[] vertTypes = new String[] {"INTEGER", "TINYINT",
"DECIMAL", "MONEY", "DOUBLE PRECISION", "REAL"};
for (int x=1; x<7; x++) {
System.out.println("\n\nColumn " + x + " (" + vertTypes[x-1]
+ ")");
System.out.println("\tgetColumnType()\t\t"
+ md.getColumnType(x));
System.out.println("\tgetColumnTypeName()\t"
+ md.getColumnTypeName(x));
System.out.println("\tgetShort()\t\t"
+ rs.getShort(x));
System.out.println("\tgetLong()\t\t" + rs.getLong(x));
System.out.println("\tgetInt()\t\t" + rs.getInt(x));
System.out.println("\tgetByte()\t\t" + rs.getByte(x));
}
}
rs.close();
statement.executeUpdate("drop table test_all_types cascade");
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
The above example prints the following on the console when run:
Column 1 (INTEGER)
getColumnType() -5
getColumnTypeName() BIGINT
getShort() 455
getLong() 111111111111
getInt() -558038585
getByte() -57
Column 2 (TINYINT)
getColumnType() -5
getColumnTypeName() BIGINT
getShort() 444
getLong() 444
getInt() 444
getByte() -68
Column 3 (DECIMAL)
getColumnType() 2
getColumnTypeName() NUMERIC
getShort() -1
getLong() 55555555555
getInt() 2147483647
getByte() -1
Column 4 (MONEY)
getColumnType() 2
getColumnTypeName() NUMERIC
getShort() -13455
getLong() 77777777
getInt() 77777777
getByte() 113
Column 5 (DOUBLE PRECISION)
getColumnType() 8
getColumnTypeName() DOUBLE PRECISION
getShort() -1
getLong() 88888888888888900
getInt() 2147483647
getByte() -1
Column 6 (REAL)
getColumnType() 8
getColumnTypeName() DOUBLE PRECISION
getShort() 8466
getLong() 10101010
getInt() 10101010
getByte() 18
3 - Using intervals with JDBC
The JDBC standard does not contain a data type for intervals (the duration between two points in time). To handle Vertica's INTERVAL data type, you must use JDBC's database-specific object type.
When reading an interval value from a result set, use the ResultSet.getObject()
method to retrieve the value, and then cast it to one of the Vertica interval classes: VerticaDayTimeInterval
(which represents all ten types of day/time intervals) or VerticaYearMonthInterval
(which represents all three types of year/month intervals).
Note
The units interval style is not supported. Do not use the SET INTERVALSTYLE statement to change the interval style in your client applications.Using intervals in batch inserts
When inserting batches into tables that contain interval data, you must create instances of the VerticaDayTimeInterval
or VerticaYearMonthInterval
classes to hold the data you want to insert. You set values either when calling the class's constructor, or afterwards using setters. You then insert your interval values using the PreparedStatement.setObject()
method. You can also use the .setString()
method, passing it a string in "
DD
**HH
:
MM
:
SS
"
or "
YY
-
MM
"
format.
The following example demonstrates inserting data into a table containing a day/time interval and a year/month interval:
import java.sql.*;
import java.util.Properties;
// You need to import the Vertica JDBC classes to be able to instantiate
// the interval classes.
import com.vertica.jdbc.*;
public class IntervalDemo {
public static void main(String[] args) {
// If running under a Java 5 JVM, use you need to load the JDBC driver
// using Class.forname here
Properties myProp = new Properties();
myProp.put("user", "ExampleUser");
myProp.put("password", "password123");
Connection conn;
try {
conn = DriverManager.getConnection(
"jdbc:vertica://VerticaHost:5433/VMart", myProp);
// Create table for interval values
Statement stmt = conn.createStatement();
stmt.execute("DROP TABLE IF EXISTS interval_demo");
stmt.executeUpdate("CREATE TABLE interval_demo("
+ "DayInt INTERVAL DAY TO SECOND, "
+ "MonthInt INTERVAL YEAR TO MONTH)");
// Insert data into interval columns using
// VerticaDayTimeInterval and VerticaYearMonthInterval
// classes.
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO interval_demo VALUES(?,?)");
// Create instances of the Vertica classes that represent
// intervals.
VerticaDayTimeInterval dayInt = new VerticaDayTimeInterval(10, 0,
5, 40, 0, 0, false);
VerticaYearMonthInterval monthInt = new VerticaYearMonthInterval(
10, 6, false);
// These objects can also be manipulated using setters.
dayInt.setHour(7);
// Add the interval values to the batch
((VerticaPreparedStatement) pstmt).setObject(1, dayInt);
((VerticaPreparedStatement) pstmt).setObject(2, monthInt);
pstmt.addBatch();
// Set another row from strings.
// Set day interval in "days HH:MM:SS" format
pstmt.setString(1, "10 10:10:10");
// Set year to month value in "MM-YY" format
pstmt.setString(2, "12-09");
pstmt.addBatch();
// Execute the batch to insert the values.
try {
pstmt.executeBatch();
} catch (SQLException e) {
System.out.println("Error message: " + e.getMessage());
}
Reading interval values
You read an interval value from a result set using the ResultSet.getObject()
method, and cast the object to the appropriate Vertica object class: VerticaDayTimeInterval
for day/time intervals or VerticaYearMonthInterval
for year/month intervals. This is easy to do if you know that the column contains an interval, and you know what type of interval it is. If your application cannot assume the structure of the data in the result set it reads in, you can test whether a column contains a database-specific object type, and if so, determine whether the object belongs to either the VerticaDayTimeInterval
or VerticaYearMonthInterval
classes.
// Retrieve the interval values inserted by previous demo.
// Query the table to get the row back as a result set.
ResultSet rs = stmt.executeQuery("SELECT * FROM interval_demo");
// If you do not know the types of data contained in the result set,
// you can read its metadata to determine the type, and use
// additional information to determine the interval type.
ResultSetMetaData md = rs.getMetaData();
while (rs.next()) {
for (int x = 1; x <= md.getColumnCount(); x++) {
// Get data type from metadata
int colDataType = md.getColumnType(x);
// You can get the type in a string:
System.out.println("Column " + x + " is a "
+ md.getColumnTypeName(x));
// Normally, you'd have a switch statement here to
// handle all sorts of column types, but this example is
// simplified to just handle database-specific types
if (colDataType == Types.OTHER) {
// Column contains a database-specific type. Determine
// what type of interval it is. Assuming it is an
// interval...
Object columnVal = rs.getObject(x);
if (columnVal instanceof VerticaDayTimeInterval) {
// We know it is a date time interval
VerticaDayTimeInterval interval =
(VerticaDayTimeInterval) columnVal;
// You can use the getters to access the interval's
// data
System.out.print("Column " + x + "'s value is ");
System.out.print(interval.getDay() + " Days ");
System.out.print(interval.getHour() + " Hours ");
System.out.println(interval.getMinute()
+ " Minutes");
} else if (columnVal instanceof VerticaYearMonthInterval) {
VerticaYearMonthInterval interval =
(VerticaYearMonthInterval) columnVal;
System.out.print("Column " + x + "'s value is ");
System.out.print(interval.getYear() + " Years ");
System.out.println(interval.getMonth() + " Months");
} else {
System.out.println("Not an interval.");
}
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
The example prints the following to the console:
Column 1 is a INTERVAL DAY TO SECOND
Column 1's value is 10 Days 7 Hours 5 Minutes
Column 2 is a INTERVAL YEAR TO MONTH
Column 2's value is 10 Years 6 Months
Column 1 is a INTERVAL DAY TO SECOND
Column 1's value is 10 Days 10 Hours 10 Minutes
Column 2 is a INTERVAL YEAR TO MONTH
Column 2's value is 12 Years 9 Months
Another option is to use database metadata to find columns that contain intervals.
// Determine the interval data types by examining the database
// metadata.
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet dbMeta = dbmd.getColumns(null, null, "interval_demo", null);
int colcount = 0;
while (dbMeta.next()) {
// Get the metadata type for a column.
int javaType = dbMeta.getInt("DATA_TYPE");
System.out.println("Column " + ++colcount + " Type name is " +
dbMeta.getString("TYPE_NAME"));
if(javaType == Types.OTHER) {
// The SQL_DATETIME_SUB column in the metadata tells you
// Specifically which subtype of interval you have.
// The VerticaDayTimeInterval.isDayTimeInterval()
// methods tells you if that value is a day time.
//
int intervalType = dbMeta.getInt("SQL_DATETIME_SUB");
if(VerticaDayTimeInterval.isDayTimeInterval(intervalType)) {
// Now you know it is one of the 10 day/time interval types.
// When you select this column you can cast to
// VerticaDayTimeInterval.
// You can get more specific by checking intervalType
// against each of the 10 constants directly, but
// they all are represented by the same object.
System.out.println("column " + colcount + " is a " +
"VerticaDayTimeInterval intervalType = "
+ intervalType);
} else if(VerticaYearMonthInterval.isYearMonthInterval(
intervalType)) {
//now you know it is one of the 3 year/month intervals,
//and you can select the column and cast to
// VerticaYearMonthInterval
System.out.println("column " + colcount + " is a " +
"VerticaDayTimeInterval intervalType = "
+ intervalType);
} else {
System.out.println("Not an interval type.");
}
}
}
4 - UUID values
UUID is a core data type in Vertica. However, it is not a core Java data type. You must use the java.util.UUID
class to represent UUID values in your Java code. The JDBC driver does not translate values from Vertica to non-core Java data types. Therefore, you must send UUID values to Vertica using generic object methods such as PreparedStatement.setObject()
. You also use generic object methods (such as ResultSet.getObject()
) to retrieve UUID values from Vertica. You then cast the retrieved objects as a member of the java.util.UUID
class.
The following example code demonstrates inserting UUID values into and retrieving UUID values from Vertica.
package jdbc_uuid_example;
import java.sql.*;
import java.util.Properties;
public class VerticaUUIDExample {
public static void main(String[] args) {
Properties myProp = new Properties();
myProp.put("user", "dbadmin");
myProp.put("password", "");
Connection conn;
try {
conn = DriverManager.getConnection("jdbc:vertica://doch01:5433/VMart",
myProp);
Statement stmt = conn.createStatement();
// Create a table with a UUID column and a VARCHAR column.
stmt.execute("DROP TABLE IF EXISTS UUID_TEST CASCADE;");
stmt.execute("CREATE TABLE UUID_TEST (id UUID, description VARCHAR(25));");
// Prepare a statement to insert a UUID and a string into the table.
PreparedStatement ps = conn.prepareStatement("INSERT INTO UUID_TEST VALUES(?,?)");
java.util.UUID uuid; // Holds the UUID value.
for (Integer x = 0; x < 10; x++) {
// Generate a random uuid
uuid = java.util.UUID.randomUUID();
// Set the UUID value by calling setObject.
ps.setObject(1, uuid);
// Set the String value to indicate which UUID this is.
ps.setString(2, "UUID #" + x);
ps.execute();
}
// Query the uuid
ResultSet rs = stmt.executeQuery("SELECT * FROM UUID_TEST ORDER BY description ASC");
while (rs.next()) {
// Cast the object from the result set as a UUID.
uuid = (java.util.UUID) rs.getObject(1);
System.out.println(rs.getString(2) + " : " + uuid.toString());
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
The previous example prints output similar to the following:
UUID #0 : 67b6dcb6-c28c-4965-b9f7-5c830a04664d
UUID #1 : 485d3835-2887-4233-b003-392254fa97e0
UUID #2 : 81421f51-c803-473d-8cfc-2c184582a117
UUID #3 : bec8b86a-b650-47b0-852c-8229155332d9
UUID #4 : 8ae5e3ec-d143-4ef7-8901-24f6d0483abf
UUID #5 : 669696ce-5e86-4e87-b8d0-a937f5fc18d7
UUID #6 : 19609ec9-ec56-4444-9cfe-ad2b8de537dd
UUID #7 : 97182e1d-5c7e-4da1-9922-67e804fde173
UUID #8 : c76c3a2b-a9ef-4d65-b2fb-7c637f872b3c
UUID #9 : 3cbbcd26-c177-4277-b3df-bf4d9389f69d
Determining whether a column has a UUID data type
JDBC does not support the UUID data type. This limitation means you cannot use the usual ResultSetMetaData.getColumnType()
method to determine column's data type is UUID. Calling this method on a UUID column returns Types.OTHER
. This value is also to identify interval columns. You can use two ways to determine if a column contains UUIDs:
-
Use
ResultSetMetaData.getColumnTypeName()
to get the name of the column's data type. For UUID columns, this method returns the value"Uuid"
as aString
. -
Query the table's metadata to get the SQL data type of the column. If this value is equal to
VerticaTypes.UUID
, the column's data type is UUID.
The following example demonstrates both of these techniques:
// This example assumes you already have a database connection
// and result set from a query on a table that may contain a UUID.
// Get the metadata of the result set to get the column definitions
ResultSetMetaData meta = rs.getMetaData();
int colcount;
int maxcol = meta.getColumnCount();
System.out.println("Using column metadata:");
for (colcount = 1; colcount < maxcol; colcount++) {
// .getColumnType() always returns "OTHER" for UUID columns.
if (meta.getColumnType(colcount) == Types.OTHER) {
// To determine that it is a UUID column, test the name of the column type.
if (meta.getColumnTypeName(colcount).equalsIgnoreCase("uuid")) {
// It's a UUID column
System.out.println("Column "+ colcount + " is UUID");
}
}
}
// You can also query the table's metadata to find its column types and compare
// it to the VerticaType.UUID constant to see if it is a UUID column.
System.out.println("Using table metadata:");
DatabaseMetaData dbmd = conn.getMetaData();
// Get the metdata for the previously-created test table.
ResultSet tableMeta = dbmd.getColumns(null, null, "UUID_TEST", null);
colcount = 0;
// Each row in the result set has metadata that describes a single column.
while (tableMeta.next()) {
colcount++;
// The SQL_DATA_TYPE column holds the Vertica database data type. You compare
// this value to the VerticvaTypes.UUID constant to see if it is a UUID.
if (tableMeta.getInt("SQL_DATA_TYPE") == VerticaTypes.UUID) {
// Column is a UUID data type...
System.out.println("Column " + colcount + " is a UUID column.");
}
}
This example prints the following to the console if it is run after running the prior example:
Using column metadata:
Column 1 is UUID
Using table metadata:
Column 1 is a UUID column.
5 - 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
.
-
-
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.
-
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)
6 - Date types in JDBC
Converting a date to a string
For the purposes of this page, a large date is defined as a date with a year that exceeds 9999.
If your database doesn't contain any large dates, then you can reliably call toString()
to convert the dates to strings.
Otherwise, if your database contains large dates, you should use java.text.SimpleDateFormat
and its format()
method:
-
Define a String format with
java.text.SimpleDateFormat
. The number of characters inyyyy
in the format defines the minimum number of characters to use in the date. -
Call
SimpleDateFormat.format()
to convert thejava.sql.Date
object to a String.
Examples
For example, the following method returns a string when passed a java.sql.Date
object as an argument. Here, the year part of the format, YYYY
indicates that this format is compatible with all dates with at least four characters in its year.
#import java.sql.Date;
private String convertDate (Date date) {
SimpleDateFormat dateFormat = new SimpleDateFormat ("yyyy-MM-dd");
return dateFormat.format (date);
}