springboot+mybatis配置clickhouse实现插入查询功能

springboot+mybatis配置clickhouse实现插入查询功能

说明

ClickHouse 是一款用于大数据实时分析的列式数据库管理系统，在大数据量查询时有着非常优秀的性能，

但是也有缺点，就是不支持事务，不支持真正的删除 / 更新，所以笔者只演示插入和查询。

1.添加maven依赖

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-aop

org.springframework.boot

spring-boot-starter-test

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.3.2

com.alibaba

druid-spring-boot-starter

1.1.13

commons-lang

commons-lang

2.6

ru.yandex.clickhouse

clickhouse-jdbc

0.1.53

org.projectlombok

lombok

true

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

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.wyu.tt16clickhouse.UserMapper">

select userId, appId, version, regTime from `default`.`user`

INSERT INTO `default`.`user` (`userId`, `appId`, `version`, `regTime`)

VALUES (#{userId}, #{appId}, #{version}, #{regTime})

6、Mapper接口

@Mapper

public interface UserMapper {

List 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, http://version, regTime) VALUES (234561, 'GG', '3.0', '2020-07-31');

9.测试

参考文章：SpringBoot2 整合 ClickHouse数据库案例解析

总结

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。