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

springboot配置多数据源mysql+TDengine保姆级教程

article 2024/11/29 7:58:53

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

  • 前言
  • 一、pom文件
  • 二、yaml
  • DataSourceConfig
  • Service
  • Mapper.xml
  • 测试
  • 总结

前言

Mybatis-plus管理多数据源，数据库为mysql和TDengine。

一、pom文件

 <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
<!--            <scope>runtime</scope>-->
        </dependency>
        <!--connection pool-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>
        <dependency>
            <groupId>com.taosdata.jdbc</groupId>
            <artifactId>taos-jdbcdriver</artifactId>
            <version>3.2.7</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.7</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>

二、yaml

spring:
  datasource:
#    driver-class-name: com.mysql.cj.jdbc.Driver
#    url: jdbc:mysql://127.0.0.1:3306/wh_vehicles?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=Asia/Shanghai
#    username: root
#    password: Lucky#2024
    dynamic:
      primary: master # 设置默认的数据源
      strict: false # 严格按照路由规则从主库读取数据，如果主库不可用，则从从库读取数据。
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/wh_vehicles?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=Asia/Shanghai
          username: root
          password: 123
        slave:
          driver-class-name: com.taosdata.jdbc.rs.RestfulDriver
          url: jdbc:TAOS-RS://localhost:6041?timezone=UTC-8&charset=utf-8&useSSL=false&user=root&password=123
          # using connection pools
          type: com.alibaba.druid.pool.DruidDataSource
          druid:
            initial-size: 5
            min-idle: 5
            max-active: 20
            max-wait: 60000
            time-between-eviction-runs-millis: 60000
            min-evictable-idle-time-millis: 300000
            validation-query: SELECT 1
            pool-prepared-statements: true
            max-pool-prepared-statement-per-connection-size: 20
# mybatis
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: org.zqh.jt808.server.model

DataSourceConfig

package org.zqh.jt808.server.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    // 主数据源 master
    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.dynamic.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    // 从数据源 slave
    @Bean(name = "slaveDataSource")
    @ConfigurationProperties(prefix = "spring.dynamic.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }
    // 主数据源的 SqlSessionFactory
    @Primary
    @Bean(name = "masterSqlSessionFactory")
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        // 这里可以添加其他 MyBatis 配置，如 mapperLocations, typeAliases 等
        String locationPattern = "classpath*:/mapper/*.xml";
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources(locationPattern));
        return sqlSessionFactoryBean.getObject();
    }

    // 从数据源的 SqlSessionFactory
    @Bean(name = "slaveSqlSessionFactory")
    public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        // 这里可以添加其他 MyBatis 配置，如 mapperLocations, typeAliases 等
        // 注意：通常从数据源的 mapperLocations 和 typeAliases 应该与主数据源分开配置
        String locationPattern = "classpath*:/mapper/*.xml";
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources(locationPattern));
        return sqlSessionFactoryBean.getObject();
    }
}

Service

package org.zqh.jt808.server.service;

