--- title: "How to connect to and query ClickHouse® from Java with examples" excerpt: "ClickHouse Java example with connection pooling done right. Production-ready code you can deploy without modifications." authors: "Cameron Archer" categories: "AI Resources" createdOn: "2025-10-14 19:30:27" publishedOn: "2025-10-17 19:30:27" updatedOn: "2025-10-17 19:30:27" status: "published" --- 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](https://www.tinybird.co/blog-posts/what-is-a-columnar-database) built for analytical queries on large datasets, adopted by [over 2,000 customers](https://clickhouse.com/blog/clickhouse-extends-series-c-financing-expands-leadership-team) 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: ```xml com.clickhouse clickhouse-jdbc 0.6.5 all ``` 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: ```groovy 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](https://www.tinybird.co/blog-posts/clickhouse-python-example) 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: ```java String url = "jdbc:clickhouse://localhost:8123/default"; ``` For secure connections over HTTPS, modify the protocol and port: ```java 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: ```java 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: ```java 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: ```java 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: ```java 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 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](https://www.tinybird.co/blog-posts/5-rules-for-writing-faster-sql-queries): ```java 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](https://www.tinybird.co/blog-posts/when-to-use-columnar-database) 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: ```java 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: ```java 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 ```java 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 ```java 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 | Algorithm | Compression Ratio | Speed | Best For | |-----------|-------------------|----------|-------------------| | LZ4 | Moderate | Fast | Real-time queries | | ZSTD | High | Moderate | Batch processing | Enable compression in your connection properties: ```java 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 ```java 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`: ```tinybird 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: ```java 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 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: ```java 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®](https://www.tinybird.co/product/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](https://cloud.tinybird.co/signup) 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.