springboot2多数据源完整示例

springboot2 + mybatis + mysql + oracle + sqlserver多数据源的配置

• 相信很多朋友在开发的时候，可能会碰到需要一个项目，配置多个数据源的需求，可能是同一种数据库，也可能是不同种类的数据库。
• 这种情况，我们就需要配置多数据源对程序的支持了。
• （本例理论上支持很多种数据库，或者同种数据库配置多个库分别作数据源也许。拓展新强）

---

环境介绍

• web框架：SpringBoot2
• orm框架：Mybatis
• 数据库连接池：Druid
• 主数据源：Mysql
• 从数据源1：Oracle
• 从数据源2：SqlServer
• 运行平台：Jdk8
• 接口文档：Swagger-ui （提供伪Restful接口）
• 日志配置：Logback

---

基本思路

• 自定义多个数据源，并指定切换规则
• 引入ThreadLocal来保存和管理数据源上下文标识
• 使用AOP切面编程，根据某些自定义条件，动态切换数据源(反射)
• 访问接口测试效果

---

大致步骤

1. 创建一个拥有ThreadLocal变量的类，用来存取数据源名称

   public class JdbcContextHolder {
   
       /** 本地线程共享对象（保证在同一线程下切换后不要被其他线程修改） */
       private final static ThreadLocal<String> local = new ThreadLocal<>();
   
       public static void putDataSource(String name){
           local.set(name);
       }
   
       public static String getDataSource(){
           return local.get();
       }
   
       public static void removeDataSource(){
           local.remove();
       }
   
   }
   

2. 创建一个枚举类，用来存放每个数据源的标识符（标识符是自定义的）

   public enum DataSourceType {
       Mysql("mysql"),
       Oracle("oracle");
   
       private String name;
   
       DataSourceType(String name) {
           this.name = name;
       }
   
       public String getName() {
           return name;
       }
   
       public void setName(String name) {
           this.name = name;
       }
   }
   

3. 在启动类上，禁用springboot自动配置的数据源：(exclude = DataSourceAutoConfiguration.class)

   @SpringBootApplication(exclude = DataSourceAutoConfiguration.class) 
   @ComponentScan("com.dcm.*.**.**")
   public class MoreDsApplication extends SpringBootServletInitializer {
   
       public static void main(String[] args) {
   
           SpringApplication.run(MoreDsApplication.class, args);
           System.out.println("[---------------more_ds项目: started......]");
   
       }
   
       /** 创建一个SpringApplicationBuilder交付给springboot框架来完成初始化运行配置 */
       @Override
       protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
           return application.sources(MoreDsApplication.class);
       }
   }
   

4. 创建动态数据源类，接管springboot的数据源配置

   public class DynamicDataSource extends AbstractRoutingDataSource {
   	
   	    /** 数据源路由，此方法用于产生要选取的数据源逻辑名称 */
   	    @Override
   	    protected Object determineCurrentLookupKey() {
   	        //从共享线程中获取数据源名称
   	        return JdbcContextHolder.getDataSource();
   	    }
   	}
   

5. 在application.yml文件中加入自定义的多数据源的配置

   datasource:
     druid:
       type: com.alibaba.druid.pool.DruidDataSource
       initialSize: 1
       minIdle: 3
       maxActive: 20
       maxWait: 60000
       timeBetweenEvictionRunsMillis: 60000
       minEvictableIdleTimeMillis: 30000
       testWhileIdle: true
       testOnBorrow: false
       testOnReturn: false
       poolPreparedStatements: true
       maxPoolPreparedStatementPerConnectionSize: 20
       # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
       filters: stat,wall,slf4j
       # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
       connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
       # 合并多个DruidDataSource的监控数据
       #useGlobalDataSourceStat: true
     mysql:
       url: jdbc:mysql://192.168.0.241:3306/pmpmain?useUnicode=true&useSSL=false&characterEncoding=utf8
       username: sdcm
       password: Sdcm_123456
       driverClassName: com.mysql.jdbc.Driver
       validationQuery: select 'x'
     oracle:
       url: jdbc:oracle:thin:@192.168.0.200:1522:sdcm
       username: sdcm
       password: Sdcm123456
       driverClassName: oracle.jdbc.OracleDriver
       validationQuery: select 1 from dual
   

