JDBC Driver

Read-only access to Sassafras's data is possible using a level 3 JDBC Driver, The driver is pure Java, and will run on any system that supports JDBC in general.

This document describes how to set up the JDBC Driver (available in KeyServer version 7.4.1.0 and higher) to access Sassafras's data from any Java program. Information on developing a program to use JDBC is widely available, and not covered in this documentation. Likewise, development of Java programs is beyond the scope of this document — sufficient Java development knowledge is assumed from here on.

Driver and Support Classes

The ksJDBC Driver is available in the full Sassafras Software archive. You can also download the latest driver from Sassafras. In most cases you will also need a support library that ksJDBC uses for parsing JSON content. This support library is developed by Google, and available from the source, or on the Sassafras Software image or online from Sassafras Software. If your Java environment already has this library, there is no need to install it again.

Once you have downloaded the necessary files, you can place them on any systems that need to use them. This will include your development systems as well as the hosts that will ultimately run your Java program. To use the driver from your Java program you must include it in your classpath. In a very simple example, a Java program that needs to use the JDBC driver could be loaded like this:

java -cp path/to/ksjdbc-7.4.1.0.jar:path/to/gson-2.8.0.jar myprogram.class

Of course in production environments the loading and execution of your Java program will require other options and might look quite different. The main requirement is that the ksJDBC driver and support files are specified in the classpath.

The driver is built with and supports Java 8. Previous versions of Java are not currently supported.

Other Prerequisites

The ksJDBC Driver uses KeyReporter as an intermediary to retrieve Sassafras's data. This means that you need to have KeyReporter running as part of your KeyServer installation. Communication is established using either HTTP or HTTPS (recommended). We strongly recommend using HTTPS, since the ksJDBC Driver uses HTTP Basic authentication, which does not otherwise encrypt your Sassafras Server admin credentials.

In order to use HTTPS, your KeyReporter must have a valid SSL certificate created specifically for the host on which it runs. For more information see Enabling HTTPS (SSL) for KeyReporter.

Lastly, your firewall settings will need to allow the required HTTP or HTTPS communication between the KeyServer host and the computer(s) running your Java program.

Simple Example

Once you've downloaded and installed the ksJDBC Driver, you can run a simple test to check that everything is set up correctly. The following sample code is not meant to be exhaustive, nor does it demonstrate the only way to access the driver.

Here's the full Java source code for a program that simply sends a query for information about the KeyServer, which it then prints out. The values for the KeyServer host, admin account, and password will be different in practice:

import java.io.*;
import java.sql.*;

public class ksexample {

  // host name on which KeyServer (and KeyReporter) runs
  static final String kshost = "keyserver.sample.net";
  // a Sassafras Server Admin account name, only need to have read access
  static final String ksadmin = "ksreader";
  // password for the Sassafras Server Admin account, use SSL for security
  static final String kspass = "secret";
  // the query that we will send, for info on the KeyServer
  static final String query = "select serverName,serverComputer,"
                              + "serverSerialNumber,serverVersion "
                              + "from KSServers";

  // the connection string needed by the JDBC Driver Manager
  static final String cstr = "jdbc:ksjdbc:https://" + kshost
                              + "/;USER=" + ksadmin
                              + ";PASSWORD=" + kspass;

  public static void main(String[] args)
  {
    try {
      // create the connection and a statement on that connection
      Connection con = DriverManager.getConnection(cstr);
      Statement stmt = con.createStatement();
      // execute a simple query and get the results
      ResultSet rs = stmt.executeQuery(query);

      // get information on the columns returned
      ResultSetMetaData md = rs.getMetaData();
      int count = md.getColumnCount();

      // print the results (there is only one result row in practice)
      while (rs.next()) {
        for (int i = 1; i <= count; i++)
          System.out.println(md.getColumnLabel(i)
                             + ": "
                             + rs.getString(i));
      }
    } catch (Exception e) {
      // catch and print any exception that might occur
      e.printStackTrace();
    }
  }
}

Assuming you've placed the ksJDBC Driver and support files in a directory named lib and saved the above example code in a file named ksexample.java, you would compile the Java program as follows: 

javac -cp lib/ksjdbc-lastest.jar:lib/gson-2.8.0.jar ksexample.java
and then it would be executed with a command like this: 
java -cp .:lib/ksjdbc-lastest.jar:lib/gson-2.8.0.jar ksexample
Assuming everything is set up correctly, the output would look like this: 
serverName: KeyServer
serverComputer: keyserver.sample.net
serverSerialNumber: K5OJ354EL4I5W3A-1122
serverVersion: 7.4.1.0

This simple example should be fairly self-explanatory, but there are a few important points to emphasize.

  • If your Java environment is recent enough, the ksJDBC driver will be automatically registered and available, as long as it is on the classpath
  • The hostname, admin account, and password will be different for your installation
  • The admin account must already be set up, with the permissions and rights for the data that your Java program will access
  • This sample uses HTTPS for communications, which is strongly recommended and might require further configuration in your installation
  • Remember that all access is read-only — inserts, deletes, etc. are not supported, even when the Sassafras Server admin account you use has modify rights
  • The queries must be made using simple Statements — PreparedStatement and CallableStatement are not supported and will generate exceptions

Any problems during execution will result in a Java exception, which will be caught in this example and printed out. If the KeyServer and KeyReporter have not been set up with a valid SSL certificate, you might see an error similar to the following: 

java.sql.SQLException: HTTP request failed:
  javax.net.ssl.SSLHandshakeException:
    java.security.cert.CertificateException:
      No subject alternative names present or some other error message
	at com.sassafras.ksjdbc.jdbc.JksdbConnection.submitQueryRequest...
	...
	at ksexample.main...
Make sure your SSL certificate is valid and properly installed before using HTTPS for communication. For testing you can use HTTP instead (change line 18 in the example above), although this will transmit the admin password over a non-secure channel.