Java

• 1: JDBC feature support
• 2: Creating and configuring a connection
• 2.1: JDBC connection properties
• 2.2: Setting and getting connection property values
• 2.3: Configuring TLS for JDBC clients
• 2.4: Setting and returning a client connection label
• 2.5: Setting the locale for JDBC sessions
• 2.6: Changing the transaction isolation level
• 2.7: JDBC connection pools
• 2.8: Load balancing in JDBC
• 2.9: JDBC connection failover
• 3: JDBC data types
• 3.1: The VerticaTypes class
• 3.2: Numeric data alias conversion
• 3.3: Using intervals with JDBC
• 3.4: UUID values
• 3.5: Complex types in JDBC
• 3.6: Date types in JDBC
• 4: Executing queries through JDBC
• 5: Canceling JDBC queries
• 6: Loading data through JDBC
• 6.1: Using a single row insert
• 6.2: Batch inserts using JDBC prepared statements
• 6.2.1: Error handling during batch loads
• 6.2.2: Identifying accepted and rejected rows (JDBC)
• 6.2.3: Rolling back batch loads on the server
• 6.3: Bulk loading using the COPY statement
• 6.4: Streaming data via JDBC
• 6.4.1: Using VerticaCopyStream
• 6.4.2: Using COPY LOCAL with JDBC
• 7: Handling errors
• 7.1: SQLState mapping to Java exception classes
• 8: Routing JDBC queries directly to a single node
• 8.1: Creating tables and projections for use with the routable query API
• 8.2: Creating a connection for routable queries
• 8.3: Defining the query for routable queries using the VerticaRoutableExecutor class
• 8.4: Defining the query for routable queries using the VGet class
• 8.5: Routable query performance and troubleshooting
• 8.6: Pre-segmenting data using VHash
• 9: Timezones and daylight savings time

1 - JDBC feature support

The Vertica JDBC driver complies with the JDBC 4.0 standards (although it does not implement all of the optional features in them). Your application can use the DatabaseMetaData class to determine if the driver supports a particular feature it wants to use. In addition, the driver implements the Wrapper interface, which lets your client code discover Vertica-specific extensions to the JDBC standard classes, such as VerticaConnection and VerticaStatement classes.

Some important facts to keep in mind when using the Vertica JDBC driver:

• Cursors are forward only and are not scrollable. Result sets cannot be updated.

• A connection supports executing a single statement at any time. If you want to execute multiple statements simultaneously, you must open multiple connections.

• CallableStatement is supported as of the version 12.0.0 of the client driver.

Multiple SQL statement support

The Vertica JDBC driver can execute strings containing multiple statements. For example:

stmt.executeUpdate("CREATE TABLE t(a INT);INSERT INTO t VALUES(10);");

Only the Statement interface supports executing strings containing multiple SQL statements. You cannot use multiple statement strings with PreparedStatement. COPY statements that copy a file from a host file system work in a multiple statement string. However, client COPY statements (COPY FROM STDIN) do not work.

Multiple batch conversion to COPY statements

The Vertica JDBC driver converts all batch inserts into Vertica COPY statements. If you turn off your JDBC connection's AutoCommit property, the JDBC driver uses a single COPY statement to load data from sequential batch inserts which can improve load performance by reducing overhead. See Batch inserts using JDBC prepared statements for details.

JDBC version

The version of JDBC is determined by the version of the JVM. A JVM version of 8 or higher uses JDBC 4.2.

Multiple active result sets (MARS)

The Vertica JDBC driver supports Multiple active result sets (MARS). MARS allows the execution of multiple queries on a single connection. While ResultBufferSize sends the results of a query directly to the client, MARS stores the results first on the server. Once query execution has finished and all of the results have been stored, you can make a retrieval request to the server to have rows returned to the client.

2 - Creating and configuring a connection

Before your Java application can interact with Vertica, it must create a connection. Connecting to Vertica using JDBC is similar to connecting to most other databases.

Importing SQL packages

Before creating a connection, you must import the Java SQL packages. A simple way to do so is to import the entire package using a wildcard:

import java.sql.*;

You may also want to import the Properties class. You can use an instance of this class to pass connection properties when instantiating a connection, rather than encoding everything within the connection string:

import java.util.Properties;

Applications can run in a Java 6 or later JVM. If so, then the JVM automatically loads the Vertica JDBC 4.0-compatible driver without requiring the call to Class.forName. However, making this call does not adversely affect the process. Thus, if you want your application to be compatible with both Java 5 and Java 6 (or later) JVMs, it can still call Class.forName.

Opening the connection

With SQL packages imported, you are ready to create your connection by calling the DriverManager.getConnection() method. You supply this method with at least the following information:

• The IP address or host name of a node in the database cluster.

  You can provide an IPv4 address, IPv6 address, or host name.

  In mixed IPv4/IPv6 networks, the DNS server configuration determines which IP version address is sent first. Use the PreferredAddressFamily option to force the connection to use either IPv4 or IPv6.

• Port number for the database

• Username of a database user account

• Password of the user (if the user has a password)

The first three parameters are always supplied as part of the connection string, a URL that tells the JDBC driver where to find the database. The format of the connection string is (/databaseName is optional):

jdbc:vertica://VerticaHost:portNumber/databaseName

The first portion of the connection string selects the Vertica JDBC driver, followed by the location of the database.

You can provide the last two parameters, username and password, to the JDBC driver, in one of three ways:

• As part of the connection string. The parameters are encoded similarly to URL parameters:

  "jdbc:vertica://VerticaHost:portNumber/databaseName?user=username&password=password"
  

• As separate parameters to DriverManager.getConnection():

  Connection conn = DriverManager.getConnection(
          "jdbc:vertica://VerticaHost:portNumber/databaseName",
          "username", "password");
  

• In a Properties object:

  Properties myProp = new Properties();
  myProp.put("user", "username");
  myProp.put("password", "password");
  Connection conn = .getConnection(
      "jdbc:vertica://VerticaHost:portNumber/databaseName", myProp);
  