import cn.hutool.json.JSONObject;
import com.baomidou.dynamic.datasource.annotation.DS;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.zqh.jt808.server.mapper.TDMapper;
import org.zqh.jt808.server.model.LocationInfo;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class TDService {

    // 超级表名称和子表模板
    private static final String SUPER_TABLE_NAME = "device_locations";
    private static final String SUB_TABLE_PREFIX = "device_";

    @Autowired
    private TDMapper tdMapper;

    /**
     * 保存坐标数据
     * @param locationInfo
     */
    @DS("slave")
    public void saveLocation(LocationInfo locationInfo) {
        tdMapper.saveLocation(locationInfo);
    }

    /**
     * 查询实时位置-全部
     * @return
     */
    @DS("slave")
    public List<LocationInfo> queryLocationAll() {
        return tdMapper.queryLocationAll();
    }

    /**
     * 查询历史轨迹
     * @param deviceId 设备id
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return
     */
    @DS("slave")
    public List<LocationInfo> queryLocationHistory(String deviceId, Long startTime, Long endTime) {
        return tdMapper.queryLocationHistory(deviceId,startTime,endTime);
    }

    /**
     * 查询实时位置-指定设备列表
     * @param deviceIds 设备id列表
     * @return
     */
    @DS("slave")
    public List<LocationInfo> queryLocations(List<String> deviceIds) {
        if(deviceIds.size()>0){
            return tdMapper.queryLocations(deviceIds);
        }else {
            return new ArrayList<>();
        }
    }

    @DS("slave")
    public JSONObject queryLocation(String deviceId) {
        return tdMapper.queryLocation(deviceId);
    }
}

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="org.zqh.jt808.server.mapper.TDMapper">
    <insert id="saveLocation">
        INSERT INTO wh_special_equipment.device_locations (tbname, ts, longitude, latitude, height, speed, direction, deviceid)
        VALUES (#{tname}, #{ts}, #{longitude}, #{latitude}, #{height}, #{speed}, #{direction}, #{deviceId})
    </insert>
    <select id="queryLocationHistory" resultType="org.zqh.jt808.server.model.LocationInfo">
        <![CDATA[
        select * from wh_special_equipment.device_locations where deviceid= #{deviceId} and ts >= #{startTime} and ts <= #{endTime};
        ]]>
    </select>
    <select id="queryLocations" resultType="org.zqh.jt808.server.model.LocationInfo">
        select last_row(deviceId),* from wh_special_equipment.device_locations
        where deviceId IN
        <foreach item="deviceId" index="index" collection="list" open="(" separator="," close=")">
            #{deviceId}
        </foreach>
        group by deviceId;
    </select>
    <select id="queryLocationAll" resultType="org.zqh.jt808.server.model.LocationInfo">
        select tbname,last_row(deviceId),* from wh_special_equipment.device_locations
        group by tbname;
    </select>
    <select id="queryLocation" resultType="cn.hutool.json.JSONObject">
        select last_row(deviceId),deviceId,ts,longitude,latitude,height,speed,direction
        from wh_special_equipment.device_locations
        where deviceId = #{deviceId}
    </select>
</mapper>

测试

package org.zqh.jt808.server.test;

import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.zqh.jt808.server.service.ApiService;
import org.zqh.jt808.server.service.TDService;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@SpringBootTest
@Slf4j
class DBTest {
    @Resource
    private ApiService apiService;
    @Resource
    private TDService tdService;

    @Test
    public void td() {
        System.out.println(apiService.queryTD());
    }
    @Test
    public void mysql() {
        log.debug("{}",apiService.queryMySql());
    }

    /**
     * 查询所有坐标
     */
    @Test
    public void testLocationAll() {
        log.debug("{}",tdService.queryLocationAll());
    }

    /**
     * 查询所有坐标-设备列表
     */
    @Test
    public void testLocations() {
        List<String> list = new ArrayList<>();
        list.add("00000000000040904004");
        log.debug("{}",tdService.queryLocations(list));
    }

    /**
     * 查询实时坐标-单设备
     */
    @Test
    public void testLocation() {
        log.info("{}",tdService.queryLocation("00000000000040904004"));
    }

    /**
     * 查询历史轨迹
     */
    @Test
    public void testLocationHistory() {
        List<String> list = new ArrayList<>();
        list.add("00000000000040904004");
        Map<String, List<String>> map = new HashMap<>();
        map.put("deviceIds",list);
        String deviceId = "00000000000040904004";
        long startTime = System.currentTimeMillis();
        long endTime = System.currentTimeMillis();
        log.debug("{}",tdService.queryLocationHistory(deviceId,startTime,endTime));
    }
}

总结

直接执行测试类，执行成功查看数据库即可


http://www.kler.cn/a/413913.html

相关文章：

  • Spring Boot【三】
  • Java 反射（Reflection）
  • 用shell脚本写一个通用的监听程序异常并重启脚本
  • 【VUE3】新版Vue3+ElementPlus全家桶开发视频项目实战
  • 第 37 章 - Go 语言 持续集成与持续部署
  • 带有悬浮窗功能的Android应用
  • 探索文件系统，Python os库是你的瑞士军刀
  • C++设计模式——Abstract Factory Pattern抽象工厂模式
  • 介绍一下printf,scanf
  • C++ 模板与泛型编程
  • OceanBase 大数据量导入（obloader）
  • 【论文复现】农作物病害叶子图像分割
  • c# httppost请求接口传参数及图片上传，非常实用
  • 电子应用设计方案-28：智能云饭锅系统方案设计
  • 网络--传输层协议--UDP
  • 【C#】调用外部应用
  • Network Link Conditioner Mac 上模拟网络环境工具的安装和使用
  • Mybatis-plus 3.5.9 版本 PaginationInnerInterceptor 插件更新
  • 数据结构与算法学习笔记----并查集
  • 人工智能中的数据结构：构建智能算法的基石
  • 3D技术如何应用到汽车营销中？
  • OpenCV相机标定与3D重建(6)将3D物体点投影到2D图像平面上函数projectPoints()的使用
  • log4c库使用
  • 【数据结构与算法】链表之美-复杂链表的复制与链表的插入排序
  • 占用磁盘100%？Apache DolphinScheduler 日志如何定时清理！
  • IntelliJ+SpringBoot项目实战（十七）--在SpringBoot中整合SpringSecurity和JWT(下B)