最近做一个项目 需要 整合mysql clickhouse 多数据源

后台用的是ruoyi框架

1. 首先pom引入相关依赖

  <!--JDBC-clickhouse数据库-->
    <dependency>
        <groupId>com.clickhouse</groupId>
        <artifactId>clickhouse-jdbc</artifactId>
        <version>0.3.2</version>	<!-- 0.2.4/0.2.5/0.2.6/0.3.0/0.3.2 -->
    </dependency>

2. 编写配置文件 application.yml(properties同理)

需要注意的是官网不建议使用ru.yandex.clickhouse驱动，应该改成com.clickhouse驱动，并且推荐使用0.3.2以上的版本

数据源配置

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      # 主库数据源  mysql
      master:
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456
      # 从库数据源   clickhouse
      slave:
        # 从数据源开关/默认关闭
        driverClassName: com.clickhouse.jdbc.ClickHouseDriver
        enabled: true
        url: jdbc:clickhouse://localhost:8123/test
        username: admin
        password: 123456
      # 初始连接数
      initialSize: 10
      # 最小连接池数量
      minIdle: 15
      # 最大连接池数量
      maxActive: 50
      # 配置获取连接等待超时的时间
      maxWait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 300000
      # 配置一个连接在池中最大生存的时间，单位是毫秒
      maxEvictableIdleTimeMillis: 900000
      # 配置检测连接是否有效
      validationQuery: SELECT 1
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false

3. 实体类

/**
 1. @author WXY
 2. @date 2023/2/24 14:46
 */
public class Curve {
    /** 设备sn */
    private  String sn;
    /** 属性sn */
    private  String psn;
    /** 属性id */
    private  Integer pid;
    /** 值 */
    private Float val;
    /** 时间 */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date time;

    /** 开始时间 */
    private String startDateTime;
    /** 结束时间 */
    private String  endDateTime;

    /** 设备名称 */
    private String  name;

    /**
     * 表时间
     */
    private String outsideTime;


    public String getOutsideTime() {
        return outsideTime;
    }

    public void setOutsideTime(String outsideTime) {
        this.outsideTime = outsideTime;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSn() {
        return sn;
    }

    public void setSn(String sn) {
        this.sn = sn;
    }

    public String getPsn() {
        return psn;
    }

    public void setPsn(String psn) {
        this.psn = psn;
    }

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    public Float getVal() {
        return val;
    }

    public void setVal(Float val) {
        this.val = val;
    }

    public Date getTime() {
        return time;
    }

    public void setTime(Date time) {
        this.time = time;
    }

    public String getStartDateTime() {
        return startDateTime;
    }

    public void setStartDateTime(String startDateTime) {
        this.startDateTime = startDateTime;
    }

    public String getEndDateTime() {
        return endDateTime;
    }

    public void setEndDateTime(String endDateTime) {
        this.endDateTime = endDateTime;
    }
}

4. mapper接口

/**
 1. 【查询clickhouse】Mapper接口
 2. 
 3. @author WXY
 4. @date 2023-02-24
 */
public interface CurveMapper {
    

    /**
     * 查询【单个曲线】列表
     *
     * @param curve 【曲线】  秒级数据
     * @return 【请填写功能名称】集合
     */
    public List<CurveVo> selectCurveVoList(Curve curve);


    /**
     * 查询单个集合里面最大最小平均值
     *
     * @param curve 【曲线】
     * @return 【查询单个集合里面最大最小平均值】集合
     */
    public Map selectMinAdnMax(Curve curve);
}

5. 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.xx.xx.mapper.CurveMapper">

    <!--正常实体-->
    <resultMap type="Curve" id="CurveResult">
        <result property="sn"    column="sn"    />
        <result property="psn"    column="psn"    />
        <result property="pid"    column="pid"    />
        <result property="val"    column="val"    />
        <result property="time"    column="time"    />
    </resultMap>

