UUID values

UUID is a core data type in Vertica.

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 a String.
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.