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;

If your application needs to run in a Java 5 JVM, it uses the older JDBC 3.0-compliant driver. This driver requires that you to manually load the Vertica JDBC driver using the Class.forName() method:

// Only required for old JDBC 3.0 driver
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; // Exit. Cannot do anything further.
}

Your application may 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
  • Name of 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:

"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 theJDBC 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 = DriverManager.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 Kestore 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

  1. Generate a certifying authority certificate. For information on this process, refer to the OpenSSL documentation. The following example shows a typical create command. 
    openssl req -config openssl.cnf -new -x509 -days 3650 -sha256 -extensions v3_ca -nodes -out certs/ca.pem -keyout private/ca.key 
-subj "/C=US/ST=Colorado/L=Denver/O=ExampleCo/OU=Software/CN=Root CA"
  2. Generate an intermediate certifying authority. The intermediate authority is not required, but can be useful for testing and debugging your connection. The following example shows a typical create command. 
    openssl req -config openssl.cnf -out csr/intermediate_ca.csr -newkey rsa:2048 -keyout private/intermediate_ca.key -nodes -subj "/C=US/ST=Colorado/L=Denver/O=ExampleCo/OU=Software/CN=Intermediate CA"
openssl ca -config openssl.cnf -in csr/intermediate_ca.csr -out certs/intermediate_ca.pem -extensions v3_intermediate_ca -notext -batch
  3. Generate and sign a certificate for your SSL certificate, as shown. 
    openssl req -config openssl.cnf -out csr/server.csr -newkey rsa:2048 -keyout private/server.key -nodes
openssl ca -config openssl.cnf -in csr/server.csr -out certs/server.pem -extensions server_cert -notext -cert certs/intermediate_ca.pem -keyfile private/intermediate_ca.key -batch
  4. Use the ALTER DATABASE statement to configure Vertica to use client/server TLS and to enable import and over SSL. 
    vsql -c "ALTER DATABASE DEFAULT SET EnableSSL = 1;"
vsql -c "$(printf "ALTER DATABASE DEFAULT SET SSLCertificate = '%s';" "`cat certs/server.pem certs/intermediate_ca.pem`")"
vsql -c "$(printf "ALTER DATABASE DEFAULT SET SSLPrivateKey = '%s';" "`cat private/server.key`")"
  5. To enable client certificate authentication, identify a certifying authority for the server. 
    vsql -c "$(printf "ALTER DATABASE DEFAULT SET SSLCA = '%s';" "`cat certs/ca.pem`")"
  6. Optionally, you can disable all non-SSL connections using the CREATE AUTHENTICATION statement. 
    vsql -c  "CREATE AUTHENTICATION no_tls METHOD 'reject' HOST NO TLS '0.0.0.0/0'; 
CREATE AUTHENTICATION no_tls METHOD 'reject' HOST NO TLS '::/128';"
  7. Generate and sign a certificate for your client using the same intermediate certifying authority as the server. 
    openssl req -config openssl.cnf -out csr/client.csr -newkey rsa:2048 -keyout private/client.key -nodes
openssl ca -config openssl.cnf -in csr/client.csr -out certs/client.pem -extensions internode_cert -notext -cert certs/intermediate_ca.pem -keyfile private/intermediate_ca.key -batch

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

    cat certs/client.pem certs/intermediate_ca.pem > certs/client_chain.pem 
openssl pkcs12 -export -in certs/client_chain.pem -inkey private/client.key -out certs/client.p12 -certfile certs/ca.pem -name my_alias -passout pass:my_password

  9. 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
  10. 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 in the Administrator's Guide for more information about complying with your license terms.