How to connect to and query ClickHouse^® from Java with examples

ClickHouse® handles analytical queries at scale, and Java remains the language of choice for many enterprise applications that need to integrate with it. Connecting the two involves choosing the right client library, understanding connection patterns, and optimizing for batch operations rather than single-row transactions.

This guide walks through adding the JDBC driver to your project, opening connections, executing queries, inserting data efficiently, and turning those queries into REST APIs using managed ClickHouse® services.

Why use ClickHouse® with Java

ClickHouse® is a columnar database built for analytical queries on large datasets, adopted by over 2,000 customers as of 2025. Java remains widely used for enterprise applications and microservices that handle data at scale. When you combine ClickHouse® with Java, you get fast analytics capabilities for building real-time dashboards, monitoring systems, and data-intensive applications.

Add the ClickHouse® JDBC driver to your project

The ClickHouse® JDBC driver provides the standard Java Database Connectivity interface for connecting to ClickHouse® servers. You can add this dependency to your Java project using Maven or Gradle.

Maven coordinates

Add this dependency to your pom.xml file:

<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.6.5</version>
    <classifier>all</classifier>
</dependency>

The all classifier includes all required dependencies in a single JAR file. The driver works with Java 8 or higher, though Java 11 or later is recommended for production use.

Gradle coordinates

For Gradle projects, add this line to your build.gradle dependencies block:

dependencies {
    implementation 'com.clickhouse:clickhouse-jdbc:0.6.5:all'
}

The driver pulls in the necessary HTTP client libraries and JSON parsers automatically. No additional repositories beyond Maven Central are required.

Choosing client v1 vs v2

The ClickHouse® Java ecosystem includes two client implementations. The v2 client offers better performance through connection pooling, async query support, and improved type mapping. For new projects, the v2 client is the better choice, though the JDBC driver (which uses v2 internally) provides the most familiar interface for Java developers. For Python developers, see our complete ClickHouse® Python integration guide with examples for both clickhouse-connect and clickhouse-driver.

Open a connection to ClickHouse®

Connecting to ClickHouse® from Java follows the standard JDBC pattern of creating a connection string, loading the driver, and establishing a connection. The connection string format changes depending on whether you're using the HTTP or native TCP interface.

HTTP interface URL format

The HTTP interface is the most common way to connect to ClickHouse® from Java applications:

String url = "jdbc:clickhouse://localhost:8123/default";

For secure connections over HTTPS, modify the protocol and port:

String url = "jdbc:clickhouse:https://your-host.com:8443/default";

The URL includes the protocol, the host and port, and the database name. Port 8123 is the default for HTTP, while 8443 is common for HTTPS.

Native TCP interface URL format

The native TCP protocol offers better performance for high-throughput scenarios. The connection string format uses jdbc:ch as the protocol:

String url = "jdbc:ch://localhost:9000/default";

The native interface typically runs on port 9000. This protocol is binary and more efficient than HTTP, but it offers fewer debugging options and is harder to proxy through load balancers.

Secure credential storage

Hardcoding credentials in source code creates security risks and makes credential rotation difficult. Store your database credentials in environment variables or configuration files that are excluded from version control:

String url = "jdbc:clickhouse://localhost:8123/default";
Properties properties = new Properties();
properties.setProperty("user", System.getenv("CLICKHOUSE_USER"));
properties.setProperty("password", System.getenv("CLICKHOUSE_PASSWORD"));

try (Connection conn = DriverManager.getConnection(url, properties)) {
    // Use connection
}

For production applications, consider using a secrets management service like AWS Secrets Manager or HashiCorp Vault.

Run your first select query

Once you have a connection, executing a SELECT query follows the standard JDBC pattern. Here's a complete example:

import java.sql.*;

public class ClickHouse®Example {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:clickhouse://localhost:8123/default";

        try (Connection conn = DriverManager.getConnection(url, "default", "");
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT number, number * 2 FROM system.numbers LIMIT 5")) {

            while (rs.next()) {
                long number = rs.getLong(1);
                long doubled = rs.getLong(2);
                System.out.println(number + " -> " + doubled);
            }
        }
    }
}

This example connects to a local ClickHouse® instance, queries the system.numbers table (a built-in table that generates sequential numbers), and prints the results.

Mapping results to plain Java objects

Instead of working directly with ResultSet, you can map query results to Java objects. Iterate through the result set and populate your object fields:

public class UserEvent {
    private String userId;
    private String eventType;
    private Timestamp eventTime;

    // Constructor, getters, setters omitted for brevity
}