6. 根据application.yml的多数据源配置，初始化各数据源并指定默认数据源

   @Configuration
   public class DataSourceConfig {
   
   	private Logger logger = LoggerFactory.getLogger(this.getClass());
   
   	// -----------------------------------------mysql config-------------------------------------
   
   	@Value("${datasource.mysql.url}")
   	private String dbUrl;
   
   	@Value("${datasource.mysql.username}")
   	private String username;
   
   	@Value("${datasource.mysql.password}")
   	private String password;
   
   	@Value("${datasource.mysql.driverClassName}")
   	private String driverClassName;
   
   	@Value("${datasource.mysql.validationQuery}")
   	private String validationQuery;
   
   	@Bean(name="dataSourceMysql")
   	public DataSource dataSourceMysql(){
   		System.out.println("----------------主配" + dbUrl);
   
   		DruidDataSource datasource = new DruidDataSource();
   		datasource.setUrl(dbUrl);
   		datasource.setUsername(username);
   		datasource.setPassword(password);
   		datasource.setDriverClassName(driverClassName);
   		datasource.setValidationQuery(validationQuery);
   		setDruidOptions(datasource); // 设置druid数据源的属性
   
   		return datasource;
   	}
   
   	// -----------------------------------------oracle config-------------------------------------
   
   	@Value("${datasource.oracle.url}")
   	private String oracleUrl;
   
   	@Value("${datasource.oracle.username}")
   	private String oracleUsername;
   
   	@Value("${datasource.oracle.password}")
   	private String oraclePassword;
   
   	@Value("${datasource.oracle.driverClassName}")
   	private String oracleDriverClassName;
   
   	@Value("${datasource.oracle.validationQuery}")
   	private String oracleValidationQuery;
    
   	@Bean(name="dataSourceOracle")
   	public DataSource dataSourceOracle(){
   		System.out.println("----------------次配" + oracleUrl);
   
   		DruidDataSource datasource = new DruidDataSource();
   		datasource.setUrl(oracleUrl);
   		datasource.setUsername(oracleUsername);
   		datasource.setPassword(oraclePassword);
   		datasource.setDriverClassName(oracleDriverClassName);
   		datasource.setValidationQuery(oracleValidationQuery);
   		setDruidOptions(datasource); // 设置druid数据源的属性
   
   		return datasource;
   	}
   
   	// -----------------------------------------druid config-------------------------------------
   
   	@Value("${datasource.druid.initialSize}")
   	private int initialSize;
   
   	@Value("${datasource.druid.minIdle}")
   	private int minIdle;
   
   	@Value("${datasource.druid.maxActive}")
   	private int maxActive;
   
   	@Value("${datasource.druid.maxWait}")
   	private int maxWait;
   
   	@Value("${datasource.druid.timeBetweenEvictionRunsMillis}")
   	private int timeBetweenEvictionRunsMillis;
   
   	@Value("${datasource.druid.minEvictableIdleTimeMillis}")
   	private int minEvictableIdleTimeMillis;
   
   	@Value("${datasource.druid.testWhileIdle}")
   	private boolean testWhileIdle;
   
   	@Value("${datasource.druid.testOnBorrow}")
   	private boolean testOnBorrow;
   
   	@Value("${datasource.druid.testOnReturn}")
   	private boolean testOnReturn;
   
   	@Value("${datasource.druid.poolPreparedStatements}")
   	private boolean poolPreparedStatements;
   
   	@Value("${datasource.druid.maxPoolPreparedStatementPerConnectionSize}")
   	private int maxPoolPreparedStatementPerConnectionSize;
   
   	@Value("${datasource.druid.filters}")
   	private String filters;
   
   	@Value("{datasource.druid.connectionProperties}")
   	private String connectionProperties;
   
   	private void setDruidOptions(DruidDataSource datasource){
   		datasource.setInitialSize(initialSize);
   		datasource.setMinIdle(minIdle);
   		datasource.setMaxActive(maxActive);
   		datasource.setMaxWait(maxWait);
   		datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
   		datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
   		datasource.setTestWhileIdle(testWhileIdle);
   		datasource.setTestOnBorrow(testOnBorrow);
   		datasource.setTestOnReturn(testOnReturn);
   		datasource.setPoolPreparedStatements(poolPreparedStatements);
   		datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
   		try {
   			datasource.setFilters(filters);
   		} catch (SQLException e) {
   			logger.error("druid configuration initialization filter Exception", e);
   		}
   		datasource.setConnectionProperties(connectionProperties);
   	}
   
   
   	@Bean(name = "dynamicDataSource")
   	@Primary  // 优先使用，多数据源
   	public DataSource dataSource(){
   
   		DynamicDataSource dynamicDataSource = new DynamicDataSource();
   		DataSource mysql = dataSourceMysql();
   		DataSource oracle = dataSourceOracle();
   
   		//设置默认数据源
   		dynamicDataSource.setDefaultTargetDataSource(mysql);
   
   		//配置多个数据源
   		Map<Object,Object> map = new HashMap<>();
   		map.put(DataSourceType.Mysql.getName(),mysql);
   		map.put(DataSourceType.Oracle.getName(),oracle);
   		dynamicDataSource.setTargetDataSources(map);
   
   		return dynamicDataSource;
   	}
   
   	@Bean // 事务管理
   	public PlatformTransactionManager txManager() {
   		return new DataSourceTransactionManager(dataSource());
   	}
   
   
   	@Bean(name="druidServlet")
   	public ServletRegistrationBean druidServlet() {
   		ServletRegistrationBean reg = new ServletRegistrationBean();
   		reg.setServlet(new StatViewServlet());
   		reg.addUrlMappings("/druid/*");
   		reg.addInitParameter("allow", ""); // 白名单
   		return reg;
   	}
    
   	@Bean(name = "filterRegistrationBean")
   	public FilterRegistrationBean filterRegistrationBean() {
   		FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
   		filterRegistrationBean.setFilter(new WebStatFilter());
   		filterRegistrationBean.addUrlPatterns("/*");
   		filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
   		filterRegistrationBean.addInitParameter("profileEnable", "true");
   		filterRegistrationBean.addInitParameter("principalCookieName","USER_COOKIE");
   		filterRegistrationBean.addInitParameter("principalSessionName","USER_SESSION");
   		filterRegistrationBean.addInitParameter("DruidWebStatFilter","/*");
   		return filterRegistrationBean;
   	}
   }
   

