mybatis自动切换多个数据源

假设现在有两个数据库myone和mytwo,读者可以理解为一个写库,一个读库,数据库中都各自有一个表,表的格式都一样，如下：

------------------------------------------------------------
id         int(11) PRI auto_increment
username varchar(20)
password varchar(20)
------------------------------------------------------------

数据库脚本如下：

create database myone;
use myone;
create table user(id int auto_increment primary key,username varchar(20),password varchar(20));

create database mytwo;
use mytwo;
create table user(id int auto_increment primary key,username varchar(20),password varchar(20));
insert into mytwo.user(username,password) value('test','test');

 

现在想要实现数据库的自动切换，有些mapper操作写库myone，有些mapper操作读库mytwo。
首先，构建maven工程，pom文件如下：

 

 

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.aliyun.security</groupId>
    <artifactId>resourcegroup</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.1.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.2.8</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.1.6.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-aop -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>4.1.6.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>4.1.6.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.8.6</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.1.1</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>
    </dependencies>
</project>

接下来是写库myone对应的mapper，如下：

 

package dal.mapper.myone;

import dal.dataobject.myone.User;

public interface OneUserManageMapper {
    int createUser(User user);
}

 

 

 

<?xml version="1.0" encoding="GBK"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dal.mapper.myone.OneUserManageMapper">
	<insert id="createUser">
		INSERT INTO user(username, password) VALUES(#{username}, #{password})
	</insert>
</mapper>

 

接下来是读库mytwo对应的mapper，如下：

 

 

package dal.mapper.mytwo;

import dal.dataobject.mytwo.User;
import org.apache.ibatis.annotations.Param;

public interface TwoUserManageMapper {
    User getUserById(@Param("id") int id);
}

 

 

 

<?xml version="1.0" encoding="GBK"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dal.mapper.mytwo.TwoUserManageMapper">
	<!--<insert id="createUser">-->
		<!--INSERT INTO user(username, password) VALUES(#{username}, #{password})-->
	<!--</insert>-->
	<resultMap id="RM-User" type="dal.dataobject.mytwo.User">
		<result column="id" property="id"/>
		<result column="username" property="username"/>
		<result column="password" property="password"/>
	</resultMap>
	<select id="getUserById" resultMap="RM-User">
		SELECT * from user WHERE id=#{id}
	</select>
</mapper>

 

接下来是实现自动切换最主要的代码，这里使用AOP编程来实现：

 

 

package dal.datasourceswitch;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
 * Created by Administrator on 2016/8/22.
 */

/**
 * Created by rabbit on 14-5-25.
 */
public class MultipleDataSource extends AbstractRoutingDataSource {

    private static ThreadLocal<String> threadLocalDatasource=new ThreadLocal<String>(){
        @Override
        protected String initialValue() {
            return null;
        }
    };

    public static void setThreadLocalDatasource(String dsName){
        threadLocalDatasource.set(dsName);
    }


    @Override
    protected Object determineCurrentLookupKey() {
        return threadLocalDatasource.get();
    }
}

 

 

 

package dal.datasourceswitch;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.stereotype.Component;

@Component
@Aspect
public class MultipleDataSourceAspectAdvice {

    @Around("execution(* dal.mapper.myone..*.*(*))")
    public Object doAround1(ProceedingJoinPoint jp) throws Throwable {
        MultipleDataSource.setThreadLocalDatasource("myone");
        return jp.proceed();
    }

    @Around("execution(* dal.mapper.mytwo..*.*(*))")
    public Object doAround2(ProceedingJoinPoint jp) throws Throwable {
        MultipleDataSource.setThreadLocalDatasource("mytwo");
        return jp.proceed();
    }

}

 

读者可以学习下AbstractRoutingDataSource类，就知道这么做的理由了。
最后，看下spring的配置文件：

 

 

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context-3.0.xsd
      http://www.springframework.org/schema/aop
          http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location">
            <value>jdbc.properties</value>
        </property>
    </bean>
    <bean id="myoneDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.myone.driver}"/>
        <property name="url" value="${jdbc.myone.url}"/>
        <property name="username" value="${jdbc.myone.username}"/>
        <property name="password" value="${jdbc.myone.password}"/>
    </bean>
    <bean id="mytwoDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.mytwo.driver}"/>
        <property name="url" value="${jdbc.mytwo.url}"/>
        <property name="username" value="${jdbc.mytwo.username}"/>
        <property name="password" value="${jdbc.mytwo.password}"/>
    </bean>

    <bean id="multipleDataSource" class="dal.datasourceswitch.MultipleDataSource">
        <property name="defaultTargetDataSource" ref="myoneDataSource"/> <!--默认主库-->
        <property name="targetDataSources">
            <map>
                <entry key="myone" value-ref="myoneDataSource"/>            <!--辅助aop完成自动数据库切换-->
                <entry key="mytwo" value-ref="mytwoDataSource"/>
            </map>
        </property>
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="multipleDataSource"/>
        <property name="mapperLocations">
            <list>
                <value>mapper/OneUserManageMapper.xml</value>
                <value>mapper/TwoUserManageMapper.xml</value>
            </list>
        </property>
    </bean>


    <bean id="baseMapper" class="org.mybatis.spring.mapper.MapperFactoryBean" abstract="true">
        <property name="sqlSessionFactory" ref="sqlSessionFactory" />
    </bean>

    <bean id="myoneUserManageMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"
          parent="baseMapper">
        <property name="mapperInterface"
                  value="dal.mapper.myone.OneUserManageMapper" />
    </bean>

    <bean id="mytwoUserManageMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"
          parent="baseMapper">
        <property name="mapperInterface"
                  value="dal.mapper.mytwo.TwoUserManageMapper" />
    </bean>
    <aop:aspectj-autoproxy/>
    <!-- 自动扫描，多个包以 逗号分隔 -->
    <context:component-scan base-package="dal"/> <!--注解自动装配-->
    <context:annotation-config />  <!--组件自动扫描-->
</beans>

上面是实现多数据源自动切换的主要代码，源码github地址：
https://github.com/ZhenShiErGe/Multi-Datasource-Autoswitch.git

 

 

文章修改自http://www.cnblogs.com/lzrabbit/p/3750803.html

 

到知乎阅读最新的技术博客：https://www.zhihu.com/people/hulianwangzhaopin/activities

提供给想学习云计算的同学，欢迎收听https://www.zhihu.com/lives/1046567982750281728

 

笔者开设了一个知乎live，详细的介绍的JAVA从入门到精通该如何学，学什么？

提供给想深入学习和提高JAVA能力的同学，欢迎收听https://www.zhihu.com/lives/932192204248682496