try (ResultSet rs = stmt.executeQuery("SELECT user_id, event_type, event_time FROM events LIMIT 100")) {
    List<UserEvent> events = new ArrayList<>();
    while (rs.next()) {
        UserEvent event = new UserEvent();
        event.setUserId(rs.getString("user_id"));
        event.setEventType(rs.getString("event_type"));
        event.setEventTime(rs.getTimestamp("event_time"));
        events.add(event);
    }
}

ClickHouse®'s DateTime64 type maps to java.sql.Timestamp, while Date maps to java.sql.Date.

Parameterized queries with PreparedStatement

Using PreparedStatement prevents SQL injection attacks and improves query performance:

String sql = "SELECT * FROM events WHERE user_id = ? AND event_time > ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setString(1, "user_12345");
    pstmt.setTimestamp(2, Timestamp.valueOf("2024-01-01 00:00:00"));

    try (ResultSet rs = pstmt.executeQuery()) {
        while (rs.next()) {
            // Process results
        }
    }
}

ClickHouse® supports standard JDBC parameter binding with question marks. The driver handles type conversion automatically for most common Java types.

Insert data in batches and streams

ClickHouse® is optimized for batch inserts rather than single-row inserts. The database achieves its best performance when inserting thousands or millions of rows at once.

Single row insert

For occasional inserts or testing, you can insert single rows using a standard INSERT statement:

String sql = "INSERT INTO events (user_id, event_type, event_time) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setString(1, "user_12345");
    pstmt.setString(2, "page_view");
    pstmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
    pstmt.executeUpdate();
}

Single-row inserts work for low-volume scenarios, but they create overhead with network round-trips and merge operations.

Batch insert with PreparedStatement

JDBC's batch API allows you to accumulate multiple insert operations and execute them together:

String sql = "INSERT INTO events (user_id, event_type, event_time) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    for (UserEvent event : eventsToInsert) {
        pstmt.setString(1, event.getUserId());
        pstmt.setString(2, event.getEventType());
        pstmt.setTimestamp(3, event.getEventTime());
        pstmt.addBatch();
    }
    pstmt.executeBatch();
}

Batch sizes between 10,000 and 100,000 rows typically offer good performance. Larger batches reduce overhead but increase memory usage.

Streaming insert with ClickHouse® client

import com.clickhouse.client.*;
import com.clickhouse.data.ClickHouse®Format;

try (ClickHouse®Client client = ClickHouse®Client.newInstance(ClickHouse®Protocol.HTTP)) {
    ClickHouse®Request<?> request = client.read(server)
        .write()
        .table("events")
        .format(ClickHouse®Format.RowBinary);

    try (ClickHouse®Response response = request.data(inputStream).execute().get()) {
        // Insert completed
    }
}

This approach works well when reading from files or external data sources where you can stream data directly into ClickHouse®.

Configure timeouts, compression, and pooling

Production applications benefit from tuning connection parameters. Timeouts prevent hung connections, compression reduces network transfer time, and connection pooling eliminates the overhead of creating new connections.

Socket and query timeouts

Properties properties = new Properties();
properties.setProperty("socket_timeout", "30000");  // 30 seconds
properties.setProperty("connection_timeout", "10000");  // 10 seconds
properties.setProperty("max_execution_time", "300");  // 5 minutes

Connection conn = DriverManager.getConnection(url, properties);

The socket_timeout controls how long the driver waits for network responses, while max_execution_time tells ClickHouse® to abort queries that run longer than the specified duration.

LZ4 and ZSTD compression

Enable compression in your connection properties:

properties.setProperty("compress", "true");
properties.setProperty("compress_algorithm", "lz4");  // or "zstd"

LZ4 offers faster compression with moderate space savings, while ZSTD provides better compression ratios at the cost of CPU time.

HikariCP connection pool settings

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:clickhouse://localhost:8123/default");
config.setUsername("default");
config.setPassword("");
config.setMaximumPoolSize(10);
config.setMinimumIdle(2);
config.setConnectionTimeout(10000);

HikariDataSource dataSource = new HikariDataSource(config);

try (Connection conn = dataSource.getConnection()) {
    // Use pooled connection
}

Start with a pool size equal to the number of CPU cores on your application server, then adjust based on your workload.

Turn a query into a REST API endpoint

Tinybird allows you to define SQL queries as API endpoints, eliminating the need to write and maintain REST API code for database access. This provides a different pattern for querying ClickHouse® from your Java application, allowing you to fetch from a REST endpoint rather than using JDBC.

Create a pipe file

