Springboot集成ClickHouse及应用场景分析

clickhou应用场景：

1.绝大多数请求都是用于读访问的
2.数据需要以大批次（大于1000行）进行更新，而不是单行更新；或者根本没有更新操作
3.数据只是添加到数据库，没有必要修改
4.读取数据时，会从数据库中提取出大量的行，但只用到一小部分列
5.表很“宽”，即表中包含大量的列
6.查询频率相对较低（通常每台服务器每秒查询数百次或更少）
7.对于简单查询，允许大约50毫秒的延迟
8.列的值是比较小的数值和短字符串（例如，每个url只有60个字节）
9.在处理单个查询时需要高吞吐量（每台服务器每秒高达数十亿行）
10.不需要事务
11.数据一致性要求较低
12.每次查询中只会查询一个大表。除了一个大表，其余都是小表
13.查询结果显著小于数据源。即数据有过滤或聚合。返回结果不超过单个服务器内存大小

行式存储对比列式存储：

(1)、行式数据

(2)、列式数据

(3)、对比分析

分析类查询，通常只需要读取表的一小部分列。在列式数据库中可以只读取需要的数据。数据总是打包成批量读取的，所以压缩是非常容易的。同时数据按列分别存储这也更容易压缩。这进一步降低了i/o的体积。由于i/o的降低，这将帮助更多的数据被系统缓存。

整合springboot：

核心依赖（mybatis plus做持久层，druid做数据源）：

<dependencies>    <!--clickhou-->    <dependency>        <groupid>ru.yandex.clickhou</groupid>        <artifactid>clickhou-jdbc</artifactid>        <version>0.3.1-patch</version>    </dependency>    <!-- https://mvnrepository.com/artifact/光学工程com.alibaba/druid -->    <dependency>        <groupid>com.alibaba</groupid>        <artifactid>druid</artifactid>        <version>1.2.6</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.4.3.1</version>    </dependency></dependencies>

配置yml文件：

spring:  datasource:    type: com.alibaba.druid.pool.druiddatasource    click:      driverclassname: ru.yandex.clickhou.clickhoudriver      url: jdbc:clickhou://127.0.0.1:8123/dbname      urname: urname      password: 123456      initialsize: 10      maxactive: 100      minidle: 10      maxwait: 6000mybatis-plus:  mapper-locations: classpath*:mapper/*.xml  configuration:    log-impl: org.apache.ibatis.logging.stdout.stdoutimpl    map-underscore-to-camel-ca: true    cache-enabled:烛之武退秦师原文 true    lazy-loading-enabled: true    multiple-result-ts-enabled: true    u-generated-keys: true    default-statement-timeout: 60    default-fetch-size: 100  type-alias-package: com.example.tonghp.entity

clickhou与druid连接池配置类：

参数配置：

package com.example.tonghp.config;import lombok.data;import org.springframework.boot.context.properties.configurationproperties;import org.springframework.stereotype.component;/** * @author: tonghp * @create: 2021/07/26 16:23 */@data@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 ;    private string urname;    pr元旦的旦是什么意思 ivate string password;    // 省略 get 和 t}

druid连接池配置

package com.example.tonghp.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;import javax.swing.*;/** * @author: tonghp * @create: 2021/07/26 16:22 *唐哀帝/@configurationpublic class druidconfig {    @resource    private jdbcparamconfig jdbcparamconfig ;    @bean    public datasource datasource() {        druiddatasource datasource = new druiddatasource();        datasource.turl(jdbcparamconfig.geturl());        datasource.tdriverclassname(jdbcparamconfig.getdriverclassname());        datasource.tinitialsize(jdbcparamconfig.getinitialsize());        datasource.tminidle(jdbcparamconfig.getminidle());        datasource.tmaxactive(jdbcparamconfig.getmaxactive());        datasource.tmaxwait(jdbcparamconfig.getmaxwait());        datasource.turname(jdbcparamconfig.geturname());        datasource.tpassword(jdbcparamconfig.getpassword());        return datasource;    }}

接下来配置实体类，mapper，rvice，controlle以及mapper.xml。与mybatisplus操作mysql一样的思路。

package com.example.tonghp.entity;import lombok.data;import java.io.rializable;/** * @author: tonghp * @create: 2021/07/26 16:31 */@datapublic class urinfo implements rializable {    private static final long rialversionuid = 1l;    private int id;    private string urname;    private string password;    private string phone;    private string email;    private string createday;}

