Java连接TDengine和MySQL双数据源

git文件地址：项目首页 - SpringBoot连接TDengine和MySQL双数据源:SpringBoot连接TDengine和MySQL双数据源 - GitCode

1、yml配置

spring:datasource:druid:mysql:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/testusername: rootpassword: 1234type: com.alibaba.druid.pool.DruidDataSourcetdengine:driver-class-name: com.taosdata.jdbc.rs.RestfulDriverurl: jdbc:TAOS-RS://localhost:6041/test?&timezone=UTC-8&charset=UTF-8&locale=en_US.UTF-8username: rootpassword: 1234type: com.alibaba.druid.pool.DruidDataSource

2、pom依赖

<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web-services</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-devtools</artifactId><scope>runtime</scope><optional>true</optional></dependency><!--mysql--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.33</version><scope>runtime</scope></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.15</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>org.apache.commons</groupId><artifactId>commons-lang3</artifactId><version>3.7</version></dependency><!-- mybatis-plus --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.3</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus</artifactId><version>3.5.3.1</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-extension</artifactId><version>3.5.3.1</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-generator</artifactId><version>3.5.3.1</version></dependency><!-- 动态数据源 --><dependency><groupId>com.baomidou</groupId><artifactId>dynamic-datasource-spring-boot-starter</artifactId><version>3.5.1</version></dependency><dependency><groupId>com.google.guava</groupId><artifactId>guava</artifactId><version>32.1.3-jre</version></dependency><dependency><groupId>io.swagger</groupId><artifactId>swagger-annotations</artifactId><version>1.6.3</version></dependency><dependency><groupId>com.taosdata.jdbc</groupId><artifactId>taos-jdbcdriver</artifactId><version>3.2.7</version></dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.3.8</version></dependency>
</dependencies>

3、重写SqlSession

