简单记录一下Springboot配置不同数据源(clickhouse和mysql)

 Maven依赖

 
        
            ru.yandex.clickhouse
            clickhouse-jdbc
            0.1.52
        
        
        
            com.alibaba
            druid-spring-boot-starter
            1.1.17
        
        
        
            com.baomidou
            dynamic-datasource-spring-boot-starter
            3.5.1
        

 application.yml配置

spring:
  autoconfigure:
    exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
  datasource:
    dynamic:
      primary: mysql
      strict: false
      datasource:
        #可以配置多个
        mysql:
          url: jdbc:mysql://${MYSQL_HOST:localhost}:${MYSQL_PORT:3306}/${MYSQL_DATABASE:easysite_3_1_2}?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&createDatabaseIfNotExist=true
          username: root
          password: root
          driver-class-name: com.mysql.cj.jdbc.Driver
        clickhouse:
          url: jdbc:clickhouse://hadoop102:8123
          username: default
          password:
          driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
      flyway:
        locations: classpath:mysql/migration

 pojo：UserInfo

注意：这里的BaseEntity是公司封装的框架，里面有已经封装好的字段，譬如ID，IsDeleted等字段。结合Flyway可以自动创建table。

我把Maven依赖贴出来

        
            net.microcental
            easysite-common-all
            3.1.2
        

package net.microcental.easysite.demo.pojo;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import net.microcental.easysite.common.core.domain.entity.BaseEntity;

import java.io.Serializable;

@Data
@TableName("cs_user_info")
public class UserInfo extends BaseEntity {
//    private static final long serialVersionUID = 1L;
//    private Integer id;
    @TableField("")
    private String userName;
    private String passWord;
    private String phone;
    private String email;
    private String createDay;
}

 Mapper

注意：可以用@DB注解标识在类上面，表示该类所有方法走clickhouse这个数据源。

package net.microcental.easysite.demo.mapper;


import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import net.microcental.easysite.demo.pojo.UserInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
@DS("clickhouse")
public interface UserInfoMapper extends BaseMapper {
    // 写入数据
    void saveData (UserInfo userInfo) ;
    // ID 查询
    UserInfo selectById (@Param("id") Integer id) ;
    // 查询全部
    List selectList () ;
}

 UserInfoMapper.xml

    
        
        
        
        
        
        
    
    
        id,user_name,pass_word,phone,email,create_day
    
    
        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})
    
    
        select
        
        from cs_user_info
        where id = #{id,jdbcType=INTEGER}
    
    
        select
        
        from cs_user_info
    

 Service层

package net.microcental.easysite.demo.service;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.extension.service.IService;
import net.microcental.easysite.common.mybatisplus.extension.service.BaseService;
import net.microcental.easysite.demo.pojo.UserInfo;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserInfoService extends BaseService {
    // 写入数据
    void saveData (UserInfo userInfo) ;
    // ID 查询
    UserInfo selectById (@Param("id") Integer id) ;
    // 查询全部
    List selectList () ;
}

ServiceImpl 

package net.microcental.easysite.demo.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import net.microcental.easysite.common.mybatisplus.extension.service.impl.BaseServiceImpl;
import net.microcental.easysite.demo.mapper.UserInfoMapper;
import net.microcental.easysite.demo.pojo.UserInfo;
import net.microcental.easysite.demo.service.UserInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
@DS("clickhouse")
public class UserInfoServiceImpl extends BaseServiceImpl implements UserInfoService {

    private final UserInfoMapper userInfoMapper;

    @Autowired
    public UserInfoServiceImpl(UserInfoMapper userInfoMapper) {
        this.userInfoMapper = userInfoMapper;
    }

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

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

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

controller 

package net.microcental.easysite.demo.controller;

import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import net.microcental.easysite.common.core.domain.api.ApiResult;
import net.microcental.easysite.demo.pojo.UserInfo;
import net.microcental.easysite.demo.service.UserInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


@Validated
@RestController
@RequestMapping("/user")
@Api(tags = "UserInfoController")
@Slf4j
public class UserInfoController {

    @Autowired
    private UserInfoService userInfoService ;

