JDBC Driver

v0.8+

Note

clickhouse-jdbc implements the standard JDBC interface using the latest java client. We recommend using the latest java client directly if performance/direct access is critical.

Environment requirements

• OpenJDK version >= 8

Setup

Maven

<!-- https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc -->
<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.9.6</version>
    <classifier>all</classifier>
</dependency>

Gradle (Kotlin)

// https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc
implementation("com.clickhouse:clickhouse-jdbc:0.9.6:all")

Gradle

// https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc
implementation 'com.clickhouse:clickhouse-jdbc:0.9.6:all'

Configuration

Driver Class: com.clickhouse.jdbc.ClickHouseDriver

Note

com.clickhouse.jdbc.ClickHouseDriver is a facade class for the new and old JDBC implementations. It uses the new JDBC implementation by default. You can use the old JDBC implementation by setting the clickhouse.jdbc.v1 property to true in the connection properties.

com.clickhouse.jdbc.Driver is new JDBC implementation. com.clickhouse.jdbc.DriverV1 is old JDBC implementation.

URL Syntax: jdbc:(ch|clickhouse)[:<protocol>]://endpoint[:port][/<database>][?param1=value1&param2=value2][#tag1,tag2,...], for example:

• jdbc:clickhouse:http://localhost:8123
• jdbc:clickhouse:https://localhost:8443?ssl=true

There are a few things to note about the URL syntax:

• only one endpoint is allowed in the URL
• protocol should be specified when it is not the default one - 'HTTP'
• port should be specified when it is not the default one '8123'
• driver do not guess the protocol from the port, you need to specify it explicitly
• ssl parameter is not required when protocol is specified.

Connection Properties

Main configuration parameters are defined in the java client. They should be passed as is to the driver. Driver has some own properties that are not part of the client configuration they are listed below.

Driver properties:

Property	Default	Description
disable_frameworks_detection	true	Disable frameworks detection for User-Agent
jdbc_ignore_unsupported_values	false	Suppresses SQLFeatureNotSupportedException where is doesn't affect the driver work
clickhouse.jdbc.v1	false	Use older JDBC implementation instead of new JDBC
default_query_settings	null	Allows passing of default query settings with query operations
jdbc_resultset_auto_close	true	Automatically closes ResultSet when Statement is closed
beta.row_binary_for_simple_insert	false	Use PreparedStatement implementation based on RowBinary writer. Works only for INSERT INTO ... VALUES queries.
jdbc_resultset_auto_close	true	Automatically closes ResultSet when Statement is closed
jdbc_use_max_result_rows	false	Enables using server property max_result_rows to limit number of rows returned by query. When enabled, overrides user-set overflow mode. See JavaDoc for details.
jdbc_sql_parser	JAVACC	Configures which SQL parser to use. Choices: ANTLR4, ANTLR4_PARAMS_PARSER, JAVACC.

Server Settings

All server settings should be prefixed with clickhouse_setting_ (same as for the client configuration).

Properties config = new Properties();
config.setProperty("user", "default");
config.setProperty("password", getPassword());

// set server setting
config.put(ClientConfigProperties.serverSetting("allow_experimental_time_time64_type"), "1");

Connection conn = Driver.connect("jdbc:ch:http://localhost:8123/", config);

Example configuration:

Properties properties = new Properties();
properties.setProperty("user", "default");
properties.setProperty("password", getPassword());
properties.setProperty("client_name", "my-app-01"); // when http protocol is used it will be `http_user_agent` in the query log but not `client_name`.

Connection conn = Driver.connect("jdbc:ch:http://localhost:8123/", properties);

what will be equivalent to the following JDBC URL:

jdbc:ch:http://localhost:8123/?user=default&password=password&client_name=my-app-01 
// credentials shoud be passed in `Properties`. Here it is just for example.

Note: no need to url encode JDBC URL or properties, they will be automatically encoded.

Supported data types

JDBC Driver supports the same data formats as the underlying java client.

JDBC Type Mapping

Following mapping applies to:

• ResultSet#getObject(columnIndex) - method will return object of the corresponding Java class. (Int8 -> java.lang.Byte, Int16 -> java.lang.Short, etc.)
• ResultSetMetaData#getColumnType(columnIndex) - method will return the corresponding JDBC type. (Int8 -> java.lang.Byte, Int16 -> java.lang.Short, etc.)

There are few ways to change the mapping:

• ResultSet#getObject(columnIndex, class) - method will try to convert value to class type. There are some conversion limitations. See each section for details.

Numeric Types

