1 背景

springboot配置mybatisPlus，mybatisPlus配置一主二从数据源。

数据源切换，通过mybatis的拦截器结合本地线程变量，自动切换数据源。

切换原则：不在事务中的查询，使用从库，其他情况均使用主库

流程图：

mybatisPlus自动切换数据源.jpg

2 版本

• JDK：1.8
• mysql：5.6
• springboot：2.2.9.RELEASE
• mybatis-plus：3.4.0

3 配置

路径说明：

3.1 pom配置

3.1.1 父类pom

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.9.RELEASE</version>
    <relativePath/>
</parent>

3.1.2 pom依赖

<!-- ==========mybatisPlus配置========== -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.30</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.23</version>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
</dependency>
<!-- ==========springboot-web依赖========== -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
</dependencies>
<!-- ==========其他依赖========== -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
    <scope>provided</scope>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.11</version>
</dependency>

3.1.3 插件

保证可以在src的包里配置mapper的xml文件，否则需要再resource文件夹中配置mapper的xml。

 <build>
     <plugins>
         <plugin>
             <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-maven-plugin</artifactId>
         </plugin>
     </plugins>
     <resources>
         <resource>
             <directory>src/main/java</directory>
             <includes>
                 <include>**/*.*</include>
             </includes>
         </resource>
         <resource>
             <directory>src/main/resources</directory>
             <includes>
                 <include>**/**</include>
             </includes>
         </resource>
     </resources>
</build>

3.2 application.yml配置

#==========自定义mybatisPlus数据源========== 
datasource:
  #数据源类型
  type: com.alibaba.druid.pool.DruidDataSource
  #从库节点数量
  slaveNodeCount: 2
  #主库
  master:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test01?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=round&useAffectedRows=true
    username: root
    password: root
    pool-config:
      initialSize: 100
      minIdle: 1
      maxActive: 50
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: false
      maxPoolPreparedStatementPerConnectionSize: -1
      filters: stat,logback
  #从库1
  slave1:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test02?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=round&useAffectedRows=true
    username: root
    password: root
    pool-config:
      initialSize: 100
      minIdle: 1
      maxActive: 50
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: false
      maxPoolPreparedStatementPerConnectionSize: -1
      filters: stat,logback
  #从库2
  slave2:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test03?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=round&useAffectedRows=true
    username: root
    password: root
    pool-config:
      initialSize: 100
      minIdle: 1
      maxActive: 50
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: false
      maxPoolPreparedStatementPerConnectionSize: -1
      filters: stat,logback

#==========整合mybatisPlus==========
mybatis-plus:
  type-aliases-package: com.lx.ms.db.mybatis.bean.ext
  mapper-locations: classpath:com/sa/example/mybatis2/ext/mapper/xml/*.xml,classpath:com/sa/example/mybatis2/auto/mapper/xml/*.xml
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: true
    lazy-loading-enabled: false
    aggressive-lazy-loading: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

3.3 bean配置

mybatisPlus配置bean所在包：com.sa.example.mybatis2.config

3.3.1 数据源配置信息

/**
 * 数据源配置信息
 */
@Configuration
public class DataSourcePropertiesConfig { 
    /**
     * 主库配置
     * @return 数据源配置
     */
    @Primary
    @Bean
    @ConfigurationProperties("datasource.master")
    public DataSourceProperties masterDataSourceProperties() {
        return new DataSourceProperties();
    }
    
    /**
     * 从库配置
     * @return 数据源配置
     */
    @Bean
    @ConfigurationProperties("datasource.slave1")
    public DataSourceProperties slave1DataSourceProperties() {
        return new DataSourceProperties();
    }
    
    /**
     * 从库配置
     * @return 数据源配置
     */
    @Bean
    @ConfigurationProperties("datasource.slave2")
    public DataSourceProperties slave2DataSourceProperties() {
        return new DataSourceProperties();
    }
}

3.3.2 数据源配置

/**
 * 数据源配置
 */
@Configuration
public class DataSourceConfig {
    
    @Resource
    private DataSourceProperties masterDataSourceProperties;
    
    @Resource
    private DataSourceProperties slave1DataSourceProperties;
    
    @Resource
    private DataSourceProperties slave2DataSourceProperties;
    
    /**
     * 数据源类型
     */
    @Value("${datasource.type:}")
    private Class<? extends DataSource> dataSourceType;
    
    /**
     * 如果配置文件未配置，默认取Druid数据源配置
     *
     * @return
     */
    private Class<? extends DataSource> getDataSourceType() {
        if (this.dataSourceType == null) {
            this.dataSourceType = com.alibaba.druid.pool.DruidDataSource.class;
        }
        return this.dataSourceType;
    }
    
    /**
     * 主库数据源
     *
     * @return
     */
    @Bean
    @ConfigurationProperties("datasource.master.pool-config")
    public DataSource masterDataSource() {
        return masterDataSourceProperties.initializeDataSourceBuilder().type(getDataSourceType()).build();
    }
    
    /**
     * 从库1数据源
     *
     * @return
     */
    @Bean
    @ConfigurationProperties("datasource.slave1.pool-config")
    public DataSource slave1DataSource() {
        return slave1DataSourceProperties.initializeDataSourceBuilder().type(getDataSourceType()).build();
    }
    
