springboot+mybatis/mybatis-plus拦截器实现多数据源切换

最新推荐文章于 2024-01-08 10:11:07 发布

小丁丐

最新推荐文章于 2024-01-08 10:11:07 发布

阅读量3.9k

点赞数 3

分类专栏： java

本文链接：https://blog.csdn.net/DJYDFT2831djydft/article/details/111084426

版权

java 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

1、数据源配置

spring.datasource.master.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.master.filters = wall,stat
spring.datasource.master.initial-size = 50
spring.datasource.master.max-active = 100
spring.datasource.master.max-open-prepared-statements = 100
spring.datasource.master.max-wait = 60000
spring.datasource.master.min-evictable-idle-time-millis = 300000
spring.datasource.master.min-idle = 50
spring.datasource.master.password = 8ZDs8U26rcU1v7zI
spring.datasource.master.pool-prepared-statements = true
spring.datasource.master.test-on-borrow = false
spring.datasource.master.test-on-return = false
spring.datasource.master.test-while-idle = true
spring.datasource.master.time-between-eviction-runs-millis = 60000
spring.datasource.master.jdbc-url = jdbc:mysql://rm-bp119f05ug9z24073782.mysql.rds.aliyuncs.com:3306/operation_porsche?allowMultiQueries=true&useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE&serverTimezone=Asia/Shanghai
spring.datasource.master.username = porsche_rw
spring.datasource.master.type = com.zaxxer.hikari.HikariDataSource
spring.datasource.master.hikari.register-mbeans = true
spring.datasource.master.hikari.pool-name = HikariConnectionPool
spring.datasource.master.hikari.minimum-idle = 50
spring.datasource.master.hikari.maximum-pool-size = 100
spring.datasource.master.hikari.connection-timeout = 60000
spring.datasource.master.hikari.idle-timeout = 30000
spring.datasource.master.hikari.connection-test-query = select 'x'
spring.datasource.master.hikari.max-lifetime = 1800000
spring.datasource.master.hikari.connection-init-sql = SET NAMES utf8mb4
spring.datasource.slave.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.slave.filters = wall,stat
spring.datasource.slave.initial-size = 50
spring.datasource.slave.max-active = 100
spring.datasource.slave.max-open-prepared-statements = 100
spring.datasource.slave.max-wait = 60000
spring.datasource.slave.min-evictable-idle-time-millis = 300000
spring.datasource.slave.min-idle = 50
spring.datasource.slave.password = root
spring.datasource.slave.pool-prepared-statements = true
spring.datasource.slave.test-on-borrow = false
spring.datasource.slave.test-on-return = false
spring.datasource.slave.test-while-idle = true
spring.datasource.slave.time-between-eviction-runs-millis = 60000
spring.datasource.slave.jdbc-url = jdbc:mysql://localhost:3306/operation_porsche?allowMultiQueries=true&useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE&serverTimezone=Asia/Shanghai
spring.datasource.slave.username = root
spring.datasource.slave.type = com.zaxxer.hikari.HikariDataSource
spring.datasource.slave.hikari.register-mbeans = true
spring.datasource.slave.hikari.pool-name = HikariConnectionPool
spring.datasource.slave.hikari.minimum-idle = 50
spring.datasource.slave.hikari.maximum-pool-size = 100
spring.datasource.slave.hikari.connection-timeout = 60000
spring.datasource.slave.hikari.idle-timeout = 30000
spring.datasource.slave.hikari.connection-test-query = select 'x'
spring.datasource.slave.hikari.max-lifetime = 1800000
spring.datasource.slave.hikari.connection-init-sql = SET NAMES utf8mb4

2、DataSourceConfig：

mybatis：


package com.zhangmen.operation.porsche.relation.config;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import com.zhangmen.operation.porsche.relation.common.enums.DBTypeEnum;
import com.zhangmen.operation.porsche.relation.mybatis.MyRoutingDataSource;

/**
 * @Author zhiyong.zhou
 * @Date 2020/8/1
 * @Description
 */

@Configuration
public class DataSourceConfig {
     /**
     * 写库
     * @return
     */
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 读库
     * @return
     */
    @Bean
    @ConfigurationProperties("spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }


    @Bean(name="routeDataSource")
    public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slaveDataSource") DataSource slaveDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE, slaveDataSource);
        MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
        myRoutingDataSource.setTargetDataSources(targetDataSources);
        return myRoutingDataSource;
    }
}



mybatis-plus:

package com.zhangmen.operation.porsche.relation.config;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import com.zhangmen.operation.porsche.relation.common.enums.DBTypeEnum;
import com.zhangmen.operation.porsche.relation.mybatis.MyRoutingDataSource;

/**
 * @Author zhiyong.zhou
 * @Date 2020/8/1
 * @Description
 */

@Configuration
public class DataSourceConfig {
	 /**
     * 写库
     * @return
     */
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 读库
     * @return
     */
    @Bean
    @ConfigurationProperties("spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    /**
     * 
    * @Title: multipleTransactionManager
    * @Description: 配置事务管理器
    * @param dataSource
    * @return
    * @throws
     */
	@Bean(name = "multipleTransactionManager")
	@Primary
	public DataSourceTransactionManager multipleTransactionManager(
			@Qualifier("routeDataSource") DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);
	}


    @Bean(name="routeDataSource")
    public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slaveDataSource") DataSource slaveDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE, slaveDataSource);
        MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
        myRoutingDataSource.setTargetDataSources(targetDataSources);
        myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
        return myRoutingDataSource;
    }
}

3、MybatisConfig：

mybatis:

package com.zhangmen.operation.porsche.relation.config;

import javax.sql.DataSource;

