JDBC Driver

This article 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

The JDBC driver is tested on Windows platforms for Java 8, 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
  

The JDBC driver is tested on Linux platforms for OpenJDK JVM 8 and OpenJDK JVM 11.

macOS (64-bit)

• macOS Catalina (10.15)

• macOS Big Sur (11.6.1)

• macOS Monterey (12.0.1)

The JDBC driver is tested on Mac OS for JVM 8 and JVM 16.

FreeBSD (64-bit)

The JDBC driver is tested on FreeBSD 12.2 for OpenJDK JVM 8.

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.

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

Exasol URL

The following URL structures are supported for the JDBC driver in Exasol.

Single host:

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

Multiple hosts:

jdbc:exa:<host_1>[/<fingerprint>]:<port_1>[,<host_2>]...[,<host_n>:<port_n>][;<prop_1>=<value_1>]...[;<prop_n>=<value_n>]

The following list explains the elements of the URL structure.

jdbc:exa

This is a necessary prefix for the driver manager.

<host>/<fingerprint>:<port>

Defines the servers and the port of the Exasol cluster.

Servers can be specified as a single hostname or IP address, as multiple hostnames or IP addresses specified as a range using the format first_host..last_host:port, or as a comma-separated list of host:port pairs. When opening a connection, the driver will randomly choose an address from the specified range. If the connection fails, the driver will continue to try all other possible addresses in the range.

If you cannot use TLS and you trust the server, you can include the server fingerprint in the URL.

Examples:

jdbc:exa:myhost/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563

jdbc:exa:myhost/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563,myhost2:8564

jdbc:exa:myhost1..4/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563 

jdbc:exa:192.168.6.11..14/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563 

You can also specify a file that contains a list of server definitions, for example: //c:\mycluster.txt. The two forward slashes indicate that the parameter value is a file path.

<prop_i=value_i>

The port can be followed by an optional semicolon-separated list of properties that should be set during login. These properties correspond with the supported driver properties that 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
...

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

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

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 (2³¹-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

The following table describes all properties that can be transferred to the JDBC driver through the URL. Properties that are not listed in the table are not supported in this version of Exasol.

The driver property names are case sensitive.

Best practices

Allocate enough memory for JVM

When operating on big tables, you can get problems if the available memory for the JVM is not big enough. Make sure that you allocate enough memory to handle the table data in your database.

Optimize the fetch size for reading big data volumes

The parameter fetchsize determines the data volume in KiB that should be fetched from the database in each 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 to 2000 KiB.

Use prepared statements

Instead of using single insert statements like INSERT INTO t VALUES 1, 2, ..., use the more efficient interface of prepared statements and their parameters. Prepared statements achieve optimal performance when using parameter sets between 500 KiB and 20 MiB.

Use native data types

Insert data using native data types. For example, for the number 1234 use SQL_C_SLONG (integer) instead of SQL_CHAR.

Free up unused resources

Free up unused resources immediately. For example, for prepared statements, use close().

Limit the number of servers in the connection

Do not specify an unnecessarily wide range of IP addresses. Since the addresses in the specified range are randomly tried until a successful connection is made, the time required to complete the connect operation can be much longer with a large number of addresses.