Mybatis多数据源的切换——AbstractRoutingDataSource

太过爱你忘了你带给我的痛 2023-06-18 13:55 21阅读 0赞

Spring数据源路由核心类***AbstractRoutingDataSource***，API文档解释如下：

> *Abstract `javax.sql.DataSource` implementation that routes `getConnection()` calls to one of various target DataSources based on a lookup key. The latter is usually (but not necessarily) determined through some thread-bound transaction context.*
> 
> *抽象javax.sql.DataSource实现，该实现将基于查询键的getConnection()调用路由到各种目标DataSource之一。 后者通常（但不是必须）通过某些线程绑定的事务上下文来确定。*

## 实现思路 ##

### 一、application.xml配置 ###

##druid和数据库配置
    spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
    #primary：前端埋点数据相关
    spring.datasource.druid.primary.url=jdbc:mysql://100.11.4.16:3306/spider?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&connectTimeout=5000
    spring.datasource.druid.primary.username=root
    spring.datasource.druid.primary.password=123456
    spring.datasource.druid.primary.driverClassName=com.mysql.jdbc.Driver
    #vice：数据同步任务监控相关
    spring.datasource.druid.vice.url=jdbc:mysql://100.11.4.16:3306/spider_monitor?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&connectTimeout=5000
    spring.datasource.druid.vice.username=root
    spring.datasource.druid.vice.password=123456
    spring.datasource.druid.vice.driverClassName=com.mysql.jdbc.Driver
    #公共配置
    spring.datasource.druid.initial-size=10
    spring.datasource.druid.max-active=100
    spring.datasource.druid.min-idle=30
    spring.datasource.druid.max-wait=30000
    spring.datasource.druid.pool-prepared-statements=true
    spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
    spring.datasource.druid.time-between-eviction-runs-millis=60000
    spring.datasource.druid.min-evictable-idle-time-millis=300000
    spring.datasource.druid.max-evictable-idle-time-millis=60000
    spring.datasource.druid.validation-query=SELECT 1 FROM DUAL
    #spring.datasource.druid.validation-query-timeout=5000
    spring.datasource.druid.test-on-borrow=false
    spring.datasource.druid.test-on-return=false
    spring.datasource.druid.test-while-idle=true
    spring.datasource.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    #filters: #配置多个英文逗号分隔(统计，sql注入，log4j过滤)
    spring.datasource.druid.filters=stat,wall
    spring.datasource.druid.stat-view-servlet.enabled=true
    spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
    
    #mybatis配置
    mybatis.mapper-locations=classpath:mapper/*Mapper.xml
    mybatis.type-aliases-package=com.cjia.spidercommon.model
    #mybatis.config-location=classpath:mybatis.cfg.xml

### **二、定义数据源key** ###

我这里的服务需要操作两个数据源，一个主一个副，定义如下：

package com.cjia.spidersink.jdbc;
    
    /**
     * 多数据源的key
     */
    public enum DatabaseType {
        spiderprimary, spidervice
    }

###  三、定义数据源key持有类 ###

package com.cjia.spidersink.jdbc;
    
    /**
     * 数据源key持有类
     */
    public class DatabaseContextHolder {
    
        private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<>();
    
        public static void setDatabaseType(DatabaseType type) {
            contextHolder.set(type);
        }
    
        public static DatabaseType getDatabaseType() {
            return contextHolder.get();
        }
    }

### 四、定义动态数据源 ###

继承Spring数据源路由核心类***AbstractRoutingDataSource***。

package com.cjia.spidersink.jdbc;
    
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    
    /**
     * 固定动态多数据源
     */
    public class DynamicDatasource extends AbstractRoutingDataSource {
    
        @Override
        protected Object determineCurrentLookupKey() {
            return DatabaseContextHolder.getDatabaseType();
        }
    }

### 五、数据源配置 ###

这里使用了druid数据库连接池，配置如下：

