当前位置：首页 > news >正文

SpringBoot 整合 clickhouse和mysql 手把手教程全网最详细

news 2025/6/22 13:54:45

最近做一个项目需要整合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或者时间丢失");}}
}