最近做一个项目 需要 整合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. 实体类

public class Curve {        private  String sn;        private  String psn;        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接口

public interface CurveMapper {            public List<CurveVo> selectCurveVoList(Curve curve);        public Map selectMinAdnMax(Curve curve);}

5. mapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-//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类

public interface ICurveService {        public List<CurveVo> selectCurveVoList(Curve curve);        public List<CurveManyVo> selectCurveManyVoList(Curve curve);        public Map selectMinAdnMax(Curve curve);}

7. ServiceImpl类

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

@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;        @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;    }    @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;    }      @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;    }        private String getCacheDsnKey(String configKey, String psn, String startDateTime, String endDateTime) {        return configKey + psn + startDateTime.replaceAll(":", "_") + "_" + endDateTime.replaceAll(":", "_");    }}

8. Controller类

@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或者时间丢失");        }    }}

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

来源地址：https://blog.csdn.net/qq_42862247/article/details/129418557