    /**
     * 从库2数据源
     *
     * @return
     */
    @Bean
    @ConfigurationProperties("datasource.slave2.pool-config")
    public DataSource slave2DataSource() {
        return slave2DataSourceProperties.initializeDataSourceBuilder().type(getDataSourceType()).build();
    }
}

3.3.3 数据源句柄

/**
 * 数据源句柄（存储当前线程中绑定的数据源名称）
 */
class DataSourceHolder {
    
    static final String MASTER_DATASOURCE = "master";
    static final String SLAVER1_DATASOURCE = "slaver1";
    static final String SLAVER2_DATASOURCE = "slaver2";
    
    /**
     * 线程变量（缓存当前数据源名称）
     */
    private static final ThreadLocal<String> LOCAL = new ThreadLocal<>();
    
    /**
     * 设置数据源
     *
     * @param dataSource 数据源名称
     */
    static void putDataSource(String dataSource) {
        LOCAL.set(dataSource);
    }
    
    /**
     * 获取数据源
     *
     * @return 数据源名称
     */
    static String getDataSource() {
        return LOCAL.get();
    }
    
    /**
     * 清空缓存
     */
    static void clearDataSource() {
        LOCAL.remove();
    }
    
    /**
     * 随机获取读库
     *
     * @return
     */
    static String getRandomReadDataSourceName() {
        int randomVal = ThreadLocalRandom.current().nextInt(0, 2);
        if (randomVal == 0) {
            return SLAVER1_DATASOURCE;
        } else {
            return SLAVER2_DATASOURCE;
        }
    }
}

3.3.4 动态数据源路由

/**
 * 动态数据源路由（对于一次有读有写的事务，事务内的读操作也不应该在slave节点上，所有操作都应该在master节点上）
 */
@Component
public class ExtRoutingDataSource extends AbstractRoutingDataSource {
    
    @Qualifier("masterDataSource")
    @Autowired
    private DataSource masterDataSource;
    
    @Qualifier("slave1DataSource")
    @Autowired
    private DataSource slave1DataSource;
    
    @Qualifier("slave2DataSource")
    @Autowired
    private DataSource slave2DataSource;
    
    @Override
    public void afterPropertiesSet() {
        
        //设置默认数据源
        this.setDefaultTargetDataSource(masterDataSource);
        
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put(DataSourceHolder.MASTER_DATASOURCE, masterDataSource);
        dataSourceMap.put(DataSourceHolder.SLAVER1_DATASOURCE, slave1DataSource);
        dataSourceMap.put(DataSourceHolder.SLAVER2_DATASOURCE, slave2DataSource);
        //指定目标数据源的映射，并使用查找键作为键
        this.setTargetDataSources(dataSourceMap);
        
        super.afterPropertiesSet();
    }
    
    @Override
    protected Object determineCurrentLookupKey() {
        ////确定当前查找键
        String key = DataSourceHolder.getDataSource();
        if (StringUtils.isEmpty(key)) {
            //返回默认数据源
            return DataSourceHolder.MASTER_DATASOURCE;
        }
        //读取后，清空内存变量
        DataSourceHolder.clearDataSource();
        return key;
    }
}

3.3.5 重写mybatis自动加载

/**
 * 重写mybatis自动加载（重新指定数据源）
 */
@Configuration
public class ExtMybatisPlusAutoConfiguration extends MybatisPlusAutoConfiguration {
    
    @Autowired
    private ExtRoutingDataSource extRoutingDataSource;
    
    public ExtMybatisPlusAutoConfiguration(MybatisPlusProperties properties, ObjectProvider<Interceptor[]> interceptorsProvider, ObjectProvider<TypeHandler[]> typeHandlersProvider, ObjectProvider<LanguageDriver[]> languageDriversProvider, ResourceLoader resourceLoader, ObjectProvider<DatabaseIdProvider> databaseIdProvider, ObjectProvider<List<ConfigurationCustomizer>> configurationCustomizersProvider, ObjectProvider<List<MybatisPlusPropertiesCustomizer>> mybatisPlusPropertiesCustomizerProvider, ApplicationContext applicationContext) {
        super(properties, interceptorsProvider, typeHandlersProvider, languageDriversProvider, resourceLoader, databaseIdProvider, configurationCustomizersProvider, mybatisPlusPropertiesCustomizerProvider, applicationContext);
    }
    
    /**
     * 自定义session工厂
     *
     * @return
     * @throws Exception
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        return super.sqlSessionFactory(extRoutingDataSource);
    }
    
    /**
     * 自定义事务管理器
     *
     * @return
     */
    @Bean
    public DataSourceTransactionManager dataSourceTransactionManager() {
        return new DataSourceTransactionManager(extRoutingDataSource);
    }
}

3.3.6 Mybatis数据源拦截器

/**
 * Mybatis数据源拦截器（用户切换线程变量中指定的数据源）
 */
