在线做投资网站/百度统计流量研究院

最近做一个项目 需要 整合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.DruidDataSourcedruid:# 主库数据源  mysqlmaster:driverClassName: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8username: rootpassword: 123456# 从库数据源   clickhouseslave:# 从数据源开关/默认关闭driverClassName: com.clickhouse.jdbc.ClickHouseDriverenabled: trueurl: jdbc:clickhouse://localhost:8123/testusername: adminpassword: 123456# 初始连接数initialSize: 10# 最小连接池数量minIdle: 15# 最大连接池数量maxActive: 50# 配置获取连接等待超时的时间maxWait: 60000# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒timeBetweenEvictionRunsMillis: 60000# 配置一个连接在池中最小生存的时间，单位是毫秒minEvictableIdleTimeMillis: 300000# 配置一个连接在池中最大生存的时间，单位是毫秒maxEvictableIdleTimeMillis: 900000# 配置检测连接是否有效validationQuery: SELECT 1testWhileIdle: truetestOnBorrow: falsetestOnReturn: false

3. 实体类

/**1. @author WXY2. @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 gjwl4. @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 agfrom 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 gjwl* @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 gjwl* @date 2022-07-01*/
@Service
@DataSource(value = DataSourceType.SLAVE)
public class CurveServiceImpl implements ICurveService {private static final Logger log = LoggerFactory.getLogger(CurveServiceImpl.class);@Autowiredprivate RedisCache redisCache;@Autowiredprivate CurveMapper curveMapper;/*** 单条曲线使用* @param curve 【查询曲线】* @return*/@Overridepublic 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*/@Overridepublic 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 【查询曲线】集合*/@Overridepublic 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 {@Autowiredprivate 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;//debugSystem.out.println("查询listCurveMany/" + time + "毫秒");return AjaxResult.success(list);}else {return AjaxResult.success("psn或者时间丢失");}}
}

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