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

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

---

前言

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#2024dynamic:primary: master # 设置默认的数据源strict: false # 严格按照路由规则从主库读取数据，如果主库不可用，则从从库读取数据。datasource:master:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/wh_vehicles?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=Asia/Shanghaiusername: rootpassword: 123slave:driver-class-name: com.taosdata.jdbc.rs.RestfulDriverurl: jdbc:TAOS-RS://localhost:6041?timezone=UTC-8&charset=utf-8&useSSL=false&user=root&password=123# using connection poolstype: com.alibaba.druid.pool.DruidDataSourcedruid:initial-size: 5min-idle: 5max-active: 20max-wait: 60000time-between-eviction-runs-millis: 60000min-evictable-idle-time-millis: 300000validation-query: SELECT 1pool-prepared-statements: truemax-pool-prepared-statement-per-connection-size: 20
# mybatis
mybatis-plus:mapper-locations: classpath:mapper/*.xmltype-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_";@Autowiredprivate 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_locationswhere 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_locationsgroup by tbname;</select><select id="queryLocation" resultType="cn.hutool.json.JSONObject">select last_row(deviceId),deviceId,ts,longitude,latitude,height,speed,directionfrom wh_special_equipment.device_locationswhere 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 {@Resourceprivate ApiService apiService;@Resourceprivate TDService tdService;@Testpublic void td() {System.out.println(apiService.queryTD());}@Testpublic void mysql() {log.debug("{}",apiService.queryMySql());}/*** 查询所有坐标*/@Testpublic void testLocationAll() {log.debug("{}",tdService.queryLocationAll());}/*** 查询所有坐标-设备列表*/@Testpublic void testLocations() {List<String> list = new ArrayList<>();list.add("00000000000040904004");log.debug("{}",tdService.queryLocations(list));}/*** 查询实时坐标-单设备*/@Testpublic void testLocation() {log.info("{}",tdService.queryLocation("00000000000040904004"));}/*** 查询历史轨迹*/@Testpublic 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));}
}

总结

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