package com.example.tonghp.mapper;import com.baomidou.mybatisplus.core.mapper.bamapper;import com.example.tonghp.entity.urinfo;import org.apache.ibatis.annotations.mapper;import org.apache.ibatis.annotations.param;import org.springframework.stereotype.repository;import java.util.list;/** * @author: tonghp * @create: 2021/07/26 16:32 */@repositorypublic interface urinfomapper extends bamapper<urinfo> {    // 写入数据    void savedata (urinfo urinfo) ;    // id 查询    urinfo lectbyid (@param("id") integer id) ;    // 查询全部    list<urinfo> lectlist () ;}

urinfomapper.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.tonghp.mapper.urinfomapper">    <resultmap id="baresultmap" type="com.example.tonghp.entity.urinfo">        <id column="id" jdbctype="integer" property="id" />        <result column="ur_name" jdbctype="varchar" property="urname" />        <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="ba_column_list">        id,ur_name,pass_word,phone,email,create_day    </sql>    <inrt id="savedata" parametertype="com.example.tonghp.entity.urinfo" >        inrt into cs_ur_info        (id,ur_name,pass_word,phone,email,create_day)        values        (#{id,jdbctype=integer},#{urname,jdbctype=varchar},#{password,jdbctype=varchar},        #{phone,jdbctype=varchar},#{email,jdbctype=varchar},#{createday,jdbctype=varchar})    </inrt>    <lect id="lectbyid" parametertype="java.lang.integer" resultmap="baresultmap">        lect        <include refid="ba_column_list" />        from cs_ur_info        where id = #{id,jdbctype=integer}    </lect>    <lect id="lectlist" resultmap="baresultmap" >        lect        <include refid="ba_column_list" />        from cs_ur_info    </lect></mapper>

rvice

package com.example.tonghp.rvice;import com.baomidou.mybatisplus.extension.rvice.irvice;import com.example.tonghp.entity.urinfo;import org.apache.ibatis.annotations.param;import org.springframework.stereotype.rvice;import java.util.list;/** * @author: tonghp * @create: 2021/07/26 16:46 */public interface urinforvice extends irvice<urinfo> {    // 写入数据    void savedata (urinfo urinfo) ;    // id 查询    urinfo lectbyid (@param("id") integer id) ;    // 查询全部    list<urinfo> lectlist () ;}

rviceimpl

package com.example.tonghp.rvice.impl;import com.baomidou.mybatisplus.extension.rvice.impl.rviceimpl;import com.example.tonghp.entity.urinfo;import com.example.tonghp.mapper.urinfomapper;import com.example.tonghp.rvice.urinforvice;import org.springframework.beans.factory.annotation.autowired;import org.springframework.stereotype.rvice;import java.util.list;/** * @author: tonghp * @create: 2021/07/26 16:48 */@rvicepublic class urinforviceimpl extends rviceimpl<urinfomapper, urinfo> implements urinforvice {    @autowired    urinfomapper urinfomapper;    @override    public void savedata(urinfo urinfo) {        urinfomapper.savedata(urinfo);    }    @override    public urinfo lectbyid(integer id) {        return urinfomapper.lectbyid(id);    }    @override    public list<urinfo> lectlist() {        return urinfomapper.lectlist();    }}

controller

package com.example.tonghp.controller;import com.example.tonghp.entity.urinfo;import com.example.tonghp.rvice.urinforvice;import org.springframework.beans.factory.annotation.autowired;import org.springframework.web.bind.annotation.requestmapping;import org.springframework.web.bind.annotation.restcontroller;import javax.annotation.resource;import java.util.list;/** * @author: tonghp * @create: 2021/07/26 16:45 */@restcontroller@requestmapping("ur")public class urinfocontroller {    @autowired    private urinforvice urinforvice ;    @requestmapping("savedata")    public string savedata (){        urinfo urinfo = new urinfo () ;        urinfo.tid(4);        urinfo.turname("winter");        urinfo.tpassword("567");        urinfo.tphone("13977776789");        urinfo.temail("winter");        urinfo.tcreateday("2020-02-20");        urinforvice.savedata(urinfo);        return "sus";    }    @requestmapping("lectbyid")    public urinfo lectbyid () {        return urinforvice.lectbyid(1) ;    }    @requestmapping("lectlist")    public list<urinfo> lectlist () {        return urinforvice.lectlist() ;    }}

main()

package com.example.tonghp;import org.mybatis.spring.annotation.mapperscan;import org.springframework.boot.springapplication;import org.springframework.boot.autoconfigure.springbootapplication;@springbootapplication@mapperscan("com.example.tonghp.mapper")public class tonghpapplication {    public static void main(string[] args) {        springapplication.run(tonghpapplication.class, args);    }}

参考链接：

https://blog.csdn.net/weixin_46792649/article/details/115306384

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

https://www.cnblogs.com/cicada-smile/p/11632251.html

https://github.com/cicadasmile/middle-ware-parent

另外还有一种直接操作jdbc的：

https://blog.csdn.net/alice_qixin/article/details/导课84957380

到此这篇关于springboot集成clickhou的文章就介绍到这了,更多相关springboot集成clickhou内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！