Java连接TDengine和MySQL双数据源
git文件地址：项目首页 - SpringBoot连接TDengine和MySQL双数据源:SpringBoot连接TDengine和MySQL双数据源 - GitCode
1、yml配置
spring:
datasource:
druid:
mysql:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test
username: root
password: 1234
type: com.alibaba.druid.pool.DruidDataSource
tdengine:
driver-class-name: com.taosdata.jdbc.rs.RestfulDriver
url: jdbc:TAOS-RS://localhost:6041/test?&timezone=UTC-8&charset=UTF-8&locale=en_US.UTF-8
username: root
password: 1234
type: com.alibaba.druid.pool.DruidDataSource2、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获取当前的会话工厂
@Override
public 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;
}
@Override
public 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 {
@Autowired
private CustomSqlSessionTemplate sqlSessionTemplate;
private static CustomSqlSessionTemplate customSqlSessionTemplate;
@Override
public SqlSessionFactory getSqlSessionFactory() {
return customSqlSessionTemplate.getSqlSessionFactory();
}
@PostConstruct
public 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 {
@Bean
public 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
@Primary
public 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();
}
}
主要配置如下图所示
