SpringBoot配置Clickhouse1 原生jdbc方式2 springboot mybaties clickhouse方式

54 阅读 0 评论 36 点赞

我是靠谱客的博主冷静过客，最近开发中收集的这篇文章主要介绍SpringBoot配置Clickhouse1 原生jdbc方式2 springboot mybaties clickhouse方式，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1 原生jdbc方式

一、加入clickhouse jar包依赖

<dependency>
        <groupId>ru.yandex.clickhouse</groupId>
        <artifactId>clickhouse-jdbc</artifactId>
        <version>0.1.53</version>
</dependency>

二、配置Clickhouse数据库连接属性配置文件，clickhouse默认没有密码

spring:
  datasource:
    clickhouse:
      address: jdbc:clickhouse://127.0.0.1:8123
      username: default
      password:
      db: system
      socketTimeout: 600000

三、编写Clickhouse数据库连接操作工具类

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;
 
import java.sql.*;
import java.util.*;
 
@Slf4j
@Component
public class ClickHouseConfig {
    private static String clickhouseAddress;
 
    private static String clickhouseUsername;
 
    private static String clickhousePassword;
 
    private static String clickhouseDB;
 
    private static Integer clickhouseSocketTimeout;
 
    @Value("${spring.datasource.clickhouse.address}")
    public  void setClickhouseAddress(String address) {
        ClickHouseConfig.clickhouseAddress = address;
    }
    @Value("${spring.datasource.clickhouse.username}")
    public  void setClickhouseUsername(String username) {
        ClickHouseConfig.clickhouseUsername = username;
    }
    @Value("${spring.datasource.clickhouse.password}")
    public  void setClickhousePassword(String password) {
        ClickHouseConfig.clickhousePassword = password;
    }
    @Value("${spring.datasource.clickhouse.db}")
    public  void setClickhouseDB(String db) {
        ClickHouseConfig.clickhouseDB = db;
    }
    @Value("${spring.datasource.clickhouse.socketTimeout}")
    public  void setClickhouseSocketTimeout(Integer socketTimeout) {
        ClickHouseConfig.clickhouseSocketTimeout = socketTimeout;
    }
 
    public static Connection getConn() {
 
        ClickHouseConnection conn = null;
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setUser(clickhouseUsername);
        properties.setPassword(clickhousePassword);
        properties.setDatabase(clickhouseDB);
        properties.setSocketTimeout(clickhouseSocketTimeout);
        ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(clickhouseAddress,properties);
        try {
            conn = clickHouseDataSource.getConnection();
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
        }
 
        return null;
    }
 
