• Springboot集成ClickHouse

    1、clickhouse应⽤场景

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

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

    2、整合Springboot：

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

    <dependencies>
    <!--clickhouse-->
    <dependency>
        <groupId>ru.yandex.clickhouse</groupId>
        <artifactId>clickhouse-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.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://127.0.0.1:8123/dbname
      username: username
      password: 123456
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 6000

mybatis-plus:
  mapper-locations: classpath*:mapper/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true
    cache-enabled: true
    lazy-loading-enabled: true
    multiple-result-sets-enabled: true
    use-generated-keys: true
    default-statement-timeout: 60
    default-fetch-size: 100
  type-aliases-package: com.example.tonghp.entity

    ClickHouse与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 username;
    private String password;
    // 省略 GET 和 SET
}

    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
 */
@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());
        datasource.setUsername(jdbcParamConfig.getUsername());
        datasource.setPassword(jdbcParamConfig.getPassword());
        return datasource;
    }
}

    接下来配置实体类，mapper，service，controlle以及mapper.xml。与mybatisplus操作mysql

    package com.example.tonghp.entity;

import lombok.Data;

import java.io.Serializable;

/**
 * @author: tonghp
 * @create: 2021/07/26 16:31
 */
@Data
public class UserInfo implements Serializable {
    private static final long serialVersionUID = 1L;
    private int id;
    private String userName;
    private String passWord;
    private String phone;
    private String email;
    private String createDay;
}

    package com.example.tonghp.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.tonghp.entity.UserInfo;
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
 */