package com.example.testtdengine.config.db;import org.apache.ibatis.exceptions.PersistenceException;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.session.*;
import org.mybatis.spring.MyBatisExceptionTranslator;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.dao.support.PersistenceExceptionTranslator;
import org.springframework.util.Assert;import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.List;
import java.util.Map;import static java.lang.reflect.Proxy.newProxyInstance;
import static org.apache.ibatis.reflection.ExceptionUtil.unwrapThrowable;
import static org.mybatis.spring.SqlSessionUtils.*;public class CustomSqlSessionTemplate extends SqlSessionTemplate {private final SqlSessionFactory sqlSessionFactory;private final ExecutorType executorType;private final SqlSession sqlSessionProxy;private final PersistenceExceptionTranslator exceptionTranslator;private Map<Object, SqlSessionFactory> targetSqlSessionFactories;private SqlSessionFactory defaultTargetSqlSessionFactory;/*** 通过Map传入** @param targetSqlSessionFactories*/public void setTargetSqlSessionFactories(Map<Object, SqlSessionFactory> targetSqlSessionFactories) {this.targetSqlSessionFactories = targetSqlSessionFactories;}public void setDefaultTargetSqlSessionFactory(SqlSessionFactory defaultTargetSqlSessionFactory) {this.defaultTargetSqlSessionFactory = defaultTargetSqlSessionFactory;}public CustomSqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {this(sqlSessionFactory, sqlSessionFactory.getConfiguration().getDefaultExecutorType());}public CustomSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType) {this(sqlSessionFactory, executorType, new MyBatisExceptionTranslator(sqlSessionFactory.getConfiguration().getEnvironment().getDataSource(), true));}public CustomSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType,PersistenceExceptionTranslator exceptionTranslator) {super(sqlSessionFactory, executorType, exceptionTranslator);this.sqlSessionFactory = sqlSessionFactory;this.executorType = executorType;this.exceptionTranslator = exceptionTranslator;this.sqlSessionProxy = (SqlSession) newProxyInstance(SqlSessionFactory.class.getClassLoader(),new Class[]{SqlSession.class},new SqlSessionInterceptor());this.defaultTargetSqlSessionFactory = sqlSessionFactory;}//通过DataSourceContextHolder获取当前的会话工厂@Overridepublic SqlSessionFactory getSqlSessionFactory() {String dataSourceKey = DbContextHolder.getDbType();SqlSessionFactory targetSqlSessionFactory = targetSqlSessionFactories.get(dataSourceKey);if (targetSqlSessionFactory != null) {return targetSqlSessionFactory;} else if (defaultTargetSqlSessionFactory != null) {return defaultTargetSqlSessionFactory;} else {Assert.notNull(targetSqlSessionFactories, "Property 'targetSqlSessionFactories' or 'defaultTargetSqlSessionFactory' are required");Assert.notNull(defaultTargetSqlSessionFactory, "Property 'defaultTargetSqlSessionFactory' or 'targetSqlSessionFactories' are required");}return this.sqlSessionFactory;}@Overridepublic Configuration getConfiguration() {return this.getSqlSessionFactory().getConfiguration();}public ExecutorType getExecutorType() {return this.executorType;}public PersistenceExceptionTranslator getPersistenceExceptionTranslator() {return this.exceptionTranslator;}/*** {@inheritDoc}*/public <T> T selectOne(String statement) {return this.sqlSessionProxy.<T>selectOne(statement);}/*** {@inheritDoc}*/public <T> T selectOne(String statement, Object parameter) {return this.sqlSessionProxy.<T>selectOne(statement, parameter);}/*** {@inheritDoc}*/public <K, V> Map<K, V> selectMap(String statement, String mapKey) {return this.sqlSessionProxy.<K, V>selectMap(statement, mapKey);}/*** {@inheritDoc}*/public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey) {return this.sqlSessionProxy.<K, V>selectMap(statement, parameter, mapKey);}/*** {@inheritDoc}*/public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey, RowBounds rowBounds) {return this.sqlSessionProxy.<K, V>selectMap(statement, parameter, mapKey, rowBounds);}/*** {@inheritDoc}*/public <E> List<E> selectList(String statement) {return this.sqlSessionProxy.<E>selectList(statement);}/*** {@inheritDoc}*/public <E> List<E> selectList(String statement, Object parameter) {return this.sqlSessionProxy.<E>selectList(statement, parameter);}/*** {@inheritDoc}*/public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds) {return this.sqlSessionProxy.<E>selectList(statement, parameter, rowBounds);}/*** {@inheritDoc}*/public void select(String statement, ResultHandler handler) {this.sqlSessionProxy.select(statement, handler);}/*** {@inheritDoc}*/public void select(String statement, Object parameter, ResultHandler handler) {this.sqlSessionProxy.select(statement, parameter, handler);}/*** {@inheritDoc}*/public void select(String statement, Object parameter, RowBounds rowBounds, ResultHandler handler) {this.sqlSessionProxy.select(statement, parameter, rowBounds, handler);}/*** {@inheritDoc}*/public int insert(String statement) {return this.sqlSessionProxy.insert(statement);}/*** {@inheritDoc}*/public int insert(String statement, Object parameter) {return this.sqlSessionProxy.insert(statement, parameter);}/*** {@inheritDoc}*/public int update(String statement) {return this.sqlSessionProxy.update(statement);}/*** {@inheritDoc}*/public int update(String statement, Object parameter) {return this.sqlSessionProxy.update(statement, parameter);}/*** {@inheritDoc}*/public int delete(String statement) {return this.sqlSessionProxy.delete(statement);}/*** {@inheritDoc}*/public int delete(String statement, Object parameter) {return this.sqlSessionProxy.delete(statement, parameter);}/*** {@inheritDoc}*/public <T> T getMapper(Class<T> type) {return getConfiguration().getMapper(type, this);}/*** {@inheritDoc}*/public void commit() {throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");}/*** {@inheritDoc}*/public void commit(boolean force) {throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");}/*** {@inheritDoc}*/public void rollback() {throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");}/*** {@inheritDoc}*/public void rollback(boolean force) {throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");}/*** {@inheritDoc}*/public void close() {throw new UnsupportedOperationException("Manual close is not allowed over a Spring managed SqlSession");}/*** {@inheritDoc}*/public void clearCache() {this.sqlSessionProxy.clearCache();}/*** {@inheritDoc}*/public Connection getConnection() {return this.sqlSessionProxy.getConnection();}/*** {@inheritDoc}** @since 1.0.2*/public List<BatchResult> flushStatements() {return this.sqlSessionProxy.flushStatements();}/*** Proxy needed to route MyBatis method calls to the proper SqlSession got from Spring's Transaction Manager It also* unwraps exceptions thrown by {@code Method#invoke(Object, Object...)} to pass a {@code PersistenceException} to* the {@code PersistenceExceptionTranslator}.*/private class SqlSessionInterceptor implements InvocationHandler {public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {final SqlSession sqlSession = getSqlSession(CustomSqlSessionTemplate.this.getSqlSessionFactory(),CustomSqlSessionTemplate.this.executorType,CustomSqlSessionTemplate.this.exceptionTranslator);try {Object result = method.invoke(sqlSession, args);if (!isSqlSessionTransactional(sqlSession, CustomSqlSessionTemplate.this.getSqlSessionFactory())) {sqlSession.commit(true);}return result;} catch (Throwable t) {Throwable unwrapped = unwrapThrowable(t);if (CustomSqlSessionTemplate.this.exceptionTranslator != null && unwrapped instanceof PersistenceException) {Throwable translated = CustomSqlSessionTemplate.this.exceptionTranslator.translateExceptionIfPossible((PersistenceException) unwrapped);if (translated != null) {unwrapped = translated;}}throw unwrapped;} finally {closeSqlSession(sqlSession, CustomSqlSessionTemplate.this.getSqlSessionFactory());}}}
}

4、设置切面