7. 自定义注解，作为AOP切面范围的一个条件（这样更加灵活）

   @Documented
   @Retention(RetentionPolicy.RUNTIME)
   @Target(ElementType.METHOD)
   public @interface TargetDataSource {
   
       DataSourceType value() default DataSourceType.Mysql;
   }
   

8. 创建AOP切换，动态切换数据源

   @Aspect
   @Order(2)
   @Component
   public class DataSourceAspect {
   
       private Logger logger = LoggerFactory.getLogger(this.getClass());
   
       // 切入点：service类的方法上(这个包的子包及所有包的里面的以Service结尾的类的任意方法名任意参数的方法，都讲被切到)
       @Pointcut("execution(* com.dcm.more_ds..*Service..*(..))")
       public void dataSourcePointCut(){
           System.out.println("dataSourcePointCut service");
       }
   
       @Before("dataSourcePointCut()")
       private void before(JoinPoint joinPoint){
           Object target = joinPoint.getTarget();
           String method = joinPoint.getSignature().getName();
           Class<?> classz = target.getClass();
           Class<?>[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getMethod().getParameterTypes();
           try {
               Method m = classz.getMethod(method,parameterTypes);
   
               // 如果 m 上存在切换数据源的注解，则根据注解内容进行数据源切换
               if (m != null && m.isAnnotationPresent(TargetDataSource.class)){
                   TargetDataSource data = m.getAnnotation(TargetDataSource.class);
                   JdbcContextHolder.putDataSource(data.value().getName());
                   logger.info("》》》》》》》 current thread " + Thread.currentThread().getName() + " add 【 " + data.value().getName() + " 】 to ThreadLocal");
               } else { // 如果不存在，则使用默认数据源
                   logger.info("》》》》》》》 use default datasource");
               }
           }catch (Exception e){
               e.printStackTrace();
           }
       }
   
       // 执行完切面后，将线程共享中的数据源名称清空
       @After("dataSourcePointCut()")
       public void after(JoinPoint joinPoint){
           JdbcContextHolder.removeDataSource();
       }
   
   }
   

9. 在需要切换数据源的dao interface或者service interface上(具体看你切面切的范围)，加上自定义的注解（这里随便列举两个）

   @Transactional(readOnly = true)
   	@TargetDataSource(DataSourceType.Oracle)
   	public List<T> list(Integer startNum, Integer limit) {
   		setDao();
   		return baseDao.selectWithPage(Arrays.asList(startNum, limit));
   	}
   
   	@TargetDataSource(DataSourceType.Oracle)
   	public List<T> selectByRequestBodyIds(@RequestBody List<P> ids) {
   		return selectByPrimaryKeys(ids);
   	}
   	// .......more
   

10. 配置mybatis

    @Configuration
    // 扫描指定包下的dao，这样就不用每个dao interface上面写@Mapper了
    @MapperScan(basePackages = "com.dcm.more_ds.dao.*.**")
    public class MyBatisConf {
    
        @Autowired
        @Qualifier("dynamicDataSource")
        private DataSource dataSource;
    
        @Bean
        public SqlSessionFactory sqlSessionFactory() {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
    
            // 分页插件
            PageHelper pageHelper = new PageHelper();
            Properties properties = new Properties();
            properties.setProperty("reasonable", "true");
            properties.setProperty("supportMethodsArguments", "true");
            properties.setProperty("returnPageInfo", "check");
            properties.setProperty("params", "count=countSql");
            properties.setProperty("autoRuntimeDialect","true"); // 运行时根据数据源自动选择方言 （这句很重要）
            pageHelper.setProperties(properties);
    
            // 添加插件
            bean.setPlugins(new Interceptor[] { pageHelper });
    
            // 添加XML目录
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            try {
                bean.setMapperLocations(resolver.getResources("classpath:com/dcm/more_ds/dao/*/*.xml"));
                bean.setConfigLocation(resolver.getResource("classpath:mybatis-conf.xml"));
                return bean.getObject();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
    
        @Bean(name = "sqlSessionTemplate")
        public SqlSessionTemplate sqlSessionTemplate() {
            return new SqlSessionTemplate(sqlSessionFactory());
        }
    
    }
    

---

源码地址

springboot2 + mybatis + mysql + oracle多数据源的配置

• 源码中用到了AOP的其它应用：统一入参校验，统一异常处理。接口是伪Restful的。
• 源码中的mapper.xml，dao，service，serviceImpl，controller都是代码生成器生成的。(本贴破10w阅读量就分享代码生成器)

---

文末寄语：

• 转载请注明出处
• 感谢