ClickHouse Type	JDBC Type	Java Class
Int8	TINYINT	java.lang.Byte
Int16	SMALLINT	java.lang.Short
Int32	INTEGER	java.lang.Integer
Int64	BIGINT	java.lang.Long
Int128	OTHER	java.math.BigInteger
Int256	OTHER	java.math.BigInteger
UInt8	OTHER	java.lang.Short
UInt16	OTHER	java.lang.Integer
UInt32	OTHER	java.lang.Long
UInt64	OTHER	java.math.BigInteger
UInt128	OTHER	java.math.BigInteger
UInt256	OTHER	java.math.BigInteger
Float32	REAL	java.lang.Float
Float64	DOUBLE	java.lang.Double
Decimal32	DECIMAL	java.math.BigDecimal
Decimal64	DECIMAL	java.math.BigDecimal
Decimal128	DECIMAL	java.math.BigDecimal
Decimal256	DECIMAL	java.math.BigDecimal
Bool	BOOLEAN	java.lang.Boolean

• numeric types are interconvertible. So Int8 can be get as Float64 and vice versa.:
  • rs.getObject(1, Float64.class) will return Float64 value of Int8 column.
  • rs.getLong(1) will return Long value of Int8 column.
  • rs.getByte(1) can return Byte value of Int16 column if it fits into Byte.
• conversion from wider to narrower type is not recommend because of data coruption risk.
• Bool type acts as number, too.
• All number types can be read as java.lang.String.

String Types

ClickHouse Type	JDBC Type	Java Class
String	VARCHAR	java.lang.String
FixedString	VARCHAR	java.lang.String

• String can be read only as java.lang.String or byte[].
• FixedString is read as is and will be padded with zeros to the length of the column. (For example FixedString(10) for 'John' will be read as 'John\0\0\0\0\0\0\0\0\0'.)

Enum Types

ClickHouse Type	JDBC Type	Java Class
Enum8	OTHER	java.lang.String
Enum16	OTHER	java.lang.String

• Enum8 and Enum16 are mapped to java.lang.String by default.
• Enum values can be read as numeric values using designtated getter method or getObject(columnIndex, Integer.class) method.
• Enum16 is mapped to short and Enum8 is mapped to byte internally. Reading Enum16 as byte should be avoided because of data coruption risk.
• Enum values can be set as string or numeric value in PreparedStatement.

Date/Time Types

ClickHouse Type	JDBC Type	Java Class
Date	DATE	java.sql.Date
Date32	DATE	java.sql.Date
DateTime	TIMESTAMP	java.sql.Timestamp
DateTime64	TIMESTAMP	java.sql.Timestamp
Time	TIME	java.sql.Time
Time64	TIME	java.sql.Time

• Date / Time types are mapped to java.sql types for better compatibility with JDBC. However getting java.time.LocalDate, java.time.LocalDateTime, java.time.LocalTime is possible by using ResultSet#getObject(columnIndex, Class<T>) with the corresponding class as the second argument.
  • rs.getObject(1, java.time.LocalDate.class) will return java.time.LocalDate value of Date column.
  • rs.getObject(1, java.time.LocalDateTime.class) will return java.time.LocalDateTime value of DateTime column.
  • rs.getObject(1, java.time.LocalTime.class) will return java.time.LocalTime value of Time column.
• Date, Date32, Time, Time64 is not affected by the timezone of the server.
• DateTime, DateTime64 is affected by the timezone of the server or session timezone.
• DateTime and DateTime64 can be retrieved as ZonedDateTime by using getObject(colIndex, ZonedDateTime.class).

Collection Types

ClickHouse Type	JDBC Type	Java Class
Array	ARRAY	java.sql.Array
Tuple	OTHER	Object[]
Map	JAVA_OBJECT	java.util.Map

• Array is mapped to java.sql.Array by default to be compatible with JDBC. This is also done to give more information about returned array value. Useful for type inference.
• Array implements getResultSet() method to return java.sql.ResultSet with the same content as the original array.
• Collection types should not be read as java.lang.String because it is not a valid way to represent the data (Ex. there is no quoting for string values in array).
• Map is mapped to JAVA_OBJECT because value can be read only with getObject(columnIndex, Class<T>) method.
  • Map is not a java.sql.Struct because it doesn't have named columns.
• Tuple is mapped to Object[] because it can contain different types and using List is not valid.
• Tuple can be read as Array by using getObject(columnIndex, Array.class) method. In this case Array#baseTypeName will return Tuple column definition.

Geo Types

ClickHouse Type	JDBC Type	Java Class
Point	OTHER	double[]
Ring	OTHER	double[][]
Polygon	OTHER	double[][][]
MultiPolygon	OTHER	double[][][][]

