springboot 整合 clickhouse的實現範例

2022-02-09 10:01:08

前言

瞭解了clickhouse的基礎概念和相關的理論之後，本篇將通過範例程式碼演示如何在Java程式碼中操作clickhouse，主要涉及的內容包括：

使用JDBC的方式操作clickhouseclickhouse與springboot的整合使用

前置準備

1、clickhouse服務確保已開啟

2、為保證實驗效果，提前建立一張表，併為該表插入一些實驗資料

create table t_order01(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2),
 create_time Datetime
) engine =MergeTree
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id,sku_id);
 
insert into t_order01 values
(101,'sku_001',1000.00,'2021-12-01 12:00:00'),
(102,'sku_002',2000.00,'2021-12-01 11:00:00'),
(102,'sku_004',2500.00,'2021-12-01 12:00:00'),
(102,'sku_002',2000.00,'2021-12-01 13:00:00'),
(102,'sku_002',12000.00,'2021-12-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-12 12:00:00');

執行完畢sql之後，查詢下default資料庫下的表是否成功建立

使用jdbc方式操作clickhouse

1、引入clickhouse的jdbc依賴

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

2、範例程式碼

實大部分的操作和我們使用jdbc操作mysql的步驟類似，下面直接貼出程式碼，可以結合註釋進行參考使用

import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;
 
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
public class CreateTableTest {
 
    private static String username = "default";
    private static String password = "你的連線密碼";
    private static String address = "jdbc:clickhouse://clickhouse的連線IP地址:8123";
    private static String db = "連線資料庫名稱（預設資料庫：default）";
    private static int socketTimeout = 600000;
 
    public static void main(String[] args) throws Exception {
        //getConn();
        //queryTable();
        //createTable("");
        //insertOne();
        //dropTable();
        deleteById();
        //updateById();
    }
 
