SpringBoot多数据源配置

  Druid 可以说是国内使用最广泛的数据源连接池产品

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid-spring-boot-starter</artifactId>
   <version>1.1.17</version>
</dependency>

application.yml配置

spring:
  datasource:
    pay:
      username: root
      password: root
      host: 192.168.37.133:3306
      jdbc-url: jdbc:mysql://${spring.datasource.pay.host}/${spring.datasource.pay.schema-name}?useUnicode=true&characterEncoding=utf8&useSSL=false
      schema-name: dlsms
    order:
      username: root
      password: root
      host: 192.168.37.134:3306
      jdbc-url: jdbc:mysql://${spring.datasource.order.host}/${spring.datasource.order.schema-name}?useUnicode=true&characterEncoding=utf8&useSSL=false
      schema-name: dlsms

方案一：手动切换数据源

适合没有分模块的功能，后期又需要加入多数据源

配置数据源和事务管理

import net.test.project.constants.DataSourceKey;

import com.alibaba.druid.pool.DruidDataSource;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;


@MapperScan({"net.test.project.dao.mapper.*"})
@Configuration
public class DataSourceProxyConfig {

	/**
	 * 配置默认数据源
	 * @return
	 */
    @Bean(name = "originOrder") // 声明其为Bean实例
	@ConfigurationProperties(prefix = "spring.datasource.order")
	public DataSource dataSourceMaster() {
		return new DruidDataSource();
	}

    @Bean(name = "originStorage") // 声明其为Bean实例
	@ConfigurationProperties(prefix = "spring.datasource.storage")
	public DataSource dataSourceStorage() {
		return new DruidDataSource();
	}

    @Bean(name = "originPay") // 声明其为Bean实例
	@ConfigurationProperties(prefix = "spring.datasource.pay")
	public DataSource dataSourcePay() {
		return new DruidDataSource();
	}

	/**
	 * 设置多数据源，配置动态路由数据源
	 * @return
	 */
	@Bean("dynamicDataSource")
	public DataSource dynamicDataSource(@Qualifier("originOrder") DataSource dataSourceOrder,
			@Qualifier("originStorage") DataSource dataSourceStorage,
			@Qualifier("originPay") DataSource dataSourcePay) {
		Map<Object, Object> dataSourceMap = new HashMap<>();
		dataSourceMap.put(DataSourceKey.ORDER.name(), dataSourceOrder);
		dataSourceMap.put(DataSourceKey.STORAGE.name(), dataSourceStorage);
		dataSourceMap.put(DataSourceKey.PAY.name(), dataSourcePay);

        //设置默认
		DynamicDataSource dynamicRoutingDataSource = new DynamicDataSource();
		dynamicRoutingDataSource.setDefaultTargetDataSource(dataSourceOrder);
		dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);

		return dynamicRoutingDataSource;
	}

	/**
	 * 设置会话工厂
	 *
	 * @return
	 * @throws Exception
	 */
	/*@Bean(name="sqlSessionFactory")
	public SqlSessionFactoryBean sqlSessionFactoryBean(
			@Qualifier("dynamicDataSource") DataSource dataSource) {
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(dataSource);
		return sqlSessionFactoryBean;
	}*/

    @Bean(name="sqlSessionFactory")
    @Primary
    public SqlSessionFactory mybatisSqlSessionFactoryBean (
        @Qualifier("dynamicDataSource") DataSource dataSource) throws Exception {
      MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
      sqlSessionFactoryBean.setDataSource(dataSource);
      return sqlSessionFactoryBean.getObject();
    }

	/**
	 * 配置事务管理器
	 *
	 * @return
	 */
	@Bean(name = "transactionManager")
	public PlatformTransactionManager transactionManager(@Qualifier("originOrder") DataSource dataSourceOrder,
			@Qualifier("originStorage") DataSource dataSourceStorage,
			@Qualifier("originPay") DataSource dataSourcePay) {
		return new DataSourceTransactionManager(dynamicDataSource(dataSourceOrder, dataSourceStorage, dataSourcePay));
	}
}

