ClickHouse数据库操作+SpringBoot+MybatisPlus+多数据源集成

@Dra

已于 2023-11-13 09:58:56 修改

阅读量1.9k

点赞数

于 2023-11-13 09:55:41 首次发布

本文链接：https://blog.csdn.net/qq_26224573/article/details/134370197

版权

ClickHouse数据库

简介

使用场景

1.绝大多数请求都是用于读访问的

2.数据需要以大批次（大于1000行）进行更新，而不是单行更新；或者根本没有更新操作

3.数据只是添加到数据库，没有必要修改

4.查询频率相对较低（通常每台服务器每秒查询数百次或更少）

安装

docker部署

参考：Docker部署clickhouse（超详细图文教程）

拉取镜像：

docker pull yandex/clickhouse-server
docker pull yandex/clickhouse-client

启动server端

1、可正常连接方法（推荐尝试）

docker run -d -p 8123:8123 -p 9000:9000 --name clickhouse yandex/clickhouse-server

2、官方方法

参考：利用docker安装启动ClickHouse

# 默认直接启动即可
docker run -d --name [启动之后的名称] --ulimit nofile=262144:262144 yandex/clickhouse-server
# 如果想指定目录启动，这里以clickhouse-test-server命令为例，可以随意写
mkdir /work/clickhouse/clickhouse-test-db       ## 创建数据文件目录
# 使用以下路径启动，在外只能访问clickhouse提供的默认9000端口，只能通过clickhouse-client连接server
docker run -d --name clickhouse-test-server --ulimit nofile=262144:262144 --volume=/work/clickhouse/clickhouse_test_db:/var/lib/clickhouse yandex/clickhouse-server

数据操作

注意：官方提供JDBC驱动包，可以使用SQL语句进行数据库操作

开始

-- 数据库创建
create database iot_zdly;
-- 数据表创建
create table iot_zdly.Iot_data(
    timestamp datetime64 comment '时间维度',
    id UInt64,
    cpu_tmp Int16 comment 'cpu温度',
    cpu_use_ratio UInt8 comment 'cpu使用率'
)engine = MergeTree -- 指定引擎
primary key id -- 指定主键
comment '物联网测试数据';
-- 查询表
select * from iot_zdly.Iot_data;
-- 插入数据
insert into iot_zdly.Iot_data VALUES ('2023-10-03 11:22:33.000', 12, 32, 22);
-- 删除数据
delete from iot_zdly.Iot_data where id = 12;
-- 修改数据
alter table Iot_data update cpu_tmp = 120, cpu_use_ratio = 0 where id = 1

其中，数据库表创建中指定的engine参考https://blog.csdn.net/qq_41106844/article/details/107067525，primary key id 为主键，并且必须创建，否则将会报错。

Java简易开发示例

参考：Java连接ClickHouse实现数据库基本增删查

pom文件：

<dependencies>
        <!--解决clickhouse lz4报错-->
        <dependency>
            <groupId>org.lz4</groupId>
            <artifactId>lz4-java</artifactId>
            <version>1.8.0</version>
        </dependency>
        <dependency>
            <groupId>com.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.4.6</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
</dependencies>

连接配置类：

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ConnectionObj {
    private String driverName; // 驱动器
    private String url;        // 连接地址
    private String user;       // 用户名
    private String password;   // 密码
}

接口类：

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

public interface Utils {
    /**
     * 基本SQL执行类
     * @param connectionObj 连接配置对象
     * @return 连接对象
     */
    Connection connection(ConnectionObj connectionObj);

    /**
     * 连接对象批量关闭
     * @param autoCloseable 连接对象集
     */
    void close(AutoCloseable... autoCloseable);

    /**
     * 基本操作SQL执行类
     * @param con 连接对象
     * @param sql sql命令
     * @param params 参数
     * @return 布尔
     */
    boolean baseSqlExecute(Connection con, String sql, String...params);
    boolean insert(Connection con, String sql, String...params);
    boolean delete(Connection con, String sql, String...params);
    /**
     * 基本查询SQL执行类
     * @param con 连接对象
     * @param sql sql命令
     * @param params 参数
     * @return 数据集合
     */
    List<Map<String, Object>> queryResultSet(Connection con, String sql, String...params);
}

