clickhouse 二（springboot+mybatis配置clickhouse，实现插入查询）

系统管理员 2021-05-10 11:13 1589阅读 0赞

### 开发步骤 ###

*   *  1.添加maven依赖
     *  2、配属数据源
     *  3、参数配置
     *  4、Druid连接池配置
     *  5、Mapper.xml
     *  6、Mapper接口
     *  7.controller接口
     *  8.创建一个clickhouse表，然后插入几条数据测试一下
     *  9.测试

> ClickHouse是俄罗斯的Yandex公司于2016年开源的用于数据分析（OLAP）的面向列的数据库管理系统（DBMS），它也是一款 MPP（Massively Parallel Processing） 架构的列式存储数据库，它能够使用SQL查询实时生成分析数据报告。非常适合进行 OLAP 分析。但是也有不适合的场景，比如不擅长按行删除数据虽然支持，不支持数据修改操作，也不支持事务（想要快就要舍弃事务处理），所以笔者只演示插入和查询。

## 1.添加maven依赖 ##

<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>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>
            
            <dependency>
                <groupId>ru.yandex.clickhouse</groupId>
                <artifactId>clickhouse-jdbc</artifactId>
                <version>0.1.53</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>

## 2、配属数据源 ##

spring:
      datasource:
        type: com.alibaba.druid.pool.DruidDataSource
    clickhouse:
      driverClassName: ru.yandex.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://106.55.55.249:8123/default
      password: ck的密码
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 6000
    mybatis:
      mapper-locations: classpath:mapper/*Mapper.xml
      type-aliases-package: com.wyu.tt16clickhouse.entity
    server:
      port: 8090

## 3、参数配置 ##

import lombok.Data;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.stereotype.Component;
    
    @Data
    @Component
    public class ChParam { 
        private String driverClassName;
        private String url;
        private String password;
        private Integer initialSize;
        private Integer maxActive;
        private Integer minIdle;
        private Integer maxWait;
    
        @Value("${clickhouse.driverClassName}")
        public void setDriverClassName(String driverClassName) { 
            this.driverClassName = driverClassName;
        }
    
        @Value("${clickhouse.url}")
        public void setUrl(String url) { 
            this.url = url;
        }
    
        @Value("${clickhouse.password}")
        public void setPassword(String password) { 
            this.password = password;
        }
    
        @Value("${clickhouse.initialSize}")
        public void setInitialSize(Integer initialSize) { 
            this.initialSize = initialSize;
        }
    
        @Value("${clickhouse.maxActive}")
        public void setMaxActive(Integer maxActive) { 
            this.maxActive = maxActive;
        }
    
        @Value("${clickhouse.minIdle}")
        public void setMinIdle(Integer minIdle) { 
            this.minIdle = minIdle;
        }
    
        @Value("${clickhouse.maxWait}")
        public void setMaxWait(Integer maxWait) { 
            this.maxWait = maxWait;
        }
    }

## 4、Druid连接池配置 ##

import com.alibaba.druid.pool.DruidDataSource;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import javax.sql.DataSource;
    
    @Configuration
    public class DruidConfig { 
    
        @Autowired
        private ChParam chParam;
    
        @Bean
        public DataSource dataSource() { 
            DruidDataSource datasource = new DruidDataSource();
            datasource.setUrl(chParam.getUrl());
            datasource.setDriverClassName(chParam.getDriverClassName());
            datasource.setInitialSize(chParam.getInitialSize());
            datasource.setMinIdle(chParam.getMinIdle());
            datasource.setMaxActive(chParam.getMaxActive());
            datasource.setMaxWait(chParam.getMaxWait());
            datasource.setPassword(chParam.getPassword());
            return datasource;
        }
    }

## 5、Mapper.xml ##

<?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.wyu.tt16clickhouse.UserMapper">
    
        <select id="queryUser" resultType="com.wyu.tt16clickhouse.entity.User">
            select userId, appId, version, regTime from `default`.`user`
        </select>
    
        <insert id="insertUser" parameterType="com.wyu.tt16clickhouse.entity.User">
             INSERT INTO `default`.`user` (`userId`, `appId`, `version`, `regTime`)
             VALUES (#{userId}, #{appId}, #{version}, #{regTime})
        </insert>
    </mapper>

## 6、Mapper接口 ##

@Mapper
    public interface UserMapper { 
    
        List<User> queryUser();
    
        Integer insertUser(User user);
    
    }

## 7.controller接口 ##

@Slf4j
    @RestController
    public class UserController { 
    
        @Autowired
        private UserMapper userMapper;
    
        @RequestMapping("/queryUser")
        public Object query(){ 
            List userList = userMapper.queryUser();
            log.info(userList.toString());
            return userList.toString();
        }
    
        @RequestMapping("/insertUser")
        public Object insertUser(){ 
            User user = new User();
            user.setAppId("SS");
            user.setRegTime(new Date());
            user.setUserId(777744);
            user.setVersion("3.2");
            Integer flag = userMapper.insertUser(user);
            return flag;
        }
    }

## 8.创建一个clickhouse表，然后插入几条数据测试一下 ##

create table user
    (
        userId  Int32,
        appId   String,
        version String,
        regTime Date
    )
        engine = MergeTree PARTITION BY toYYYYMM(regTime) ORDER BY userId SETTINGS index_granularity = 8192;
        
    INSERT INTO default.user (userId, appId, version, regTime) VALUES (123457, 'RR', '3.6', '2020-01-07');
    INSERT INTO default.user (userId, appId, version, regTime) VALUES (43234, 'HH', '2.5', '2020-06-06');
    INSERT INTO default.user (userId, appId, version, regTime) VALUES (1234, 'TT', '2.4', '2020-07-24');
    INSERT INTO default.user (userId, appId, version, regTime) VALUES (12345, 'RR', '2.5', '2020-07-29');
    INSERT INTO default.user (userId, appId, version, regTime) VALUES (123456, 'TT', '2.1', '2020-07-09');
    INSERT INTO default.user (userId, appId, version, regTime) VALUES (234561, 'GG', '3.0', '2020-07-31');

## 9.测试 ##

![在这里插入图片描述][2020073116252141.png]  
想要这个demo源码的，可以在评论区留下邮箱

参考文章：[SpringBoot2 整合 ClickHouse数据库，实现高性能数据查询分析][SpringBoot2 _ ClickHouse]

[2020073116252141.png]: /images/20210505/2b1a85cde85b42ab8370de99ffd7c995.png
[SpringBoot2 _ ClickHouse]: https://juejin.im/post/6844903958255763463