import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.zhangmen.operation.porsche.relation.mybatis.MybatisPluginInterceptor;

/**
 * @Author zhiyong.zhou
 * @Date 2020/8/1
 * @Description
 */

@EnableTransactionManagement
@Configuration
public class MybatisConfig {

	@Bean
	public PaginationInterceptor paginationInterceptor() {
		PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
		return paginationInterceptor;

	}

	@Bean(name="sqlSessionFactory")
	@ConditionalOnMissingBean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("routeDataSource") DataSource routeDataSource) throws Exception {
		SqlSessionFactoryBean  sqlSessionFactoryBean=new SqlSessionFactoryBean();
    	sqlSessionFactoryBean.setDataSource(routeDataSource);
    	sqlSessionFactoryBean.setPlugins(new Interceptor[] {new MybatisPluginInterceptor()});
		sqlSessionFactoryBean
				.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*.xml"));
		//设置驼峰式映射
		org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
		configuration.setMapUnderscoreToCamelCase(true);
		sqlSessionFactoryBean.setConfiguration(configuration);
		return sqlSessionFactoryBean.getObject();
    }
}


mybatis-plus:
package com.zhangmen.operation.porsche.relation.config;

import javax.sql.DataSource;

import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zhangmen.operation.porsche.relation.mybatis.MybatisPluginInterceptor;

/**
 * @Author zhiyong.zhou
 * @Date 2020/8/1
 * @Description
 */

@EnableTransactionManagement
@Configuration
public class MybatisConfig {
	

	@Bean
	public PaginationInterceptor paginationInterceptor() {
		PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
		 // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        paginationInterceptor.setOverflow(true);
		return paginationInterceptor;

	}

	@Bean(name="sqlSessionFactory")
	@ConditionalOnMissingBean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("routeDataSource") DataSource routeDataSource) throws Exception {
		MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(routeDataSource);
        sqlSessionFactory.setTypeAliasesPackage("com.zhangmen.operation.porsche.relation.entity");

        /**
         * 设置扫描路径
         */
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] resource = resolver.getResources("classpath:mapper/*.xml");
		sqlSessionFactory.setMapperLocations(resource);
        /**
         * mybatis-plus yml 配置不生效，要在这里代码里配置
         */
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        //是否使用转驼峰
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        sqlSessionFactory.setConfiguration(configuration);

        //添加分页功能
        Interceptor[] plugins = {paginationInterceptor(),new MybatisPluginInterceptor()};
        sqlSessionFactory.setPlugins(plugins);
        

        return sqlSessionFactory.getObject();
    }
	
}

4、DBContextHolder：

package com.zhangmen.operation.porsche.relation.mybatis;

import com.zhangmen.operation.porsche.relation.common.enums.DBTypeEnum;

import lombok.extern.slf4j.Slf4j;

/**
 * 
 * @ClassName: DBContextHolder
 * @Description: 设置线程
 * @author dingjy
 * @date 2020年12月12日 上午11:06:21
 */
@Slf4j
public class DBContextHolder {
	private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();

	public static void set(DBTypeEnum dbType) {
		contextHolder.set(dbType);
		//log.warn("set thread:{},dbType:{}",Thread.currentThread().getId(),dbType);
	}

	public static DBTypeEnum get() {
		DBTypeEnum dbType=contextHolder.get();
		//log.warn("get thread:{},dbType:{}",Thread.currentThread().getId(),dbType);
		return dbType;
	}
	
	public static void master() {
		remove();
		set(DBTypeEnum.MASTER);
	}

	public static void slave() {
		remove();
		set(DBTypeEnum.SLAVE);
	}
	
	public static void remove() {
		//log.warn("remove thread:{},dbType:{}",Thread.currentThread().getId(),contextHolder.get());
		contextHolder.remove();
	}
}

5、MybatisPluginInterceptor：

package com.zhangmen.operation.porsche.relation.mybatis;

import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;
import org.springframework.transaction.support.TransactionSynchronizationManager;

/**
 * 
 * @ClassName: MyatisPluginInterceptor
 * @Description: mybatis拦截器
 * @author dingjy
 * @date 2020年12月12日 下午6:58:17
 */
@Component
@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 }), })
public class MybatisPluginInterceptor implements Interceptor {
	
	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		boolean synchronizationActive = TransactionSynchronizationManager.isSynchronizationActive();
		Object[] objects = invocation.getArgs();
		MappedStatement ms = (MappedStatement) objects[0];
		
		if (!synchronizationActive) {
			if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
				DBContextHolder.slave();
			}else {
				DBContextHolder.master();
			}
		} else {
			DBContextHolder.master();
		}
		return invocation.proceed();
	}

}

6、MyRoutingDataSource：

package com.zhangmen.operation.porsche.relation.mybatis;

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

import com.alibaba.fastjson.JSONObject;

import lombok.extern.slf4j.Slf4j;

/**
 * 
* @ClassName: MyRoutingDataSource
* @Description: 路由key
* @author dingjy
* @date 2020年12月12日 上午11:05:08
 */
@Slf4j
public class MyRoutingDataSource extends AbstractRoutingDataSource {
	
    @Override
    protected Object determineCurrentLookupKey() {
    	Object obj=DBContextHolder.get();
    	log.info("MyRoutingDataSource obj:{}",JSONObject.toJSONString(obj));
        return obj;
    }
}

注意：

当有先查询，后变更的操作时：

a、当变更操作上不存在事务时，用原生的mybatis xml变更操作没问题

b、当变更操作上存在事务时，去掉事务即可

原因：http://t.zoukankan.com/yjmyzz-p-7390331.html

大致是因为开启事务时，会从缓存中获取上一次的数据源，就算当前切换了数据源也没用