Of these three methods, the Properties object is the most flexible because it makes passing additional connection properties to the getConnection() method easy. See Connection Properties and Setting and getting connection property values for more information about the additional connection properties.

If there is any problem establishing a connection to the database, the getConnection() method throws a SQLException on one of its subclasses. To prevent an exception, enclose the method within a try-catch block, as shown in the following complete example of establishing a connection.

import java.sql.*;
import java.util.Properties;

public class VerySimpleVerticaJDBCExample {
    public static void main(String[] args) {
        /*
         * If your client needs to run under a Java 5 JVM, It will use the older
         * JDBC 3.0-compliant driver, which requires you manually load the
         * driver using Class.forname
         */
        /*
         * try { Class.forName("com.vertica.jdbc.Driver"); } catch
         * (ClassNotFoundException e) { // Could not find the driver class.
         * Likely an issue // with finding the .jar file.
         * System.err.println("Could not find the JDBC driver class.");
         * e.printStackTrace(); return; // Bail out. We cannot do anything
         * further. }
         */
        Properties myProp = new Properties();
        myProp.put("user", "dbadmin");
        myProp.put("password", "vertica");
        myProp.put("loginTimeout", "35");
        myProp.put("KeystorePath", "c:/keystore/keystore.jks");
     myProp.put("KeystorePassword", "keypwd");
     myProp.put("TrustStorePath", "c:/truststore/localstore.jks");
     myProp.put("TrustStorePassword", "trustpwd");
        Connection conn;
        try {
            conn = DriverManager.getConnection(
                    "jdbc:vertica://V_vmart_node0001.example.com:5433/vmart", myProp);
            System.out.println("Connected!");
            conn.close();
        } catch (SQLTransientConnectionException connException) {
            // There was a potentially temporary network error
            // Could automatically retry a number of times here, but
            // instead just report error and exit.
            System.out.print("Network connection issue: ");
            System.out.print(connException.getMessage());
            System.out.println(" Try again later!");
            return;
        } catch (SQLInvalidAuthorizationSpecException authException) {
            // Either the username or password was wrong
            System.out.print("Could not log into database: ");
            System.out.print(authException.getMessage());
            System.out.println(" Check the login credentials and try again.");
            return;
        } catch (SQLException e) {
            // Catch-all for other exceptions
            e.printStackTrace();
        }
    }
}

Creating a connection with a keystore and truststore

You can create secure connections with your JDBC client driver using a keystore and a truststore. For more information on security within Vertica, refer to Security and authentication.

For examples and instructions on how to generate (or import external) certificates in Vertica, see Generating TLS certificates and keys.

To view your keys and certificates in Vertica, see CERTIFICATES and CRYPTOGRAPHIC_KEYS.

1. Generate your own self-signed certificate or use an existing CA (certificate authority) certificate as the root CA. For information on this process, refer to the Schannel documentation.

2. Optional: Generate or import an intermediate CA certificate signed by your root CA. While not required, having an intermediate CA can be useful for testing and debugging your connection.

3. Generate and sign (or import) a server certificate for Vertica.

4. Use ALTER TLS CONFIGURATION to configure Vertica to use client/server TLS for new connections. For more information, see Configuring client-server TLS.

   For Server Mode (no client-certificate verification):

   => ALTER TLS CONFIGURATION server TLSMODE 'ENABLE';
   => ALTER TLS CONFIGURATION server CERTIFICATE server_cert;
   

   For Mutual Mode (client-certificate verification of varying strictness depending on the TLSMODE):

   => ALTER TLS CONFIGURATION server TLSMODE 'TRY_VERIFY';
   => ALTER TLS CONFIGURATION server CERTIFICATE server_cert ADD CA CERTIFICATES ca_cert;
   

5. Optionally, you can disable all non-SSL connections with CREATE AUTHENTICATION.

   => CREATE AUTHENTICATION no_tls METHOD 'reject' HOST NO TLS '0.0.0.0/0';
   => CREATE AUTHENTICATION no_tls METHOD 'reject' HOST NO TLS '::/128';
   

6. Generate and sign a certificate for your client using the same CA that signed your server certificate.

7. Convert your chain of pem certificates to a single pkcs 12 file.

8. Import the client key and chain into a keystore JKS file from your pkcs12 file. For information on using the keytool command interface, refer to the Java documentation.

   $ keytool -importkeystore -srckeystore -alias my_alias -srcstoretype PKCS12 -srcstorepass my_password -noprompt -deststorepass my_password -destkeypass my_password -destkeystore /tmp/keystore.jks
   

9. Import the CA into a truststore JKS file.

   $ keytool -import -file certs/intermediate_ca.pem -alias my_alias -trustcacerts -keystore /tmp/truststore.jks -storepass my_truststore_password -noprompt
   

Usage considerations

• When you disconnect a user session, any uncommitted transactions are automatically rolled back.

• If your database is not compliant with your Vertica license terms, Vertica issues a SQLWarning when you establish the connection to the database. You can retrieve this warning using the Connection.getWarnings() method. See Managing licenses for more information about complying with your license terms.

2.1 - JDBC connection properties

You use connection properties to configure the connection between your JDBC client application and your Vertica database. The properties provide the basic information about the connections, such as the server name and port number to use to connect to your database. They also let you tune the performance of your connection and enable logging.

You can set a connection property in one of the following ways:

• Include the property name and value as part of the connection string you pass to the method DriverManager.getConnection().

• Set the properties in a Properties object, and then pass it to the method DriverManager.getConnection().

• Use the method VerticaConnection.setProperty(). With this approach, you can change only those connection properties that remain changeable after the connection has been established.

Also, some standard JDBC connection properties have getters and setters on the Connection interface, such as Connection.setAutoCommit().

Connection properties

The properties in the following table can only be set before you open the connection to the database. Two of them are required for every connection.

OAuth connection properties

The following connection properties pertain to OAuth in JDBC.

Timeout properties

With the following parameters, you can specify various timeouts for each step and the overall connection of JDBC to your Vertica database.

The relationship between these properties and the role they play when JDBC attempts to connect to a Vertica database is illustrated in the following diagram:

General properties