@Component
@Order(value = -100)
@Slf4j
@Aspect
public class DataSourceSwitchAspect {@Pointcut("execution(* com.example.testtdengine.mapper.mysql..*.*(..))")private void mysqlAspect() {}@Pointcut("execution(* com.example.testtdengine.mapper.tdengine..*.*(..))")private void tdengineAspect() {}@Before("mysqlAspect()")public void mysql() {log.info("切换到mysql 数据源...");DbContextHolder.setDbType(DBTypeEnum.mysql);}@Before("tdengineAspect()")public void tdengine() {log.info("切换到tdengine 数据源...");DbContextHolder.setDbType(DBTypeEnum.tdengine);}@After("mysqlAspect()")public void clear1() {log.info("清除mysql数据源...");DbContextHolder.clearDbType();}@After("tdengineAspect()")public void clear2() {log.info("清除tdengine数据源...");DbContextHolder.clearDbType();}}

5、DBContextHolder使用ThreadLocal将数据源连接存储在当前线程的threadlocals(ThreadLocalMap)中，在连接数据库时自动获取当前线程对于的数据源

package com.example.testtdengine.config.db;public class DbContextHolder {private static final ThreadLocal contextHolder = new ThreadLocal<>();/*** 设置数据源** @param dbTypeEnum*/public static void setDbType(DBTypeEnum dbTypeEnum) {contextHolder.set(dbTypeEnum.getValue());}/*** 取得当前数据源** @return*/public static String getDbType() {return (String) contextHolder.get();}/*** 清除上下文数据*/public static void clearDbType() {contextHolder.remove();}
}

6、数据库枚举

package com.example.testtdengine.config.db;public enum DBTypeEnum {mysql("mysql"),tdengine("tdengine");private String value;DBTypeEnum(String value) {this.value = value;}public String getValue() {return value;}
}

7、封装动态切库

package com.example.testtdengine.config.db;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;public class DynamicDataSource extends AbstractRoutingDataSource {protected Object determineCurrentLookupKey() {return DbContextHolder.getDbType();}
}

8、修改mybatis全局配置

package com.example.testtdengine.config.db;import com.baomidou.mybatisplus.core.config.GlobalConfig;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;import javax.annotation.PostConstruct;public class MyGlobalConfig extends GlobalConfig {@Autowiredprivate CustomSqlSessionTemplate sqlSessionTemplate;private static CustomSqlSessionTemplate customSqlSessionTemplate;@Overridepublic SqlSessionFactory getSqlSessionFactory() {return customSqlSessionTemplate.getSqlSessionFactory();}@PostConstructpublic void init() {MyGlobalConfig.customSqlSessionTemplate = sqlSessionTemplate;}
}

9、注入分页插件

package com.example.testtdengine.config;import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.example.testtdengine.config.db.DBTypeEnum;
import com.example.testtdengine.config.db.DynamicDataSource;
import com.example.testtdengine.config.interceptor.TaosDynamicTableNameInnerInterceptor;
import com.example.testtdengine.config.interceptor.TaosTableNameHandler;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.transaction.annotation.EnableTransactionManagement;import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;@EnableTransactionManagement
@Configuration
public class MybatisPlusConfig {@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
//        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));// 动态表名插件TaosDynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new TaosDynamicTableNameInnerInterceptor();dynamicTableNameInnerInterceptor.setTableNameHandler(new TaosTableNameHandler());interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);return interceptor;}@Bean(name = "mysql")@ConfigurationProperties(prefix = "spring.datasource.druid.mysql")public DataSource mysql() {return DruidDataSourceBuilder.create().build();}@Bean(name = "tdengine")@ConfigurationProperties(prefix = "spring.datasource.druid.tdengine")public DataSource tdengine() {return DruidDataSourceBuilder.create().build();}@Bean@Primarypublic DataSource multipleDataSource(@Qualifier("mysql") DataSource mysql,@Qualifier("tdengine") DataSource tdengine) {DynamicDataSource dynamicDataSource = new DynamicDataSource();Map<Object, Object> targetDataSources = new HashMap<>();targetDataSources.put(DBTypeEnum.mysql.getValue(), mysql);targetDataSources.put(DBTypeEnum.tdengine.getValue(), tdengine);// 程序默认数据源，这个要根据程序调用数据源频次，经常把常调用的数据源作为默认dynamicDataSource.setDefaultTargetDataSource(tdengine);dynamicDataSource.setTargetDataSources(targetDataSources);return dynamicDataSource;}@Bean("sqlSessionFactory")public SqlSessionFactory sqlSessionFactory() throws Exception {MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();sqlSessionFactory.setDataSource(multipleDataSource(mysql(), tdengine()));MybatisConfiguration configuration = new MybatisConfiguration();configuration.setJdbcTypeForNull(JdbcType.NULL);configuration.setMapUnderscoreToCamelCase(true);configuration.setCallSettersOnNulls(true);configuration.setCacheEnabled(false);GlobalConfig.DbConfig dab = new GlobalConfig.DbConfig();dab.setIdType(IdType.AUTO);sqlSessionFactory.setConfiguration(configuration);//添加分页功能sqlSessionFactory.setPlugins(new Interceptor[]{mybatisPlusInterceptor()});return sqlSessionFactory.getObject();}}

主要配置如下图所示