Nullable and LowCardinality Types

• Nullable and LowCardinality are special types that wrap other types.
• Nullable affects how type names are returned in ResultSetMetaData

Special Types

ClickHouse Type	JDBC Type	Java Class
UUID	OTHER	java.util.UUID
IPv4	OTHER	java.net.Inet4Address
IPv6	OTHER	java.net.Inet6Address
JSON	OTHER	java.lang.String
AggregateFunction	OTHER	(binary representation)
SimpleAggregateFunction	(wrapped type)	(wrapped class)

• UUID is not JDBC standard type. However it is part of JDK. By default java.util.UUID is returned on getObject() method.
• UUID can be read/written as String by using getObject(columnIndex, String.class) method.
• IPv4 and IPv6 are not JDBC standard types. However they are part of JDK. By default java.net.Inet4Address and java.net.Inet6Address are returned on getObject() method.
• IPv4 and IPv6 can be read/written as String by using getObject(columnIndex, String.class) method.

Handling Dates, Times, and Timezones

java.sql.Date, java.sql.Time, and java.sql.Timestamp can complicate how Timezones are calculated - though they're of course supported, you may want to consider using the java.time package. ZonedDateTime and OffsetDateTime are both great replacements for java.sql.Timestamp, java.sql.Date, and java.sql.Time.

Date vs DateTime

Date is stored without timezone, while DateTime is stored with timezone. This can lead to unexpected results if you're not careful.

Creating Connection

String url = "jdbc:ch://my-server:8123/system";

