假设现在有两个数据库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