JDBC Driver

This section describes the JDBC driver provided by Exasol to connect third party applications to Exasol.

System Requirements

JRE Requirement

The JDBC driver requires a Java runtime environment. You can use the driver on any platform with JRE version 1.8.0_282 or later.

Tested Systems

The driver has been tested on the following systems using the specified Java version with the latest available updates.

  • Windows (64-bit)

    • Windows 10

    • Windows Server 2016

    • Windows Server 2019
      JDBC driver is tested on Windows platforms for the Java 11 and Java 17.

  • Linux (64-bit)

    • Red Hat / CentOS 7

    • Red Hat / CentOS 8 Stream

    • OpenSUSE Leap 15.2

    • Debian 10

    • Ubuntu 18.04 LTS

    • Ubuntu 20.04 LTS
      JDBC driver is tested on Linux platforms for the OpenJDK JVM 11.

  • macOS (64-bit)

    • macOS Big Sur (11.6.1)

    • macOS Monterey (12.0.1)
      JDBC driver is tested on Mac OS for the JVM 11.

  • Free BSD (64-bit)

    • Free BSD 12.2
      JDBC driver is tested on Free BSD for the OpenJDK JVM 11.

Download JDBC Driver

  1. Download the driver for your Exasol version and operating system from the Exasol Download Portal.

    Exasol currently does not provide specific drivers for Exasol 8. The latest drivers for Exasol 7.1.x are fully compatible with Exasol 8.

  2. Run the downloaded executable file and follow the setup wizard to complete the installation.

Installing JDBC Driver

On Windows

The driver is installed as a .jar archive in the installation directory.

On Linux/Unix

The driver is included in the download. Depending on the application, the archive must be added to the search path for the Java classes (CLASSPATH).

All classes of the JDBC driver belong to the Java com.exasol.jdbc package. The main class of the driver is com.exasol.jdbc.EXADriver

License

Each driver package includes the latest version of the license for the driver. The license allows you to bundle the driver with third-party software, for example when creating plugins for a BI tool. For more details, refer to the license file which is located in the folder where the driver was installed.

Integrating JDBC Driver using Maven

