我是靠谱客的博主 繁荣故事，最近开发中收集的这篇文章主要介绍springboot2、HikariCP连接池的JdbcTemplate的clickhouse连接，觉得挺不错的，现在分享给大家，希望可以做个参考。
概述
pom.xml
<!-- clickhouse -->
<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.2.4</version>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
</dependency>cofing:
package com.iot.component.ch.config;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
/**
* Hikari连接池
*
* @author duhai
* @date 2021年12月3日
*/
@Component
public class ClickHouseHikariConfig {
@Value("${clickhouse.datasource.hikari.connection-timeout:60000}")
private volatile long connectionTimeout;
@Value("${clickhouse.datasource.hikari.idle-timeout:600000}")
private volatile long idleTimeout;
@Value("${clickhouse.datasource.hikari.max-lifetime:1800000}")
private volatile long maxLifetime;
@Value("${clickhouse.datasource.hikari.maximum-pool-size:12}")
private volatile int maxPoolSize;
@Value("${clickhouse.datasource.hikari.minimum-idle:4}")
private volatile int minIdle;
public long getConnectionTimeout() {
return connectionTimeout;
}
public void setConnectionTimeout(final long connectionTimeout) {
this.connectionTimeout = connectionTimeout;
}
public long getIdleTimeout() {
return idleTimeout;
}
public void setIdleTimeout(final long idleTimeout) {
this.idleTimeout = idleTimeout;
}
public long getMaxLifetime() {
return maxLifetime;
}
public void setMaxLifetime(final long maxLifetime) {
this.maxLifetime = maxLifetime;
}
public int getMaxPoolSize() {
return maxPoolSize;
}
public void setMaxPoolSize(final int maxPoolSize) {
this.maxPoolSize = maxPoolSize;
}
public int getMinIdle() {
return minIdle;
}
public void setMinIdle(final int minIdle) {
this.minIdle = minIdle;
}
}
package com.iot.component.ch.config;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import ru.yandex.clickhouse.BalancedClickhouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;
/**
* clickhouse数据源工厂类,返回的是BalancedClickhouseDataSource
*
* @author duhai
* @date 2021年12月3日
*/
@Component
public class ClickHouseDataSourceFactory {
/**
* clickhouse数据库连接地址，集群模式下使用逗号分隔
*/
@Value("${clickhouse.datasource.url:}")
private String url;
/**
* clickhouse数据库密码
*/
@Value("${clickhouse.datasource.password:}")
private String password;
/**
* clickhouse用户
*/
@Value("${clickhouse.datasource.user:}")
private String user;
/**
* 数据库实例名称
*/
@Value("${clickhouse.datasource.database:}")
private String database;
/**
* 超时时间
*/
@Value("${clickhouse.datasource.socketTimeOut:60000}")
private String socketTimeOut;
/**
* 时区
*/
@Value("${clickhouse.datasource.useTimeZone:Asia/Harbin}")
private String useTimeZone;
@Autowired
private ClickHouseHikariConfig clickHouseHikariConfig;
/**
* 获取
*
* @return
*/
public DataSource getClickHouseDataSource() {
//
ClickHouseProperties clickHouseProperties = new ClickHouseProperties();
clickHouseProperties.setSocketTimeout(Integer.parseInt(socketTimeOut));
clickHouseProperties.setUseServerTimeZone(false);
clickHouseProperties.setUseTimeZone(useTimeZone);
if (!StringUtils.isEmpty(password) && !StringUtils.isEmpty(user)) {
clickHouseProperties.setUser(user);
clickHouseProperties.setPassword(password);
}
//
return convertToHikari(new BalancedClickhouseDataSource(getCovertUrl(), clickHouseProperties));
}
/**
* 获取处理过的clickhouse连接
*
* @return
*/
private String getCovertUrl() {
if (!url.startsWith("jdbc:clickhouse:")) {
url = "jdbc:clickhouse://" + url + "/" + database;
}
return url;
}
/**
* 转换为Hikari源
*
* @param dataSource
* @return
*/
private HikariDataSource convertToHikari(final DataSource dataSource) {
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setConnectionTimeout(clickHouseHikariConfig.getConnectionTimeout());
hikariConfig.setIdleTimeout(clickHouseHikariConfig.getIdleTimeout());
hikariConfig.setMaximumPoolSize(clickHouseHikariConfig.getMaxPoolSize());
hikariConfig.setMinimumIdle(clickHouseHikariConfig.getMinIdle());
hikariConfig.setMaxLifetime(clickHouseHikariConfig.getMaxLifetime());
hikariConfig.setDataSource(dataSource);
//
return new HikariDataSource(hikariConfig);
}
}
package com.iot.component.ch.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
/**
* ClickHouseConfiguration
*
* @author duhai
* @date 2021年12月3日
*/
@Configuration
public class ClickHouseConfiguration {
@Autowired
private ClickHouseDataSourceFactory clickHouseDataSourceFactory;
/**
* ClickHouse数据源配置
*
* @return
*/
@Bean
public JdbcTemplate clickHouseTemplate() {
return new JdbcTemplate(clickHouseDataSourceFactory.getClickHouseDataSource());
}
}
使用JdbcClickHouseRepository：
package com.iot.component.ch.repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;
import com.iot.component.ch.constant.ClickHouseSqlConstant;
/**
* ClickHouse持久层基于JDBC驱动的实现
*
* @author duhai
* @date 2021年12月3日
*/
@Service
public class JdbcClickHouseRepository {
private final static Logger log = LoggerFactory.getLogger(JdbcClickHouseRepository.class);
@Autowired
@Qualifier(value = "clickHouseTemplate")
private JdbcTemplate clickHouseTemplate;
public boolean exists(final String tableName) {
String sql = MessageFormat.format(ClickHouseSqlConstant.SQL_EXISTS_TABLE, tableName);
Map<String, Object> exists = queryObject(sql);
return "1".equals(exists.get("result").toString());
}
public int update(final String sql) {
return clickHouseTemplate.update(sql);
}
public Map<String, Object> queryObject(final String sql) {
return queryObject(sql, Function.identity());
}
public Map<String, Object> queryObject(final String sql, final Function<Object, Object> resultConvert) {
log.info("query sql : {} ", sql);
return clickHouseTemplate.queryForObject(sql, new ColumnMapRowMapper() {
@Override
protected Object getColumnValue(final ResultSet rs, final int index) throws SQLException {
Object value = super.getColumnValue(rs, index);
return resultConvert.apply(value);
}
});
}
public List<Map<String, Object>> queryList(final String sql) {
return queryList(sql, Function.identity());
}
public List<Map<String, Object>> queryList(final String sql, final Function<Object, Object> resultConvert) {
log.info("query sql : rn{} ", sql);
return clickHouseTemplate.query(sql, new ColumnMapRowMapper() {
@Override
protected Object getColumnValue(final ResultSet rs, final int index) throws SQLException {
Object value = super.getColumnValue(rs, index);
return resultConvert.apply(value);
}
});
}
public <T> T executeQuery(final String sql, final ResultSetExtractor<T> rse) {
try {
return clickHouseTemplate.query(sql, rse);
} catch (Exception e) {
String message = e.getMessage();
if (message.indexOf("Client should retry") >= 0) {
try {
Thread.sleep(2000);
} catch (InterruptedException e1) {
e1.printStackTrace();
}
return clickHouseTemplate.query(sql, rse);
} else {
throw e;
}
}
}
public SqlRowSet queryRowSet(final String sql) {
return clickHouseTemplate.queryForRowSet(sql);
}
}
最后
以上就是繁荣故事为你收集整理的springboot2、HikariCP连接池的JdbcTemplate的clickhouse连接的全部内容，希望文章能够帮你解决springboot2、HikariCP连接池的JdbcTemplate的clickhouse连接所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复