clickhouse接口实现类：

import com.clickhouse.jdbc.ClickHousePreparedStatement;
import lombok.extern.slf4j.Slf4j;
import org.yaml.snakeyaml.util.EnumUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class ClickHouseUtils implements Utils{
    @Override
    public Connection connection(ConnectionObj connectionObj) {
        Connection con = null;
        try {
            // 获取驱动类
            Class.forName(connectionObj.getDriverName());
            // 建立连接获取连接对象
            con = DriverManager.getConnection(connectionObj.getUrl(), connectionObj.getUser(), connectionObj.getPassword());
        }catch (Exception e){
            log.error("Connection fail, please check your config：", e);
        }
        return con;
    }

    @Override
    public void close(AutoCloseable... autoCloseable) {
        for (AutoCloseable closeable : autoCloseable) {
            if (closeable != null){
                try {
                    closeable.close();
                }catch (Exception e){
                    log.error("Close fail：", e);
                }finally {
                    closeable = null;
                }
            }
        }
    }

    @Override
    public boolean baseSqlExecute(Connection con, String sql, String... params) {
        boolean is = false;
        ClickHousePreparedStatement pst = null;
        if (con == null){
            log.error("Connection is empty");
            return is;
        }
        try {
            // 获取控制台
            pst = (ClickHousePreparedStatement) con.prepareStatement(sql); // 需要做安全处理
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i+1, params[i]);
            }
            is = pst.execute();
        }catch (SQLException e){
            log.error("SQLException：", e);
        }finally {
//            close(pst, con);
        }
        return is;
    }

    @Override
    public boolean insert(Connection con, String sql, String... params) {
        return false;
    }

    @Override
    public boolean delete(Connection con, String sql, String... params) {
        return false;
    }

    @Override
    public List<Map<String, Object>> queryResultSet(Connection con, String sql, String... params) {
        List<Map<String, Object>> list = new ArrayList<>();
        ResultSet r = null;
        ClickHousePreparedStatement pst = null;
        if (con == null){
            log.error("Connection is empty");
            return null;
        }
        try {
            // 获取控制台
            pst = (ClickHousePreparedStatement) con.prepareStatement(sql); // 需要做安全处理
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i+1, params[i]);
            }
            r = pst.executeQuery();
            ResultSetMetaData metaData = r.getMetaData();
            while (r.next()){
                Map<String, Object> row = new HashMap<>();
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    row.put(metaData.getColumnName(i+1), r.getObject(metaData.getColumnName(i+1)));
                }
                list.add(row);
            }
        }catch (SQLException e){
            log.error("SQLException：", e);
        }finally {
//            close(r, pst, con);
        }
        return list;
    }
}

测试类：

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class ClickHouseTest {
    public static void main(String[] args) throws SQLException {
        String driverName = "com.clickhouse.jdbc.ClickHouseDriver";
        String url = "jdbc:clickhouse://localhost:8123/iot_zdly";
        String sqli="insert into Iot_data";
        String sql="select * from Iot_data";
        String[] paramsi={"2023-10-03 11:22:33.000", "1", "32", "22"};
        String[] params={};
        ConnectionObj connectionObj = new ConnectionObj(driverName, url, null, null);
        Utils clickHouseUtils = new ClickHouseUtils();
        Connection connection = clickHouseUtils.connection(connectionObj);
        // 插入数据
        clickHouseUtils.baseSqlExecute(connection, sqli, paramsi);
        // 查询数据
        List<Map<String, Object>> maps = clickHouseUtils.queryResultSet(connection, sql, params);
        maps.forEach(System.out::println);
    }
}

执行结果：