    <sql id="selectCurveVo">
        select val,time from curve
    </sql>


    <!--用于单条曲线-->
    <select id="selectCurveVoList" parameterType="Curve" resultMap="CurveVoResult">
        <include refid="selectCurveVo"/>
        <where>
            <if test="psn != null  and psn != ''"> and psn = #{psn}</if>
            <if test="startDateTime != null "> and time &gt;= #{startDateTime}</if>
            <if test="endDateTime != null "> and time &lt;=  #{endDateTime}</if>
        </where>
        order by time asc
    </select>

    <!--拿最大最小平均值-->
    <select id="selectMinAdnMax" parameterType="Curve" resultType="Map">
        select max(val) as big, min(val) as small, round(avg(val),1) as ag
        from curve
        <where>
            <if test="psn != null  and psn != ''"> and psn = #{psn}</if>
            <if test="startDateTime != null "> and time &gt;= #{startDateTime}</if>
            <if test="endDateTime != null "> and time &lt;=  #{endDateTime}</if>
        </where>
    </select>


</mapper>

6. service类

/**
 * 【查询曲线】Service接口
 * 
 * @author WXY
 * @date 2022-07-01
 */
public interface ICurveService {


    /**
     * 查询【查询曲线】列表  单条返回
     *
     * @param curve 【查询曲线】
     * @return 【查询曲线】集合
     */
    public List<CurveVo> selectCurveVoList(Curve curve);

    /**
     * 查询【查询曲线】列表  多条返回
     * 分钟级别
     *
     * @param curve 【查询曲线】
     * @return 【查询曲线】集合
     */
    public List<CurveManyVo> selectCurveManyVoList(Curve curve);


    /**
     * 查询【查询曲线】列表  单条返回
     * 带最大最小值
     *
     * @param curve 【查询曲线】
     * @return 【查询曲线】集合
     */
    public Map selectMinAdnMax(Curve curve);

}

7. ServiceImpl类

重点在@DataSource(value = DataSourceType.SLAVE) 注解上 在这里切换从库代表这个类里面的方法都切换成从库的数据库

/**
 * 【查询曲线】Service业务层处理
 *  
 * @author WXY
 * @date 2022-07-01
 */
@Service
@DataSource(value = DataSourceType.SLAVE)
public class CurveServiceImpl implements ICurveService {

    private static final Logger log = LoggerFactory.getLogger(CurveServiceImpl.class);

    @Autowired
    private RedisCache redisCache;

    @Autowired
    private CurveMapper curveMapper;


    /**
     * 单条曲线使用
     * @param curve 【查询曲线】
     * @return
     */
    @Override
    public List<CurveVo> selectCurveVoList(Curve curve) {
        String key = getCacheDsnKey(Constants.curve, curve.getPsn(), curve.getStartDateTime(), curve.getEndDateTime());
        List<CurveVo> list = curveMapper.selectCurveVoList(curve);
        if (list.size() > 0) {
            redisCache.setCacheList(key, list);
            //使用 删除缓存
            redisCache.expire(key, 2, TimeUnit.MINUTES);
        }
        return list;
    }

	/**
     * 单条曲线使用
     * @param curve 【查询曲线】
     *  最大值最小值平均值
     * @return
     */
    @Override
    public Map selectMinAdnMax(Curve curve) {
        String key = getCacheDsnKey(Constants.curve, curve.getPsn(), curve.getStartDateTime(), curve.getEndDateTime());
        Map mapList = new HashMap();
        //查询曲线
        List<CurveVo> list = curveMapper.selectCurveVoList(curve);
        //查询最大最小平均值
        Map maps = curveMapper.selectMinAdnMax(curve);
        mapList.put("list",list);
        mapList.put("min",maps.get("small"));
        mapList.put("max",maps.get("big"));
        mapList.put("average",maps.get("ag"));
        if (list.size() > 0) {
            redisCache.setCacheMap(key, mapList);
            //使用 删除缓存
            redisCache.expire(key, 2, TimeUnit.MINUTES);
        }
        return mapList;
    }

