Setting and Getting Connection Property Values

You can set a connection property in any of three ways:

  • Include the property name and value as part of the connection string you pass to the DriverManager.getConnection() method.
  • Set the properties in a Properties object, and then pass it to the DriverManager.getConnection()method.
  • Use the VerticaConnection.setProperty() method. With this approach, you can change only those connection properties that remain changeable after the connection has been established.

In addition, some of the standard JDBC connection properties have getters and setters on the Connection interface (such as Connection.setAutocommit()).

Setting Properties When Connecting

There are two ways you can set connection properties when creating a connection to Vertica:

  • In the connection string, using the same URL parameter format that you can use to set the username and password. The following example enables a TLS/SSL 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 as seen above. If this occurs, Vertica may 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 may 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");
  • In a Properties object that you pass to the getConnection() call. You will need to import the java.util.Properties class in order to instantiate a Properties object. Then you use the put() method to add the property name and value 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();
    }

    The data type of all of the values you set in the Properties object are strings, regardless of the property value's data type.

Getting and Setting Properties After Connecting

The VerticaConnection.getProperty() method lets you get the value of some connection properties. You can use VerticaConnection.setProperty() method to change the value for properties that can be set after the database connection has been established. Since these methods are Vertica-specific, to use them you must cast your Connection object to the VerticaConnection interface. To cast to VerticaConnection, you must either import it into your client application or use a fully-qualified reference (com.vertica.jdbc.VerticaConnection). The following example demonstrates getting and setting the value of the DirectBatchInsert 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 DirectBatchInsert to true initially
        myProp.put("DirectBatchInsert", "true");
        Connection conn;
        try {
            conn = DriverManager.getConnection(
                            "jdbc:vertica://VerticaHost:5433/ExampleDB",
                            myProp);
            // Show state of the DirectBatchInsert property. This was set at the
            // time the connection was created.
            System.out.println("DirectBatchInsert state: "
                            + ((VerticaConnection) conn).getProperty(
                                            "DirectBatchInsert"));
            
            // Change it and show it again
            ((VerticaConnection) conn).setProperty("DirectBatchInsert", false);
            System.out.println("DirectBatchInsert state is now: " +
                             ((VerticaConnection) conn).getProperty(
                                             "DirectBatchInsert"));
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

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

DirectBatchInsert state: true
DirectBatchInsert state is now: false