Springboot集成Clickhouse附完整代码和测试数据。工程结构：maven依赖配属数据源ControllerentityserviceServiceImplmapperMapper.xml测试数据：查看一下数据库（可视化数据库安装翻看我以前的文章）启动项目访问一下接口

74 阅读 0 评论 49 点赞

我是靠谱客的博主等待香烟，最近开发中收集的这篇文章主要介绍Springboot集成Clickhouse附完整代码和测试数据。工程结构：maven依赖配属数据源ControllerentityserviceServiceImplmapperMapper.xml测试数据：查看一下数据库（可视化数据库安装翻看我以前的文章）启动项目访问一下接口，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

ClickHouse的安装教程查看前面的文章。

工程结构：

在这里插入图片描述

maven依赖

        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.2.4</version>
        </dependency>

配属数据源

spring:
  datasource:
    driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
    url: jdbc:clickhouse://192.168.8.145:8123/test
  application:
    name: clickouse-application
mybatis:
  mapper-locations: classpath:mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
server:
  port: 9000

Controller

package com.geewise.bigdata.controller;

import com.geewise.bigdata.entity.Order;
import com.geewise.bigdata.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author lgn
 * @created 2021-01-13 9:41
 */
@RestController
@RequestMapping("/order")
public class OrderController {

    @Autowired
    private OrderService orderService;

    @GetMapping("/{skuId}")
    public BigDecimal getAmountBySkuId(
            @PathVariable(name = "skuId", required = true) String skuId
    ){
        return orderService.getTotalAmountBySkuId(skuId);
    }

    @GetMapping("/detail/{id}")
    public List<Order> getOderBySkuId(
            @PathVariable(name = "id", required = true) Integer id
    ){
        return orderService.getOrderById(id);
    }
}

entity

import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

/**
 * @author lgn
 * @created 2021-01-13 15:24
 */
@Data
public class Order {
    private Integer id;
    private String skuId;
    private BigDecimal totalAmount;
    private Date createTime;
}

service

import com.geewise.bigdata.entity.Order;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author lgn
 * @created 2021-01-13 9:39
 */
public interface OrderService {
    BigDecimal getTotalAmountBySkuId(String skuId);
    List<Order> getOrderById(Integer id);
}

ServiceImpl

import com.geewise.bigdata.entity.Order;
import com.geewise.bigdata.mapper.OrderMapper;
import com.geewise.bigdata.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author lgn
 * @created 2021-01-13 9:40
 */
@Service
public class OrderServiceImpl implements OrderService {

    @Autowired
    private OrderMapper orderMapper;

    @Override
    public BigDecimal getTotalAmountBySkuId(String skuId) {
        return orderMapper.selectTotalAmountSkuId(skuId);
    }

    @Override
    public List<Order> getOrderById(Integer id) {
        return orderMapper.selectOrder(id);
    }
}

mapper

import com.geewise.bigdata.entity.Order;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author lgn
 * @created 2021-01-13 9:28
 */
public interface OrderMapper {
    BigDecimal selectTotalAmountSkuId(String skuId);
    List<Order> selectOrder(Integer Id);
}

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.geewise.bigdata.mapper.OrderMapper">
    <sql id="baseSql">
        SELECT id as id,sku_id as skuId, total_amount as totalAmount,create_time as createTime
    </sql>

    <!--map-underscore-to-camel-case 大写转下划线, 所以column的配置需要写成驼峰 result property="totalAmount" column="totalAmount"-->
    <resultMap id="baseResult" type="com.geewise.bigdata.entity.Order">
        <id property="id" column="id" />
        <result property="skuId" column="skuId" />
        <result property="totalAmount" column="totalAmount" />
        <result property="createTime" column="createTime" />
    </resultMap>
    <select id="selectTotalAmountSkuId" parameterType="String" resultType="java.math.BigDecimal">
      SELECT SUM(total_amount) as sum_amount FROM `order` WHERE sku_id=#{skuId}
    </select>

    <select id="selectOrder" parameterType="Integer" resultMap="baseResult">
      <include refid="baseSql"></include>
        FROM `order` WHERE id = #{Id}
    </select>

</mapper>

测试数据：

create table order(id UInt32,sku_id String,total_amount Decimal(16,2),create_time Datetime) engine =MergeTree partition by toYYYYMMDD(create_time) primary key (id) order by (id,sku_id);