ClickHouse——SpringBoot集成clickhou

作者: 小波同学 | 来源:发表于2023-03-06 09:17 被阅读0次

ClickHouse复制表、分布式表机制与使用方法
Shopee x JuiceFS：ClickHouse 冷热数据
ClickHouse WindowFunnel 函数修改建议
Hangout with ClickHouse【转载】
SpringBoot 集成
ClickHouse 数据迁移[remote表、clickhou
SpringBoot 集成 Shiro
springboot 集成redis 和http请求
xrebel&jrebel
springboot集成mybaties详细步骤

建表

CREATE TABLE cs_user_info (
                              `id` UInt64,
                              `user_name` String,
                              `pass_word` String,
                              `phone` String,
                              `email` String,
                              `create_day` Date DEFAULT CAST(now(),'Date')
) ENGINE = MergeTree(create_day, intHash32(id), 8192)

INSERT INTO cs_user_info
(id,user_name,pass_word,phone,email)
VALUES
(1,'cicada','123','13923456789','cicada@com'),
(2,'smile','234','13922226789','smile@com'),
(3,'spring','345','13966666789','spring@com');

pom 依赖

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.6.1</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!--如果要用传统的xml或properties配置，则需要添加此依赖-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-configuration-processor</artifactId>
    </dependency>

    <dependency>
        <groupId>tk.mybatis</groupId>
        <artifactId>mapper-spring-boot-starter</artifactId>
        <version>2.1.5</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    
    <!--升级 druid驱动 1.1.10支持ClickHouse-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.13</version>
    </dependency>


    <!--ClickHouse 依赖-->
    <dependency>
        <groupId>ru.yandex.clickhouse</groupId>
        <artifactId>clickhouse-jdbc</artifactId>
        <version>0.1.53</version>
    </dependency>
 
    <!-- fastjson -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.70</version>
    </dependency>


    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>

</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>

        <plugin>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-maven-plugin</artifactId>
            <version>1.3.6</version>
            <configuration>
                <configurationFile>
                    ${basedir}/src/main/resources/generator/generatorConfig.xml
                </configurationFile>
                <overwrite>true</overwrite>
                <verbose>true</verbose>
            </configuration>
            <dependencies>
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>5.1.41</version>
                </dependency>
                <dependency>
                    <groupId>tk.mybatis</groupId>
                    <artifactId>mapper</artifactId>
                    <version>4.1.5</version>
                </dependency>
            </dependencies>
        </plugin>
    </plugins>
</build>

yaml配置

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


#mybatis的相关配置
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.yibo.ck.domain.entity
  
mapper:
  identity: MYSQL
  not-empty: false

jdbc配置

JdbcParamConfig

@Component
@ConfigurationProperties(prefix = "spring.datasource.click")
@Data
public class JdbcParamConfig {
    private String driverClassName ;
    private String url ;
    private Integer initialSize ;
    private Integer maxActive ;
    private Integer minIdle ;
    private Integer maxWait ;

}

DruidConfig

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

import javax.annotation.Resource;
import javax.sql.DataSource;

@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;
    }
}

实体定义 UserInfo

import lombok.Data;
import lombok.ToString;

@Data
@ToString
public class UserInfo {

    private Integer id;
    private String userName;
    private String passWord;
    private String phone;
    private String email;
    private String createDay;
}

mapper定义

UserInfoMapper

import org.apache.ibatis.annotations.Param;

import java.util.List;

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

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.yibo.ck.mapper.UserInfoMapper">
    <resultMap id="BaseResultMap" type="com.yibo.ck.domain.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.yibo.ck.domain.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>

service

UserInfoService

import com.yibo.doris.domain.entity.UserInfo;

import java.util.List;

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

实现

import com.yibo.ck.mapper.UserInfoMapper;
import com.yibo.ck.domain.entity.UserInfo;
import com.yibo.ck.service.UserInfoService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;

@Service
public class UserInfoServiceImpl implements UserInfoService {

    @Resource
    private UserInfoMapper userInfoMapper ;

    @Override
    public void saveData(UserInfo userInfo) {
        userInfoMapper.saveData(userInfo);
    }

    @Override
    public UserInfo selectById(Integer id) {
        return userInfoMapper.selectById(id);
    }

    @Override
    public List<UserInfo> selectList() {
        return userInfoMapper.selectList();
    }
}

test

import com.yibo.ck.domain.entity.UserInfo;
import com.yibo.ck.service.UserInfoService;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;
import java.util.List;

@SpringBootTest
@Slf4j
public class UserInfoServiceTest {

    @Resource
    UserInfoService userInfoService;

    @Test
    public void testSelect() {
        List<UserInfo> userInfos = userInfoService.selectList();
        log.info("userInfos={}", userInfos);
    }
}

参考：
https://blog.csdn.net/csdn_xpw/article/details/121981940

https://www.cnblogs.com/hanease/p/16414376.html

ClickHouse复制表、分布式表机制与使用方法
Replication & Sharding 在ClickHouse文集的第一篇文章中，笔者介绍了ClickHou...
Shopee x JuiceFS：ClickHouse 冷热数据
本文来自 shopee 技术团队摘要 Shopee ClickHouse 是一款基于开源数据库 ClickHou...
ClickHouse WindowFunnel 函数修改建议
背景 ClickHouse 作为性能卓越的OLAP引擎，有丰富的数据分析函数。公司增长分析侧使用 ClickHou...
Hangout with ClickHouse【转载】
ClickHouse作为我们的重要数据仓库，接收了大量用于统计、分析的日志。那么这些数据是怎么进到ClickHou...
SpringBoot 集成
SpringBoot 集成 redis SpringBoot集成mongodb SpringBoot集成Beetl...
ClickHouse 数据迁移[remote表、clickhou
一、 clickhouse-copier 1、what's this？ clickhouse-copier是官方出...
SpringBoot 集成 Shiro
SpringBoot 集成 Shiro 1、框架搭建创建SpringBoot项目，集成Shiro组件。打...
springboot 集成redis 和http请求
springboot 集成redis ，使用原生的jedis ，有点繁琐，本身springboot 已经集成了 ...
xrebel&jrebel
springboot集成swagger2 一、swagger简介二、 springboot集成三、 swagg...
springboot集成mybaties详细步骤
关于springboot集成mybaites，虽然springboot支持mybaties的集成，但是在官方文档...