    /**
     * 查詢資料
     */
    public static void queryTable(){
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "select * from user_info";
        Connection connection = getConn();
        try {
            Statement statement = connection.createStatement();
            ResultSet rs  = statement.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            while(rs.next()){
                Map<String, Object> row = new HashMap<>();
                for(int i = 1; i <= rsmd.getColumnCount(); i++){
                    row.put(rsmd.getColumnName(i), rs.getObject(rsmd.getColumnName(i)));
                }
                list.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
 
        //在此可以根據實際需求將解析的資料封裝到物件中
        list.stream().forEach(item ->{
            Map<String, Object> rowData = item;
            System.out.println(rowData);
        });
        //System.out.println(list);
    }
 
    /**
     * 建立表
     * @throws Exception
     */
    public static void createTable(String tableSql) throws Exception{
        /*tableSql = "create table t_order02(n" +
                " id UInt32,n" +
                " sku_id String,n" +
                " total_amount Decimal(16,2),n" +
                " create_time Datetimen" +
                ") engine =MergeTreen" +
                " partition by toYYYYMMDD(create_time)n" +
                " primary key (id)n" +
                " order by (id,sku_id);";*/
        Connection connection = getConn();
        Statement statement = connection.createStatement();
        boolean execute = statement.execute(tableSql);
        if(execute){
            System.out.println(execute);
            System.out.println("建立表成功");
        }
    }
 
    /**
     * 刪除表
     * @throws Exception
     */
    public static void dropTable() throws Exception{
        Connection connection = getConn();
        Statement statement = connection.createStatement();
        statement.execute("drop table t_order01;");
        System.out.println("刪除表成功");
    }
 
    /**
     * 插入資料
     * 實際使用時候，插入的語句裡面的引數從外部傳入進去
     * @throws Exception
     */
    public static void insertOne() throws Exception{
        Connection connection = getConn();
        PreparedStatement pstmt = connection.prepareStatement("insert into t_order01 values('103', 'sku_004', '2500.00','2021-06-01 12:00:00')");
        pstmt.execute();
        System.out.println("insert success");
    }
 
    /**
     * 刪除資料
     * 實際使用時候，刪除的語句裡面的引數從外部傳入進去
     */
    public static void deleteById() throws Exception{
        Connection connection = getConn();
        //sku_id ='sku_001'
        PreparedStatement pstmt = connection.prepareStatement("alter table t_order01 delete where sku_id = 'sku_002';");
        pstmt.execute();
        System.out.println("delete success");
    }
 
    /**
     * 修改資料
     * 實際使用時候，修改的語句裡面的引數從外部傳入進去
     */
    public static void updateById() throws Exception{
        Connection connection = getConn();
        PreparedStatement pstmt = connection.prepareStatement("alter table t_order01 update total_amount=toDecimal32(2000.00,2) where id = '102'");
        pstmt.execute();
        System.out.println("update success");
    }
 
    public static Connection getConn() {
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setUser(username);
        properties.setPassword(password);
        properties.setDatabase(db);
        properties.setSocketTimeout(socketTimeout);
        ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(address, properties);
        ClickHouseConnection conn = null;
        try {
            conn = clickHouseDataSource.getConnection();
            System.out.println(conn);
            System.out.println("連線成功");
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

3、測試，選擇查詢和刪除一條資料為例做測試

查詢功能測試結果，見下面的控制檯資料列印

刪除功能測試結果，刪除 "sku_id = sku_002 "的資料，執行方法之後，見下面的控制檯資料列印

執行方法之後，再次查詢該表

與springboot的整合

在實際開發過程中，更多是與框架整合在一起進行使用，比如很多專案中都使用springboot進行開發，下面演示如何在springboot中使用clickhouse

前置準備

確保clickhouse服務正常可用

1、準備一張表，以及表中插入一些實驗資料

CREATE TABLE user_info (
  `id` UInt64,
  `user_name` String,
  `pass_word` String,
  `phone` String,
  `create_day` Date DEFAULT CAST(now(),'Date')
)ENGINE = MergeTree
primary key (id)
order by (id);
 
 
INSERT INTO user_info
  (id,user_name,pass_word,phone)
VALUES
  (1,'xiaowang','123456','13325511231'),
  (2,'xiaoma','123456','13825511231'),
  (3,'xiaozhao','123456','18925511231');

2、執行完畢上面的建表後，查詢下表資料

程式碼完整整合步驟

1、匯入完整依賴

<dependencies>
 
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
 
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-aop</artifactId>
    </dependency>
 
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
    </dependency>
 
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>1.3.2</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.38</version>
    </dependency>
 
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.13</version>
    </dependency>
 
    <dependency>
        <groupId>commons-lang</groupId>
        <artifactId>commons-lang</artifactId>
        <version>2.6</version>
    </dependency>
 
    <!-- clickHouse資料庫 -->
    <dependency>
        <groupId>ru.yandex.clickhouse</groupId>
        <artifactId>clickhouse-jdbc</artifactId>
        <version>0.1.53</version>
    </dependency>
 
</dependencies>

2、基礎組態檔

server:
  port: 7010
 
  # mybatis 設定
mybatis:
  type-aliases-package: com.congge.entity
  mapper-locations: classpath:/mapper/*.xml
 
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    click:
      driverClassName: ru.yandex.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://IP地址:8123/default
      username: default
      password: 123456
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 6000

3、使用一個設定類，關聯第二步中的click設定屬性

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
 
@Component
@ConfigurationProperties(prefix = "spring.datasource.click")
public class ConnectionParamConfig {
 
    private String driverClassName ;
    private String url ;
    private Integer initialSize ;
    private Integer maxActive ;
    private Integer minIdle ;
    private Integer maxWait ;
 
    private String username;
    private String password;
 
    public String getDriverClassName() {
        return driverClassName;
    }
    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }
    public String getUrl() {
        return url;
    }
    public void setUrl(String url) {
        this.url = url;
    }
    public Integer getInitialSize() {
        return initialSize;
    }
    public void setInitialSize(Integer initialSize) {
        this.initialSize = initialSize;
    }
    public Integer getMaxActive() {
        return maxActive;
    }
    public void setMaxActive(Integer maxActive) {
        this.maxActive = maxActive;
    }
    public Integer getMinIdle() {
        return minIdle;
    }
    public void setMinIdle(Integer minIdle) {
        this.minIdle = minIdle;
    }
    public Integer getMaxWait() {
        return maxWait;
    }
    public void setMaxWait(Integer maxWait) {
        this.maxWait = maxWait;
    }
 
    public String getUsername() {
        return username;
    }
 
    public void setUsername(String username) {
        this.username = username;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }
}

4、重寫datasource的設定，使用自定義的clickhouse的屬性設定

import javax.annotation.Resource;
 
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
 
import javax.sql.DataSource;
 
@Configuration
public class DruidConfig {
 
    @Resource
    private ConnectionParamConfig jdbcParamConfig;
 
    /**
     * 重寫 DataSource
     * @return
     */
    @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());
        datasource.setUsername(jdbcParamConfig.getUsername());
        datasource.setPassword(jdbcParamConfig.getPassword());
        return datasource;
    }
 
}

5、提供一個介面和mybatis的查詢xml檔案

public interface UserInfoMapper {
    void saveData (UserInfo userInfo) ;
    UserInfo selectById (@Param("id") Integer id) ;
    List<UserInfo> selectList () ;
}
 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.congge.mapper.UserInfoMapper">
    <resultMap id="BaseResultMap" type="com.congge.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="create_day" jdbcType="VARCHAR" property="createDay" />
    </resultMap>
 
    <sql id="Base_Column_List">
        id,user_name,pass_word,phone,create_day
    </sql>
 
    <insert id="saveData" parameterType="com.congge.entity.UserInfo" >
        INSERT INTO user_info
        (id,user_name,pass_word,phone,create_day)
        VALUES
        (#{id,jdbcType=INTEGER},#{userName,jdbcType=VARCHAR},#{passWord,jdbcType=VARCHAR},
        #{phone,jdbcType=VARCHAR},#{createDay,jdbcType=VARCHAR})
    </insert>
 
    <select id="selectById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from user_info
        where id = #{id,jdbcType=INTEGER}
    </select>
 
    <select id="selectList" resultMap="BaseResultMap" >
        select
        <include refid="Base_Column_List" />
        from user_info
    </select>
 
</mapper>

6、介面和服務實現類

@RestController
public class UserInfoController {
 
    @Resource
    private UserInfoService userInfoService ;
 
    //localhost:7010/saveData
    @GetMapping("/saveData")
    public String saveData (){
        UserInfo userInfo = new UserInfo () ;
        userInfo.setId(4);
        userInfo.setUserName("xiaolin");
        userInfo.setPassWord("54321");
        userInfo.setPhone("18500909876");
        userInfo.setCreateDay("2022-02-06");
        userInfoService.saveData(userInfo);
        return "success";
    }
 
    //localhost:7010/getById?id=1
    @GetMapping("/getById")
    public UserInfo getById (int id) {
        return userInfoService.selectById(id) ;
    }
 
    @GetMapping("/getList")
    public List<UserInfo> getList () {
        return userInfoService.selectList() ;
    }
 
}

7、服務實現類，UserInfoService

@Service
public class UserInfoService {
 
    @Resource
    private UserInfoMapper userInfoMapper ;
 
    public void saveData(UserInfo userInfo) {
        userInfoMapper.saveData(userInfo);
    }
 
    public UserInfo selectById(Integer id) {
        return userInfoMapper.selectById(id);
    }
 
    public List<UserInfo> selectList() {
        return userInfoMapper.selectList();
    }
 
}

8、啟動類

@SpringBootApplication
@MapperScan(basePackages = {"com.congge.mapper"})
public class App {
 
    public static void main(String[] args) {
        SpringApplication.run(App.class,args);
    }
 
}

9、功能介面測試

查詢測試，呼叫介面：localhost:7010/getById?id=1