@Intercepts({
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})
})
@Component
public class MybatisDataSourceInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        boolean synchronizationActive = TransactionSynchronizationManager.isSynchronizationActive();
        if (!synchronizationActive) {
            Object[] objects = invocation.getArgs();
            MappedStatement ms = (MappedStatement) objects[0];
            if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
                //不在事务中，且调用的sql是select类型时，选择从库。其他情况，默认选择主库
                DataSourceHolder.putDataSource(DataSourceHolder.getRandomReadDataSourceName());
            }
        }
        return invocation.proceed();
    }
    
    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }
    
    @Override
    public void setProperties(Properties properties) {
        
    }
}

3.4 启动类配置

@SpringBootApplication(scanBasePackages = "com.sa.example")
@MapperScan(basePackages = {"com.sa.example.mybatis2.ext.mapper", "com.sa.example.mybatis2.auto.mapper"})
public class Mybatis2Application {
    public static void main(String[] args) {
        SpringApplication.run(Mybatis2Application.class, args);
    }
}

4 使用

4.1 建表脚本

CREATE TABLE `c_m_phone` (
  `id` int(10) NOT NULL COMMENT 'id',
  `phone_first` varchar(20) DEFAULT NULL COMMENT '前缀号段',
  `phone_provence` varchar(20) DEFAULT NULL COMMENT '手机所在省份',
  `phone_city` varchar(20) DEFAULT NULL COMMENT '手机所在城市',
  `service` varchar(20) DEFAULT NULL COMMENT '服务商',
  PRIMARY KEY (`id`)
) COMMENT='电话区域表';

4.2 实体类

//包名：com.sa.example.mybatis2.ext.bean
@Data
public class CMPhone {
    private Integer id;
    private String phoneFirst;
    private String phoneProvence;
    private String phoneCity;
    private String service;
}

4.3 mapper

// 包名：com.sa.example.mybatis2.ext.mapper
public interface CMPhoneMapper {
    /**
     * 保存（配置主库）
     * @param model
     * @return
     */
    int save(CMPhone model);
    
    /**
     * 查询（配置从库）
     * @param model
     * @return
     */
    List<CMPhone> findList(CMPhone model);
}

4.4 mapper映射

<!-- 包名：com.sa.example.mybatis2.ext.mapper.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.sa.example.mybatis2.ext.mapper.CMPhoneMapper">
    <resultMap id="BaseResultMap" type="com.sa.example.mybatis2.ext.bean.CMPhone">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="phone_first" property="phoneFirst" jdbcType="VARCHAR"/>
        <result column="phone_provence" property="phoneProvence" jdbcType="VARCHAR"/>
        <result column="phone_city" property="phoneCity" jdbcType="VARCHAR"/>
        <result column="service" property="service" jdbcType="VARCHAR"/>
    </resultMap>

    <select id="findList" parameterType="com.sa.example.mybatis2.ext.bean.CMPhone" resultMap="BaseResultMap">
        select id,phone_first,phone_provence,phone_city,service from c_m_phone
    </select>

    <insert id="save" parameterType="com.sa.example.mybatis2.ext.bean.CMPhone">
        INSERT INTO `c_m_phone` (`id`, `phone_first`, `phone_provence`, `phone_city`, `service`) VALUES (
            #{id},#{phoneFirst},#{phoneProvence},#{phoneCity},#{service}
        )
    </insert>
</mapper>

4.5 使用

@RunWith(SpringRunner.class)
@SpringBootTest(classes = MybatisApplication.class)
public class MybatisTest {
    
    @Autowired
    private CMPhoneMapper cmPhoneMapper;
    
    @Test
    public void testMapper() {
        List<CMPhone> cmPhoneList = cmPhoneMapper.findList(new CMPhone());
        System.out.println(cmPhoneList);
    }
    
    @Test
    public void testSave(){
        CMPhone model = new CMPhone();
        model.setId(1000);
        model.setPhoneCity("PhoneCity test");
        model.setPhoneFirst("PhoneFirst test");
        model.setPhoneProvence("PhoneProvence test");
        model.setService("Service test");
        cmPhoneMapper.save(model);
    }
}

4.6 事务中的查询

事务中的查询，连接的为主库master

4.6.1 service

public interface CMPhoneService {
    List<CMPhone> doSomething();
}

@Service
public class CMPhoneServiceImpl implements CMPhoneService {
    @Autowired
    private CMPhoneMapper cmPhoneMapper;
    
    @Transactional(rollbackFor = RuntimeException.class)
    @Override
    public List<CMPhone> doSomething() {
        return cmPhoneMapper.findList(new CMPhone());
    }
}

4.6.2 单元测试

@Test
public void testTransaction(){
    List<CMPhone> cmPhoneList = cmPhoneService.doSomething();
    System.out.println(cmPhoneList);
}

4.6.3 输出日志

Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@79c5460e]
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@7ab4ae59] will be managed by Spring
==>  Preparing: select id,phone_first,phone_provence,phone_city,service from c_m_phone
==> Parameters: 
<==    Columns: id, phone_first, phone_provence, phone_city, service
<==        Row: 1, 1300000, 山东, 济南, 中国联通
<==      Total: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@79c5460e]
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@79c5460e]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@79c5460e]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@79c5460e]