由于是MyBatsi-Plus，所以配的是MybatisSqlSessionFactoryBean，如果是MyBatis，则应该是SqlSessionFactoryBean

实现选择目标数据源

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {
    /**
     * 取得当前使用哪个数据源
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        log.info("当前数据源 [{}]", DynamicDataSourceContextHolder.getDataSourceType());
        return DynamicDataSourceContextHolder.getDataSourceType();
    }
}

DataSourceKey.java

public enum DataSourceKey {
  /**
   * Order data source key.
   */
  ORDER,
  /**
   * STORAGE data source key.
   */
  STORAGE,
  /**
   * PAY data source key.
   */
  PAY
}

切换数据源

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 数据源切换处理
 * 
 */
public class DynamicDataSourceContextHolder
{
    public static final Logger log = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);

    /**
     * 使用ThreadLocal维护变量，ThreadLocal为每个使用该变量的线程提供独立的变量副本，
     *  所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。
     */
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();

    /**
     * 设置数据源的变量
     */
    public static void setDataSourceType(String dsType)
    {
        log.info("切换到{}数据源", dsType);
        CONTEXT_HOLDER.set(dsType);
    }

    /**
     * 获得数据源的变量
     */
    public static String getDataSourceType()
    {
        return CONTEXT_HOLDER.get();
    }

    /**
     * 清空数据源变量
     */
    public static void clearDataSourceType()
    {
        CONTEXT_HOLDER.remove();
    }
}

如何使用

DynamicDataSourceContextHolder.setDataSourceType(DataSourceKey.ORDER.name());

多数据源下的事务问题

多数据源下使用事务时，需要在注解上明确是哪个数据源，类似下面这样，否则会报找不到事务管理实例的错误。

@Transactional(value = "transactionManager",rollbackFor = Exception.class)

方案二：自动数据源切换

组织两个配置类。原理：mybatis多数据源的原理是根据不同包，调用不同的数据源，你只需要把你的mapper.java和mapper.xml写在某个package中，springboot自动帮你实现数据源切换。注意第二个配置类不需要@Primary

• @MapperScan注解中的basePackages指向的是指定的Dao层。
• @MapperScan注解中sqlSessionFactoryRef 用来指定使用某个SqlSessionFactory来操作数据源。
• setMapperLocations指向的是操作执行数据库的Mapper层。

import com.alibaba.druid.pool.DruidDataSource;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

@Configuration
@MapperScan(basePackages = "com.example.dao.crm", sqlSessionTemplateRef = "crmSqlSessionTemplate")
public class CrmDataSourceConfig {

  @Bean(name = "crmDataSource")
  @ConfigurationProperties(prefix = "spring.datasource.crm")
  public DataSource crmDataSourceConfig() {
    return new DruidDataSource();
  }


  @Bean(name = "crmSqlSessionFactory")
  @Primary
  public SqlSessionFactory crmSqlSessionFactory(@Qualifier("crmDataSource") DataSource dataSource) throws Exception {
    MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
    sqlSessionFactory.setDataSource(dataSource);
    sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/crm/*.xml"));
    return sqlSessionFactory.getObject();
  }

  @Bean(name = "crmTransactionManager")
  @Primary
  public DataSourceTransactionManager crmTransactionManager(@Qualifier("crmDataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }

  @Bean(name = "crmSqlSessionTemplate")
  @Primary
  public SqlSessionTemplate crmSqlSessionTemplate(@Qualifier("crmSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
    return new SqlSessionTemplate(sqlSessionFactory);
  }
}

常见问题

druid抛出异常：javax.management.InstanceAlreadyExistsException: com.alibaba.druid:type=DruidDataSource,id=xxx

同一个domain里面的MBean要求name唯一，检查SpringBoot应用的application.properties等配置文件name配置