最近项目中需要切换数据库,查找了一些资料,也做了一些实验,实现了动态切换多数据源的数据库,做法如下:
1、依赖两种数据库的数据源
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- sqlserver -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
</dependency>
2、在yml中追加多个数据源
spring:
datasource:
sqlserver:
jdbc-url: jdbc:sqlserver://xxx.xxx.xx.xxx:1433;DatabaseName=RFDB
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
username: sa
password: 'xxx'
mysql:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://xxx.xxx.xx.xxx:3306/jndjplatform?characterEncoding=utf8&serverTimezone=Hongkong
username: root
password: xxx
3、配置类的代码
package com.jndj.platform.config.mybatisplus;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.jndj.platform.config.mybatisplus.custom.DataSourceEnum;
import com.jndj.platform.config.mybatisplus.custom.MultipleDataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.annotation.MapperScan;
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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* @author yaohj
* @date 2020/1/15
*/
@Configuration
@MapperScan({"com.jndj.platform.common.mapper","com.jndj.platform.*.*.mapper"})
public class MybatisPlusConfig {
/**
* mybatis-plus分页插件<br>
* 文档:http://mp.baomidou.com<br>
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
/**
* @author yangzh
* 配置mysql datasource
* @return
*/
@Bean(name = "dataSourceMysql")
@ConfigurationProperties(prefix = "spring.datasource.mysql")
public DataSource dataSourceMysql() {
return DataSourceBuilder.create().build() ;
}
/**
* @author yangzh
* 配置 sql server datasource
* @return
*/
@Bean(name = "dataSourceSqlServer")
@ConfigurationProperties(prefix = "spring.datasource.sqlserver")
public DataSource dataSourceSqlServer() {
return DataSourceBuilder.create().build() ;
}
/**
* 使用枚举类 映射两个datasource
* @param mysql
* @param sqlServer
* @return
*/
@Bean
@Primary
public DataSource multipleDataSource(@Qualifier("dataSourceMysql") DataSource mysql,
@Qualifier("dataSourceSqlServer") DataSource sqlServer) {
MultipleDataSource multipleDataSource = new MultipleDataSource() ;
Map<Object, Object> targetDataSources = new HashMap<>() ;
targetDataSources.put(DataSourceEnum.dataSourceMysql.getValue(), mysql) ;
targetDataSources.put(DataSourceEnum.dataSourceSqlServer.getValue(), sqlServer) ;
//添加数据源
multipleDataSource.setTargetDataSources(targetDataSources) ;
//设置默认数据源db1
multipleDataSource.setDefaultTargetDataSource(mysql );
return multipleDataSource ;
}
@Bean("sqlSessionFactory")
public MybatisSqlSessionFactoryBean sqlSessionFactory() throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean() ;
sqlSessionFactory.setDataSource(multipleDataSource(dataSourceMysql(), dataSourceSqlServer())) ;
MybatisConfiguration configuration = new MybatisConfiguration() ;
configuration.setJdbcTypeForNull(JdbcType.NULL) ;
configuration.setMapUnderscoreToCamelCase(true) ;
configuration.setCacheEnabled(false) ;
sqlSessionFactory.setConfiguration(configuration) ;
sqlSessionFactory.setPlugins(new Interceptor[]{ paginationInterceptor() });
return sqlSessionFactory ;
}
}
package com.jndj.platform.config.mybatisplus.custom;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
@Component
@Slf4j
@Aspect
@Order(-1)
public class DataSourceAspect {
/**
* 设置切面范围
*/
@Pointcut("@within(com.jndj.platform.config.mybatisplus.custom.DataSource) || " +
"@annotation(com.jndj.platform.config.mybatisplus.custom.DataSource)")
public void pointCut(){}
/**
* 添加数据源上下文
* @param dataSource
*/
@Before("pointCut() && @annotation(dataSource)")
public void doBefore(DataSource dataSource){
DataSourceContextHolder.setDataSource(dataSource.value().getValue());
}
/**
* 清除数据源上下文
*/
@After("pointCut()")
public void doAfter(){
DataSourceContextHolder.clear();
}
}
package com.jndj.platform.config.mybatisplus.custom;
/**
*
*/
public class DataSourceContextHolder {
private static final ThreadLocal<String> contextHolder = new InheritableThreadLocal<>() ;
/**
* 设置数据源
* @param db
*/
public static void setDataSource(String db){
contextHolder.set(db) ;
}
/**
* 取得当前数据源
* @return
*/
public static String getDataSource(){
return contextHolder.get() ;
}
/**
* 清除上下文数据
*/
public static void clear(){
contextHolder.remove() ;
}
}
package com.jndj.platform.config.mybatisplus.custom;
/**
* 枚举类 用于切换数据库
*/
public enum DataSourceEnum {
/**
* 数据库1
*/
dataSourceMysql("dataSourceMysql"),
/**
* 数据库2
*/
dataSourceSqlServer("dataSourceSqlServer");
private String value ;
DataSourceEnum(String value){
this.value = value ;
}
public String getValue() {
return value;
}
}
package com.jndj.platform.config.mybatisplus.custom;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 自由切换datasource
*/
public class MultipleDataSource extends AbstractRoutingDataSource {
/**
* 重写determineCurrentLookupKey(),通过DataSourceContextHolder 获取数据源变量,用于当作lookupKey取出指定的数据源
* determineCurrentLookupKey,该方法返回一个Object,一般是返回字符串
*
* @return
*/
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSource();
}
}
package com.jndj.platform.config.mybatisplus.custom;
import java.lang.annotation.*;
/**
* 自定义的注解
* 可以在 mapper 或者 service 中使用
* 可以用于方法,也可以用于类
*
* 本注解用于切换数据库 例 @DataSource("dataSourceMsql")
*/
@Target({ElementType.METHOD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
DataSourceEnum value() default DataSourceEnum.dataSourceMysql;
}
3、mapper中使用新定义的注解标签就可以了
package com.jndj.platform.system.user.mapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.jndj.platform.common.mapper.BaseMapperEx;
import com.jndj.platform.config.mybatisplus.custom.DataSource;
import com.jndj.platform.config.mybatisplus.custom.DataSourceEnum;
import com.jndj.platform.system.user.entity.User;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
* @author yaohj
* @date 2020/2/19
*/
@Repository
@DataSource(DataSourceEnum.dataSourceSqlServer)
public interface UserMapper extends BaseMapperEx<User> {
/**
*
* @param page 翻页对象,传递参数 Page 即自动分页
* @return
*/
List<User> getUserList(User form, Page page);
}
4、想切换数据库时,使用上面mapper中使用的@DataSource(DataSourceEnum.dataSourceSqlServer)进行切换就可以了