ClickHouse spring jpa hibernate support

Question

I am using Spring JPA with hibernate ORM project and want to integrate with ClickHouse. I found some references of ClickHouse JDBC which is working

ClickHouseJDBCDemo ClickHouseJDBCDemo

but was keen to know if there any better way to interact with database from Spring JPA like any ORM tool like Hibernate .

Roswald

were you able to find any solution? – Utile

Clickhouse is not relational DB, so there is no way to get full support using JPA and ORM frameworks – Coltin

Mongodb and Neo4J not relational too, but both of them have full JPA support. JPA is about persistence interfaces, not about specific implementation. – Topsyturvy

Answer 1

I didn't manage to find the way to interact with clickhouse using, for example, spring-data-jpa.

I'm not sure, whether this is a proper solution, but i've found another way to interact with clickhouse from spring application.

1. Your pom.xml should contain the following dependencies:

<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>${clickhouse-jdbc.version}</version>
    <classifier>http</classifier>
</dependency>
<dependency>
    <groupId>org.apache.httpcomponents.client5</groupId>
    <artifactId>httpclient5</artifactId>
    <version>${http5client.version}</version>
</dependency>

2. Configure application.yml as follows:

spring:
  application:
    name: @appname@
  datasource:
    url: ${CLICKHOUSE_URL:jdbc:ch://localhost:8123/default}
    username: ${CLICKHOUSE_USERNAME:default}
    password: ${CLICKHOUSE_PASSWORD:default}
    driver-class-name: com.clickhouse.jdbc.ClickHouseDriver
  jpa:
    database-platform: org.hibernate.dialect.MySQLDialect

3. Inject JdbcTemplate bean into your service class and use it like this (pretend, we store Person entity in clickhouse with fields: first_name, last_name, age and pay attention that Person.java is a simple pojo without any annotations like @Entity, @Table and etc., which we use in a "classic" approach):

@Autowired
JdbcTemplate jdbcTemplate;

public void getData() {
    List<Person> list = jdbcTemplate.query("""
            select first_name, last_name, age
                from default.person """,
            (resultSet, rowNum) -> new DataItem(
                    resultSet.getString("first_name"),
                    resultSet.getString("second_name"),
                    resultSet.getInt("age"))
    );
    //do smth
}

list will contain persons, satisfying the query. That's it.

Regarding the entity model: annotating the entity with @Entity requires from entity to have an identifier, though the primary key in clickhouse is not what what used to see in classic row-oriented relational DBs.
So, our entity may not have id field at all! See link.
So, i'm not sure, that this is actually possible to interact with clickhouse via hibernate.

Gerri