The following properties can be set after the connection is established. None of these properties are required.

Logging properties

The properties that control client logging must be set before the connection is opened. None of these properties are required, and none can be changed after the Connection object has been instantiated.

Kerberos connection parameters

Use the following parameters to set the service and host name principals for client authentication using Kerberos.

Routable connection API connection parameters

Use the following parameters to set properties to enable and configure the connection for Routable Connection lookups.

For information about manipulating these attributes, see Setting and getting connection property values.

2.2 - Setting and getting connection property values

You can set a connection property in one of the following ways:

• Include the property name and value as part of the connection string you pass to the method DriverManager.getConnection().

• Set the properties in a Properties object, and then pass it to the method DriverManager.getConnection().

• Use the method VerticaConnection.setProperty(). With this approach, you can change only those connection properties that remain changeable after the connection has been established.

Also, some standard JDBC connection properties have getters and setters on the Connection interface, such as Connection.setAutoCommit().

Setting properties when connecting

When creating a connection to Vertica, you can set connection properties by:

• Specifying them in the connection string.

• Modifying the Properties object passed to getConnection().

Connection string properties

You can specify connection properties in the connection string with the same URL parameter format used for usernames and passwords. For example, the following string enables a TLS connection:

"jdbc:vertica://VerticaHost:5433/db?user=UserName&password=Password&TLSmode=require"

Setting a host name using the setProperty() method overrides the host name set in a connection string. If this occurs, Vertica might not be able to connect to a host. For example, using the connection string above, the following overrides the VerticaHost name:

Properties props = new Properties();
props.setProperty("dataSource", dataSourceURL);
props.setProperty("database", database);
props.setProperty("user", user);
props.setProperty("password", password);
ps.setProperty("jdbcDriver", jdbcDriver);
props.setProperty("hostName", "NonVertica_host");

However, if a new connection or override connection is needed, you can enter a valid host name in the hostname properties object.

The NonVertica_host hostname overrides VerticaHost name in the connection string. To avoid this issue, comment out the props.setProperty("hostName", "NonVertica_host");line:

//props.setProperty("hostName", "NonVertica_host");

Properties object

To set connection properties with the Properties object passed to the getConnection() call:

1. Import the java.util.Properties class to instantiate a Properties object.

2. Use the put() method to add name-value pairs to the object.

Properties myProp = new Properties();
myProp.put("user", "ExampleUser");
myProp.put("password", "password123");
myProp.put("LoginTimeout", "35");
Connection conn;
try {
    conn = DriverManager.getConnection(
        "jdbc:vertica://VerticaHost:/ExampleDB", myProp);
} catch (SQLException e) {
    e.printStackTrace();
}

Getting and setting properties after connecting

After you establish a connection with Vertica, you can use the VerticaConnection methods getProperty() and setProperty() to set the values of some connection properties, respectively.

The VerticaConnection.getProperty() method lets you get the value of some connection properties. Use this method to change the value for properties that can be set after you establish a connection with Vertica.

Because these methods are Vertica-specific, you must cast your Connection object to the VerticaConnection interface with one of the following methods:

• Import the Connection object into your client application.

• Use a fully-qualified reference: com.vertica.jdbc.VerticaConnection.

The following example demonstrates getting and setting the value of the ReadOnly property.

import java.sql.*;
import java.util.Properties;
import com.vertica.jdbc.*;

