clickhouse数据库java连接使用和设置超时时间

maven依赖引入

• 本次使用alibaba的druid连接池
• 使用maven引入依赖，在pom.xml里添加

			<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.2.5</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.2.5</version>
            </dependency>

            <dependency>
                <groupId>com.sagframe</groupId>
                <artifactId>sagacity-sqltoy-starter</artifactId>
                <version>4.18.8</version>
            </dependency>
            <dependency>
                <groupId>com.sagframe</groupId>
                <artifactId>sagacity-sqltoy</artifactId>
                <version>4.18.8</version>
            </dependency>
            <dependency>
                <groupId>com.sagframe</groupId>
                <artifactId>sqltoy-quickvo</artifactId>
                <version>4.18.8</version>
            </dependency>
            <dependency>
                <groupId>ru.yandex.clickhouse</groupId>
                <artifactId>clickhouse-jdbc</artifactId>
                <version>0.2.6</version>
            </dependency>

spring配置文件

• 直接使用spring配置

spring:
  type: com.alibaba.druid.pool.DruidDataSource
  datasource:
    click-house:
      name: clickhouseDataSource
      driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://192.168.1.231:8123 # clickhouse 地址
      username: default # clickhouse 用户名
      password: 123456  # clickhouse 密码，建议使用复杂密码
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 600

java代码创建bean和使用

• 直接构造bean，使用时注入

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.Properties;

/**
 * @ClassName ClickHouseDataSourceConfiguration
 * @Description ClickHouse连接配置 bean
 * @Date 2021/3/29 14:42
 * @Author 
 **/
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.click-house")
public class ClickHouseDataSourceConfiguration {

    private String driverClassName ;
    private String url ;
    private Integer initialSize ;
    private Integer maxActive ;
    private Integer minIdle ;
    private Integer maxWait ;
    private String username;
    private String password;
    private String timeout;

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public void setInitialSize(Integer initialSize) {
        this.initialSize = initialSize;
    }

    public void setMaxActive(Integer maxActive) {
        this.maxActive = maxActive;
    }

    public void setMinIdle(Integer minIdle) {
        this.minIdle = minIdle;
    }

    public void setMaxWait(Integer maxWait) {
        this.maxWait = maxWait;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setTimeout(String timeout) {
        this.timeout = timeout;
    }
    

    @Bean(name = "clickHouseDataSource")
    @ConditionalOnProperty(prefix = "spring.datasource.click-house",name = "url")
    public DataSource clickHouseDataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(url);
        datasource.setDriverClassName(driverClassName);
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setUsername(username);
        datasource.setPassword(password);
        Properties properties = new Properties();
        properties.put("socket_timeout", timeout);
        datasource.setConnectProperties(properties);
        return datasource;
    }
}

• 使用时，注入即可，使用结束注意关闭

	@Autowired
    @Qualifier("clickHouseDataSource")
    private DataSource clickHouseDataSource;

    protected DataSource getDataSource() {
        return clickHouseDataSource;
    }


    protected Connection getConnection() {
        Connection conn = null;
        try {
            conn = clickHouseDataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
	
	// 后续方法使用时  Connection connection = getConnection();
	@Override
    public Long getPageSourceCount(VisitRecordQueryVO visitRecordQueryVO) {
        Long count = 0L;
        StringBuffer sql;
        
        sql = new StringBuffer("select count(1) as value from visit_record where 1=1 ");
        // 中间省略        
        Connection connection = getConnection();
        PreparedStatement pst = null;
        try {
            logger.info("sql = " + sql.toString());
            pst = connection.prepareStatement(sql.toString());
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                count = rs.getLong("value");
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    //ignore
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    //ignore
                }
            }
        }
        return count;
    }

超时设置

• 主要是在spring配置里，增加timeout参数
• 在构造DruidDataSource的bean时，将配置加进去

		Properties properties = new Properties();
        properties.put("socket_timeout", timeout);
        datasource.setConnectProperties(properties);