springboot 多数据源的2种实现

springboot 多数据源的2种实现

这里介绍2种多数据源：

• 固定数据源

    数据源是已知且相对固定的，直接在配置文件里写入各个数据库的连接字符串、用域名、密码等
  

• 动态数据源

    数据源是动态的，在数据库或其他可动态存储的地方保存数据源，可按需调整（增删改）
  

1 固定数据源实现

业务场景：项目涉及多个数据库，比如本项目数据库、财务系统数据库、物资系统数据库。系统需要操作多个数据库。

1.1 分别定义各个数据源

第1个数据源： dataSourceMySql

@Configuration
public class DataSourceMySqlConfig {

    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driverClassName}")
    private String driverClassName;

    @Bean("dataSourceMySql")
    public DataSource dataSourceMySql() throws Exception {
        Properties props = new Properties();
        props.put("driverClassName", driverClassName);
        props.put("url", url);
        props.put("username", username);
        props.put("password", password);
        return DruidDataSourceFactory.createDataSource(props);
    }
    
    @Bean("jdbcTemplate")
    public JdbcTemplate jdbcTemplate(@Autowired DataSource dataSourceMySql){
        return new JdbcTemplate(dataSourceMySql);
    }
}

第2个数据源： dataSource2

@Configuration
public class DataSource2Config {

    @Value("${spring.datasource.2.url}")
    private String url;

    @Value("${spring.datasource.2.username}")
    private String username;

    @Value("${spring.datasource.2.password}")
    private String password;

    @Value("${spring.datasource.2.driverClassName}")
    private String driverClassName;

    @Bean("dataSource2")
    public DataSource dataSource2() throws Exception {
        Properties props = new Properties();
        props.put("driverClassName", driverClassName);
        props.put("url", url);
        props.put("username", username);
        props.put("password", password);
        return DruidDataSourceFactory.createDataSource(props);
    }
    
    @Bean("jdbcTemplate2")
    public JdbcTemplate jdbcTemplate(@Autowired DataSource dataSource2){
        return new JdbcTemplate(dataSource2);
    }
}

第3个数据源： dataSource3

@Configuration
public class DataSource3Config {

    @Value("${spring.datasource.3.url}")
    private String url;

    @Value("${spring.datasource.3.username}")
    private String username;

    @Value("${spring.datasource.3.password}")
    private String password;

    @Value("${spring.datasource.3.driverClassName}")
    private String driverClassName;

    @Bean("dataSource3")
    public DataSource dataSource3() throws Exception {
        Properties props = new Properties();
        props.put("driverClassName", driverClassName);
        props.put("url", url);
        props.put("username", username);
        props.put("password", password);
        return DruidDataSourceFactory.createDataSource(props);
    }
    
    @Bean("jdbcTemplate3")
    public JdbcTemplate jdbcTemplate(@Autowired DataSource dataSource3){
        return new JdbcTemplate(dataSource3);
    }
}

1.2 动态数据源

public class DataSourceContextHolder {
    public static final Logger log = LoggerFactory.getLogger(DataSourceContextHolder.class);
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    public static void setDBType(String dbType) {
        contextHolder.set(dbType);
    }

    public static String getDBType() {
        return ((String) contextHolder.get());
    }

    public static void clearDBType() {
        contextHolder.remove();
    }
}

public class DataSourceDynamic extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDBType();
    }
}

1.3 MyBatis配置

在Mybatis配置文件里注入和配置数据源