A pipe file contains your SQL query and defines how it becomes an HTTP endpoint. Create a file named user_events.pipe:

TOKEN user_events_read READ

DESCRIPTION >
    Retrieve user events with optional filtering

NODE user_events_endpoint
SQL >
    %
    SELECT
        user_id,
        event_type,
        event_time
    FROM events
    WHERE event_time >= {{DateTime(start_time, '2024-01-01 00:00:00')}}
    {/% if defined(user_id) %}
        AND user_id = {{String(user_id)}}
    {/% end %}
    ORDER BY event_time DESC
    LIMIT {{Int32(limit, 100)}}

TYPE endpoint

The double curly braces define query parameters with type validation and default values. The if defined() syntax creates optional parameters that only apply when provided.

Add route parameters

• String(param_name): Text values, automatically escaped to prevent SQL injection
• Int32(param_name, default): Integer values with optional defaults
• DateTime(param_name, default): Timestamp values in ISO format
• Float64(param_name): Decimal numbers

Call the endpoint from Java HttpClient

After deploying your pipe with tb --cloud deploy, call it from Java using the standard HTTP client:

String url = "https://api.tinybird.co/v0/pipes/user_events.json" +
             "?start_time=2024-01-01%2000:00:00" +
             "&user_id=user_12345" +
             "&limit=50";

HttpRequest request = HttpRequest.newBuilder()
    .uri(URI.create(url))
    .header("Authorization", "Bearer " + System.getenv("TINYBIRD_TOKEN"))
    .build();

HttpResponse<String> response = client.send(request, HttpResponse.BodyHandlers.ofString());

ObjectMapper mapper = new ObjectMapper();
JsonNode json = mapper.readTree(response.body());
JsonNode data = json.get("data");

The response includes metadata about the query execution and a data array containing your results.

Common errors and how to fix them

Working with ClickHouse® from Java occasionally produces errors that aren't immediately obvious. Here are the most common issues and their solutions.

Unsupported type mapping

• Arrays: Use rs.getArray() and cast to java.sql.Array, then call getArray() to get a Java array
• Tuples: Retrieved as strings in the format "(value1, value2)", parse manually or use JSON serialization
• Nested: Stored as arrays of tuples, best handled by selecting individual nested fields

For complex types, consider using ClickHouse®'s JSONExtract functions to convert data to JSON strings, then parse with Jackson or Gson.

Data too large for buffer

When querying large result sets, you might encounter buffer overflow errors. Stream results instead of loading everything into memory:

stmt.setFetchSize(10000);
try (ResultSet rs = stmt.executeQuery("SELECT * FROM large_table")) {
    while (rs.next()) {
        // Process one row at a time
    }
}

Setting max_result_bytes in your connection properties also prevents runaway queries from consuming excessive memory.

Cannot assign requested address

This error typically indicates connection pool exhaustion or network configuration issues. Check that your max_connections setting in ClickHouse® can accommodate your connection pool size, and verify that your firewall allows connections on the ClickHouse® port (8123 for HTTP, 9000 for native).

Next steps with managed ClickHouse® and Tinybird

Tinybird provides a managed ClickHouse® platform that eliminates infrastructure complexity while maintaining full access to ClickHouse®'s query capabilities. The platform handles cluster management, scaling, backups, and monitoring automatically.

Beyond basic query execution, Tinybird offers streaming data ingestion from Kafka, webhooks, and other sources, plus built-in API generation that turns SQL queries into production-ready REST endpoints. Sign up for a free Tinybird account to start building real-time analytics features without managing ClickHouse® infrastructure.

FAQs about ClickHouse® Java integration

How do I enable SSL for the ClickHouse® JDBC driver?

Add ssl=true to your JDBC URL parameters: jdbc:clickhouse://host:8443/database?ssl=true. If your ClickHouse® server uses a self-signed certificate, configure a custom trust store using sslrootcert and sslmode parameters.

Can I run ClickHouse® queries inside a Spring Boot transaction?

ClickHouse® doesn't support traditional ACID transactions with rollback capabilities. Spring's @Transactional annotation can manage connection lifecycle and handle exceptions, but it won't provide atomic rollback of ClickHouse® operations.

How do I read ClickHouse® data into Spark from Java?

Use Spark's JDBC DataSource with the ClickHouse® driver: spark.read().jdbc(url, "table_name", properties). For better performance, specify a partition column and set numPartitions to enable parallel reads across multiple Spark executors.

Which Java versions are supported by the driver?

The ClickHouse® JDBC driver supports Java 8 and higher. Java 11 or later is recommended for production use due to better TLS support and performance improvements.