spring mybatis实现多数据源

 

目录

 第一种：自定义多数据源

第二种：框架集成的多数据源

---

在实际应用场景中，一个项目难免用到多数据源，下面说一下Spring中如何配置多数据源

 第一种：自定义多数据源

1.application.yml中

master:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://127.0.0.1:3306/spider?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    initialSize: 1
    minIdle: 1
    maxActive: 20
    maxWait: 60000

second:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:sqlserver://139.198.16.175:1637;databaseName=LawData;
    username: zhaojingyang
    password: 123456
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    initialSize: 1
    minIdle: 1
    maxActive: 20
    maxWait: 60000

2.手动创建两个数据源的config配置文件

package com.bdyh.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
    static final String PACKAGE = "com.bdyh.dao.master";
    static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";

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

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

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

    @Value("${master.datasource.driver-class-name}")
    private String driverClass;

    @Bean(name = "masterDataSource")
    @Primary
    public DataSource masterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }

    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }


}

package com.bdyh.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = SecondDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "secondSqlSessionFactory")
public class SecondDataSourceConfig {

    // 精确到 cluster 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.bdyh.dao.second";
    static final String MAPPER_LOCATION = "classpath:mapper/second/*.xml";

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

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

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

    @Value("${second.datasource.driver-class-name}")
    private String driverClass;

    @Bean(name = "secondDataSource")
    public DataSource clusterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "secondTransactionManager")
    public DataSourceTransactionManager clusterTransactionManager() {
        return new DataSourceTransactionManager(clusterDataSource());
    }

    @Bean(name = "secondSqlSessionFactory")
    public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("secondDataSource") DataSource clusterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(clusterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(SecondDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

}

3创建两个dao和xml

    

①dao主要注意目录位置就行

@Repository
@Mapper
public interface TestMapper {

}

②xml注意命名空间

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.bdyh.dao.master.TestMapper">

    <select id="getSsyjFromJiangsu" resultType="com.bdyh.bean.JiangSuOriginBean">
        select  ids,ssyj from powerlist_jiangsu where ids between #{begin} and #{end};
    </select>
</mapper>

ok，结束！！！

第二种：框架集成的多数据源

1、导入pom

<!-- 多数据源 -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
</dependency>

<!-- druid 官方 starter -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
</dependency>

2、修改配置文件

spring:
  datasource:
    dynamic:
      primary: master   #设置默认的数据源或者数据源组，默认master
      strict: false     #是否严格匹配数据源，默认false，true未匹配到指定数据源时抛出异常，true则使用默认数据源
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/test?Unicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
          username: root
          password: 123456
        slave:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/test?Unicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
          username: root
          password: 123456

3、使用@DS注解指定使用数据源

@DS的使用说明	备注
没有@DS	没有@DS，则匹配使用默认的数据源，master
有@DS	有@DS，则匹配指定的数据源，示例@DS("mysql")，使用mysql数据源

@DS使用的地方	备注
Service的实现类上、方法上	在类上使用，代表整个实现类的数据源指向某一个，在方法上使用时，代表这个方法的数据源指向某一个；在方法上的优先级 > 在类上的优先级
Mapper的类上、方法上

4、@DS的使用示例

@Mapper
@DS("slave")
public interface TSysUserMapper extends BaseMapperX<TSysUser> {

}

5、@DS失效情况

①、使用动态数据源（@DS）时，@Transactional使用可能会照成@DS失效。

解决方案：1.去掉事务（不建议）
2.@DS切换数据源的方法添加事务传播属性@Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class)
3.去掉@DS切换数据源方法的事务，主方法用@DSTransactional注解。

②、@DS注解加到mapper接口、service接口、service方法里都不生效
解决方案：添加到service实现类或者实现类里具体的方法上。

③、在同一个实现类中，一个非DS注解的常规方法里调用@DS注解的方法可能失效
解决方案：将该DS注解方法定义在不同的类中，通过bean注入的方式调用