    @PostMapping("saveData")
    public ApiResult saveData () throws InterruptedException {
        UserInfo userInfo = new UserInfo () ;
        userInfo.setId(4L);
        userInfo.setUserName("winter");
        userInfo.setPassWord("567");
        userInfo.setPhone("13977776789");
        userInfo.setEmail("winter");
        userInfo.setCreateDay("2020-02-20");
        for (int i=0;i<1000;i++){
            userInfoService.saveData(userInfo);
        }

        return ApiResult.success();
    }

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

}

上面是clickhouse数据源代码，下面是mysql数据源代码

pojo

package net.microcental.easysite.demo.pojo;

import lombok.Data;
import net.microcental.easysite.common.core.domain.entity.BaseEntity;

@Data
public class Person extends BaseEntity {

    private String name;

}

flyway需要的sql文件和存储路径

注意：路径一定要放在resources文件夹下面，可以像我一样创建一个mysql.migration文件夹存储sql文件，命名规范一定要按照我这个来。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person`  (
                           `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
                           `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                           `remarks` varchar(4000) DEFAULT NULL COMMENT '备注',
                           `is_deleted` tinyint(1) unsigned DEFAULT NULL COMMENT '是否删除',
                           `create_by` bigint(20) unsigned DEFAULT NULL COMMENT '创建人',
                           `create_date` datetime DEFAULT NULL COMMENT '创建时间',
                           `last_modified_by` bigint(20) unsigned DEFAULT NULL COMMENT '最后更新人',
                           `last_modified_date` datetime DEFAULT NULL COMMENT '最后更新时间',
                           `ext1` bigint(20) DEFAULT NULL COMMENT '扩展字段1',
                           `ext2` bigint(20) DEFAULT NULL COMMENT '扩展字段2',
                           `ext3` bigint(20) DEFAULT NULL COMMENT '扩展字段3',
                           `ext4` bigint(20) DEFAULT NULL COMMENT '扩展字段4',
                           `ext5` bigint(20) DEFAULT NULL COMMENT '扩展字段5',
                           `ext6` varchar(255) DEFAULT NULL COMMENT '扩展字段6',
                           `ext7` varchar(255) DEFAULT NULL COMMENT '扩展字段7',
                           `ext8` varchar(255) DEFAULT NULL COMMENT '扩展字段8',
                           `ext9` varchar(255) DEFAULT NULL COMMENT '扩展字段9',
                           `ext10` varchar(255) DEFAULT NULL COMMENT '扩展字段10',
                           PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Person';

SET FOREIGN_KEY_CHECKS = 1;

mapper

package net.microcental.easysite.demo.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import net.microcental.easysite.demo.pojo.Person;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

@Mapper
@Repository
@DS("mysql")
public interface PersonMapper extends BaseMapper {
}

package net.microcental.easysite.demo.service;

import net.microcental.easysite.common.mybatisplus.extension.service.BaseService;
import net.microcental.easysite.demo.pojo.Person;

public interface PersonService extends BaseService {
}

package net.microcental.easysite.demo.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import net.microcental.easysite.common.mybatisplus.extension.service.impl.BaseServiceImpl;
import net.microcental.easysite.demo.mapper.PersonMapper;
import net.microcental.easysite.demo.pojo.Person;
import net.microcental.easysite.demo.service.PersonService;
import org.springframework.stereotype.Service;

@Service
@DS("mysql")

public class PersonServiceImpl extends BaseServiceImpl implements PersonService {


}

package net.microcental.easysite.demo.controller;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import net.microcental.easysite.common.core.domain.api.ApiResult;
import net.microcental.easysite.demo.pojo.Person;
import net.microcental.easysite.demo.service.PersonService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@Validated
@RestController
@RequestMapping("/person")
@Api(tags = "PersonController")
@Slf4j
public class PersonController {

    @Autowired
    private PersonService personService;

    @GetMapping ("/get")
    @ApiOperation("获取Person数据")
    public ApiResult saveSupplier() {
        List list = personService.list();
        log.info("list:"+list);
        return ApiResult.success();
    }

}