10:57:54.280 [main] DEBUG com.clickhouse.jdbc.ClickHouseDriver - ClickHouse Driver 0.4.6.0(JDBC: 4.2.0.0) registered
10:57:54.303 [main] DEBUG com.clickhouse.jdbc.ClickHouseDriver - Creating connection
10:57:54.371 [main] DEBUG com.clickhouse.client.ClickHouseNodeSelector - Checking [com.clickhouse.client.cli.ClickHouseCommandLineClient@35851384] against [HTTP]...
10:57:54.373 [main] DEBUG com.clickhouse.client.ClickHouseNodeSelector - Checking [com.clickhouse.client.http.ClickHouseHttpClient@256216b3] against [HTTP]...
10:57:54.379 [main] DEBUG com.clickhouse.jdbc.internal.ClickHouseConnectionImpl - Connecting to: ClickHouseNode [uri=http://localhost:8123/iot_zdly]@-532860298
10:57:54.380 [main] DEBUG com.clickhouse.client.AbstractClient - Connecting to: ClickHouseNode [uri=http://localhost:8123/iot_zdly]@-532860298
10:57:54.388 [main] DEBUG com.clickhouse.client.AbstractClient - Connection established: com.clickhouse.client.http.HttpUrlConnectionImpl@3cb5cdba
10:57:54.388 [main] DEBUG com.clickhouse.client.http.ClickHouseHttpClient - Query: select currentUser() user, timezone() timezone, version() version, toUInt8(ifnull((select value from system.settings where name = 'readonly'), '0')) as readonly, toInt8(ifnull((select value from system.settings where name = 'throw_on_unsupported_query_inside_transaction'), '-1')) as throw_on_unsupported_query_inside_transaction, (ifnull((select value from system.settings where name = 'wait_changes_become_visible_after_commit_mode'), '')) as wait_changes_become_visible_after_commit_mode,toInt8(ifnull((select value from system.settings where name = 'implicit_transaction'), '-1')) as implicit_transaction, toUInt64(ifnull((select value from system.settings where name = 'max_insert_block_size'), '0')) as max_insert_block_size, toInt8(ifnull((select value from system.settings where name = 'allow_experimental_lightweight_delete'), '-1')) as allow_experimental_lightweight_delete, (ifnull((select value from system.settings where name = 'custom_jdbc_config'), '')) as custom_jdbc_config FORMAT RowBinaryWithNamesAndTypes
10:57:54.798 [main] DEBUG com.clickhouse.client.AbstractClient - Connecting to: ClickHouseNode [uri=http://localhost:8123/iot_zdly]@-532860298
10:57:54.799 [main] DEBUG com.clickhouse.client.AbstractClient - Connection established: com.clickhouse.client.http.HttpUrlConnectionImpl@6cc4c815
10:57:54.799 [main] DEBUG com.clickhouse.client.http.ClickHouseHttpClient - Query: SELECT * FROM `Iot_data` WHERE 0
10:57:54.866 [main] DEBUG com.clickhouse.client.AbstractClient - Connecting to: ClickHouseNode [uri=http://localhost:8123/iot_zdly]@-532860298
10:57:54.866 [main] DEBUG com.clickhouse.client.AbstractClient - Connection established: com.clickhouse.client.http.HttpUrlConnectionImpl@1ce92674
10:57:54.866 [main] DEBUG com.clickhouse.client.http.ClickHouseHttpClient - Query: insert into Iot_data
 FORMAT RowBinary
10:57:54.889 [main] DEBUG com.clickhouse.client.AbstractClient - Connecting to: ClickHouseNode [uri=http://localhost:8123/iot_zdly]@-532860298
10:57:54.889 [main] DEBUG com.clickhouse.client.AbstractClient - Connection established: com.clickhouse.client.http.HttpUrlConnectionImpl@5f2108b5
10:57:54.890 [main] DEBUG com.clickhouse.client.http.ClickHouseHttpClient - Query: select * from Iot_data
{cpu_use_ratio=22, cpu_tmp=32, id=1, timestamp=2023-10-03T11:22:33}

SpringBoot集成

使用mybatis plus作为开发框架

参考：SpringBoot 集成 clickhouse + mybatis-plus 配置及使用问题说明（含建表语句、demo源码、测试说明）

pom文件：

<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>
        <!--解决clickhouse lz4报错-->
        <dependency>
            <groupId>org.lz4</groupId>
            <artifactId>lz4-java</artifactId>
            <version>1.8.0</version>
        </dependency>
        <dependency>
            <groupId>com.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.4.6</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>

    </dependencies>

application.yml:

server:
  port: 18999
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    clickhouse:
      driverClassName: com.clickhouse.jdbc.ClickHouseDriver
      url: jdbc:clickhouse://localhost:8123/iot_zdly
#      username:
#      password:
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 60

配置类：

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

import javax.sql.DataSource;

/**
 * @author dra
 */
@Configuration
public class ClickHouseDataSourceConfiguration {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.clickhouse")
    public DataSource clickDruidDataSource(){
        return new DruidDataSource();
    }

}

实体类：

import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDateTime;

/**
 * @author dra
 */
@TableName("Iot_data")
@Data
public class IotData {
    Long id;
    Integer cpuTmp;
    Short cpuUseRatio;
    // 时间相关属性必须使用@JsonFormat
    @JsonFormat(locale="zh", timezone="GMT+8", pattern="yyyy-MM-dd HH:mm:ss")
    LocalDateTime timestamp;
}

持久化层：

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.dra.iot.entity.click.IotData;

/**
 * @author dra
 */
public interface IotDataMapper extends BaseMapper<IotData> {
}

业务层接口：

import com.baomidou.mybatisplus.extension.service.IService;
import com.dra.iot.entity.click.IotData;

/**
 * @author dra
 */
public interface IotDataService extends IService<IotData> {
}

业务层实现类：

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.dra.iot.entity.click.IotData;
import com.dra.iot.mapper.IotDataMapper;
import com.dra.iot.serivce.IotDataService;
import org.springframework.stereotype.Service;

/**
 * @author dra
 */
@Service
public class IotDataServiceImpl extends ServiceImpl<IotDataMapper, IotData> implements IotDataService {
}

控制层：

import com.dra.iot.serivce.IotDataService;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @author dra
 */
@RestController
@RequestMapping("/iot")
@RequiredArgsConstructor
public class IotDataController {
    final IotDataService iotDataService;
    @GetMapping("/list")
    public Object list(){
        return iotDataService.list();
    }
}

响应结果：

[
  {
    "id": 1,
    "cpuTmp": 120,
    "cpuUseRatio": 0,
    "timestamp": "2023-10-03 11:22:33"
  }
]

Tips：MP对于clickhouse数据只有在读与插入操作是兼容的，修改与删除由于与MySQL的SQL语法不一致需要在Mapper中的xml单独编写。

多数据源集成

参考：MP官方-多数据源

配置：

server:
  port: 18999
spring:
  datasource:
    dynamic:
      primary: mysql # 默认数据源
      datasource:
        mysql:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/Iot_test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true
          username: xxx
          password: xxx
        clickhouse:
          type: com.alibaba.druid.pool.DruidDataSource
          driverClassName: com.clickhouse.jdbc.ClickHouseDriver
          url: jdbc:clickhouse://localhost:8123/iot_zdly
          # 本教程默认clickhouse无username与password 需要可以添加以下代码
          username: xxx
          password: xxx

实体类、持久化层Mapper接口、业务层接口写法都相同，该处不多赘述。

业务层实现类：

@Service
@DS("clickhouse")
public class IotDataServiceImpl extends ServiceImpl<IotDataMapper, IotData> implements IIotDataService {
}

当需要使用与clickhouse库相关的操作业务类上加上**@DS**注解去指定当前业务类的数据源。

当然，@DS可以放在方法上，并且其遵从就近原则。方法上注解优先于类上注解。

控制层：

@RestController
@RequestMapping("/mysql")
@RequiredArgsConstructor
public class MysqlTestController {

    final IMysqlTestService iMysqlTestService;

    @GetMapping("/list")
    public Object list(){
        return iMysqlTestService.list();
    }
}

响应结果：

# clickhosue
[
  {
    "id": 1,
    "cpuTmp": 120,
    "cpuUseRatio": 0,
    "timestamp": "2023-10-03 11:22:33"
  }
]
# mysql
[
  {
    "id": 1,
    "name": "test success"
  }
]