package com.cjia.spidersink.config;
    
    import java.util.HashMap;
    import java.util.Map;
    
    import javax.sql.DataSource;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    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 org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.transaction.support.TransactionTemplate;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import com.cjia.spidersink.jdbc.DatabaseType;
    import com.cjia.spidersink.jdbc.DynamicDatasource;
    
    /**
     * Druid数据库连接池配置文件
     */
    @Configuration
    public class DruidConfig {
        private static final Logger logger = LoggerFactory.getLogger(DruidConfig.class);
    
        @Value("${spring.datasource.druid.primary.url}")
        private String dbUrl4Primary;
    
        @Value("${spring.datasource.druid.primary.username}")
        private String username4Primary;
    
        @Value("${spring.datasource.druid.primary.password}")
        private String password4Primary;
    
        @Value("${spring.datasource.druid.primary.driverClassName}")
        private String driverClassName4Primary;
    
        @Value("${spring.datasource.druid.vice.url}")
        private String dbUrl4Vice;
    
        @Value("${spring.datasource.druid.vice.username}")
        private String username4Vice;
    
        @Value("${spring.datasource.druid.vice.password}")
        private String password4Vice;
    
        @Value("${spring.datasource.druid.vice.driverClassName}")
        private String driverClassName4Vice;
    
        @Value("${spring.datasource.druid.initial-size}")
        private int initialSize;
    
        @Value("${spring.datasource.druid.max-active}")
        private int maxActive;
    
        @Value("${spring.datasource.druid.min-idle}")
        private int minIdle;
    
        @Value("${spring.datasource.druid.max-wait}")
        private int maxWait;
    
        @Value("${spring.datasource.druid.pool-prepared-statements}")
        private boolean poolPreparedStatements;
    
        @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
        private int maxPoolPreparedStatementPerConnectionSize;
    
        @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
        private int timeBetweenEvictionRunsMillis;
    
        @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
        private int minEvictableIdleTimeMillis;
    
        @Value("${spring.datasource.druid.max-evictable-idle-time-millis}")
        private int maxEvictableIdleTimeMillis;
    
        @Value("${spring.datasource.druid.validation-query}")
        private String validationQuery;
    
        @Value("${spring.datasource.druid.test-while-idle}")
        private boolean testWhileIdle;
    
        @Value("${spring.datasource.druid.test-on-borrow}")
        private boolean testOnBorrow;
    
        @Value("${spring.datasource.druid.test-on-return}")
        private boolean testOnReturn;
    
        @Value("${spring.datasource.druid.filters}")
        private String filters;
    
        @Value("{spring.datasource.druid.connection-properties}")
        private String connectionProperties;
    
        /**
         * Druid 连接池配置 主数据源:前端埋点数据相关
         */
        @Bean
        @Primary
        public DruidDataSource dataSource4Primary() {
            DruidDataSource datasource = new DruidDataSource();
            datasource.setUrl(dbUrl4Primary);
            datasource.setUsername(username4Primary);
            datasource.setPassword(password4Primary);
            datasource.setDriverClassName(driverClassName4Primary);
            datasource.setInitialSize(initialSize);
            datasource.setMinIdle(minIdle);
            datasource.setMaxActive(maxActive);
            datasource.setMaxWait(maxWait);
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setMaxEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setValidationQuery(validationQuery);
            datasource.setTestWhileIdle(testWhileIdle);
            datasource.setTestOnBorrow(testOnBorrow);
            datasource.setTestOnReturn(testOnReturn);
            datasource.setPoolPreparedStatements(poolPreparedStatements);
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            try {
                datasource.setFilters(filters);
            } catch (Exception e) {
                logger.error("Primary druid configuration initialization filter", e);
            }
            datasource.setConnectionProperties(connectionProperties);
            return datasource;
        }
    
        /**
         * Druid 连接池配置 副数据源:数据同步任务监控相关
         */
        @Bean
        public DruidDataSource dataSource4Vice() {
            DruidDataSource datasource = new DruidDataSource();
            datasource.setUrl(dbUrl4Vice);
            datasource.setUsername(username4Vice);
            datasource.setPassword(password4Vice);
            datasource.setDriverClassName(driverClassName4Vice);
            datasource.setInitialSize(initialSize);
            datasource.setMinIdle(minIdle);
            datasource.setMaxActive(maxActive);
            datasource.setMaxWait(maxWait);
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setMaxEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setValidationQuery(validationQuery);
            datasource.setTestWhileIdle(testWhileIdle);
            datasource.setTestOnBorrow(testOnBorrow);
            datasource.setTestOnReturn(testOnReturn);
            datasource.setPoolPreparedStatements(poolPreparedStatements);
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            try {
                datasource.setFilters(filters);
            } catch (Exception e) {
                logger.error("Vice druid configuration initialization filter", e);
            }
            datasource.setConnectionProperties(connectionProperties);
            return datasource;
        }
    
        @Bean
        public DynamicDatasource datasource(@Qualifier("dataSource4Primary") DataSource primaryDataSource, @Qualifier("dataSource4Vice") DataSource viceDataSource) {
    	Map<Object, Object> targetDataSources = new HashMap<>();
    	targetDataSources.put(DatabaseType.spiderprimary, primaryDataSource);
    	targetDataSources.put(DatabaseType.spidervice, viceDataSource);
    
    	DynamicDatasource datasource = new DynamicDatasource();
    	// 该方法是AbstractRoutingDataSource的方法
    	datasource.setTargetDataSources(targetDataSources);
    	// 默认的datasource设置为primary
    	datasource.setDefaultTargetDataSource(primaryDataSource);
    	return datasource;
        }
    
        @Bean
        public SqlSessionFactory sqlSessionFactory(@Autowired DynamicDatasource ds) throws Exception {
        	SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
        	fb.setDataSource(ds);
        	fb.setTypeAliasesPackage("com.cjia.spidercommon.model");
        	fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*Mapper.xml"));
        	return fb.getObject();
        }
    
        /**
         * 配置 Druid 监控界面
         */
        @Bean
        public ServletRegistrationBean statViewServlet() {
            ServletRegistrationBean srb =
                    new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
            //设置控制台管理用户
            srb.addInitParameter("loginUsername","root");
            srb.addInitParameter("loginPassword","root");
            //是否可以重置数据
            srb.addInitParameter("resetEnable","false");
            return srb;
        }
    
        @Bean
        public FilterRegistrationBean statFilter() {
            //创建过滤器
            FilterRegistrationBean frb =
                    new FilterRegistrationBean(new WebStatFilter());
            //设置过滤器过滤路径
            frb.addUrlPatterns("/*");
            //忽略过滤的形式
            frb.addInitParameter("exclusions",
                    "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return frb;
        }
    }

### 六、dao层切换数据源 ###

在各个dao类中，mapper接口操作之前，切换到对应的数据源即可。

package com.cjia.spidersink.dao;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;
    
    import com.cjia.spidercommon.model.MonitorHistory;
    import com.cjia.spidersink.jdbc.DatabaseContextHolder;
    import com.cjia.spidersink.jdbc.DatabaseType;
    import com.cjia.spidersink.mapper.MonitorHistoryMapper;
    
    @Repository
    public class MonitorHistoryDao {
    
        @Autowired
        private MonitorHistoryMapper monitorHistoryMapper;
    
        public void update(MonitorHistory history) {
            DatabaseContextHolder.setDatabaseType(DatabaseType.spidervice);
            monitorHistoryMapper.update(history);
        }
    }

至此。