ClickHouse-SpringBoot集成

最新推荐文章于 2024-06-21 10:59:56 发布

魔笛Love

最新推荐文章于 2024-06-21 10:59:56 发布

阅读量1.3k

点赞数

文章标签： spring boot spring java

本文链接：https://blog.csdn.net/clearlxj/article/details/121862157

版权

本文详细介绍了如何在SpringBoot应用中集成ClickHouse数据库，包括在pom.xml中添加依赖，配置application.yml，创建Druid数据源，定义实体类和Mapper，以及进行数据的增删查改操作。示例代码展示了从数据库读取和插入数据的过程，并提供了测试数据和表结构。

摘要由CSDN通过智能技术生成

SpringBoot集成ClickHouse

pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
         
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.starnet.clickhouse</groupId>
    <artifactId>clickhouse</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>clickhouse</name>

    <properties>
        <java.version>11</java.version>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath/>
    </parent>
    <dependencies>
        <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>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>
        <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>

        <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>
        </plugins>
    </build>

</project>

application.yml

server:
  port: ${port:9980}
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
clickhouse:
  driverClassName: ru.yandex.clickhouse.ClickHouseDriver
  url: jdbc:clickhouse://hadoop113:8123/default
  password:
  initialSize: 10
  maxActive: 100
  minIdle: 10
  maxWait: 6000

mybatis:
  type-aliases-package: com.wyu.tt16clickhouse.entity
  mapper-locations:
    - classpath:mapper/*.xml
    - classpath*:com/**/mapper/*.xml

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

启动类

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;

@SpringBootApplication
@EnableScheduling
@EnableAsync
@MapperScan("com.starnet.clickhouse.uda.mapper") // 加载所有的mapper
public class ClickHouseApplication {

    public static void main(String[] args) {
        SpringApplication.run(ClickHouseApplication.class, args);
    }

}

ClickHouse配置信息类

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

Druid连接ClickHouse

import com.alibaba.druid.pool.DruidDataSource;
import com.starnet.clickhouse.common.ChParam;
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;
    }
}

User模型类

import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;

import java.util.Date;

@Data
public class User {

    @TableField(value = "userId")
    private Long userId;

    @TableField(value = "appId")
    private String appId;

    @TableField(value = "version")
    private String version;

    @TableField(value = "regTime")
    private Date regTime;
}

UserMapper，

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.starnet.clickhouse.uda.entity.User;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper extends BaseMapper<User> {

}

Controller，此处仅使用BaseMapper中提供的接口测试基本获取和插入，需要额外更复杂的查询通过xml编写即可，xml文件的编写与Mysql的xml编写几乎完全相同。

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.starnet.clickhouse.uda.entity.User;
import com.starnet.clickhouse.uda.mapper.UserMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.Date;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserMapper userMapper;

    private Logger log = LoggerFactory.getLogger(UserController.class);


    @GetMapping(value = "/list/get")
    public Object getQuestionList() {

        return userMapper.selectList(new QueryWrapper<>());
    }

    @GetMapping(value = "/add")
    public Object add() {

        User user = new User();
        user.setUserId(123L);
        user.setAppId("test");
        user.setVersion("1.1.1.1");
        user.setRegTime(new Date());

        userMapper.insert(user);
        return "success";
    }
}

测试数据和表

create table user
(
    userId  Int32,
    appId   String,
    version String,
    regTime Date
) engine = MergeTree PARTITION BY toYYYYMM(regTime) ORDER BY userId SETTINGS ndex_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');

先调用新增，然后调用查询，测试结果如下：

[
  {
    "userId": 123457,
    "appId": "RR",
    "version": "3.6",
    "regTime": "2020-01-07 00:00:00"
  },
  {
    "userId": 1234,
    "appId": "TT",
    "version": "2.4",
    "regTime": "2020-07-24 00:00:00"
  },
  {
    "userId": 12345,
    "appId": "RR",
    "version": "2.5",
    "regTime": "2020-07-29 00:00:00"
  },
  {
    "userId": 123456,
    "appId": "TT",
    "version": "2.1",
    "regTime": "2020-07-09 00:00:00"
  },
  {
    "userId": 234561,
    "appId": "GG",
    "version": "3.0",
    "regTime": "2020-07-31 00:00:00"
  },
  {
    "userId": 43234,
    "appId": "HH",
    "version": "2.5",
    "regTime": "2020-06-06 00:00:00"
  },
  {
    "userId": 123,
    "appId": "test",
    "version": "1.1.1.1",
    "regTime": "2021-12-10 00:00:00"
  }
]

魔笛Love

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
ClickHouse-SpringBoot集成

SpringBoot集成ClickHousepom文件<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://mave
复制链接

扫一扫