The JDBC driver is also available on Maven Central (https://search.maven.org/artifact/com.exasol/exasol-jdbc). Add the following dependency to the build configuration of your project (for example, pom.xml for Maven).

Replace the version number in this example with the latest supported driver version.

<dependencies>
    <dependency>
        <groupId>com.exasol</groupId>
        <artifactId>exasol-jdbc</artifactId>
        <version>7.1.20</version>
    </dependency>
</dependencies>

Exasol URL

The JDBC Driver uses the following URL structure for Exasol:

jdbc:exa:<host>/<fingerprint>:<port>[;<prop_1>=<value_1>]...[;<prop_n>=<value_n>]

The following table explains the elements of the URL structure.

Element Details
jdbc:exa This prefix is necessary for the driver manager.
<host>/<fingerprint>:<port>

Defines the servers and the port of the Exasol cluster (for example, 192.168.6.11..14:8563). You can also have a hostname mapped to multiple IP addresses.

When opening a connection, the driver will randomly choose an address from the specified address range. If the connection fails, the driver will continue to try all other possible addresses. You can also specify an IP range with a comma-separated list. For example:

  • myhost/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563: Single server with name myhost and port 8563. Or single host myhost with multiple IP addresses mapped to it, fingerprint 72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30, and port 8563.
  • myhost1,myhost2/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563: Two servers with fingerprint 72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30 and port 8563.
  • myhost1..4/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563: Four servers (myhost1, myhost2, myhost3, myhost4), fingerprint 72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30, and port 8563.
  • 192.168.6.11..14/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563: Four servers from 192.168.6.11 to 192.168.6.14, fingerprint 72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30, and port 8563.
  • 192.168.6.11..14/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563: Four servers from 192.168.6.11 to 192.168.6.14, fingerprint 72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30, and port 8563.
<prop_i=value_i>

Instead of a list you can also specify a file that contains a list (for example, //c:\mycluster.txt). The two slashes ("/") indicate that a filename is specified. An optional list of properties separated by a ";" follows the port, the values of which should be set when logging-in.

These properties correspond with the supported Driver Properties and are described in the following section.

The values of properties within the URL can only consist of alphanumeric characters.

Example from a Java program 

import java.sql.*;
import com.exasol.jdbc.*;

public class jdbcsample
{
    public static void main(String[] args)
    {
        try { Class.forName("com.exasol.jdbc.EXADriver");
    } catch (ClassNotFoundException e) { 
            e.printStackTrace();
    }
    Connection con=null; 
    Statement stmt=null; 
    try {
        con = DriverManager.getConnection( 
            "jdbc:exa:192.168.6.11..14:8563;schema=SYS",
            "sys", 
            "exasol"
            );
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM CAT"); 
        System.out.println("Schema SYS contains:"); 
        while(rs.next())
        {
            String str1 = rs.getString("TABLE_NAME"); 
            String str2 = rs.getString("TABLE_TYPE"); 
            System.out.println(str1 + ", " + str2);
        }
    } catch (SQLException e) { 
        e.printStackTrace();
    } finally {
        try {stmt.close();} catch (Exception e) {e.printStackTrace();} 
        try {con.close();} catch (Exception e) {e.printStackTrace();}
        }
    }
}

The code sample above builds a JDBC connection to an Exasol database on servers in IP address range 192.168.6.11 to 192.168.6.14 using port 8563. User sys with the password exasol is logged in, and the schema sys is opened. After that, all tables in this schema are shown.

Schema SYS contains: 
EXA_SQL_KEYWORDS, TABLE 
DUAL, TABLE 
EXA_PARAMETERS, TABLE
...

Supported Interfaces

Exasol provides the following interfaces:

  • EXADataSource

  • EXASocketFactory

EXADataSource

The JDBC driver provides a CommonDataSource interface called EXADataSource. Use EXADataSource to pass arguments using the setProperty() method. For example:

EXADataSource ds = new EXADataSource();
ds.setProperty("socketfactory", "myTool.MySocketFactory");
ds.setProperty("<User>", "<MyDBUser");
...

EXASocketFactory

The JDBC driver provides a SocketFactory interface called EXASocketFactory. With EXASocketFactory you can use the Exasol specific method createCustomSocket to connect with a set timeout for the connection. For example:

public abstract class EXASocketFactory extends SocketFactory {
    public abstract Socket createCustomSocket(<InetAddressHost>, <Port>, <Timeout>) throws IOException;        

When more than one host is specified in a connection string, the connect attempts to each of these hosts are made using the SocketFactory specified by the user and the timeout (if given) until one host is connected.

Supported Driver Standards

The JDBC driver supports JDBC 4.2 Core API. Detailed information about the supported interfaces are provided in the API Reference which you can find in the html folder of the driver installation directory. If you want to learn more about JDBC, see JDBC Basics.

The standard includes the following:

  • Access to the driver through the DriverManager API and configuration of the driver using the DriverPropertyInfo interface.
  • Direct execution of SQL statements as a prepared statement and in batch mode.
  • Support of more than one open ResultSet.
  • Support of the DatabaseMetaData and ResultSetMetaData metadata APIs.

The following features are not supported:

  • Savepoints
  • User-defined data types and the types Blob, Clob, Array, and Ref.
  • Stored procedures
  • Read-only connections
  • The API ParameterMetaData

If the rowcount of a query exceeds 2147483647 (231-1), the JDBC driver will return the value 2147483647. This is because of the 32-bit limitation for this return value defined by the JDBC standard.

Supported Driver Properties

You can transfer the following properties to the JDBC driver through the URL. Property names are case sensitive.

The encryption and legacyencryption properties are no longer supported in Exasol. TLS encryption is required for all connections and cannot be disabled. See also fingerprint.

Properties not included in the following table are not supported in this version of Exasol.

Property Value Description
authmethod String

Specifies the authentication method for OpenID.

If the value for the parameter is accesstoken, the connection string will be "jdbc:exa:<host>:<port>;authmethod=accesstoken" and the password will be your OpenID Access Token.

If the value for the parameter is refreshtoken, the connection string will be "jdbc:exa:<host>:<port>;authmethod=refreshtoken" and the password will be your OpenID Refresh Token.

For more information, see Authentication using OpenID.
autocommit

0=off, 1=on

Switches autocommit on or off.

Default: 1
clientname

String

Defines the name of the client sent to the server.

Default: "Generic JDBC client"
clientversion

String

Defines the version of the client sent to the server.

Default: empty ("")
connectionPoolSize numeric, >=0

Changes the maximum size of the connection pool. You can change it only once in the driver instance.

Default: 64

Valid values: 0<connectionPoolSize<8192
debug

0=off, 1=on

Switches on the driver's log function. The driver then writes a log file named jdbc_timestamp.log for each established connection.

These files contain information on the called driver methods and the progress of the JDBC connection. It can assist the Exasol Support in the diagnosis of problems.

Due to performance reasons the logging should not be used in a productive system

Default: 0
enablenumerictypeconversion 1=on, 0=off

The driver will show column decimal data types of a result set that can be converted to integer types as int or longint.

Default: 1
feedbackinterval Integer

During a query the server sends feedback to the client at set intervals. This feedback:

  • Signals that the server is still there and executing the client command

  • Prevents the connection from being cut by the networks for inactivity

  • Checks whether a command is canceled

Default: 1 second

A feedback interval set too high may cause a long lag time before a command is canceled.
fetchsize

numeric, >0

Amount of data in kB which should be obtained by Exasol during a fetch. The JVM can run out of memory if the value is too high.

Default: 2000

The same can be achieved by using the function setFetchSize().
fingerprint String

If TLS validation fails and you trust the server, use this parameter to include the server fingerprint in the connection string. For example:

jdbc:exa:exadb1.example.com:8563;fingerprint=BA7816BF8F01CFEA414140DE5DAE2223B00361A396177A9CB410FF61F20015AD;

To bypass TLS certificate checking, use the NOCERTCHECK option. NOCERTCHECK is case insensitive and can be placed after the host or port or in the fingerprint argument. For example:

  • jdbc:exa:exadb1.example.com/NOCERTCHECK:8563
  • jdbc:exa:exadb1.example.com:8563/nocertcheck
  • jdbc:exa:exadb1.example.com:8563;fingerprint=NoCertCheck;
hosttimeout numeric, >=0

Specifies the connection timeout for each TCP connection attempt in milliseconds. If multiple hosts are specified in the connection string, each host will wait for the specified time for a successful connection.

Default: 2000

kerberoshostname

String

 Host name of the Kerberos service. If nothing is specified, the host name of the connections string will be used as default
kerberosservicename

String

 Principal name of the Kerberos service. If nothing is specified, the name "exasol" will be used as default.
keystore String

To use a self signed certificate in JDBC, the certificate needs to be added to the system default keystore or you must create an keystore and add the certificate to the keystore. When the certificate is added to system default keystore, the JDBC driver will automatically load the certificate during the establishment of connection. If you create the keystore, you must specify the keystore in the connection string along with the keystore password and keystore type (optional).

Default: None
keystorepassword String

If you specify the keystore in the connection string, you must include a keystore password.

Default: None
keystoretype String

The keystore type is optional.

Default: System keystore type
logdir

String

Defines the directory where the JDBC debug log files should be written to (in debug mode).

Example: jdbc:exa:192.168.6.11..14:8563;debug=1;logdir=/tmp/my folder/;schema=sys

Default is the application's current directory, which is not always transparent. That is why you should always set the log directory in debug mode.
logintimeout

numeric, >=0

The maximum time in milliseconds that the driver will wait to establish a connection. This timeout is required to limit the overall login time, especially in the case of a large cluster with several reserve nodes.

Default: 0 (infinite)
logintype

"1" or "SSPI",

"2" or "GSSAPI"

Default on windows systems is "1" or "SSPI"

 

Default on non-windows system is "2" or "GSSAPI"
querytimeout

numeric, >=0

Defines the time (in seconds) for a statement to run before it is automatically aborted.

Default: 0 (infinite)
schema

String

Name of the schema that should be opened after login. If the schema cannot be opened, the login fails with a java.sql.SQLException.

Default: no schema to open
snapshottransactions

1=on, 0=off

Changes the snapshottransaction setting in the session.

Possible values:

  • 1
  • 0

If not set, the default in the session is used.
socketfactory String Specifies the name of a SocketFactory that should be used while connecting to the server.
superconnection

Y=on, N=off

Enables the user to execute queries even if the limit for active sessions (executing a query) has been reached.

Note:

  • Only the SYS user can set the parameter.
  • superconnection should only be used in case of significant performance problems where it is impossible to log in and execute queries within a reasonable time. By that parameter you can analyze the system and kill certain processes which cause the problem.

Default: database default
validateservercertificate 1=on, 0=off

Enables or disables the TLS server certificate validation.

Default: 1

If the SSL certificate is not verified or a client does not have the certificate, the connection fails. To overcome this problem, you could use a fingerprint in the connection string that allows the connection. For fingerprint based verification, the connection string is jdbc:exa:exadb1.example.com/<fingerprint>:8563. The fingerprint is automatically added to the database connection string when you upload a TLS certificate. For more information about TLS certificate upload, see Upload TLS Certificate.

worker

0=off, 1=on

The sub-connections for parallel read and insert have this flag switched on.

Details and examples are available in this Exasol Knowledge Base article.

Default: 0

workertoken

numeric, >=0

Is necessary to establish parallel sub-connections.

Default: 0

Best Practices

Objective Details

Memory of JVM

When operating on big tables, you can get problems if the specified memory for the JVM is not big enough.

Reading big data volumes

Through the parameter fetchsize, you can determine the data volume which should be fetched from the database per communication round. If this value is too low, the data transfer may take longer to complete. If this value is too high, the JVM may run out of memory. The recommended fetch size is 1000-2000.

Inserting data into the database

Instead of using single insert statements like "INSERT INTO t VALUES 1, 2, ..." you should use the more efficient interface of prepared statements and their parameters. Prepared statements achieve optimal performance when using parameter sets between 500 kB and 20 MB. Moreover you should insert the data by using the native data types.

Unused resources

Unused resources should be freed immediately. For example, Prepared Statements through "close()".

Connection servers

Don't specify any unnecessary wide IP address range. Since those addresses are randomly tried until a successful connection, the "connect" could take much time.

Only use one fingerprint per connection string

If you include more than one fingerprint, the connection will fail. This not only includes multiple fingerprint arguments, but also includes adding the fingerprint in multiple locations within the connection string. For example, you cannot include the fingerprint after the host or port and in a fingerprint argument. Rewrite the connection string with only one fingerprint.