Mybatis多数据源配置

mybatis多数据源配置方式一

项目结构

  • [ ] 数据源配置

    • application.yml 配置

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      47
      48
      49
      50
      51
      52
      53
      54
      55
      server:
        port: 8000
      ### 单数据源自动配置
      spring:
       datasource:
         druid:
           url: jdbc:mysql://localhost:3306/db_master?useSSL=false&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true
           username: root
           password: 123456
      #### 多数据源配置
      multiple:
        datasource:
          master:
            driver-class-name: com.mysql.jdbc.Driver
            url: jdbc:mysql://localhost:3306/master?useSSL=false&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true
            username: root
            password: 123456

          one:
            driver-class-name: com.mysql.jdbc.Driver
            url: jdbc:mysql://localhost:3306/one?useSSL=false&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true
            username: root
            password: 123456
          two:
            driver-class-name: com.mysql.jdbc.Driver
            url: jdbc:mysql://localhost:3306/two?useSSL=false&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true
            username: root
            password: 123456
            ###################以下为druid增加的配置###########################
            type: com.alibaba.druid.pool.DruidDataSource
            # 下面为连接池的补充设置，应用到上面所有数据源中
            # 初始化大小，最小，最大
            initialSize: 5
            minIdle: 5
            maxActive: 20
            # 配置获取连接等待超时的时间
            maxWait: 60000
            # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
            timeBetweenEvictionRunsMillis: 60000
            # 配置一个连接在池中最小生存的时间，单位是毫秒
            minEvictableIdleTimeMillis: 300000
            validationQuery: SELECT 1 FROM DUAL
            testWhileIdle: true
            testOnBorrow: false
            testOnReturn: false
            # 打开PSCache，并且指定每个连接上PSCache的大小
            poolPreparedStatements: true
            maxPoolPreparedStatementPerConnectionSize: 20
            # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙，此处是filter修改的地方
            filters:
              commons-log.connection-logger-name: stat,wall,log4j
            # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
            connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
            # 合并多个DruidDataSource的监控数据
            useGlobalDataSourceStat: true

    • master 数据源配置

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      /**
       * @author liuwang
       * @date 2018/6/30
       */
      @Configuration
      @MapperScan(basePackages = "com.share.mapper.master",sqlSessionFactoryRef = "masterSqlSessionFactory")
      public class MasterDataSourceConfiguration {

          @Bean
          @ConfigurationProperties(prefix = "multiple.datasource.master")
          public DataSource masterDataSource(){
              return DruidDataSourceBuilder.create().build();
          }

          @Bean(name = "masterSqlSessionFactory")
          @Primary
          public SqlSessionFactory sqlSessionFactoryBean() throws Exception {
              SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
              sqlSessionFactoryBean.setDataSource(masterDataSource());
              sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/master/*.xml"));
              return sqlSessionFactoryBean.getObject();
          }
      }

    • 从数据源配置

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      /**
       * @author liuwang
       * @date 2018/6/30
       */
      @Configuration
      @MapperScan(basePackages = "com.share.mapper.one",sqlSessionFactoryRef = "oneSqlSessionFactory")
      public class OneDataSourceConfiguration {

          @Bean
          @ConfigurationProperties(prefix = "multiple.datasource.one")
          public DataSource oneDataSource(){
              return DruidDataSourceBuilder.create().build();
          }

          @Bean(name = "oneSqlSessionFactory")
          public SqlSessionFactory sqlSessionFactory() throws Exception {
              SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
              sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/one/*.xml"));
              sqlSessionFactoryBean.setDataSource(oneDataSource());
              return sqlSessionFactoryBean.getObject();
          }

      }

    • [ ] 调用

    • 注入不同的mapper调用不同的数据源

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      /**
       * @author liuwang
       * @date 2018/6/26
       */
      @Service("studentServiceImpl")
      public class StudentServiceImpl implements IStudentService {


          @Autowired
          @Qualifier("master")
          private com.share.mapper.master.StudentMapper master;
          @Autowired
          @Qualifier("one")
          private com.share.mapper.one.StudentMapper one;
          @Autowired
          @Qualifier("two")
          private com.share.mapper.two.StudentMapper two;


          @Override
      //    @TargetDataSource(DataSourceKey.MASTER)
          public List<Student> selectMaster() {
              return master.listAll();
          }

          @Override
      //    @TargetDataSource(DataSourceKey.ONE)
          public List<Student> selectOne() {
              return one.listAll();
          }

          @Override
      //    @TargetDataSource(DataSourceKey.TWO)
          public List<Student> selectTwo() {
              return two.listAll();
          }

说明

  1. 先看看传统的Spring的xml是怎么配置数据源，SessionFactory，Mapper

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    <!-- dataSource 配置-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
            destroy-method="close">
            ...
    </bean>
    <!-- SessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource" />
            <property name="configLocation" value="classpath:mybatis-config.xml" />
    </bean>
    <!-- mapper 配置 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
          <property name="basePackage" value="org.mybatis.spring.sample.mapper" />
          <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
    </bean>

    可以看出SessionFactory依赖于dataSource;Mapper 依赖于SessionFactory

    所以要实现让不同的mapper指定不同的数据源：创建多个SessionFactory，让不同的SessionFactory依赖不同的数据源；然后让Mapper依赖不同的SessionFactory就实现了Mapper指定数据源

  2. Spring boot 怎么为mapper指定数据源

    本文在配置了多个数据源，多个SessionFactory。达到了为不同SessionFactory指定不同数据源。那么怎么为不同的Mapper指定不同SessionFactory呢？

    通过注解@MapperScan() 指定basepackage和SessionFactory

  3. 避免spring boot autoconfigure错误，设置默认数据库使用@Primary

    spring-boot-autoconfigure包下有个DataSourceAutoConfiguration自动配置类

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    @Configuration
    @ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
    @EnableConfigurationProperties(DataSourceProperties.class)
    @Import({ Registrar.class, DataSourcePoolMetadataProvidersConfiguration.class })
    public class DataSourceAutoConfiguration {

    	private static final Log logger = LogFactory
    			.getLog(DataSourceAutoConfiguration.class);

    	@Bean
    	@ConditionalOnMissingBean
    	public DataSourceInitializer dataSourceInitializer(DataSourceProperties properties,
    			ApplicationContext applicationContext) {
    		return new DataSourceInitializer(properties, applicationContext);
    	}
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    @PostConstruct
    	public void init() {
    		if (!this.properties.isInitialize()) {
    			logger.debug("Initialization disabled (not running DDL scripts)");
    			return;
    		}
    		if (this.applicationContext.getBeanNamesForType(DataSource.class, false,
    				false).length > 0) {
                //这一行代码会在配置多个datasource时报错。
    			this.dataSource = this.applicationContext.getBean(DataSource.class);
    		}
    		if (this.dataSource == null) {
    			logger.debug("No DataSource found so not initializing");
    			return;
    		}
    		runSchemaScripts();
    	}

mybatis多数据源配置方式二

项目结构

  • [ ] 配置

    • application.yml和上面一样

    • 动态数据源配置

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      47
      48
      49
      50
      51
      52
      53
      54
      55
      56
      57
      58
      59
      60
      61
      62
      63
      64
      65
      66
      67
      68
      /**
       * @author liuwang
       * @date 2018/6/26
       */
      @MapperScan(basePackages = "com.share.multipledatasourcedynamic.mapper")
      @Configuration
      public class DynamicDataSourceConfiguration {
          @Bean
          @Primary
          @ConfigurationProperties(prefix = "multiple.datasource.master")
          public DataSource master() {
              return DruidDataSourceBuilder.create().build();
          }

          @Bean
          @ConfigurationProperties(prefix = "multiple.datasource.one")
          public DataSource one() {
              return DruidDataSourceBuilder.create().build();
          }

          @Bean
          @ConfigurationProperties(prefix = "multiple.datasource.two")
          public DataSource two() {
              return DruidDataSourceBuilder.create().build();
          }



          /**
           * 核心动态数据源
           *
           * @return 数据源实例
           */
          @Bean
          public DataSource dynamicDataSource() {
              DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
              dataSource.setDefaultTargetDataSource(master());
              Map<Object, Object> dataSourceMap = new HashMap<>(10);
              dataSourceMap.put(DataSourceKey.MASTER, master());
              dataSourceMap.put(DataSourceKey.ONE, one());
              dataSourceMap.put(DataSourceKey.TWO, two());
              dataSource.setTargetDataSources(dataSourceMap);
              return dataSource;
          }

          @Bean
          public SqlSessionFactory sqlSessionFactory() throws Exception {
              SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
              sqlSessionFactoryBean.setDataSource(dynamicDataSource());
              //此处设置为了解决找不到mapper文件的问题
              sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
              return sqlSessionFactoryBean.getObject();
          }

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

          /**
           * 事务管理
           *
           * @return 事务管理实例
           */
          @Bean
          public PlatformTransactionManager platformTransactionManager() {
              return new DataSourceTransactionManager(dynamicDataSource());
          }

  • [ ] 实现动态切换

    1. 继承AbstractRoutingDataSource类

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      /**
       * @author liuwang
       * @date 2018/6/26
       */
      public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
          private static final Logger logger = LoggerFactory.getLogger(DynamicRoutingDataSource.class);

          @Override
          protected Object determineCurrentLookupKey() {
              logger.info("当前数据源:{}"+ DynamicDataSourceContextHolder.getKey());
              return DynamicDataSourceContextHolder.getKey();
          }
      }

    2. 编写DynamicDataSourceContextHolder类进行数据库切换

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      /**
       * @author liuwang
       * @date 2018/6/26
       */
      public class DynamicDataSourceContextHolder {

          private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);

          private static final ThreadLocal<DataSourceKey> currentDataSource = new ThreadLocal<>();

          public static void clear(){
              currentDataSource.remove();
          }

          public static DataSourceKey getKey(){
              return currentDataSource.get();
          }

          public static void setKey(DataSourceKey dataSouceKey){
              currentDataSource.set(dataSouceKey);
          }
      }

    3. 定义数据源的枚举

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      /**
       * @author liuwang
       * @date 2018/6/26
       */
      public enum DataSourceKey {
          MASTER("默认使用数据库"),
          ONE("数据库One"),
          TWO("数据库Two");
          private String description;

          private DataSourceKey(String description){
              this.description = description;
          }

          public static List<DataSourceKey> listAll(){
              List<DataSourceKey> list = new ArrayList<DataSourceKey>(10);
              list.addAll(Arrays.asList(DataSourceKey.values()));
              return  list;
          }
      }

  • [ ] AOP+注解的方式指定数据源

    1. 注解

      1
      2
      3
      4
      5
      6
      7
      8
      9
      /**
       * @author liuwang
       * @date 2018/6/27
       */
      @Target({ElementType.TYPE,ElementType.METHOD})
      @Retention(RetentionPolicy.RUNTIME)
      public @interface TargetDataSource {
          DataSourceKey value() default DataSourceKey.MASTER;
      }

    2. 切面和切点

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      /**
       * @author liuwang
       * @date 2018/6/27
       */
      @Aspect
      @Order(-1)
      @Component
      public class DynamicDataSourceAspect {
          private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);

          @Pointcut("execution(* com.share.multipledatasourcedynamic.service.*.*(..))")
          public void dataSourcepoint(){

          }

          /**
           *
           * @param joinPoint
           * @param targetDataSource
           */
          @Before("@annotation(targetDataSource)")
          public void doBefore(JoinPoint joinPoint, TargetDataSource targetDataSource){
              logger.info(String.format("设置数据源为  %s", targetDataSource.value()));
              DynamicDataSourceContextHolder.setKey(targetDataSource.value());
          }

          /**
           *
           * @param joinPoint
           * @param targetDataSource
           */
          @After("@annotation(targetDataSource)")
          public void doAfter(JoinPoint joinPoint, TargetDataSource targetDataSource) {
              logger.info(String.format("当前数据源  %s  执行清理方法",targetDataSource.value()));
              DynamicDataSourceContextHolder.clear();
          }
      }

  • [ ] 调用

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    /**
     * @author liuwang
     * @date 2018/6/26
     */
    @Service("studentServiceImpl")
    public class StudentServiceImpl implements IStudentService {

        @Autowired
        private StudentMapper studentMapper;


        @Override
        @TargetDataSource(DataSourceKey.MASTER)
        public List<Student> selectMaster() {
            return studentMapper.listAll();
        }

        @Override
        @TargetDataSource(DataSourceKey.ONE)
        public List<Student> selectOne() {
            return studentMapper.listAll();
        }

        @Override
        @TargetDataSource(DataSourceKey.TWO)
        public List<Student> selectTwo() {
            return studentMapper.listAll();
        }
    }

说明

  1. SessionFactory 依赖动态数据源

    在SessionFactory配置中，让SessionFactory依赖DynamicRoutingDataSource这个类，而这个类继承了

    AbstractRoutingDataSource（Spring提供的）重写了determineCurrentLookupKey()，通过返回不同的key来指定SessionFactory依赖哪个数据库。

  2. DynamicRoutingDataSource是如何通过重写determineCurrentLookupKey()放回不同的key，来为SessionFactory指定不同的数据源呢？

    1. 首先来看一下AbstractRoutingDataSource这个类

      1
      2
      3
      public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
      	private Map<Object, Object> targetDataSources;
      	private Object defaultTargetDataSource;

      在配置DynamicRoutingDataSource给targetDataSources和defaultTargetDataSource赋值

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      @Bean
         public DataSource dynamicDataSource() {
             DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
             dataSource.setDefaultTargetDataSource(master());
             Map<Object, Object> dataSourceMap = new HashMap<>(10);
             dataSourceMap.put(DataSourceKey.MASTER, master());
             dataSourceMap.put(DataSourceKey.ONE, one());
             dataSourceMap.put(DataSourceKey.TWO, two());
             dataSource.setTargetDataSources(dataSourceMap);
             return dataSource;
         }

    2. 然后在看一下DynamicRoutingDataSource类重写的determineCurrentLookupKey()做了什么

      1
      protected abstract Object determineCurrentLookupKey();

      这是一个抽象方法什么都没有做，看来是专门设计来让子类来实现的。(模板方法的设计模式)

    3. determineTargetDataSource()

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      protected DataSource determineTargetDataSource() {
      		Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
      		Object lookupKey = determineCurrentLookupKey();
      		DataSource dataSource = this.resolvedDataSources.get(lookupKey);
      		if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
      			dataSource = this.resolvedDefaultDataSource;
      		}
      		if (dataSource == null) {
      			throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
      		}
      		return dataSource;
      	}

      在determineTargetDataSource()这个方法中调用了determineCurrentLookupKey()这个方法。

      1
      private Map<Object, DataSource> resolvedDataSources;

      this.resolvedDataSources.get(lookupKey)通过determineCurrentLookupKey()的返回值，返回了一个datasource.

    4. 再来看看resolvedDataSources做了什么？

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      @Override
      	public void afterPropertiesSet() {
      		if (this.targetDataSources == null) {
      			throw new IllegalArgumentException("Property 'targetDataSources' is required");
      		}
      		this.resolvedDataSources = new HashMap<Object, DataSource>(this.targetDataSources.size());
      		for (Map.Entry<Object, Object> entry : this.targetDataSources.entrySet()) {
      			Object lookupKey = resolveSpecifiedLookupKey(entry.getKey());
      			DataSource dataSource = resolveSpecifiedDataSource(entry.getValue());
      			this.resolvedDataSources.put(lookupKey, dataSource);
      		}
      		if (this.defaultTargetDataSource != null) {
      			this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
      		}
      	}

      resolvedDataSources是通过TargetDataSource来初始化的。和上面指定TargetDataSource的值是不是联系到一起了

    5. determineTargetDataSource()做了什么？

      1
      2
      3
      4
      @Override
      public Connection getConnection() throws SQLException {
         return determineTargetDataSource().getConnection();
      }

      AbstractRoutingDataSource extends AbstractDataSource implements DataSource .

      AbstractRoutingDataSource 重写了DataSource 的getConnection()，getConnection(String username, String password)方法。

    6. 关于AOP+注解指定数据源

      具体请看aop章节

    7. DynamicDataSourceContextHolder类干什么？

      里面提供了getKey的具体逻辑。并使用了 private static final ThreadLocal currentDataSource = new ThreadLocal<>();来避免多线程并发的Key值线程安全问题。

赏

谢谢你请我吃糖果

支付宝
微信

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

Charle，<br>记录日常点滴，但愿以后有用！</br>