Creating and configuring a connection

Before your Java application can interact with Vertica, it must create 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.

In this section