  /**
     * 查询【查询曲线】列表  多条返回
     * 分钟级别
     *
     * @param curve 【查询曲线】
     * @return 【查询曲线】集合
     */
    @Override
    public List<CurveManyVo> selectCurveManyVoList(Curve curve) {
        String[] items = curve.getPsn().split(",");
        List<CurveManyVo> curveManyVos = new ArrayList<>();
        CurveManyVo curveManyVo ;
        for (String s :items){
            curve.setPsn(s);
            curveManyVo = new CurveManyVo();
            curveManyVo.setCurveVos(curveMapper.selectCurveVoList(curve));
            curveManyVo.setPsn(s);
            curveManyVos.add(curveManyVo);
        }
        return curveManyVos;
    }



    /**
     * 设置cache key
     *
     * @param configKey 参数键
     * @return 缓存键key
     */
    private String getCacheDsnKey(String configKey, String psn, String startDateTime, String endDateTime) {
        return configKey + psn + startDateTime.replaceAll(":", "_") + "_" + endDateTime.replaceAll(":", "_");
    }

}

8. Controller类

/**
 * @author WXY
 * @date 2023/2/24 15:28
 */
@RestController
@RequestMapping("/xx/curvemessage")
public class CurveController extends BaseController {

    @Autowired
    private ICurveService iCurveService;


    /**
     * 查询单条【曲线日志】列表  按秒级 来查询
     * 需要查询出来 最大值最小值 还有平均值
     */
    @GetMapping("/listMinMax")
    public AjaxResult listMinMax(Curve curve) {
        curve.setTable(TABLE + curve.getOutsideTime());
       if (StringUtils.isNotEmpty(curve.getPsn())&&StringUtils.isNotEmpty(curve.getStartDateTime())&&StringUtils.isNotEmpty(curve.getEndDateTime())){
           //查询之后缓存  缓存5分钟  当再次请求此接口在替换掉缓存的值  方便下载功能
           Map list = iCurveService.selectMinAdnMax(curve);
           return AjaxResult.success(list);
       }else {
           return AjaxResult.success("psn或者时间丢失");
       }
    }


    /**
     * 查询单条【曲线日志】列表  按秒级 来查询
     * 没有最大最小值和平均值
     */
    @GetMapping("/list")
    public AjaxResult list(Curve curve) {
        curve.setTable(TABLE + curve.getOutsideTime());
        if (StringUtils.isNotEmpty(curve.getPsn())&&StringUtils.isNotEmpty(curve.getStartDateTime())&&StringUtils.isNotEmpty(curve.getEndDateTime())){
            //查询之后缓存  缓存5分钟  当再次请求此接口在替换掉缓存的值  方便下载功能
            List<CurveVo> list = iCurveService.selectCurveVoList(curve);
            return AjaxResult.success(list);
        }else {
            return AjaxResult.success("psn或者时间丢失");
        }
    }

    /**
     * 查询多条【曲线日志】列表
     */
    @GetMapping("/listCurveMany")
    public AjaxResult listCurveMany(Curve curve) {
        Long start = System.currentTimeMillis();
        if (StringUtils.isNotEmpty(curve.getPsn())&&StringUtils.isNotEmpty(curve.getStartDateTime())&&StringUtils.isNotEmpty(curve.getEndDateTime())){
            curve.setTable(TABLE + curve.getOutsideTime());
            List<CurveManyVo> list = iCurveService.selectCurveManyVoList(curve);
            Long end = System.currentTimeMillis();
            long time = end - start;
            //debug
            System.out.println("查询listCurveMany/" + time + "毫秒");
            return AjaxResult.success(list);
        }else {
            return AjaxResult.success("psn或者时间丢失");
        }
    }
}

晚点在写 没有用若依框架 怎么搭建主从数据库