Springboot查询Clickhouse

准备：请按照下面的连接安装ck的集群

https://www.cnblogs.com/ywjfx/p/14329605.html

# final关键字查询最新数据,不会merge，勉强实现了at least once 保证了数据的一致性
select * from user final ; 

建表语句

CREATE TABLE default.cluster3s1r_all
(
    `id` Int32,
    `website` String,
    `wechat` String,
    `FlightDate` Date,
    `Year` UInt16
)
ENGINE = Distributed('nx_clickhouse_3shards_1replicas', 'default', 'cluster3s1r_local', rand())

 

1、pom.xml配置文件添加依赖

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.9</version>
        </dependency>
        <!--ck依赖-->
        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.1.53</version>
        </dependency>

2、application.yml文件配置

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    click:
      driverClassName: ru.yandex.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://192.168.75.101:8123/default
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 6000
mybatis-plus:
  typeAliasesPackage: com.example.demo.clickhouse.entity
  mapperLocations: classpath:mapper/*.xml

3、自定义数据源DruidConfig.java

package com.example.demo.clickhouse.config;

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;

/**
 * @program: demo
 * @description:
 * @author: yang
 * @create: 2021-01-26 19:16
 */
@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;
    }
}

4、jdbc配置文件JdbcParamConfig.java

package com.example.demo.clickhouse.config;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * @program: demo
 * @description:
 * @author: yang
 * @create: 2021-01-26 19:16
 */
@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 ;


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

5、实体对象ClusterAll.java

package com.example.demo.clickhouse.entity;

/**
 * @program: demo
 * @description:
 * @author: yang
 * @create: 2021-01-27 10:09
 */
public class ClusterAll {

    private Integer id ;
    private String website;
    private String wechat;
    private String FlightDate;
    private String Year;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getWebsite() {
        return website;
    }

    public void setWebsite(String website) {
        this.website = website;
    }

    public String getWechat() {
        return wechat;
    }

    public void setWechat(String wechat) {
        this.wechat = wechat;
    }

    public String getFlightDate() {
        return FlightDate;
    }

    public void setFlightDate(String flightDate) {
        FlightDate = flightDate;
    }

    public String getYear() {
        return Year;
    }

    public void setYear(String year) {
        Year = year;
    }
}

6、接口文件ClusterAllMapper.java

package com.example.demo.clickhouse.mapper;

import com.example.demo.clickhouse.entity.ClusterAll;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @program: demo
 * @description:
 * @author: yang
 * @create: 2021-01-26 19:18
 */
@Mapper
public interface ClusterAllMapper {
    // 写入数据
    void saveData (ClusterAll clusterAll) ;
    // ID 查询
    ClusterAll selectById (@Param("id") Integer id) ;
    // 查询全部
    List<ClusterAll> selectList () ;
}

7、mapper配置文件ClusterAllMapper.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.example.demo.clickhouse.mapper.ClusterAllMapper">
    <resultMap id="BaseResultMap" type="com.example.demo.clickhouse.entity.ClusterAll">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="website" jdbcType="VARCHAR" property="website" />
        <result column="wechat" jdbcType="VARCHAR" property="wechat" />
        <result column="FlightDate" jdbcType="VARCHAR" property="FlightDate" />
        <result column="Year" jdbcType="VARCHAR" property="Year" />
    </resultMap>
    <sql id="Base_Column_List">
        id,website,wechat,FlightDate,`Year`
    </sql>
    <insert id="saveData" parameterType="com.example.demo.clickhouse.entity.ClusterAll" >
        INSERT INTO cluster3s1r_all
        ( id,website,wechat,FlightDate,`Year`)
        VALUES
        (#{id,jdbcType=INTEGER},#{website,jdbcType=VARCHAR},#{wechat,jdbcType=VARCHAR},
        #{FlightDate,jdbcType=VARCHAR},#{Year,jdbcType=VARCHAR})
    </insert>
    <select id="selectById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from cluster3s1r_all final
        where id = #{id,jdbcType=INTEGER}
    </select>
    <select id="selectList" resultMap="BaseResultMap" >
        select
        <include refid="Base_Column_List" />
        from cluster3s1r_all final
    </select>
</mapper>

8、请求接口类CKController.java

package com.example.demo.clickhouse;

import com.example.demo.clickhouse.entity.ClusterAll;
import com.example.demo.clickhouse.mapper.ClusterAllMapper;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

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

/**
 * @program: demo
 * @description:
 * @author: yang
 * @create: 2021-01-26 18:57
 */
@RestController
@RequestMapping("/ck")
public class CKController {

    @Resource
    private ClusterAllMapper clusterAllMapper;

    @RequestMapping("/test")
    public String test () {
        return "test";
    }

    @RequestMapping("/saveData")
    public String saveData (){
        ClusterAll clusterAll = new ClusterAll () ;
        clusterAll.setId(4);
        clusterAll.setWebsite("hushuo");
        clusterAll.setWechat("yyyy");
        clusterAll.setFlightDate("2021-1-27");
        clusterAll.setYear("2020");
        clusterAllMapper.saveData(clusterAll);
        return "sus";
    }
    @RequestMapping("/selectById")
    public ClusterAll selectById () {
        return clusterAllMapper.selectById(1) ;
    }
    @RequestMapping("/selectList")
    public List<ClusterAll> selectList () {
        return clusterAllMapper.selectList() ;
    }
}

9、clickhouse库表，请看集群安装与测试(使用的是测试的分片库即：default.cluster3s1r_all)

　　集群安装与测试：https://www.cnblogs.com/ywjfx/p/14329605.html

 

 

 

 10、接口访问