JDBC API

Introduction

Comdb2 provides Java programming language binding with Cdb2jdbc. Cdb2jdbc is a JDBC Type 4 driver, which means that the driver is a platform-independent, pure Java implementation.

Installation

Installing from Source

To check out the source, please follow the instructions.

To install cdb2jdbc from source, the following additional software packages are required:

• JDK 1.6 or above
• Protocol Buffers compiler 2.6 or above. Make sure that Protocol Buffers compiler is included in your PATH.
• Maven 3.x

Once you check out the source and have all the required software installed on the system, change directory to cdb2jdbc under comdb2 source and type mvn clean install.

cd cdb2jdbc
mvn clean install

Cdb2jdbc should be successfully installed in your local Maven repository. The JAR files normally can be found in ~/.m2/repository/com/bloomberg/comdb2/cdb2jdbc/.

Setting up Cdb2jdbc

There are 2 approaches to set up cdb2jdbc: with or without Maven.

With Maven

To introduce cdb2jdbc in your applications as a Maven dependency, add the following to pom.xml, with the version available from your Maven repository.

<dependency>
  <groupId>com.bloomberg.comdb2</groupId>
  <artifactId>cdb2jdbc</artifactId>
  <version>major.minor.patch</version>
</dependency>

Without Maven

By default, an uber JAR is built along with cdb2jdbc and is named cdb2jdbc-<version>-shaded.jar. An uber JAR is a JAR file which contains all its dependencies. To use the JAR without Maven, you would include it in CLASSPATH.

export CLASSPATH=<path_to_the_uber_jar>:$CLASSPATH

Using Cdb2jdbc

* The section is not intended as a guide to JDBC programming. For more information, please refer to the official JDBC website

Loading the Driver

One common approach for loading the driver is to use Class.forName(). To load cdb2jdbc, you would use:

Class.forName("com.bloomberg.comdb2.jdbc.Driver");

Connecting to a Database

A Comdb2 database is represented by a JDBC URL. Cdb2jdbc takes one of the following forms:

jdbc:comdb2://<stage>/<database>[?options]
jdbc:comdb2://<host>[:port][, <host>[:port], ...]/<database>[?options]

For example, to connect to a local database called testdb, you would use:

Connection conn = DriverManager.getConnection("jdbc:comdb2://localhost/testdb");

The parameters are as follows:

• stage

  Please refer to cdb2_open for the information about stage.

• host

  The host name of the server.

• port

  The port number the database is listening on. If not specified, cdb2jdbc queries pmux to get the dynamically allocated port number.

  * To support connection failover, you would define multiple host/port pairs in the URL, separated by commas.

• database

  The database name.

• options

  • user=String

    The database user. The default is empty.

  • password=String

    The password of the database user. The default is empty.

  • comdb2_timeout=Integer

    The server-side socket read and write timeouts in seconds. The default is 10 seconds.

  • load_balance=”room” | “randomroom” | “random”

    The load balance policy.

  • default_type=String

    Please refer to Client Setup for more information.

  • room=String

    Please refer to Client Setup for more information.

  • comdb2dbname=String

    Please refer to Client Setup for more information.

  • dnssuffix=String

    Please refer to Client Setup for more information.

  • portmuxport=Integer

    The port number which pmux is listening on. The default is 5105.

  • tcpbufsz=Integer

    The value for SO_RCVBUF, which is the maximum socket receive buffer size for the underlying TCP connections to the database server.

  • microsecond_fraction=Boolean

    The precision of the fractional second when binding a java.sql.Timestamp parameter. If it is set to false, millisecond precision is used. otherwise, microsecond precision is used. The default is true.

  • preferred_machine=String

    When set, the driver connects to the preferred machine whenever possible, with “RANDOM” load balance strategy.

  • ssl_mode=String

    Can be one of the following values:

    • ALLOW

    • REQUIRE

    • VERIFY-CA

    • VERIFY-HOSTNAME

  • key_store=String

    Path to the client keystore.

  • key_store_password=String

  Passphrase of the client keystore.

  • key_store_type=String

    Type of the keystore. The default is "JKS".

  • trust_store=String

    Path to the certificate authority (CA) keystore.

  • trust_store_password=String

    Passphrase of the CA keystore.

  • trust_store_type=String

    Type of the trusted CA keystore. The default is "JKS".

  * To define multiple options, separate them by ampersands.

Issuing a Query and Browsing the Result

The example below issues a simple SELECT query and prints the rows.

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT column FROM table WHERE column = 1");
while (rs.next())
    System.format("column is %s\n", rs.getString(1));
rs.close();
stmt.close();

The example uses PreparedStatement to issue a query.

int column = 1;
PreparedStatement ps = conn.prepareStatement("SELECT column FROM table WHERE column = ?");
ps.setInt(1, column);
ResultSet rs = ps.executeQuery();
while (rs.next())
    System.format("column is %s\n", rs.getString(1));
rs.close();
ps.close();

Performing Updates

To perform updates (INSERT, UPDATE or DELETE), you would use executeUpdate(). This function does not return a ResultSet object. Instead it returns the number of rows affected.

The example performs a delete and prints the number of rows deleted.

Statement stmt = conn.createStatement();
int ndel = stmt.executeUpdate("DELETE FROM table WHERE column = 1");
System.format("%d row(s) deleted\n", ndel);
stmt.close();

Invoking Stored Procedures

Cdb2jdbc does not implement the CallableStatement interface. Instead, you should use the Statement or PreparedStatement.

The example invokes a stored procedure called foo.

Statement sp = conn.createStatement();
sp.executeQuery("exec procedure foo()");

The result would come back as a normal ResultSet object.

Using SSL connections

To configure the driver with SSL support, you would simply append ssl=true to the JDBC URL. If the server requires client certificates, you need to provide the driver with a Java KeyStore (JKS).

A JKS can be converted from an OpenSSL certificate. First, you would convert the certificate to DER format if it is in PEM format using OpenSSL:

# Convert PEM to something java understands first
$ openssl x509 -in client.pem -out client.crt.der -outform der

To convert a DER certificate to JKS, you would use $JAVA_HOME/bin/keytool:

# Convert the DER certificate to a java KeyStore file.
$ keytool -import -file client.crt.der -keystore path/to/keystore -alias comdb2

To load the JKS into the driver, the JDBC URL looks like this:

jdbc:comdb2//<hostname>/<database>?ssl=true&key_store=<path/to/jks>&key_store_password=<passwd>

A trusted CA keystore can be generated the same way.

To load the trusted CA JKS into the driver, the JDBC URL looks like this:

jdbc:comdb2//<hostname>/<database>?ssl=true&trust_store=<path/to/jks>&trust_store_password=<passwd>

Java and Comdb2 Types

The table below shows the conversions between Java and Comdb2 types.

SQLSTATEs in Cdb2jdbc

The following table shows how SQLSTATEs are mapped to SQLExceptions in cdb2jdbc.