@Repository
public interface UserInfoMapper extends BaseMapper<UserInfo> {
    // 写入数据
    void saveData (UserInfo userInfo) ;
    // ID 查询
    UserInfo selectById (@Param("id") Integer id) ;
    // 查询全部
    List<UserInfo> selectList () ;
}

    UserInfoMapper.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.UserInfoMapper">

    <resultMap id="BaseResultMap" type="com.example.tonghp.entity.UserInfo">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="user_name" jdbcType="VARCHAR" property="userName" />
        <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="Base_Column_List">
        id,user_name,pass_word,phone,email,create_day
    </sql>
    <insert id="saveData" parameterType="com.example.tonghp.entity.UserInfo" >
        INSERT INTO cs_user_info
        (id,user_name,pass_word,phone,email,create_day)
        VALUES
        (#{id,jdbcType=INTEGER},#{userName,jdbcType=VARCHAR},#{passWord,jdbcType=VARCHAR},
        #{phone,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{createDay,jdbcType=VARCHAR})
    </insert>
    <select id="selectById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from cs_user_info
        where id = #{id,jdbcType=INTEGER}
    </select>
    <select id="selectList" resultMap="BaseResultMap" >
        select
        <include refid="Base_Column_List" />
        from cs_user_info
    </select>

</mapper>

    Service

    package com.example.tonghp.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.example.tonghp.entity.UserInfo;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author: tonghp
 * @create: 2021/07/26 16:46
 */
public interface UserInfoService extends IService<UserInfo> {
    // 写入数据
    void saveData (UserInfo userInfo) ;
    // ID 查询
    UserInfo selectById (@Param("id") Integer id) ;
    // 查询全部
    List<UserInfo> selectList () ;
}

    ServiceImpl

    package com.example.tonghp.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.tonghp.entity.UserInfo;
import com.example.tonghp.mapper.UserInfoMapper;
import com.example.tonghp.service.UserInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author: tonghp
 * @create: 2021/07/26 16:48
 */

@Service
public class UserInfoServiceImpl extends ServiceImpl<UserInfoMapper, UserInfo> implements UserInfoService {

    @Autowired
    UserInfoMapper userInfoMapper;

    @Override
    public void saveData(UserInfo userInfo) {
        userInfoMapper.saveData(userInfo);
    }

    @Override
    public UserInfo selectById(Integer id) {
        return userInfoMapper.selectById(id);
    }

    @Override
    public List<UserInfo> selectList() {
        return userInfoMapper.selectList();
    }
}

    Controller

    package com.example.tonghp.controller;

import com.example.tonghp.entity.UserInfo;
import com.example.tonghp.service.UserInfoService;
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("user")
public class UserInfoController {

    @Autowired
    private UserInfoService userInfoService ;

    @RequestMapping("saveData")
    public String saveData (){
        UserInfo userInfo = new UserInfo () ;
        userInfo.setId(4);
        userInfo.setUserName("winter");
        userInfo.setPassWord("567");
        userInfo.setPhone("13977776789");
        userInfo.setEmail("winter");
        userInfo.setCreateDay("2020-02-20");
        userInfoService.saveData(userInfo);
        return "sus";
    }

    @RequestMapping("selectById")
    public UserInfo selectById () {
        return userInfoService.selectById(1) ;
    }

    @RequestMapping("selectList")
    public List<UserInfo> selectList () {
        return userInfoService.selectList() ;
    }
}

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

    =============================================================================================

    使用jdbc方式操作clickhouse

    1、创建表及数据

    create table t_order01(
 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);
  
insert into t_order01 values
(101,'sku_001',1000.00,'2021-12-01 12:00:00'),
(102,'sku_002',2000.00,'2021-12-01 11:00:00'),
(102,'sku_004',2500.00,'2021-12-01 12:00:00'),
(102,'sku_002',2000.00,'2021-12-01 13:00:00'),
(102,'sku_002',12000.00,'2021-12-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-12 12:00:00');

    2、引入clickhouse的jdbc依赖

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

    3、实例代码

    实大部分的操作和我们使用jdbc操作mysql的步骤类似，下面直接贴出代码，可以结合注释进行参考使用

    import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;
  
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
  
public class CreateTableTest {
  
    private static String username = "default";
    private static String password = "你的连接密码";
    private static String address = "jdbc:clickhouse://clickhouse的连接IP地址:8123";
    private static String db = "连接数据库名称（默认数据库：default）";
    private static int socketTimeout = 600000;
  
    public static void main(String[] args) throws Exception {
        //getConn();
        //queryTable();
        //createTable("");
        //insertOne();
        //dropTable();
        deleteById();
        //updateById();
    }
  
    /**
     * 查询数据
     */
    public static void queryTable(){
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "select * from user_info";
        Connection connection = getConn();
        try {
            Statement statement = connection.createStatement();
            ResultSet rs  = statement.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            while(rs.next()){
                Map<String, Object> row = new HashMap<>();
                for(int i = 1; i <= rsmd.getColumnCount(); i++){
                    row.put(rsmd.getColumnName(i), rs.getObject(rsmd.getColumnName(i)));
                }
                list.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
  
        //在此可以根据实际需求将解析的数据封装到对象中
        list.stream().forEach(item ->{
            Map<String, Object> rowData = item;
            System.out.println(rowData);
        });
        //System.out.println(list);
    }
  
    /**
     * 创建表
     * @throws Exception
     */
    public static void createTable(String tableSql) throws Exception{
        /*tableSql = "create table t_order02(\n" +
                " id UInt32,\n" +
                " sku_id String,\n" +
                " total_amount Decimal(16,2),\n" +
                " create_time Datetime\n" +
                ") engine =MergeTree\n" +
                " partition by toYYYYMMDD(create_time)\n" +
                " primary key (id)\n" +
                " order by (id,sku_id);";*/
        Connection connection = getConn();
        Statement statement = connection.createStatement();
        boolean execute = statement.execute(tableSql);
        if(execute){
            System.out.println(execute);
            System.out.println("创建表成功");
        }
    }
  
    /**
     * 删除表
     * @throws Exception
     */
    public static void dropTable() throws Exception{
        Connection connection = getConn();
        Statement statement = connection.createStatement();
        statement.execute("drop table t_order01;");
        System.out.println("删除表成功");
    }
  
    /**
     * 插入数据
     * 实际使用时候，插入的语句里面的参数从外部传入进去
     * @throws Exception
     */
    public static void insertOne() throws Exception{
        Connection connection = getConn();
        PreparedStatement pstmt = connection.prepareStatement("insert into t_order01 values('103', 'sku_004', '2500.00','2021-06-01 12:00:00')");
        pstmt.execute();
        System.out.println("insert success");
    }
  
    /**
     * 删除数据
     * 实际使用时候，删除的语句里面的参数从外部传入进去
     */
    public static void deleteById() throws Exception{
        Connection connection = getConn();
        //sku_id ='sku_001'
        PreparedStatement pstmt = connection.prepareStatement("alter table t_order01 delete where sku_id = 'sku_002';");
        pstmt.execute();
        System.out.println("delete success");
    }
  
    /**
     * 修改数据
     * 实际使用时候，修改的语句里面的参数从外部传入进去
     */
    public static void updateById() throws Exception{
        Connection connection = getConn();
        PreparedStatement pstmt = connection.prepareStatement("alter table t_order01 update total_amount=toDecimal32(2000.00,2) where id = '102'");
        pstmt.execute();
        System.out.println("update success");
    }
  
    public static Connection getConn() {
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setUser(username);
        properties.setPassword(password);
        properties.setDatabase(db);
        properties.setSocketTimeout(socketTimeout);
        ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(address, properties);
        ClickHouseConnection conn = null;
        try {
            conn = clickHouseDataSource.getConnection();
            System.out.println(conn);
            System.out.println("连接成功");
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

    或---操作util工具类

    import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import net.sf.json.JSONObject;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;
 
import java.sql.*;
import java.util.*;
 
/**
 * @Description:
 * @Date 2018/11/12
 */
@Slf4j
@Component
public class ClickHouseUtil {
 
    private static String clickhouseAddress;
 
    private static String clickhouseUsername;
 
    private static String clickhousePassword;
 
    private static String clickhouseDB;
 
    private static Integer clickhouseSocketTimeout;
 
    @Value("${clickhouse.address}")
    public  void setClickhouseAddress(String address) {
        ClickHouseUtil.clickhouseAddress = address;
    }
    @Value("${clickhouse.username}")
    public  void setClickhouseUsername(String username) {
        ClickHouseUtil.clickhouseUsername = username;
    }
    @Value("${clickhouse.password}")
    public  void setClickhousePassword(String password) {
        ClickHouseUtil.clickhousePassword = password;
    }
    @Value("${clickhouse.db}")
    public  void setClickhouseDB(String db) {
        ClickHouseUtil.clickhouseDB = db;
    }
    @Value("${clickhouse.socketTimeout}")
    public  void setClickhouseSocketTimeout(Integer socketTimeout) {
        ClickHouseUtil.clickhouseSocketTimeout = socketTimeout;
    }
 
 
    public static Connection getConn() {
 
        ClickHouseConnection conn = null;
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setUser(clickhouseUsername);
        properties.setPassword(clickhousePassword);
        properties.setDatabase(clickhouseDB);
        properties.setSocketTimeout(clickhouseSocketTimeout);
        ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(clickhouseAddress,properties);
        try {
            conn = clickHouseDataSource.getConnection();
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
        }
 
        return null;
    }
 
    public static List<JSONObject> exeSql(String sql){
        log.info("cliockhouse 执行sql：" + sql);
        Connection connection = getConn();
        try {
            Statement statement = connection.createStatement();
            ResultSet results = statement.executeQuery(sql);
            ResultSetMetaData rsmd = results.getMetaData();
            List<JSONObject> list = new ArrayList();
            while(results.next()){
                JSONObject row = new JSONObject();
                for(int i = 1;i<=rsmd.getColumnCount();i++){
                    row.put(rsmd.getColumnName(i),results.getString(rsmd.getColumnName(i)));
                }
                list.add(row);
            }
 
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
 
}

    Test简单使用执行sql查询数据

    import com.renrenche.databus.common.ClickHouseUtil;
import com.renrenche.databus.common.Result;
import com.renrenche.databus.domain.logdata.fem.FemParam;
import com.renrenche.databus.service.fem.FemMainService;
import net.sf.json.JSONObject;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
 
import java.util.List;
 
/**
 * @Auther: qixin
 * @Date: 2018/12/11 15:05
 * @Description:
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class SemTest {
 
    @Test
    public void getFrsDataTest(){
        System.out.println("******************");
        String sql="select * from marketing.sem_campaign_real_time_report";
        List<JSONObject> result= ClickHouseUtil.exeSql(sql);
        System.out.println("******************");
    }
 
}

    ============================================================================================

    springboot的整合

    1、准备一张表，以及表中插入一些实验数据

    CREATE TABLE user_info (
  `id` UInt64,
  `user_name` String,
  `pass_word` String,
  `phone` String,
  `create_day` Date DEFAULT CAST(now(),'Date')
)ENGINE = MergeTree
primary key (id)
order by (id);
  
  
INSERT INTO user_info
  (id,user_name,pass_word,phone)
VALUES
  (1,'xiaowang','123456','13325511231'),
  (2,'xiaoma','123456','13825511231'),
  (3,'xiaozhao','123456','18925511231');

    2、代码完整整合步骤

    导入完整依赖

    <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>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>1.3.2</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.38</version>
    </dependency>
  
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.13</version>
    </dependency>
  
    <dependency>
        <groupId>commons-lang</groupId>
        <artifactId>commons-lang</artifactId>
        <version>2.6</version>
    </dependency>
  
    <!-- clickHouse数据库 -->
    <dependency>
        <groupId>ru.yandex.clickhouse</groupId>
        <artifactId>clickhouse-jdbc</artifactId>
        <version>0.1.53</version>
    </dependency>
  
</dependencies>

    2、基础配置文件

    server:
  port: 7010
  
  # mybatis 配置
mybatis:
  type-aliases-package: com.congge.entity
  mapper-locations: classpath:/mapper/*.xml
  
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    click:
      driverClassName: ru.yandex.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://IP地址:8123/default
      username: default
      password: 123456
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 6000

    3、使用一个配置类，关联第二步中的click配置属性

    import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
  
@Component
@ConfigurationProperties(prefix = "spring.datasource.click")
public class ConnectionParamConfig {
  
    private String driverClassName ;
    private String url ;
    private Integer initialSize ;
    private Integer maxActive ;
    private Integer minIdle ;
    private Integer maxWait ;
  
    private String username;
    private String password;
  
    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;
    }
  
    public String getUsername() {
        return username;
    }
  
    public void setUsername(String username) {
        this.username = username;
    }
  
    public String getPassword() {
        return password;
    }
  
    public void setPassword(String password) {
        this.password = password;
    }
}

    4、重写datasource的配置，使用自定义的clickhouse的属性配置

    import javax.annotation.Resource;
  
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
  
import javax.sql.DataSource;
  
@Configuration
public class DruidConfig {
  
    @Resource
    private ConnectionParamConfig jdbcParamConfig;
  
    /**
     * 重写 DataSource
     * @return
     */
    @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());
        datasource.setUsername(jdbcParamConfig.getUsername());
        datasource.setPassword(jdbcParamConfig.getPassword());
        return datasource;
    }
  
}

    5、提供一个接口和mybatis的查询xml文件

    public interface UserInfoMapper {
    void saveData (UserInfo userInfo) ;
    UserInfo selectById (@Param("id") Integer id) ;
    List<UserInfo> selectList () ;
}
  
<?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.congge.mapper.UserInfoMapper">
    <resultMap id="BaseResultMap" type="com.congge.entity.UserInfo">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="user_name" jdbcType="VARCHAR" property="userName" />
        <result column="pass_word" jdbcType="VARCHAR" property="passWord" />
        <result column="phone" jdbcType="VARCHAR" property="phone" />
        <result column="create_day" jdbcType="VARCHAR" property="createDay" />
    </resultMap>
  
    <sql id="Base_Column_List">
        id,user_name,pass_word,phone,create_day
    </sql>
  
    <insert id="saveData" parameterType="com.congge.entity.UserInfo" >
        INSERT INTO user_info
        (id,user_name,pass_word,phone,create_day)
        VALUES
        (#{id,jdbcType=INTEGER},#{userName,jdbcType=VARCHAR},#{passWord,jdbcType=VARCHAR},
        #{phone,jdbcType=VARCHAR},#{createDay,jdbcType=VARCHAR})
    </insert>
  
    <select id="selectById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from user_info
        where id = #{id,jdbcType=INTEGER}
    </select>
  
    <select id="selectList" resultMap="BaseResultMap" >
        select
        <include refid="Base_Column_List" />
        from user_info
    </select>
  
</mapper>

    6、接口和服务实现类

    @RestController
public class UserInfoController {
  
    @Resource
    private UserInfoService userInfoService ;
  
    //localhost:7010/saveData
    @GetMapping("/saveData")
    public String saveData (){
        UserInfo userInfo = new UserInfo () ;
        userInfo.setId(4);
        userInfo.setUserName("xiaolin");
        userInfo.setPassWord("54321");
        userInfo.setPhone("18500909876");
        userInfo.setCreateDay("2022-02-06");
        userInfoService.saveData(userInfo);
        return "success";
    }
  
    //localhost:7010/getById?id=1
    @GetMapping("/getById")
    public UserInfo getById (int id) {
        return userInfoService.selectById(id) ;
    }
  
    @GetMapping("/getList")
    public List<UserInfo> getList () {
        return userInfoService.selectList() ;
    }
  
}

    7、服务实现类，UserInfoService

    @Service
public class UserInfoService {
  
    @Resource
    private UserInfoMapper userInfoMapper ;
  
    public void saveData(UserInfo userInfo) {
        userInfoMapper.saveData(userInfo);
    }
  
    public UserInfo selectById(Integer id) {
        return userInfoMapper.selectById(id);
    }
  
    public List<UserInfo> selectList() {
        return userInfoMapper.selectList();
    }
  
}

    8、启动类

    @SpringBootApplication
@MapperScan(basePackages = {"com.congge.mapper"})
public class App {
  
    public static void main(String[] args) {
        SpringApplication.run(App.class,args);
    }
  
}

    9、功能接口测试

    查询测试，调用接口：localhost:7010/getById?id=1

    在这里插入图片描述

    插入数据测试，调用接口：localhost:7010/saveData

    在这里插入图片描述

    然后再去clickhouse表中查询下数据

    在这里插入图片描述

     到此这篇关于springboot 整合 clickhouse的文章就介绍到这了,更多相关springboot 整合 clickhouse内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

    ========================================================================================

     
  • 相关阅读:
    Oracle EBS订单的流程(Order>AR)
    ORA12547: TNS:lost contact
    Shipping lock fix –> WSH_DELIVERY_DETAILS Column has leading or trailing spaces
    PLSQL 操作 ORACLE 服务器上的文件
    ORACLE EBS 11i常见问题
    solr 查询解析流程
    spring管理servlet的利器SimpleServletHandlerAdapter
    Solr Cache使用介绍及分析
    solr 自定义 dismax查询方式
    spring2中jpa的配置和使用
  • 原文地址：https://www.cnblogs.com/hanease/p/16414376.html
Copyright © 2020-2023  润新知