@Configuration
@EnableTransactionManagement
public class MyBatisConfig{
    private Logger logger = LoggerFactory.getLogger(this.getClass());
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean(@Autowired DataSourceDynamic dataSource) {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setPlugins(mybatisPlusInterceptor());
        //添加XML目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            bean.setMapperLocations(resolver.getResources("classpath*:com/master/*/*/mapping/*.xml"));
            return bean.getObject();
        } catch (Exception e) {
            logger.error("发生错误！", e);
            throw new RuntimeException(e);
        }
    }

    /**
     * Mybatis-plus分页插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }


    @Bean
    @Primary
    public DataSourceDynamic dataSource(
            @Autowired DataSource dataSourceMySql
            ,@Autowired DataSource dataSource2
            ,@Autowired DataSource dataSource3
    ){
        Map<Object, Object> targetDataSources = new HashMap<>();
        //不建议下面这样直接写字符串，可以考虑用枚举类型或其他方式来配置
        targetDataSources.put("dataSourceMySql", dataSourceMySql);
        targetDataSources.put("dataSource2", dataSource2);
        targetDataSources.put("dataSource3", dataSource3);
        DataSourceDynamic dataSource = new DataSourceDynamic();
        dataSource.setTargetDataSources(targetDataSources);//可用DataSource
        dataSource.setDefaultTargetDataSource(dataSourceMySql);// 默认datasource
        return dataSource;
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean("transactionManager")
    public DataSourceTransactionManager dataSourceTransactionManager(@Autowired DataSourceDynamic dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    
}

1.4 通过切面切换数据源

@Aspect
@Order(1)
@Component
public class DataSourceAspect {
    @Pointcut("@annotation(com.master.app.aspectj.lang.annotation.DataSource) || @within(com.master.app.aspectj.lang.annotation.DataSource)")
    public void dsPointCut() {}

    @Around("dsPointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        DataSource dataSource = getDataSource(point);
        if (Objects.nonNull(dataSource)) {
            DataSourceContextHolder.setDBType(dataSource.value().name());
        }
        try {
            return point.proceed();
        } finally {
            //还原数据源
            DataSourceContextHolder.clearDBType();
        }
    }

    /**
     * 切换数据源
     */
    public DataSource getDataSource(ProceedingJoinPoint point) {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Class<? extends Object> targetClass = point.getTarget().getClass();
        DataSource targetDataSource = targetClass.getAnnotation(DataSource.class);
        if (Objects.nonNull(targetDataSource)) {
            return targetDataSource;
        } else {
            Method method = signature.getMethod();
            return method.getAnnotation(DataSource.class);
        }
    }
}

1.5 使用

代码里未指定时，默认数据源是dataSourceMySql，当需要指定时

有2种使用方法：

• 直接用jdbcTemplate：

@Resource private JdbcTemplate jdbcTemplate3;
//直接jdbcTemplate3执行sql

• 在mapper使用@DataSource注释

public interface SynExpenditureMapper {
    不建议下面这样直接写字符串，可以考虑用枚举类型或其他方式来配置
    @DataSource("dataSource3")
    List<SomeEntity> doSomeThing(Map<String, Object> param);
}

2 动态数据源实现

数据源通过数据库来配置，系统先读取配置的数据源，再初始化它们。

我的业务场景：多租户模式。因为各种原因，我这个项目多租户模式最终做成了多数据库模式，每个租户一个库。初始化租户时，程序通过模板库为租户创建独立的数据库。下面介绍处理多数据源的方式。

租户访问采用域名/上下文路径的方式，域名固定，每个租户上下文路径不同。在nginx反向代理映射时，在header增加数据源的参数，然后在spring里增加拦截器，读取这个参数并写入session的datasource属性。DynamicDataSource再通过session存的值判断要使用的数据源。

2.1 数据源相关配置文件

DBContextHolder(和上文的一样)

public class DBContextHolder {
    private final static Logger log = LoggerFactory.getLogger(DBContextHolder.class);
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    // 调用此方法，切换数据源
    public static void setDataSource(String dataSource) {
        contextHolder.set(dataSource);
        log.info("已切换到数据源:{}",dataSource);
    }

    // 获取数据源
    public static String getDataSource() {
        return contextHolder.get();
    }

    // 删除数据源
    public static void clearDataSource() {
        contextHolder.remove();
        log.info("clear数据源");
    }
}

