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
The JDBC driver is tested on Windows platforms for 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 11.
macOS (64-bit)
-
macOS Big Sur (11.6.1)
-
macOS Monterey (12.0.1)
The JDBC driver is tested on Mac OS for
FreeBSD (64-bit)
The JDBC driver is tested on FreeBSD 12.2 for OpenJDK
Download JDBC driver
-
Download the latest driver for your operating system from the Exasol Download Portal.
-
Run the downloaded executable file and follow the setup wizard to complete the installation.
Install 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.
Integrate 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.
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.
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
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.
TLS encryption is required by default for all connections. The legacy ChaCha encryption method is deprecated and is not recommended. Connections from drivers that disable encryption through the encryption
property or switch to ChaCha encryption through the legacyencryption
property are not accepted in Exasol 8.19.0 or later.
If TLS encryption cannot be used and you trust the server, you can include the server fingerprint in the connection string to bypass TLS validation. For more information, see fingerprint.
Property names are case sensitive.
Property | Value | Description |
---|---|---|
authmethod
|
string |
Specifies the authentication method for OpenID. If the value for the parameter is If the value for the parameter is For more information, see Authentication using OpenID. |
autocommit
|
|
Switches 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 (number of connections). You can change this value only once in the driver instance. Default: 64 Valid values: 0<connectionPoolSize<8192 |
debug
|
|
Switches on the driver's log function. The driver then writes a log file named 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
|
|
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 does the following:
Default: 1 second Setting a very high feedback interval value 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 fetch size can also be set by using the function |
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:
To bypass TLS certificate checking, use the
A connection string must only contain a single fingerprint, specified either after the host or port or in a |
hosttimeout
|
numeric, >=0 |
Specifies the connection timeout in milliseconds for each TCP connection attempt. If multiple hosts are specified in the connection string, each host will wait for the specified time for a successful connection. Default: 2000 |
ignoreparams
|
string |
Comma-separated list of string parameters to be ignored by the driver’s spell checker. Example: |
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 |
A self signed certificate in JDBC it must be added to either the system default keystore or to a keystore that you have created.
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. For information about valid keystore types, refer to the documentation for the JRE/JDK version used. Default: System keystore type |
logdir
|
string |
Defines the directory where the JDBC debug log files should be written to (in debug mode). Example: 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 |
|
Default on Windows systems is 1 or SSPI Default on non-Windows systems 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 Default: no schema to open |
snapshottransactions
|
|
Changes the snapshottransaction setting in the session. Possible values: 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
|
|
Enables the user to execute queries even if the limit for active sessions (executing a query) has been reached.
Default: |
validateservercertificate
|
|
Enables or disables the TLS server certificate validation. Default: 1
|
worker |
|
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 |
Inserting data into the database |
Instead of using single insert statements like Insert the data using the native data types. For example, for the number |
Unused resources |
Unused resources should be freed immediately. For example, Prepared Statements
through |
Connection servers |
Do not specify an unnecessary wide range of IP addresses. Since the addresses in the specified range are
randomly tried until a successful connection is made, the |