Mybatis 多数据源切换-优快云博客

本文详细介绍如何在Spring环境中配置并使用多个数据源，包括主从数据源的设置及动态数据源切换策略。通过AOP实现对不同操作的数据源选择，并提供了一种基于线程变量的解决方案。

2019独角兽企业重金招聘Python工程师标准>>>

首先在spring下配置多个dataSource

<bean id="masterDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
		<property name="driverClass" value="${master.driverClass}" />
		<property name="jdbcUrl" value="${master.jdbcUrl}" />
		<property name="user" value="${master.user}" />
		<property name="password" value="${master.password}" />
		<property name="minPoolSize" value="${master.minPoolSize}"></property>
		<property name="maxPoolSize" value="${master.maxPoolSize}"></property>
		<property name="maxIdleTime" value="${master.maxIdleTime}"></property>
		<property name="acquireIncrement" value="${master.acquireIncrement}"></property>
		<property name="maxStatements" value="${master.maxStatements}"></property>
		<property name="initialPoolSize" value="${master.initialPoolSize}"></property>
		<property name="idleConnectionTestPeriod" value="${master.idleConnectionTestPeriod}"></property>
		<property name="acquireRetryAttempts" value="${master.acquireRetryAttempts}"></property>
		<property name="breakAfterAcquireFailure" value="${master.breakAfterAcquireFailure}"></property>
		<property name="testConnectionOnCheckout" value="${master.testConnectionOnCheckout}"></property>
		<property name="autoCommitOnClose" value="${master.autoCommitOnClose}"></property>
	</bean>
	

	<bean id="slaveDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
		<property name="driverClass" value="${slave.driverClass}" />
		<property name="jdbcUrl" value="${slave.jdbcUrl}" />
		<property name="user" value="${slave.user}" />
		<property name="password" value="${slave.password}" />
		<property name="minPoolSize" value="${slave.minPoolSize}"></property>
		<property name="maxPoolSize" value="${slave.maxPoolSize}"></property>
		<property name="maxIdleTime" value="${slave.maxIdleTime}"></property>
		<property name="acquireIncrement" value="${slave.acquireIncrement}"></property>
		<property name="maxStatements" value="${slave.maxStatements}"></property>
		<property name="initialPoolSize" value="${slave.initialPoolSize}"></property>
		<property name="idleConnectionTestPeriod" value="${slave.idleConnectionTestPeriod}"></property>
		<property name="acquireRetryAttempts" value="${slave.acquireRetryAttempts}"></property>
		<property name="breakAfterAcquireFailure" value="${slave.breakAfterAcquireFailure}"></property>
		<property name="testConnectionOnCheckout" value="${slave.testConnectionOnCheckout}"></property>
		<property name="autoCommitOnClose" value="${slave.autoCommitOnClose}"></property>
	</bean>

之后自定义一个数据源

<bean id="myDynamicDataSource" class="com.up366.lsp.db.datasource.DynamicDataSource">
		<property name="targetDataSources">
		<!-- 标识符类型 	-->
			<map key-type="java.lang.String">
				<entry key="master" value-ref="masterDataSource"/> 
				<entry key="slave" value-ref="slaveDataSource" />
			</map>
		</property>
		<property name="defaultTargetDataSource" ref="masterDataSource" />
	</bean>

自己写的自定义数据源

package com.up366.lsp.db.datasource;

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

public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceHolder.getDataSouce();
    }

}

之后用线程变量控制

package com.up366.lsp.db.datasource;

public class DynamicDataSourceHolder {
    public static final ThreadLocal<String> holder = new ThreadLocal<String>();

    public static void setDataSource(String name) {
        holder.set(name);
    }

    public static String getDataSouce() {
        return holder.get();
    }
    
    public static void clearDataSouce() {    
    	holder.remove();    
    }
    
    public static void setMaster() {
    	DynamicDataSourceHolder.clearDataSouce();
    }
    
    public static void setSlave() {
    	DynamicDataSourceHolder.setDataSource("slave");
    }
}

之后配置一个执行前的aop 动态加载数据源

<aop:aspectj-autoproxy></aop:aspectj-autoproxy>
    <bean id="manyDataSourceAspect" class="com.up366.lsp.db.datasource.DataSourceAspect" />
    <aop:config>
        <aop:aspect id="c" ref="manyDataSourceAspect">
            <aop:pointcut id="tx" expression="execution(* com.up366.lsp.db.dao.*.*(..))"/>
            <aop:before pointcut-ref="tx" method="before"/>
        </aop:aspect>
    </aop:config>

package com.up366.lsp.db.datasource;

import java.lang.reflect.Method;

import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.session.SqlSessionFactory;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.reflect.MethodSignature;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;

import com.up366.lsp.db.datasource.anno.Master;
import com.up366.lsp.db.datasource.anno.Slave;


public class DataSourceAspect {

	@Autowired
	private SqlSessionFactoryBean sqlSessionFactory;
	
    public void before(JoinPoint point)
    {
        Object target = point.getTarget();
        String method = point.getSignature().getName();

        Class<?>[] classz = target.getClass().getInterfaces();

        Class<?>[] parameterTypes = ((MethodSignature) point.getSignature())
                .getMethod().getParameterTypes();
        try {
            Method m = classz[0].getMethod(method, parameterTypes);
            if (m != null ) {
            	if(m.isAnnotationPresent(Slave.class)){
            		DynamicDataSourceHolder.setSlave();
            		return ;
            	}
            	if(m.isAnnotationPresent(Master.class)){
            		DynamicDataSourceHolder.setMaster();
            		return ;
            	}
            	//在没有配置的情况下走默认
            	String key = classz[0].getName()+"." + m.getName();
            	SqlSessionFactory ss =  sqlSessionFactory.getObject();
            	//获取类型
            	SqlCommandType type = ss.getConfiguration().getMappedStatement(key,false).getSqlCommandType();
            	//查询默认从库
            	if(SqlCommandType.SELECT == type){
            		DynamicDataSourceHolder.setSlave();
            		return ;
            	}
            	//增删改默认主库
            	if(type == SqlCommandType.DELETE || SqlCommandType.INSERT == type || SqlCommandType.UPDATE == type)
            	DynamicDataSourceHolder.setMaster();
            }
            
        } catch (Exception e) {
            
        }
    }
	
}

基本上完成了

如果是负载均衡的话可以对从库进行负载多个配置用算法去确定

转载于:https://my.oschina.net/u/1584347/blog/681154