DataSourceUtil 用于获取当前数据源

public class DataSourceUtil {
    public static String getDataSource(){
        String datasource = "";
        Subject subject;
        Session session;
        try{
            subject = SecurityUtils.getSubject();
            session = subject.getSession();
            //当前数据源通过session保存
            datasource = (String)session.getAttribute(SessionConst.DATASOURCE);
        }catch (Exception e){}
        if(StringUtils.isEmpty(datasource)){
            //如果没有就返回base数据源
            datasource = MasterConst.BASE_DB_KEY;
        }
        return datasource;
    }
}

DynamicDataSource 动态数据源的实现。代码里的Partner对应租户数数据库信息，这里省略

public class DynamicDataSource extends AbstractRoutingDataSource {
    private boolean debug = true;
    private final Logger log = LoggerFactory.getLogger(getClass());
    private Map<Object, Object> dynamicTargetDataSources;
    private Object dynamicDefaultTargetDataSource;

    @Override
    protected Object determineCurrentLookupKey() {
        String datasource = DBContextHolder.getDataSource();
        if(StringUtils.isEmpty(datasource)){
            try{
                datasource = DataSourceUtil.getDataSource();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        if(StringUtils.isEmpty(datasource)){
            datasource = MasterConst.BASE_DB_KEY;
        }
        Map<Object, Object> dynamicTargetDataSources2 = this.dynamicTargetDataSources;
        if (!dynamicTargetDataSources2.containsKey(datasource)) {
            log.error("不存在的数据源：" + datasource);
            return null;
        }
        return datasource;
    }

    @Override
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        super.setTargetDataSources(targetDataSources);
        this.dynamicTargetDataSources = targetDataSources;
    }

    // 创建数据源
    public boolean createDataSource(String key, String driveClass, String url, String username, String password, String databasetype) throws Exception {
        Class.forName(driveClass);
        DriverManager.getConnection(url, username, password);// 相当于连接数据库
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setName(key);
        druidDataSource.setDriverClassName(driveClass);
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(username);
        druidDataSource.setPassword(password);
        druidDataSource.init();
        this.dynamicTargetDataSources.put(key, druidDataSource);
        setTargetDataSources(this.dynamicTargetDataSources);// 将map赋值给父类的TargetDataSources
        super.afterPropertiesSet();// 将TargetDataSources中的连接信息放入resolvedDataSources管理
        log.info(key+"数据源初始化成功");
        return true;
    }
    // 删除数据源
    public boolean delDatasources(String datasourceid) {
        Map<Object, Object> dynamicTargetDataSources2 = this.dynamicTargetDataSources;
        if (dynamicTargetDataSources2.containsKey(datasourceid)) {
            Set<DruidDataSource> druidDataSourceInstances = DruidDataSourceStatManager.getDruidDataSourceInstances();
            for (DruidDataSource l : druidDataSourceInstances) {
                if (datasourceid.equals(l.getName())) {
                    dynamicTargetDataSources2.remove(datasourceid);
                    DruidDataSourceStatManager.removeDataSource(l);
                    setTargetDataSources(dynamicTargetDataSources2);// 将map赋值给父类的TargetDataSources
                    super.afterPropertiesSet();// 将TargetDataSources中的连接信息放入resolvedDataSources管理
                    return true;
                }
            }
            return false;
        } else {
            return false;
        }
    }

    // 测试数据源连接是否有效
    public boolean testDatasource(String key, String driveClass, String url, String username, String password) {
        try {
            Class.forName(driveClass);
            DriverManager.getConnection(url, username, password);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
        this.setDynamicDefaultTargetDataSource(defaultTargetDataSource);
        this.dynamicDefaultTargetDataSource = defaultTargetDataSource;
    }

    public void setDebug(boolean debug) {
        this.debug = debug;
    }

    public boolean isDebug() {
        return debug;
    }

    public Map<Object, Object> getDynamicTargetDataSources() {
        return dynamicTargetDataSources;
    }

    public void setDynamicTargetDataSources(Map<Object, Object> dynamicTargetDataSources) {
        this.dynamicTargetDataSources = dynamicTargetDataSources;
    }

    public Object getDynamicDefaultTargetDataSource() {
        return dynamicDefaultTargetDataSource;
    }

    public void setDynamicDefaultTargetDataSource(Object dynamicDefaultTargetDataSource) {
        this.dynamicDefaultTargetDataSource = dynamicDefaultTargetDataSource;
    }
    
    //创建数据连接
    public void createDataSourceWithCheck(Partner dataSource) throws Exception {
        String datasourceId = dataSource.getCode();
        log.info("正在检查数据源："+datasourceId);
        Map<Object, Object> dynamicTargetDataSources2 = this.dynamicTargetDataSources;
        if (dynamicTargetDataSources2.containsKey(datasourceId)) {
            log.info("数据源"+datasourceId+"之前已经创建，准备测试数据源是否正常...");
            DruidDataSource druidDataSource = (DruidDataSource) dynamicTargetDataSources2.get(datasourceId);
            boolean rightFlag = true;
            Connection connection = null;
            try {
                log.info(datasourceId+"数据源的概况->当前闲置连接数："+druidDataSource.getPoolingCount());
                long activeCount = druidDataSource.getActiveCount();
                log.info(datasourceId+"数据源的概况->当前活动连接数："+activeCount);
                if(activeCount > 0) {
                    log.info(datasourceId+"数据源的概况->活跃连接堆栈信息："+druidDataSource.getActiveConnectionStackTrace());
                }
                log.info("准备获取数据库连接...");
                connection = druidDataSource.getConnection();
                log.info("数据源"+datasourceId+"正常");
            } catch (Exception e) {
                log.error(e.getMessage(),e); //把异常信息打印到日志文件
                rightFlag = false;
                log.info("缓存数据源"+datasourceId+"已失效，准备删除...");
                if(delDatasources(datasourceId)) {
                    log.info("缓存数据源删除成功");
                } else {
                    log.info("缓存数据源删除失败");
                }
            } finally {
                if(null != connection) {
                    connection.close();
                }
            }
            if(rightFlag) {
                log.info("不需要重新创建数据源");
                return;
            } else {
                log.info("准备重新创建数据源...");
                createDataSource(dataSource);
                log.info("重新创建数据源完成");
            }
        } else {
            createDataSource(dataSource);
        }
    }

    private  void createDataSource(Partner dataSource) throws Exception {
        String datasourceId = dataSource.getCode();
        log.info("准备创建数据源"+datasourceId);
        String databasetype = dataSource.getDbType();
        String username = dataSource.getDbUser();
        String password = dataSource.getDbPwd();
        String url = dataSource.getDbUrl();
        String driveClass = "com.mysql.jdbc.Driver";
        if(testDatasource(datasourceId,driveClass,url,username,password)) {
            boolean result = this.createDataSource(datasourceId, driveClass, url, username, password, databasetype);
            if(!result) {
                log.error("数据源"+datasourceId+"配置正确，但是创建失败");
                throw new Exception("数据源"+datasourceId+"配置正确，但是创建失败");
            }
        } else {
            log.error("数据源配置有错误");
            throw new Exception("数据源配置有错误");
        }
    }

}

BaseDataSourceConfig 基础数据连接配置

@Configuration
@EnableTransactionManagement
public class BaseDataSourceConfig {
    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.driverClassName}")
    private String driverClassName;
    
    @Bean("dataSourceBase")
    public DataSource dataSourceBase() throws Exception {
        Properties props = new Properties();
        props.put("driverClassName", driverClassName);
        props.put("url", url);
        props.put("username", username);
        props.put("password", password);
        return DruidDataSourceFactory.createDataSource(props);
    }

    @Bean
    @Primary
    public DynamicDataSource dataSource(@Autowired DataSource dataSourceBase){

        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setDefaultTargetDataSource(dataSourceBase);
        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
        targetDataSources.put("dataSourceBase", dataSourceBase);
        dataSource.setTargetDataSources(targetDataSources);
        return dataSource;
    }

    @Bean("transactionManager")
    public DataSourceTransactionManager dataSourceTransactionManager(@Autowired DynamicDataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean("jdbcTemplate")
    public JdbcTemplate jdbcTemplate(@Autowired DynamicDataSource dataSource){
        return new JdbcTemplate(dataSource);
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(@Autowired DynamicDataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
         sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:com/master/*/*/mapping/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
        mapperScannerConfigurer.setBasePackage("com.master.*.*.mapper");
        return mapperScannerConfigurer;
    }

}

2.2 初始化各动态数据源

PartnerDataSourceService 主要2个地方是用：

• 项目启动时，调用initAllPartnerDataSource() 初始化所有租户的连接
• 租户有变动时，更新连接

@Service
public class PartnerDataSourceService {
    @Resource private IPartnerService partnerService;
    @Resource private DynamicDataSource dynamicDataSource;
    @Resource private MongoContext mongoContext;
    @Resource private MongoClient mongoClientBase;
    @Resource private MongoDbFactory mongoDbFactoryBase;
    @Value("${spring.data.mongodb.database}") private String baseMongoDb;

    public void initAllPartnerDataSource(){
        List<Partner> list = partnerService.getAllPartner("");
        mongoContext.createMongoFactory(MasterConst.BASE_DB_KEY, mongoClientBase, mongoDbFactoryBase, baseMongoDb);
        for (Partner partner : list) {
            try{
                initDataSource(partner);
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    public void initDataSource(Partner partner) throws Exception{
        String code = partner.getCode();
        dynamicDataSource.delDatasources(code);
        dynamicDataSource.createDataSource(
                code,
                "com.mysql.jdbc.Driver",
                partner.getDbUrl(),
                partner.getDbUser(),
                partner.getDbPwd(),
                partner.getDbType()
        );
        //附件保存在mongodb，也在这里动态连接
        //mongoContext.createMongoFactory(partner);
    }

}

2.3 nginx配置（主要部分）

访问系统时，在header记录datasource

location /base/ {
    proxy_set_header datasource base;
    proxy_pass         http://localhost:88/app/;
    proxy_cookie_path   /app /base;
}
location /a1/ {
    proxy_set_header datasource a1;
    proxy_pass         http://localhost:88/app/;
    proxy_cookie_path   /app /a1;
}
location /a2/ {
    proxy_set_header datasource a2;
    proxy_pass         http://localhost:88/app/;
    proxy_cookie_path   /app /a2;
}

2.4 spring拦截器处理

DataSourceInterceptor。 每次请求时，都把header里的datasource记录到session

@Component
public class DataSourceInterceptor implements HandlerInterceptor {
    /*
    * 用于设置数据源。
    */
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        String datasourceHeader = request.getHeader("datasource");
        Subject subject = SecurityUtils.getSubject();
        Session session = subject.getSession();
        if(!StringUtil.isEmpty(datasourceHeader)){
            session.setAttribute(SessionConst.DATASOURCE, datasourceHeader);
            request.setAttribute("ctx", "/" + datasourceHeader);
        }
        return true;
    }
}

2.5 非web访问场景的处理

部分场景不是web访问触发，比如定时任务。我在这里的处理是手动切换，程序执行完以后再清空：

DBContextHolder.setDataSource(目标源);
//do something
DBContextHolder.clearDataSource();

3. 其他

进行了复合场景测试：

• 后台不定期执行定时任务，触发切换
• web随机访问

记录定时任务和web访问的执行结果，都符合预期。

欢迎讨论