public class SetConnectionProperties {
    public static void main(String[] args) {
        // Note: If your application needs to run under Java 5, you need to
        // load the JDBC driver using Class.forName() here.
        Properties myProp = new Properties();
        myProp.put("user", "ExampleUser");
        myProp.put("password", "password123");
        // Set ReadOnly to true initially
        myProp.put("ReadOnly", "true");
        Connection conn;
        try {
            conn = DriverManager.getConnection(
                            "jdbc:vertica://VerticaHost:5433/ExampleDB",
                            myProp);
            // Show state of the ReadOnly property. This was set at the
            // time the connection was created.
            System.out.println("ReadOnly state: "
                            + ((VerticaConnection) conn).getProperty(
                                            "ReadOnly"));

            // Change it and show it again
            ((VerticaConnection) conn).setProperty("ReadOnly", false);
            System.out.println("ReadOnly state is now: " +
                             ((VerticaConnection) conn).getProperty(
                                             "ReadOnly"));
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

When run, the example prints the following on the standard output:

ReadOnly state: true
ReadOnly state is now: false

2.3 - Configuring TLS for JDBC clients

To configure TLS for JDBC clients:

• Configure Vertica for client-server TLS.

• Set the keystore and truststore properties.

• Set the TLSmode parameter.

• (Optional) Run the SSL debug utility to test your configuration.

Setting keystore/truststore properties

You can set the keystore and truststore properties in the following ways, each with their own pros and cons:

• At the driver level.

• At the JVM level.

Driver-level configuration

If you use tools like DbVizualizer with many connections, configure the keystore and truststore with the JDBC connection properties. This does, however, expose these values in the connection string:

• KeyStorePath

• KeyStorePassword

• TrustStorePath

• TrustStorePassword

For example:

Properties props = new Properties();
props.setProperty("KeyStorePath", keystorepath);
props.setProperty("KeyStorePassword", keystorepassword);
props.setProperty("TrustStorePath", truststorepath);
props.setProperty("TrustStorePassword", truststorepassword);

JVM-level configuration

Setting keystore and truststore parameters at the JVM level excludes them from the connection string, which may be more accommodating for environments with more stringent security requirements:

• javax.net.ssl.keyStore

• javax.net.ssl.trustStore

• javax.net.ssl.keyStorePassword

• javax.net.ssl.trustStorePassword

For example:

System.setProperty("javax.net.ssl.keyStore","clientKeyStore.key");
System.setProperty("javax.net.ssl.trustStore","clientTrustStore.key");
System.setProperty("javax.net.ssl.keyStorePassword","new_keystore_password")
System.setProperty("javax.net.ssl.trustStorePassword","new_truststore_password");

Set the TLSmode connection property

You can set the TLSmode connection property to determine how certificates are handled. TLSmode is disabled by default.

TLSmode identifies the security level that Vertica applies to the JDBC connection. Vertica must be configured to handle TLS connections before you can establish an encrypted connection to it. See TLS protocol for details. Valid values are:

• disable: JDBC connects using plain text and implements no security measures.

• require: JDBC connects using TLS without verifying the CA certificate.

• verify-ca: JDBC connects using TLS and confirms that the server certificate has been signed by the certificate authority. This setting is equivalent to the deprecated ssl=true property.

• verify-full: JDBC connects using TLS, confirms that the server certificate has been signed by the certificate authority, and verifies that the host name matches the name provided in the server certificate.

If this property and the SSL property are set, this property takes precedence.

For example, to configure JDBC to connect to the server with TLS without verifying the CA certificate, you can set the TLSmode property to 'require' with the method VerticaConnection.setProperty():

Properties props = new Properties();
props.setProperty("TLSmode", "verify-full");

Run the SSL debug utility

After configuring TLS, you can run the following for a debugging utility:

$ java -Djavax.net.debug=ssl

You can use several debug specifiers (options) with the debug utility. The specifiers help narrow the scope of the debugging information that is returned. For example, you could specify one of the options that prints handshake messages or session activity.

For information on the debug utility and its options, see Debugging Utilities in the Oracle document, JSSE Reference Guide.

For information on interpreting debug information, refer to the Oracle document, Debugging SSL/TLS Connections.

2.4 - Setting and returning a client connection label

The JDBC Client has a method to set and return the client connection label: getClientInfo() and setClientInfo(). You can use these methods with the SQL Functions GET_CLIENT_LABEL and SET_CLIENT_LABEL.

When you use these two methods, make sure you pass the string value APPLICATIONNAME to both the setter and getter methods.

Use setClientInfo() to create a client label, and use getClientInfo() to return the client label:

import java.sql.*;
import java.util.Properties;

public class ClientLabelJDBC {

    public static void main(String[] args) {
        Properties myProp = new Properties();
        myProp.put("user", "dbadmin");
        myProp.put("password", "");
        myProp.put("loginTimeout", "35");
        Connection conn;
        try {
            conn = DriverManager.getConnection(
                    "jdbc:vertica://example.com:5433/mydb", myProp);
            System.out.println("Connected!");
            conn.setClientInfo("APPLICATIONNAME", "JDBC Client - Data Load");
            System.out.println("New Conn label: " + conn.getClientInfo("APPLICATIONNAME"));
            conn.close();
        } catch (SQLTransientConnectionException connException) {
            // There was a potentially temporary network error
            // Could automatically retry a number of times here, but
            // instead just report error and exit.
            System.out.print("Network connection issue: ");
            System.out.print(connException.getMessage());
            System.out.println(" Try again later!");
            return;
        } catch (SQLInvalidAuthorizationSpecException authException) {
            // Either the username or password was wrong
            System.out.print("Could not log into database: ");
            System.out.print(authException.getMessage());
            System.out.println(" Check the login credentials and try again.");
            return;
        } catch (SQLException e) {
            // Catch-all for other exceptions
            e.printStackTrace();
        }
    }
}

When you run this method, it prints the following result to the standard output:

Connected!
New Conn Label: JDBC Client - Data Load

2.5 - Setting the locale for JDBC sessions

You set the locale for a connection while opening it by including a SET LOCALE statement in the ConnSettings property, or by executing a SET LOCALE statement at any time after opening the connection. Changing the locale of a Connection object affects all of the Statement objects you instantiated using it.

You can get the locale by executing a SHOW LOCALE query. The following example demonstrates setting the locale using ConnSettings and executing a statement, as well as getting the locale:

import java.sql.*;
import java.util.Properties;

public class GetAndSetLocale {
    public static void main(String[] args) {

     // If running under a Java 5 JVM, you need to load the JDBC driver
     // using Class.forname here

     Properties myProp = new Properties();
        myProp.put("user", "ExampleUser");
        myProp.put("password", "password123");

        // Set Locale to true en_GB on connection. After the connection
        // is established, the JDBC driver runs the statements in the
        // ConnSettings property.
        myProp.put("ConnSettings", "SET LOCALE TO en_GB");
        Connection conn;
        try {
            conn = DriverManager.getConnection(
                            "jdbc:vertica://VerticaHost:5433/ExampleDB",
                            myProp);

            // Execute a query to get the locale. The results should
            // show "en_GB" as the locale, since it was set by the
            // conn settings property.
            Statement stmt = conn.createStatement();
            ResultSet rs = null;
            rs = stmt.executeQuery("SHOW LOCALE");
            System.out.print("Query reports that Locale is set to: ");
            while (rs.next()) {
                System.out.println(rs.getString(2).trim());
            }

            // Now execute a query to set locale.
            stmt.execute("SET LOCALE TO en_US");

            // Run query again to get locale.
            rs = stmt.executeQuery("SHOW LOCALE");
            System.out.print("Query now reports that Locale is set to: ");
            while (rs.next()) {
                System.out.println(rs.getString(2).trim());
            }
            // Clean up
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Running the above example displays the following on the system console:

Query reports that Locale is set to: en_GB (LEN)
Query now reports that Locale is set to: en_US (LEN)

Notes:

• JDBC applications use a UTF-16 character set encoding and are responsible for converting any non-UTF-16 encoded data to UTF-16. Failing to convert the data can result in errors or the data being stored incorrectly.

• The JDBC driver converts UTF-16 data to UTF-8 when passing to the Vertica server and converts data sent by Vertica server from UTF-8 to UTF-16 .

2.6 - Changing the transaction isolation level

Changing the transaction isolation level lets you choose how transactions prevent interference from other transactions. By default, the JDBC driver matches the transaction isolation level of the Vertica server. The Vertica default transaction isolation level is READ_COMMITTED, which means any changes made by a transaction cannot be read by any other transaction until after they are committed. This prevents a transaction from reading data inserted by another transaction that is later rolled back.

Vertica also supports the SERIALIZABLE transaction isolation level. This level locks tables to prevent queries from having the results of their WHERE clauses changed by other transactions. Locking tables can have a performance impact, since only one transaction is able to access the table at a time.

A transaction retains its isolation level until it completes, even if the session's isolation level changes during the transaction. Vertica internal processes (such as the Tuple Mover and refresh operations) and DDL operations always run at the SERIALIZABLE isolation level to ensure consistency.

You can change the transaction isolation level connection property after the connection has been established using the Connection object's setter (setTransactionIsolation()) and getter (getTransactionIsolation()). The value for transaction isolation property is an integer. The Connection interface defines constants to help you set the value in a more intuitive manner:

The following example demonstrates setting the transaction isolation level to SERIALIZABLE.

import java.sql.*;
import java.util.Properties;

public class SetTransactionIsolation {
    public static void main(String[] args) {
        Properties myProp = new Properties();
        myProp.put("user", "ExampleUser");
        myProp.put("password", "password123");
        Connection conn;
        try {
            conn = DriverManager.getConnection(
                            "jdbc:vertica://VerticaHost:5433/ExampleDB",
                            myProp);
            // Get default transaction isolation
            System.out.println("Transaction Isolation Level: "
                            + conn.getTransactionIsolation());
            // Set transaction isolation to SERIALIZABLE
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            // Get the transaction isolation again
            System.out.println("Transaction Isolation Level: "
                            + conn.getTransactionIsolation());
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Running the example results in the following being printed out to the console:

Transaction Isolation Level: 2Transaction Isolation Level: 8

2.7 - JDBC connection pools

A pooling data source uses a collection of persistent connections in order to reduce the overhead of repeatedly opening network connections between the client and server. Opening a new connection for each request is more costly for both the server and the client than keeping a small pool of connections open constantly, ready to be used by new requests. When a request comes in, one of the pre-existing connections in the pool is assigned to it. Only if there are no free connections in the pool is a new connection created. Once the request is complete, the connection returns to the pool and waits to service another request.

The Vertica JDBC driver supports connection pooling as defined in the JDBC 4.0 standard. If you are using a J2EE-based application server in conjunction with Vertica, it should already have a built-in data pooling feature. All that is required is that the application server work with the PooledConnection interface implemented by Vertica's JDBC driver. An application server's pooling feature is usually well-tuned for the works loads that the server is designed to handle. See your application server's documentation for details on how to work with pooled connections. Normally, using pooled connections should be transparent in your code—you will just open connections and the application server will worry about the details of pooling them.

If you are not using an application server, or your application server does not offer connection pooling that is compatible with Vertica, you can use a third-party pooling library, such as the open-source c3p0 or DBCP libraries, to implement connection pooling.

2.8 - Load balancing in JDBC

Native connection load balancing

Native connection load balancing helps spread the overhead caused by client connections on the hosts in the Vertica database. Both the server and the client must enable native connection load balancing. If enabled by both, then when the client initially connects to a host in the database, the host picks a host to handle the client connection from a list of the currently up hosts in the database, and informs the client which host it has chosen.

If the initially-contacted host does not choose itself to handle the connection, the client disconnects, then opens a second connection to the host selected by the first host. The connection process to this second host proceeds as usual—if SSL is enabled, then SSL negotiations begin, otherwise the client begins the authentication process. See About native connection load balancing for details.

To enable native load balancing on your client, set the ConnectionLoadBalance connection parameter to true. The following example demonstrates:

• Connecting to the database several times with native connection load balancing enabled.

• Fetching the name of the node handling the connection from the V_MONITOR.CURRENT_SESSION system table.

import java.sql.*;
import java.util.Properties;
import java.sql.*;
import java.util.Properties;

public class JDBCLoadingBalanceExample {
    public static void main(String[] args) {
        Properties myProp = new Properties();
        myProp.put("user", "dbadmin");
        myProp.put("password", "example_password123");
        myProp.put("loginTimeout", "35");
        myProp.put("ConnectionLoadBalance", "1");
        Connection conn;

        for (int x = 1; x <= 4; x++) {
            try {
                System.out.print("Connect attempt #" + x + "...");
                conn = DriverManager.getConnection(
                    "jdbc:vertica://node01.example.com:5433/vmart", myProp);
                Statement stmt = conn.createStatement();
                // Set the load balance policy to round robin before testing the database's load balancing.
                stmt.execute("SELECT SET_LOAD_BALANCE_POLICY('ROUNDROBIN');");
                // Query system to table to see what node we are connected to. Assume a single row
                // in response set.
                ResultSet rs = stmt.executeQuery("SELECT node_name FROM v_monitor.current_session;");
                rs.next();
                System.out.println("Connected to node " + rs.getString(1).trim());
                conn.close();
            } catch (SQLTransientConnectionException connException) {
                // There was a potentially temporary network error
                // Could automatically retry a number of times here, but
                // instead just report error and exit.
                System.out.print("Network connection issue: ");
                System.out.print(connException.getMessage());
                System.out.println(" Try again later!");
                return;
            } catch (SQLInvalidAuthorizationSpecException authException) {
                // Either the username or password was wrong
                System.out.print("Could not log into database: ");
                System.out.print(authException.getMessage());
                System.out.println(" Check the login credentials and try again.");
                return;
            } catch (SQLException e) {
                // Catch-all for other exceptions
                e.printStackTrace();
            }
        }
    }
}

Running the previous example produces the following output:

Connect attempt #1...Connected to node v_vmart_node0002
Connect attempt #2...Connected to node v_vmart_node0003
Connect attempt #3...Connected to node v_vmart_node0001
Connect attempt #4...Connected to node v_vmart_node0002

Hostname-based load balancing

You can load balance workloads by resolving a single hostname to multiple IP addresses. When you specify the hostname for the DriverManager.getConnection() method, the hostname resolves to a random listed IP address from the each connection.

For example, the hostname verticahost.example.com has the following entries in etc/hosts:

192.0.2.0 verticahost.example.com
192.0.2.1 verticahost.example.com
192.0.2.2 verticahost.example.com

Specifying verticahost.example.com as the connection for DriverManager.getConnection() randomly resolves to one of the listed IP address.

2.9 - JDBC connection failover

If a client application attempts to connect to a host in the Vertica cluster that is down, the connection attempt fails when using the default connection configuration. This failure usually returns an error to the user. The user must either wait until the host recovers and retry the connection or manually edit the connection settings to choose another host.

Due to Vertica Analytic Database's distributed architecture, you usually do not care which database host handles a client application's connection. You can use the client driver's connection failover feature to prevent the user from getting connection errors when the host specified in the connection settings is unreachable. The JDBC driver gives you several ways to let the client driver automatically attempt to connect to a different host if the one specified in the connection parameters is unreachable:

• Configure your DNS server to return multiple IP addresses for a host name. When you use this host name in the connection settings, the client attempts to connect to the first IP address from the DNS lookup. If the host at that IP address is unreachable, the client tries to connect to the second IP, and so on until it either manages to connect to a host or it runs out of IP addresses.

• Supply a list of backup hosts for the client driver to try if the primary host you specify in the connection parameters is unreachable.

• (JDBC only) Use driver-specific connection properties to manage timeouts before attempting to connect to the next node.

For all methods, the process of failover is transparent to the client application (other than specifying the list of backup hosts, if you choose to use the list method of failover). If the primary host is unreachable, the client driver automatically tries to connect to other hosts.

Failover only applies to the initial establishment of the client connection. If the connection breaks, the driver does not automatically try to reconnect to another host in the database.

Choosing a failover method

You usually choose to use one of the two failover methods. However, they do work together. If your DNS server returns multiple IP addresses and you supply a list of backup hosts, the client first tries all of the IPs returned by the DNS server, then the hosts in the backup list.

The DNS method of failover centralizes the configuration client failover. As you add new nodes to your Vertica Analytic Database cluster, you can choose to add them to the failover list by editing the DNS server settings. All client systems that use the DNS server to connect to Vertica Analytic Database automatically use connection failover without having to change any settings. However, this method does require administrative access to the DNS server that all clients use to connect to the Vertica Analytic Database cluster. This may not be possible in your organization.

Using the backup server list is easier than editing the DNS server settings. However, it decentralizes the failover feature. You may need to update the application settings on each client system if you make changes to your Vertica Analytic Database cluster.

Using DNS failover

To use DNS failover, you need to change your DNS server's settings to map a single host name to multiple IP addresses of hosts in your Vertica Analytic Database cluster. You then have all client applications use this host name to connect to Vertica Analytic Database.

You can choose to have your DNS server return as many IP addresses for the host name as you want. In smaller clusters, you may choose to have it return the IP addresses of all of the hosts in your cluster. However, for larger clusters, you should consider choosing a subset of the hosts to return. Otherwise there can be a long delay as the client driver tries unsuccessfully to connect to each host in a database that is down.

Using the backup host list

To enable backup list-based connection failover, your client application has to specify at least one IP address or host name of a host in the BackupServerNode parameter. The host name or IP can optionally be followed by a colon and a port number. If not supplied, the driver defaults to the standard Vertica port number (5433). To list multiple hosts, separate them by a comma.

The following example demonstrates setting the BackupServerNode connection parameter to specify additional hosts for the connection attempt. The connection string intentionally has a non-existent node, so that the initial connection fails. The client driver has to resort to trying the backup hosts to establish a connection to Vertica.

import java.sql.*;
import java.util.Properties;

public class ConnectionFailoverExample {
    public static void main(String[] args) {
        // Assume using JDBC 4.0 driver on JVM 6+. No driver loading needed.
        Properties myProp = new Properties();
        myProp.put("user", "dbadmin");
        myProp.put("password", "vertica");
        // Set two backup hosts to be used if connecting to the first host
        // fails. All of these hosts will be tried in order until the connection
        // succeeds or all of the connections fail.
        myProp.put("BackupServerNode", "VerticaHost02,VerticaHost03");
        Connection conn;
        try {
            // The connection string is set to try to connect to a known
            // bad host (in this case, a host that never existed).
            // The database name is optional.
            conn = DriverManager.getConnection(
                    "jdbc:vertica://BadVerticaHost:5433/vmart", myProp);
            System.out.println("Connected!");
            // Query system to table to see what node we are connected to.
            // Assume a single row in response set.
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT node_name FROM v_monitor.current_session;");
            rs.next();
            System.out.println("Connected to node " + rs.getString(1).trim());
            // Done with connection.
            conn.close();
        } catch (SQLException e) {
            // Catch-all for other exceptions
            e.printStackTrace();
        }
    }
}

When run, the example outputs output similar to the following on the system console:

Connected!
Connected to node v_vmart_node0002

Notice that the connection was made to the first node in the backup list (node 2).

Specifying connection timeouts

LoginTimeout controls the timeout for JDBC to establish establish a TCP connection with a node and log in to Vertica.

LoginNodeTimeout controls the timeout for JDBC to log in to the Vertica database. After the specified timeout, JDBC attempts to connect to the "next" node, which is determined by either the connection property BackupServerNode or DNS resolution. This is useful if the node is up, but something is wrong with the Vertica process.

LoginNetworkTimeout controls the timeout for JDBC to establish a TCP connection to a Vertica node. If you do not set this connection property, if the node to which the JDBC client attempts to connect is down, the JDBC client will wait "indefinitely," but practically, the system default timeout of 70 seconds is used. A typical use case for LoginNetworkTimeout is to let JDBC connect to another node if the current Vertica node is down for maintenance and modifying the JDBC application's connection string is infeasible.

NetworkTimeout controls the timeout for Vertica to respond to a request from a client after it has established a connection and logged in to the database.

To set these parameters in a connection string:

# LoginTimeout is 30 seconds, LoginNodeTimeout is 10 seconds, LoginNetworkTimeout is 2 seconds, NetworkTimeout is 0.5 seconds
Connection conn = DriverManager.getConnection("jdbc:vertica://VerticaHost:5433/verticadb?user=dbadmin&loginTimeout=30&loginNodeTimeout=10"&loginNetworkTimeout=2&networkTimeout=500");

To set these parameters as a connection property:

Properties myProp = new Properties();
myProp.put("user", "dbadmin");
myProp.put("loginTimeout", "30"); // overall connection timeout is 30 seconds to make sure it is not too small for failover
myProp.put("loginNodeTimeout", "10"); // JDBC waits 10 seconds before attempting to connect to the next node if the Vertica process is running but does not respond
myProp.put("loginNetworkTimeout", "2"); // node connection timeout is 2 seconds
myProp.put("networkTimeout", "500"); // after the client has logged in, Vertica has 0.5 seconds to respond to each request
Connection conn = DriverManager.getConnection("jdbc:vertica://VerticaHost:5433/verticadb", myProp);

Interaction with load balancing

When native connection load balancing is enabled, the additional servers specified in the BackupServerNode connection parameter are only used for the initial connection to a Vertica host. If host redirects the client to another host in the database cluster to handle its connection request, the second connection does not use the backup node list. This is rarely an issue, since native connection load balancing is aware of which nodes are currently up in the database.

See Load balancing in JDBC for more information.

3 - JDBC data types

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.

3.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 and VerticaYearMonthInterval 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.

3.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:

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.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).

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.");
      }
    }
}

3.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 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.

3.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 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:

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)

3.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:

1. Define a String format with java.text.SimpleDateFormat. The number of characters in yyyy in the format defines the minimum number of characters to use in the date.

2. Call SimpleDateFormat.format() to convert the java.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);
}

4 - Executing queries through JDBC

To run a query through JDBC:

1. Connect with the Vertica database. See Creating and configuring a connection.

2. Run the query.

The method you use to run the query depends on the type of query you want to run:

• a DDL query that does not return a result set.

• a DDL query that returns a result set.

• a DML query

Executing DDL (data definition language) queries

To run DDL queries, such as CREATE TABLE and COPY, use the Statement.execute() method. You get an instance of this class by calling the createStatement method of your connection object.

The following example creates an instance of the Statement class and uses it to execute a CREATE TABLE and a COPY query:

Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE address_book (Last_Name char(50) default ''," +
    "First_Name char(50),Email char(50),Phone_Number char(50))");
stmt.execute("COPY address_book FROM 'address.dat' DELIMITER ',' NULL 'null'");

Executing queries that return result sets

Use the Statement class's executeQuery method to execute queries that return a result set, such as SELECT. To get the data from the result set, use methods such as getInt, getString, and getDouble to access column values depending upon the data types of columns in the result set. Use ResultSet.next to advance to the next row of the data set.

ResultSet rs = null;
rs = stmt.executeQuery("SELECT First_Name, Last_Name FROM address_book");
int x = 1;
while(rs.next()){
    System.out.println(x + ". " + rs.getString(1).trim() + " "
                       + rs.getString(2).trim());
    x++;
}

Executing DML (data manipulation language) queries using executeUpdate

Use the executeUpdate method for DML SQL queries that change data in the database, such as INSERT, UPDATE and DELETE which do not return a result set.

stmt.executeUpdate("INSERT INTO address_book " +
                   "VALUES ('Ben-Shachar', 'Tamar', 'tamarrow@example.com'," +
                   "'555-380-6466')");
stmt.executeUpdate("INSERT INTO address_book (First_Name, Email) " +
                   "VALUES ('Pete','pete@example.com')");

Executing stored procedures

You can create and execute stored procedures with CallableStatements.

To create a stored procedure:

Statement st = conn.createStatement();

String createSimpleSp = "CREATE OR REPLACE PROCEDURE raiseInt(IN x INT) LANGUAGE PLvSQL AS $$ " +
"BEGIN" +
    "RAISE INFO 'x = %', x;" +
"END;" +
"$$;";

st.execute(createSimpleSp);

To call a stored procedure:

String spCall = "CALL raiseInt (?)";
CallableStatement stmt = conn.prepareCall(spCall);
stmt.setInt(1, 42);

Stored procedures do not yet support OUT parameters. Instead, you can return and retrieve execution information with RAISE and getWarnings() respectively:

System.out.println(stmt.getWarnings().toString());

5 - Canceling JDBC queries

You can cancel JDBC queries with the Statement.cancel() method.

The following example creates a table jdbccanceltest and runs two queries, canceling the first:

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 CancelSamples
{
    /**
     * Sets up a large test table, queries its contents and cancels the query.
     * @param  conn A connection to a Vertica database
     * @throws SQLException
     */
    public static void sampleCancelTest(Connection conn) throws SQLException
    {
        setup(conn);
        try
        {
            runQueryAndCancel(conn);
            runSecondQuery(conn);
        }
        finally
        {
            cleanup(conn);
        }
    }

    // Set up table used in test.
    private static void setup(Connection conn) throws SQLException
    {
        System.out.println("Creating and loading table...");
        Statement stmt = conn.createStatement();
        String queryText = "DROP TABLE IF EXISTS jdbccanceltest";
        stmt.execute(queryText);

        queryText = "CREATE TABLE jdbccanceltest(id INTEGER, time TIMESTAMP)";
        stmt.execute(queryText);

        queryText = "INSERT INTO jdbccanceltest SELECT row_number() OVER(), slice_time "
                    + "FROM(SELECT slice_time FROM("
                    + "SELECT '2021-01-01'::timestamp s UNION ALL SELECT '2022-01-01'::timestamp s"
                    + ") sq TIMESERIES slice_time AS '1 second' OVER(ORDER BY s)) sq2";
        stmt.execute(queryText);
    }

    /**
     * Execute a long-running query and cancel it.
     * @param  conn A connection to a Vertica database
     * @throws SQLException
     */
    private static void runQueryAndCancel(Connection conn) throws SQLException
    {
        System.out.println("Running and canceling query...");
        Statement stmt = conn.createStatement();
        String queryText = "select id, time from jdbccanceltest";
        ResultSet rs = stmt.executeQuery(queryText);

        int i=0;
        stmt.cancel();
        try
        {
            while (rs.next()) ;
            i++;
        }
        catch (SQLException e)
        {
            System.out.println("Query canceled after retrieving " + i + " rows");
            System.out.println(e.getMessage());
        }
    }

    /**
     * Run a simple query to demonstrate that it can be run after
     * the previous query was canceled.
     * @param conn A connection to a Vertica database
     * @throws SQLException
     */
    private static void runSecondQuery(Connection conn) throws SQLException
    {
        String queryText = "select 1 from dual";
        Statement stmt = conn.createStatement();
        try
        {
            ResultSet rs = stmt.executeQuery(queryText);
            while (rs.next()) ;
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
            System.out.println("warning: no exception should have been thrown on query after cancel");
        }
    }

    /**
     * Clean up table used in test.
     * @param conn A connetion to a Vertica database
     * @throws SQLException
     */
    private static void cleanup(Connection conn) throws SQLException
    {
        String queryText = "drop table if exists jdbccanceltest";
        Statement stmt = conn.createStatement();
        stmt.execute(queryText);
    }
}

6 - Loading data through JDBC

You can use any of the following methods to load data via the JDBC interface:

• Executing a SQL INSERT statement to insert a single row directly.

• Batch loading data using a prepared statement.

• Bulk loading data from files or streams using COPY.

The following sections explain in detail how you load data using JDBC.

6.1 - Using a single row insert

The simplest way to insert data into a table is to use the SQL INSERT statement. You can use this statement by instantiating a member of the Statement class, and use its executeUpdate() method to run your SQL statement.

The following code fragment demonstrates how you can create a Statement object and use it to insert data into a table named address_book:

Statement stmt = conn.createStatement();
stmt.executeUpdate("INSERT INTO address_book " +
             "VALUES ('Smith', 'John', 'jsmith@example.com', " +
             "'555-123-4567')");

This method has a few drawbacks: you need convert your data to string and escape any special characters in your data. A better way to insert data is to use prepared statements. See Batch inserts using JDBC prepared statements.

6.2 - Batch inserts using JDBC prepared statements

You can load batches of data into Vertica using prepared INSERT statements—server-side statements that you set up once, and then call repeatedly. You instantiate a member of the PreparedStatement class with a SQL statement that contains question mark placeholders for data. For example:

PreparedStatement pstmt = conn.prepareStatement(
                    "INSERT INTO customers(last, first, id) VALUES(?,?,?)");

You then set the parameters using data-type-specific methods on the PreparedStatement object, such as setString() and setInt(). Once your parameters are set, call the addBatch() method to add the row to the batch. When you have a complete batch of data ready, call the executeBatch() method to execute the insert batch.

Behind the scenes, the batch insert is converted into a COPY statement. When the connection's AutoCommit parameter is disabled, Vertica keeps the COPY statement open and uses it to load subsequent batches until the transaction is committed, the cursor is closed, or your application executes anything else (or executes any statement using another Statement or PreparedStatement object). Using a single COPY statement for multiple batch inserts makes loading data more efficient. If you are loading multiple batches, you should disable the AutoCommit property of the database to take advantage of this increased efficiency.

When performing batch inserts, experiment with various batch and row sizes to determine the settings that provide the best performance.

The following example demonstrates using a prepared statement to batch insert data.

import java.sql.*;
import java.util.Properties;

public class BatchInsertExample {
    public static void main(String[] args) {
        Properties myProp = new Properties();
        myProp.put("user", "ExampleUser");
        myProp.put("password", "password123");

     //Set streamingBatchInsert to True to enable streaming mode for batch inserts.
     //myProp.put("streamingBatchInsert", "True");

     Connection conn;
        try {
            conn = DriverManager.getConnection(
                            "jdbc:vertica://VerticaHost:5433/ExampleDB",
                            myProp);
            // establish connection and make a table for the data.
            Statement stmt = conn.createStatement();


            // Set AutoCommit to false to allow Vertica to reuse the same
            // COPY statement
            conn.setAutoCommit(false);


            // Drop table and recreate.
            stmt.execute("DROP TABLE IF EXISTS customers CASCADE");
            stmt.execute("CREATE TABLE customers (CustID int, Last_Name"
                            + " char(50), First_Name char(50),Email char(50), "
                            + "Phone_Number char(12))");
            // Some dummy data to insert.
            String[] firstNames = new String[] { "Anna", "Bill", "Cindy",
                            "Don", "Eric" };
            String[] lastNames = new String[] { "Allen", "Brown", "Chu",
                            "Dodd", "Estavez" };
            String[] emails = new String[] { "aang@example.com",
                            "b.brown@example.com", "cindy@example.com",
                            "d.d@example.com", "e.estavez@example.com" };
            String[] phoneNumbers = new String[] { "123-456-7890",
                            "555-444-3333", "555-867-5309",
                            "555-555-1212", "781-555-0000" };
            // Create the prepared statement
            PreparedStatement pstmt = conn.prepareStatement(
                            "INSERT INTO customers (CustID, Last_Name, " +
                            "First_Name, Email, Phone_Number)" +
                            " VALUES(?,?,?,?,?)");
            // Add rows to a batch in a loop. Each iteration adds a
            // new row.
            for (int i = 0; i < firstNames.length; i++) {
                // Add each parameter to the row.
                pstmt.setInt(1, i + 1);
                pstmt.setString(2, lastNames[i]);
                pstmt.setString(3, firstNames[i]);
                pstmt.setString(4, emails[i]);
                pstmt.setString(5, phoneNumbers[i]);
                // Add row to the batch.
                pstmt.addBatch();
            }

            try {
                // Batch is ready, execute it to insert the data
                pstmt.executeBatch();
            } catch (SQLException e) {
                System.out.println("Error message: " + e.getMessage());
                return; // Exit if there was an error
            }

            // Commit the transaction to close the COPY command
            conn.commit();


            // Print the resulting table.
            ResultSet rs = null;
            rs = stmt.executeQuery("SELECT CustID, First_Name, "
                            + "Last_Name FROM customers ORDER BY CustID");
            while (rs.next()) {
                System.out.println(rs.getInt(1) + " - "
                                + rs.getString(2).trim() + " "
                                + rs.getString(3).trim());
            }
            // Cleanup
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}