    public static List<Map<String,String>> exeSql(String sql){
        log.info("cliockhouse 执行sql：" + sql);
        Connection connection = getConn();
        try {
            Statement statement = connection.createStatement();
            ResultSet results = statement.executeQuery(sql);
            ResultSetMetaData rsmd = results.getMetaData();
            List<Map<String,String>> list = new ArrayList<>();
            while(results.next()){
                Map<String,String> row = new HashMap<>();
                for(int i = 1;i<=rsmd.getColumnCount();i++){
                    row.put(rsmd.getColumnName(i),results.getString(rsmd.getColumnName(i)));
                }
                list.add(row);
            }
 
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

四、是用测试方法执行sql查询数据

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
 
import java.util.List;
import java.util.Map;
 
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class ClickHouseConfigTest {
 
    @Test
    public void exeSql() {
            log.info("===========测试开始============");
            String sql="select cluster,shard_num from clusters";
            List<Map<String,String>> result= ClickHouseConfig.exeSql(sql);
            log.info("===========查询技术============");
            log.info("clickhouse查询结果为：{}",result);
    }
}

2 springboot mybaties clickhouse方式

该案例基于：Druid连接池和mybatis进行整合。Druid 1.1.10 版本 SQL Parser对clickhouse的开始提供支持。

1、核心依赖

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.1.53</version>
</dependency>

2、配属数据源

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    click:
      driverClassName: ru.yandex.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://127.0.0.1:8123/default
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 6000

3、Druid连接池配置

@Configuration
public class DruidConfig {
    @Resource
    private JdbcParamConfig jdbcParamConfig ;
    @Bean
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(jdbcParamConfig.getUrl());
        datasource.setDriverClassName(jdbcParamConfig.getDriverClassName());
        datasource.setInitialSize(jdbcParamConfig.getInitialSize());
        datasource.setMinIdle(jdbcParamConfig.getMinIdle());
        datasource.setMaxActive(jdbcParamConfig.getMaxActive());
        datasource.setMaxWait(jdbcParamConfig.getMaxWait());
        return datasource;
    }
}

基于以上的配置可以获得DataSource数据源对象，因此我们可以将ClickHouse数据库以Bean的形式注入，调用Spring的JdbcTemplate模板来管理和配置ClickHouse数据源，这种方式能够提升开发效率，并且更具备灵活性。具体实现方式如下：

@Configuration
public class DruidConfig {
    @Resource
    private JdbcParamConfig jdbcParamConfig ;
    
    @Bean(name="clickHouseTemplate")
    public JdbcTemplate dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(jdbcParamConfig.getUrl());
        datasource.setDriverClassName(jdbcParamConfig.getDriverClassName());
        datasource.setInitialSize(jdbcParamConfig.getInitialSize());
        datasource.setMinIdle(jdbcParamConfig.getMinIdle());
        datasource.setMaxActive(jdbcParamConfig.getMaxActive());
        datasource.setMaxWait(jdbcParamConfig.getMaxWait());
        return new JdbcTemplate(datasource);
    }
}

或者

@Bean(name="clickHouseTemplate")
public JdbcTemplate clickHouseTemplate(){
    ClickHouseProperties clickHouseProperties = new ClickHouseProperties();
    clickHouseProperties.setSockeTimeout(5*60*1000);
    ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(url,clickHouseProperties);
    return new JdbcTemplate(clickHouseDataSource );
}

在使用的时候，只需要按照如下方式注入即可正常访问ClickHouse数据库：

@Autowired
@Qualifier("clickHouseTemplate")
private JdbcTemplate clickHouseTemplate;

4、参数配置类

@Component
@ConfigurationProperties(prefix = "spring.datasource.click")
public class JdbcParamConfig {
    private String driverClassName ;
    private String url ;
    private Integer initialSize ;
    private Integer maxActive ;
    private Integer minIdle ;
    private Integer maxWait ;
    // 省略 GET 和 SET
}

这样整合代码就完成了。

5、Mapper接口

public interface UserInfoMapper {
    // 写入数据
    void saveData (UserInfo userInfo) ;
    // ID 查询
    UserInfo selectById (@Param("id") Integer id) ;
    // 查询全部
    List<UserInfo> selectList () ;
}

这里就演示简单的三个接口。

6、Mapper.xml文件

<mapper namespace="com.click.house.mapper.UserInfoMapper">
    <resultMap id="BaseResultMap" type="com.click.house.entity.UserInfo">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="user_name" jdbcType="VARCHAR" property="userName" />
        <result column="pass_word" jdbcType="VARCHAR" property="passWord" />
        <result column="phone" jdbcType="VARCHAR" property="phone" />
        <result column="email" jdbcType="VARCHAR" property="email" />
        <result column="create_day" jdbcType="VARCHAR" property="createDay" />
    </resultMap>
    <sql id="Base_Column_List">
        id,user_name,pass_word,phone,email,create_day
    </sql>
    <insert id="saveData" parameterType="com.click.house.entity.UserInfo" >
        INSERT INTO cs_user_info
        (id,user_name,pass_word,phone,email,create_day)
        VALUES
        (#{id,jdbcType=INTEGER},#{userName,jdbcType=VARCHAR},#{passWord,jdbcType=VARCHAR},
        #{phone,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{createDay,jdbcType=VARCHAR})
    </insert>
    <select id="selectById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from cs_user_info
        where id = #{id,jdbcType=INTEGER}
    </select>
    <select id="selectList" resultMap="BaseResultMap" >
        select
        <include refid="Base_Column_List" />
        from cs_user_info
    </select>
</mapper>

这里 create_day 是以字符串的方式在转换，这里需要注意下。

7、控制层接口

@RestController
@RequestMapping("/user")
public class UserInfoController {
    @Resource
    private UserInfoService userInfoService ;
    @RequestMapping("/saveData")
    public String saveData (){
        UserInfo userInfo = new UserInfo () ;
        userInfo.setId(4);
        userInfo.setUserName("winter");
        userInfo.setPassWord("567");
        userInfo.setPhone("13977776789");
        userInfo.setEmail("winter");
        userInfo.setCreateDay("2020-02-20");
        userInfoService.saveData(userInfo);
        return "sus";
    }
    @RequestMapping("/selectById")
    public UserInfo selectById () {
        return userInfoService.selectById(1) ;
    }
    @RequestMapping("/selectList")
    public List<UserInfo> selectList () {
        return userInfoService.selectList() ;
    }
}