clickhouse支持jdbc连接，也支持http，还支持Python等，这篇文章尝试使用SpringBoot Jpa连接和操作ClickHouse数据库。这里的示例使用的是多数据源的形式，所以需要自定义Configuration。
1.引入依赖
1
2
3
4
5
6
|
<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.2.4</version>
</dependency>
|
2.修改application
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| spring:
jackson:
time-zone: GMT+8
mvc:
throw-exception-if-no-handler-found: true
resources:
add-mappings: false
datasource:
third:
driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
jdbc-url: jdbc:clickhouse://192.168.1.90:8123/databasename
username: xxx
password: xxx
connection-timeout: 20000
maximum-pool-size: 5
jpa:
hibernate:
naming:
implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
show-sql: true
|
其中的databasename是你自己数据库的名字。
3.创建实体
1
2
3
4
5
6
7
8
9
10
11
12
13
| package com.proheng.gis.clickhouseEntity;
import javax.persistence.*;
@Entity
@Table(name="pipeHeatAnalyse")
public class PipeHeatAnalyse {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String calcTable;
}
|
4.编写resposity
1
2
3
4
5
6
7
| package com.proheng.gis.clickhouseReposities;
import com.proheng.gis.clickhouseEntity.PipeHeatAnalyse;
import org.springframework.data.jpa.repository.JpaRepository;
public interface PipeHeatAnalyseRepository extends JpaRepository<PipeHeatAnalyse,Long> {
}
|
5.编写JpaConfiguration
其中的实体和Reposities的位置要换成自己的。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
| @Configuration
@EntityScan(basePackages = "com.proheng.gis.clickhouseEntity")
@EnableJpaRepositories(
basePackages = "com.proheng.gis.clickhouseReposities",
entityManagerFactoryRef = "thirdEntityManagerFactoryBean",
transactionManagerRef = "thirdTransactionManager")
@EnableTransactionManagement
public class JpaThirdConfiguration {
@Bean(name = "dataSourceThird")
@ConfigurationProperties(prefix = "spring.datasource.third")
public DataSource dataSourceThird() {
return DataSourceBuilder.create().build();
}
@Autowired
@Qualifier("dataSourceThird")
private DataSource dataSource;
@Autowired
private JpaProperties jpaProperties;
@Autowired
private EntityManagerFactoryBuilder factoryBuilder;
@Bean(name = "thirdEntityManagerFactoryBean")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean() {
return factoryBuilder.dataSource(dataSource)
.properties(getVendorProperties())
.packages("com.proheng.gis.clickhouseEntity")
.persistenceUnit("thirdPersistenceUnit")
.build();
}
@Autowired
private HibernateProperties hibernateProperties;
private Map<String, Object> getVendorProperties() {
Map<String,String> properties=jpaProperties.getProperties();
properties.put("hibernate.dialect", "org.hibernate.dialect.MySQLDialect");
return hibernateProperties.determineHibernateProperties(
properties, new HibernateSettings());
}
@Bean(name = "thirdEntityManager")
public EntityManager entityManager() {
return entityManagerFactoryBean().getObject().createEntityManager();
}
@Bean(name = "thirdTransactionManager")
public JpaTransactionManager transactionManager() {
JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
jpaTransactionManager.setEntityManagerFactory(entityManagerFactoryBean().getObject());
return jpaTransactionManager;
}
}
|
注意
在执行分页查询的时候，Hibernate默认的使用的是ROW_NUMBER() OVER的方式，但是clickhouse不支持，就可能会出错，所以需要设置一个方言Dialect，我上面设置了MySQLDialect
6.使用
有些地方还需要自己实现@Service层，但是我这里的比较简单，就没有实现@Service层。
1
2
3
4
5
6
7
8
9
10
11
12
13
| @RestController
@RequestMapping(value = "/api")
public class PipeHeatAnalysis {
@Autowired
private PipeHeatAnalyseRepository pipeHeatAnalyseRepository;
@RequestMapping("/getPipeHeatAnalysis")
public List<PipeHeatAnalyse> getPipeHeatAnalysis(){
List<PipeHeatAnalyse> pipeHeatAnalyses=pipeHeatAnalyseRepository.findAll();
return pipeHeatAnalyses;
}
}
|
7.结果
