JDBC driver

Please read [Date/Time Guide](/docs/integrations/language-clients/java/jdbc_date_time_guide) that explains common pitfalls
and logic of the driver when handling Date/Time and Timestamps.

## Creating Connection[​](#creating-connection "Direct link to 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[​](#supplying-credentials-and-settings "Direct link to 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[​](#simple-statement "Direct link to Simple Statement")

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

## Insert[​](#insert "Direct link to 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`[​](#hikaricp "Direct link to 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[​](#more-information "Direct link to More Information")

For more information, see our [GitHub repository](https://github.com/ClickHouse/clickhouse-java) and [Java Client documentation](/docs/integrations/language-clients/java/client).

## Troubleshooting[​](#troubleshooting "Direct link to Troubleshooting")

### Logging[​](#logging "Direct link to Logging")

The driver uses [slf4j](https://www.slf4j.org/) for logging, and will use the first available implementation on the `classpath`.

### Resolving JDBC Timeout on Large Inserts[​](#resolving-jdbc-timeout-on-large-inserts "Direct link to 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[​](#mac-os "Direct link to 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[​](#linux "Direct link to 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[​](#migration-guide "Direct link to Migration Guide")

### Key Changes[​](#key-changes "Direct link to 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 isn't supported in JDBC V2 because it isn't 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[​](#configuration-changes "Direct link to 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](/docs/integrations/language-clients/java/client#migration_from_v1_config).

Connection properties are parsed in the following way:

- URL is parsed first for properties. They override all other properties.
- Driver properties aren't 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[​](#data-types-changes "Direct link to Data Types Changes")

**Numeric Types**