Properties properties = new Properties();
DataSource dataSource = new DataSource(url, properties);//DataSource or DriverManager are the main entry points
try (Connection conn = dataSource.getConnection()) {
... // do something with the connection

Supplying Credentials and Settings

String url = "jdbc:ch://localhost:8123?jdbc_ignore_unsupported_values=true&socket_timeout=10";

Properties info = new Properties();
info.put("user", "default");
info.put("password", "password");
info.put("database", "some_db");

//Creating a connection with DataSource
DataSource dataSource = new DataSource(url, info);
try (Connection conn = dataSource.getConnection()) {
... // do something with the connection
}

//Alternate approach using the DriverManager
try (Connection conn = DriverManager.getConnection(url, info)) {
... // do something with the connection
}

Simple Statement

try (Connection conn = dataSource.getConnection(...);
    Statement stmt = conn.createStatement()) {
    ResultSet rs = stmt.executeQuery("select * from numbers(50000)");
    while(rs.next()) {
        // ...
    }
}

Insert

try (PreparedStatement ps = conn.prepareStatement("INSERT INTO mytable VALUES (?, ?)")) {
    ps.setString(1, "test"); // id
    ps.setObject(2, LocalDateTime.now()); // timestamp
    ps.addBatch();
    ...
    ps.executeBatch(); // stream everything on-hand into ClickHouse
}

HikariCP

// connection pooling won't help much in terms of performance,
// because the underlying implementation has its own pool.
// for example: HttpURLConnection has a pool for sockets
HikariConfig poolConfig = new HikariConfig();
poolConfig.setConnectionTimeout(5000L);
poolConfig.setMaximumPoolSize(20);
poolConfig.setMaxLifetime(300_000L);
poolConfig.setDataSource(new ClickHouseDataSource(url, properties));

try (HikariDataSource ds = new HikariDataSource(poolConfig);
     Connection conn = ds.getConnection();
     Statement s = conn.createStatement();
     ResultSet rs = s.executeQuery("SELECT * FROM system.numbers LIMIT 3")) {
    while (rs.next()) {
        // handle row
        log.info("Integer: {}, String: {}", rs.getInt(1), rs.getString(1));//Same column but different types
    }
}

More Information

For more information, see our GitHub repository and Java Client documentation.

Troubleshooting

Logging

The driver uses slf4j for logging, and will use the first available implementation on the classpath.

Resolving JDBC Timeout on Large Inserts

When performing large inserts in ClickHouse with long execution times, you may encounter JDBC timeout errors like:

Caused by: java.sql.SQLException: Read timed out, server myHostname [uri=https://hostname.aws.clickhouse.cloud:8443]

These errors can disrupt the data insertion process and affect system stability. To address this issue you may need to adjust a few timeout settings in the client's OS.

Mac OS

On Mac OS, the following settings can be adjusted to resolve the issue:

• net.inet.tcp.keepidle: 60000
• net.inet.tcp.keepintvl: 45000
• net.inet.tcp.keepinit: 45000
• net.inet.tcp.keepcnt: 8
• net.inet.tcp.always_keepalive: 1

Linux

On Linux, the equivalent settings alone may not resolve the issue. Additional steps are required due to the differences in how Linux handles socket keep-alive settings. Follow these steps:

1. Adjust the following Linux kernel parameters in /etc/sysctl.conf or a related configuration file:

• net.inet.tcp.keepidle: 60000
• net.inet.tcp.keepintvl: 45000
• net.inet.tcp.keepinit: 45000
• net.inet.tcp.keepcnt: 8
• net.inet.tcp.always_keepalive: 1
• net.ipv4.tcp_keepalive_intvl: 75
• net.ipv4.tcp_keepalive_probes: 9
• net.ipv4.tcp_keepalive_time: 60 (You may consider lowering this value from the default 300 seconds)

2. After modifying the kernel parameters, apply the changes by running the following command:

sudo sysctl -p

After Setting those settings, you need to ensure that your client enables the Keep Alive option on the socket:

properties.setProperty("socket_keepalive", "true");

Migration Guide

Key Changes

Feature	V1 (Old)	V2 (New)
Transaction Support	Partially supported	Not supported
Response Column Renaming	Partially supported	Not supported
Multi-Statement SQL	Not supported	Not allowed
Named Parameters	Supported	Not supported (not in JDBC spec)
Streaming Data With PreparedStatement	Supported	Not supported

• JDBC V2 is implemented to be more lightweight and some features were removed.
  • Streaming Data is not supported in JDBC V2 because it is not part of the JDBC spec and Java.
• JDBC V2 expects explicit configuration. No failover defaults.
  • Protocol should be specified in the URL. No implicit protocol detection using port numbers.

Configuration Changes

There are only two enums:

• com.clickhouse.jdbc.DriverProperties - the driver own configuration properties.
• com.clickhouse.client.api.ClientConfigProperties - the client configuration properties. Client configuration changes are described in the Java Client documentation.

Connection properties are parsed in the following way:

• URL is parsed first for properties. They override all other properties.
• Driver properties are not passed to the client.
• Endpoints (host, port, protocol) are parsed from the URL.

Example:

String url = "jdbc:ch://my-server:8443/default?" +
            "jdbc_ignore_unsupported_values=true&" +
            "socket_rcvbuf=800000";

Properties properties = new Properties();
properties.setProperty("socket_rcvbuf", "900000");
try (Connection conn = DriverManager.getConnection(url, properties)) {
    // Connection will use socket_rcvbuf=800000 and jdbc_ignore_unsupported_values=true
    // Endpoints: my-server:8443 protocol: http (not secure)
    // Database: default
}

Data Types Changes

Numeric Types

ClickHouse Type	Changed	JDBC Type (V2)	Java Class (V2)	JDBC Type (V1)	Java Class (V1)
Int8	No	TINYINT	java.lang.Byte	TINYINT	java.lang.Byte
Int16	No	SMALLINT	java.lang.Short	SMALLINT	java.lang.Short
Int32	No	INTEGER	java.lang.Integer	INTEGER	java.lang.Integer
Int64	No	BIGINT	java.lang.Long	BIGINT	java.lang.Long
Int128	No	OTHER	java.math.BigInteger	OTHER	java.math.BigInteger
Int256	No	OTHER	java.math.BigInteger	OTHER	java.math.BigInteger
UInt8	Yes	OTHER	java.lang.Short	OTHER	com.clickhouse.data.value.UnsignedByte
UInt16	Yes	OTHER	java.lang.Integer	OTHER	com.clickhouse.data.value.UnsignedShort
UInt32	Yes	OTHER	java.lang.Long	OTHER	com.clickhouse.data.value.UnsignedInteger
UInt64	Yes	OTHER	java.math.BigInteger	OTHER	com.clickhouse.data.value.UnsignedLong
UInt128	No	OTHER	java.math.BigInteger	OTHER	java.math.BigInteger
UInt256	No	OTHER	java.math.BigInteger	OTHER	java.math.BigInteger
Float32	No	REAL	java.lang.Float	REAL	java.lang.Float
Float64	No	DOUBLE	java.lang.Double	DOUBLE	java.lang.Double
Decimal32	No	DECIMAL	java.math.BigDecimal	DECIMAL	java.math.BigDecimal
Decimal64	No	DECIMAL	java.math.BigDecimal	DECIMAL	java.math.BigDecimal
Decimal128	No	DECIMAL	java.math.BigDecimal	DECIMAL	java.math.BigDecimal
Decimal256	No	DECIMAL	java.math.BigDecimal	DECIMAL	java.math.BigDecimal
Bool	No	BOOLEAN	java.lang.Boolean	BOOLEAN	java.lang.Boolean

• The biggest differences is that unsigned types are mapped to java types for better portability.

String Types

ClickHouse Type	Changed	JDBC Type (V2)	Java Class (V2)	JDBC Type (V1)	Java Class (V1)
String	No	VARCHAR	java.lang.String	VARCHAR	java.lang.String
FixedString	No	VARCHAR	java.lang.String	VARCHAR	java.lang.String

• FixedString is read as is in both versions. For example FixedString(10) for 'John' will be read as 'John\0\0\0\0\0\0\0\0\0'.

Date/Time Types

ClickHouse Type	Changed	JDBC Type (V2)	Java Class (V2)	JDBC Type (V1)	Java Class (V1)
Date	Yes	DATE	java.sql.Date	DATE	java.time.LocalDate
Date32	Yes	DATE	java.sql.Date	DATE	java.time.LocalDate
DateTime	Yes	TIMESTAMP	java.sql.Timestamp	TIMESTAMP	java.time.OffsetDateTime
DateTime64	Yes	TIMESTAMP	java.sql.Timestamp	TIMESTAMP	java.time.OffsetDateTime
Time	No	TIME	java.sql.Time	new type/not supported	new type/not supported
Time64	No	TIME	java.sql.Time	new type/not supported	new type/not supported

• Time and Time64 are supported in V2 only as new types.
• DateTime and DateTime64 are mapped to java.sql.Timestamp for better compatibility with JDBC.

Enum Types

ClickHouse Type	Changed	JDBC Type (V2)	Java Class (V2)	JDBC Type (V1)	Java Class (V1)
Enum	No	VARCHAR	java.lang.String	OTHER	java.lang.String
Enum8	No	VARCHAR	java.lang.String	OTHER	java.lang.String
Enum16	No	VARCHAR	java.lang.String	OTHER	java.lang.String

Collection Types

ClickHouse Type	Changed	JDBC Type (V2)	Java Class (V2)	JDBC Type (V1)	Java Class (V1)
Array	Yes	ARRAY	java.sql.Array	ARRAY	Object[] or array of primitive types
Tuple	Yes	OTHER	Object[]	STRUCT	java.sql.Struct
Map	Yes	JAVA_OBJECT	java.util.Map	STRUCT	java.util.Map

• In V2 Array is mapped to java.sql.Array by default to be compatible with JDBC. This is also done to give more information about returned array value. Useful for type inference.
• In V2 Array implements getResultSet() method to return java.sql.ResultSet with the same content as the original array.
• V1 uses STRUCT for Map but returns java.util.Map object always. V2 fixes this by mapping Map to JAVA_OBJECT. Besides STRUCT is invalid for Map because it doesn't have named columns.
• V1 uses STRUCT for Tuple but returns List<Object> object always. V2 fixes this by mapping Tuple to OTHER and returns Object[] because using List is not valid as different types can be present.

Geo Types

ClickHouse Type	Changed	JDBC Type (V2)	Java Class (V2)	JDBC Type (V1)	Java Class (V1)
Point	No	OTHER	double[]	OTHER	double[]
Ring	No	OTHER	double[][]	OTHER	double[][]
Polygon	No	OTHER	double[][][]	OTHER	double[][][]
MultiPolygon	No	OTHER	double[][][][]	OTHER	double[][][][]

Nullable and LowCardinality Types

• Nullable and LowCardinality are special types that wrap other types.
• No changes are made to these types in V2.

Special Types

ClickHouse Type	Changed	JDBC Type (V2)	Java Class (V2)	JDBC Type (V1)	Java Class (V1)
JSON	Yes	OTHER	java.lang.String	not supported	not supported
AggregateFunction	No	OTHER	(binary representation)	OTHER	(binary representation)
SimpleAggregateFunction	No	(wrapped type)	(wrapped class)	(wrapped type)	(wrapped class)
UUID	No	OTHER	java.util.UUID	VARCHAR	java.util.UUID
IPv4	No	OTHER	java.net.Inet4Address	VARCHAR	java.net.Inet4Address
IPv6	No	OTHER	java.net.Inet6Address	VARCHAR	java.net.Inet6Address
Dynamic	Yes	OTHER	java.Object	not supported	not supported
Variant	Yes	OTHER	java.Object	not supported	not supported

• V1 uses VARCHAR for UUID but returns java.util.UUID object always. V2 fixes this by mapping UUID to OTHER and returns java.util.UUID object.
• V1 uses VARCHAR for IPv4 and IPv6 but returns java.net.Inet4Address and java.net.Inet6Address objects always. V2 fixes this by mapping IPv4 and IPv6 to OTHER and returns java.net.Inet4Address and java.net.Inet6Address objects.
• Dynamic and Variant are new types in V2. Not supported in V1.
• JSON is based on Dynamic type. Therefore it is supported only in V2.

clickhouse-jdbc implements the standard JDBC interface. Being built on top of clickhouse-client, it provides additional features like custom type mapping, transaction support, and standard synchronous UPDATE and DELETE statements, etc., so that it can be easily used with legacy applications and tools.

Note

Latest JDBC (0.7.2) version uses Client-V1

clickhouse-jdbc API is synchronous, and generally, it has more overheads(e.g., SQL parsing and type mapping/conversion, etc.). Consider clickhouse-client when performance is critical or if you prefer a more direct way to access ClickHouse.

Environment requirements

• OpenJDK version >= 8

Setup

Maven

<!-- https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc -->
<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.7.2</version>
    <!-- use uber jar with all dependencies included, change classifier to http for smaller jar -->
    <classifier>shaded-all</classifier>
</dependency>

Gradle (Kotlin)

// https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc
// use uber jar with all dependencies included, change classifier to http for smaller jar
implementation("com.clickhouse:clickhouse-jdbc:0.7.2:shaded-all")

Gradle

// https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc
// use uber jar with all dependencies included, change classifier to http for smaller jar
implementation 'com.clickhouse:clickhouse-jdbc:0.7.2:shaded-all'

Since version 0.5.0, we are using Apache HTTP Client that's packed the Client. Since there is not a shared version of the package, you need to add a logger as a dependency.

Maven

<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>2.0.16</version>
</dependency>

Gradle (Kotlin)

// https://mvnrepository.com/artifact/org.slf4j/slf4j-api
implementation("org.slf4j:slf4j-api:2.0.16")

Gradle

// https://mvnrepository.com/artifact/org.slf4j/slf4j-api
implementation 'org.slf4j:slf4j-api:2.0.16'

Configuration

Driver Class: com.clickhouse.jdbc.ClickHouseDriver

URL Syntax: jdbc:(ch|clickhouse)[:<protocol>]://endpoint1[,endpoint2,...][/<database>][?param1=value1&param2=value2][#tag1,tag2,...], for example:

• jdbc:ch://localhost is same as jdbc:clickhouse:http://localhost:8123
• jdbc:ch:https://localhost is same as jdbc:clickhouse:http://localhost:8443?ssl=true&sslmode=STRICT
• jdbc:ch:grpc://localhost is same as jdbc:clickhouse:grpc://localhost:9100

Connection Properties:

Property	Default	Description
continueBatchOnError	false	Whether to continue batch processing when error occurred
createDatabaseIfNotExist	false	Whether to create database if it does not exist
custom_http_headers		comma separated custom http headers, for example: User-Agent=client1,X-Gateway-Id=123
custom_http_params		comma separated custom http query parameters, for example: extremes=0,max_result_rows=100
nullAsDefault	0	0 - treat null value as is and throw exception when inserting null into non-nullable column; 1 - treat null value as is and disable null-check for inserting; 2 - replace null to default value of corresponding data type for both query and insert
jdbcCompliance	true	Whether to support standard synchronous UPDATE/DELETE and fake transaction
typeMappings		Customize mapping between ClickHouse data type and Java class, which will affect result of both getColumnType() and getObject(Class<>?>). For example: UInt128=java.lang.String,UInt256=java.lang.String
wrapperObject	false	Whether getObject() should return java.sql.Array / java.sql.Struct for Array / Tuple.

Note: please refer to JDBC specific configuration for more.

Supported data types

JDBC Driver supports same data formats as client library does.

Note

• AggregatedFunction - ⚠️ does not support SELECT * FROM table ...
• Decimal - SET output_format_decimal_trailing_zeros=1 in 21.9+ for consistency
• Enum - can be treated as both string and integer
• UInt64 - mapped to long (in client-v1)

Creating Connection

String url = "jdbc:ch://my-server/system"; // use http protocol and port 8123 by default

Properties properties = new Properties();

ClickHouseDataSource dataSource = new ClickHouseDataSource(url, properties);
try (Connection conn = dataSource.getConnection("default", "password");
    Statement stmt = conn.createStatement()) {
}

Simple Statement

try (Connection conn = dataSource.getConnection(...);
    Statement stmt = conn.createStatement()) {
    ResultSet rs = stmt.executeQuery("select * from numbers(50000)");
    while(rs.next()) {
        // ...
    }
}

Insert

Note

• Use PreparedStatement instead of Statement

It's easier to use but slower performance compare to input function (see below):

try (PreparedStatement ps = conn.prepareStatement("insert into mytable(* except (description))")) {
    ps.setString(1, "test"); // id
    ps.setObject(2, LocalDateTime.now()); // timestamp
    ps.addBatch(); // parameters will be write into buffered stream immediately in binary format
    ...
    ps.executeBatch(); // stream everything on-hand into ClickHouse
}

With input table function

An option with great performance characteristics:

try (PreparedStatement ps = conn.prepareStatement(
    "insert into mytable select col1, col2 from input('col1 String, col2 DateTime64(3), col3 Int32')")) {
    // The column definition will be parsed so the driver knows there are 3 parameters: col1, col2 and col3
    ps.setString(1, "test"); // col1
    ps.setObject(2, LocalDateTime.now()); // col2, setTimestamp is slow and not recommended
    ps.setInt(3, 123); // col3
    ps.addBatch(); // parameters will be write into buffered stream immediately in binary format
    ...
    ps.executeBatch(); // stream everything on-hand into ClickHouse
}

• input function doc whenever possible

Insert with placeholders

This option is recommended only for small inserts because it would require a long SQL expression (that will be parsed on client side and it will consume CPU & Memory):

try (PreparedStatement ps = conn.prepareStatement("insert into mytable values(trim(?),?,?)")) {
    ps.setString(1, "test"); // id
    ps.setObject(2, LocalDateTime.now()); // timestamp
    ps.setString(3, null); // description
    ps.addBatch(); // append parameters to the query
    ...
    ps.executeBatch(); // issue the composed query: insert into mytable values(...)(...)...(...)
}

Handling DateTime and time zones

Please to use java.time.LocalDateTime or java.time.OffsetDateTime instead of java.sql.Timestamp, and java.time.LocalDate instead of java.sql.Date.

try (PreparedStatement ps = conn.prepareStatement("select date_time from mytable where date_time > ?")) {
    ps.setObject(2, LocalDateTime.now());
    ResultSet rs = ps.executeQuery();
    while(rs.next()) {
        LocalDateTime dateTime = (LocalDateTime) rs.getObject(1);
    }
    ...
}

Handling AggregateFunction

Note

As of now, only groupBitmap is supported.

// batch insert using input function
try (ClickHouseConnection conn = newConnection(props);
        Statement s = conn.createStatement();
        PreparedStatement stmt = conn.prepareStatement(
                "insert into test_batch_input select id, name, value from input('id Int32, name Nullable(String), desc Nullable(String), value AggregateFunction(groupBitmap, UInt32)')")) {
    s.execute("drop table if exists test_batch_input;"
            + "create table test_batch_input(id Int32, name Nullable(String), value AggregateFunction(groupBitmap, UInt32))engine=Memory");
    Object[][] objs = new Object[][] {
            new Object[] { 1, "a", "aaaaa", ClickHouseBitmap.wrap(1, 2, 3, 4, 5) },
            new Object[] { 2, "b", null, ClickHouseBitmap.wrap(6, 7, 8, 9, 10) },
            new Object[] { 3, null, "33333", ClickHouseBitmap.wrap(11, 12, 13) }
    };
    for (Object[] v : objs) {
        stmt.setInt(1, (int) v[0]);
        stmt.setString(2, (String) v[1]);
        stmt.setString(3, (String) v[2]);
        stmt.setObject(4, v[3]);
        stmt.addBatch();
    }
    int[] results = stmt.executeBatch();
    ...
}

// use bitmap as query parameter
try (PreparedStatement stmt = conn.prepareStatement(
    "SELECT bitmapContains(my_bitmap, toUInt32(1)) as v1, bitmapContains(my_bitmap, toUInt32(2)) as v2 from {tt 'ext_table'}")) {
    stmt.setObject(1, ClickHouseExternalTable.builder().name("ext_table")
            .columns("my_bitmap AggregateFunction(groupBitmap,UInt32)").format(ClickHouseFormat.RowBinary)
            .content(new ByteArrayInputStream(ClickHouseBitmap.wrap(1, 3, 5).toBytes()))
            .asTempTable()
            .build());
    ResultSet rs = stmt.executeQuery();
    Assert.assertTrue(rs.next());
    Assert.assertEquals(rs.getInt(1), 1);
    Assert.assertEquals(rs.getInt(2), 0);
    Assert.assertFalse(rs.next());
}

Configuring HTTP library

The ClickHouse JDBC connector supports three HTTP libraries: HttpClient, HttpURLConnection, and Apache HttpClient.

Note

HttpClient is only supported in JDK 11 or above.

The JDBC driver uses HttpClient by default. You can change the HTTP library used by the ClickHouse JDBC connector by setting the following property:

properties.setProperty("http_connection_provider", "APACHE_HTTP_CLIENT");

Here is a full list of the corresponding values:

Property Value	HTTP Library
HTTP_CLIENT	HttpClient
HTTP_URL_CONNECTION	HttpURLConnection
APACHE_HTTP_CLIENT	Apache HttpClient

Connect to ClickHouse with SSL

To establish a secure JDBC connection to ClickHouse using SSL, you need to configure your JDBC properties to include SSL parameters. This typically involves specifying SSL properties such as sslmode and sslrootcert in your JDBC URL or Properties object.

SSL Properties

Name	Default Value	Optional Values	Description
ssl	false	true, false	Whether to enable SSL/TLS for the connection
sslmode	strict	strict, none	Whether to verify SSL/TLS certificate
sslrootcert			Path to SSL/TLS root certificates
sslcert			Path to SSL/TLS certificate
sslkey			RSA key in PKCS#8 format
key_store_type		JKS, PKCS12	Specifies the type or format of the KeyStore/TrustStore file
trust_store			Path to the TrustStore file
key_store_password			Password needed to access the KeyStore file specified in the KeyStore config

These properties ensure that your Java application communicates with the ClickHouse server over an encrypted connection, enhancing data security during transmission.

  String url = "jdbc:ch://your-server:8443/system";

  Properties properties = new Properties();
  properties.setProperty("ssl", "true");
  properties.setProperty("sslmode", "strict"); // NONE to trust all servers; STRICT for trusted only
  properties.setProperty("sslrootcert", "/mine.crt");
  try (Connection con = DriverManager
          .getConnection(url, properties)) {

      try (PreparedStatement stmt = con.prepareStatement(

          // place your code here

      }
  }

Resolving JDBC Timeout on Large Inserts

When performing large inserts in ClickHouse with long execution times, you may encounter JDBC timeout errors like:

Caused by: java.sql.SQLException: Read timed out, server myHostname [uri=https://hostname.aws.clickhouse.cloud:8443]

These errors can disrupt the data insertion process and affect system stability. To address this issue you need to adjust a few timeout settings in the client's OS.

Mac OS

On Mac OS, the following settings can be adjusted to resolve the issue:

• net.inet.tcp.keepidle: 60000
• net.inet.tcp.keepintvl: 45000
• net.inet.tcp.keepinit: 45000
• net.inet.tcp.keepcnt: 8
• net.inet.tcp.always_keepalive: 1

Linux

On Linux, the equivalent settings alone may not resolve the issue. Additional steps are required due to the differences in how Linux handles socket keep-alive settings. Follow these steps:

1. Adjust the following Linux kernel parameters in /etc/sysctl.conf or a related configuration file:

• net.inet.tcp.keepidle: 60000
• net.inet.tcp.keepintvl: 45000
• net.inet.tcp.keepinit: 45000
• net.inet.tcp.keepcnt: 8
• net.inet.tcp.always_keepalive: 1
• net.ipv4.tcp_keepalive_intvl: 75
• net.ipv4.tcp_keepalive_probes: 9
• net.ipv4.tcp_keepalive_time: 60 (You may consider lowering this value from the default 300 seconds)

2. After modifying the kernel parameters, apply the changes by running the following command:

sudo sysctl -p

After Setting those settings, you need to ensure that your client enables the Keep Alive option on the socket:

properties.setProperty("socket_keepalive", "true");

Note

Currently, you must use Apache HTTP Client library when setting the socket keep-alive, as the other two HTTP client libraries supported by clickhouse-java do not allow setting socket options. For a detailed guide, see Configuring HTTP library.

Alternatively, you can add equivalent parameters to the JDBC URL.

The default socket and connection timeout for the JDBC driver is 30 seconds. The timeout can be increased to support large data insert operations. Use the options method on ClickHouseClient together with the SOCKET_TIMEOUT and CONNECTION_TIMEOUT options as defined by ClickHouseClientOption:

final int MS_12H = 12 * 60 * 60 * 1000; // 12 h in ms
final String sql = "insert into table_a (c1, c2, c3) select c1, c2, c3 from table_b;";

try (ClickHouseClient client = ClickHouseClient.newInstance(ClickHouseProtocol.HTTP)) {
    client.read(servers).write()
        .option(ClickHouseClientOption.SOCKET_TIMEOUT, MS_12H)
        .option(ClickHouseClientOption.CONNECTION_TIMEOUT, MS_12H)
        .query(sql)
        .executeAndWait();
}