1.自定义注解
@Target({ElementType.TYPE,ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSource {
String value();
}
2.
package com.utils;
public class DbContextHolder {
private static final ThreadLocal<String>THREAD_DATA_SOURCE =new ThreadLocal<>();
/**
* 设置当前数据库
*/
public static void setDataSource(String dataSource) {
THREAD_DATA_SOURCE.set(dataSource);
}
/**
* 取得当前数据库
*/
public static String getDataSource() {
return THREAD_DATA_SOURCE.get();
}
/**
* 清除上下文数据
*/
public static void clearDataSource() {
THREAD_DATA_SOURCE.remove();
}
}
3.
package com.utils;
import java.lang.reflect.Method;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.reflect.MethodSignature;
public class DataSourceAspect {
// 拦截目标方法，获取由@DataSource指定的数据源标识，设置到线程存储中以便切换数据源
public void intercept(JoinPoint point) throws Exception{
Class<?> target = point.getTarget().getClass();
MethodSignature signature=(MethodSignature)point.getSignature();
// 默认使用目标类型的注解，如果没有则使用其实现接口的注解
for (Class<?> clazz : target.getInterfaces()) {
resolveDataSource(clazz, signature.getMethod());
}
resolveDataSource(target, signature.getMethod());
}
/**
* 提取目标对象方法注解和类型注解中的数据源标识
*/
public void resolveDataSource(Class<?>clazz,Method method) {
try {
Class<?>[]types=method.getParameterTypes();
// 默认使用类型注解
if (clazz.isAnnotationPresent(DataSource.class)) {
DataSource source = clazz.getAnnotation(DataSource.class);
DbContextHolder.setDataSource(source.value());
}
// 方法注解可以覆盖类型注解
Method m=clazz.getMethod(method.getName(), types);
if (m!=null && m.isAnnotationPresent(DataSource.class)) {
DataSource source = m.getAnnotation(DataSource.class);
DbContextHolder.setDataSource(source.value());
}
} catch (Exception e) {
System.out.println(clazz+":"+e.getMessage());
}
}
}
4.
package com.utils;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class MultipleDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
// TODO Auto-generated method stub
return DbContextHolder.getDataSource();
}
}
<!-- 将多个配置文件读取到容器中，交给Spring管理 -->
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<!-- 这里支持多种寻址方式：classpath和file -->
<value>classpath:db.properties</value>
</list>
</property>
</bean>
<bean id="dataSource_mysql" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
<property name="username" value="${user}"/>
<property name="password" value="${password}"/>
<property name="driverClassName" value="${driverClassName}"/>
<property name="jdbcUrl" value="${jdbcUrl}"/>
<!--<property name="jdbcUrl" value="jdbc:mysql://47.96.75.158:3306/a?characterEncoding=UTF-8"/>-->
</bean>
<bean id="dataSource_oracle" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.dataSourceDriver}" />
<property name="jdbcUrl" value="${jdbc.dataSourceUrl}" />
<property name="username" value="${jdbc.dataSourceUser}" />
<property name="password" value="${jdbc.dataSourcePasswd}" />
</bean>
<!-- 下面的是切换数据库的自定义类 -->
<bean id="dataSource" class="com.utils.MultipleDataSource">
<!-- 默认使用mysql数据库 -->
<property name="defaultTargetDataSource" ref="dataSource_mysql"></property>
<property name="targetDataSources">
<map>
<entry key="dataSource_mysql" value-ref="dataSource_mysql"></entry>
<entry key="dataSource_oracle" value-ref="dataSource_oracle"></entry>
</map>
</property>
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations" value="classpath:mapper/*.xml" />
<!--注意其他配置-->
<property name="plugins">
<array>
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<!--使用下面的方式配置参数，一行配置一个 -->
<value>
rowBoundsWithCount=true
PageRowBounds=true
offsetAsPageNum=true
pageSizeZero=true
reasonable=true
</value>
</property>
</bean>
</array>
</property>
<property name="configLocation" value="classpath:mybatis_configer.xml"></property><!-- 配置mybatis的日志记录 -->
</bean>
<!--扫描依赖注入关联接口-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.dao"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
<!-- ================================事务相关控制================================================= -->
<bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<tx:advice id="userTxAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="delete*" propagation="REQUIRED" read-only="false"
rollback-for="java.lang.Exception" no-rollback-for="java.lang.RuntimeException"/>
<tx:method name="insert*" propagation="REQUIRED" read-only="false"
rollback-for="java.lang.RuntimeException" />
<tx:method name="update*" propagation="REQUIRED" read-only="false"
rollback-for="java.lang.Exception" />
<tx:method name="find*" propagation="SUPPORTS"/>
<tx:method name="get*" propagation="SUPPORTS"/>
<tx:method name="select*" propagation="SUPPORTS"/>
</tx:attributes>
</tx:advice>
<!-- 切面 -->
<aop:aspectj-autoproxy></aop:aspectj-autoproxy>
<bean id="dataSourceAspect" class="com.utils.DataSourceAspect"></bean>
<!--@Transactional(rollbackFor = Exception.class)使用下面的aop就不用每个类，或者方法使用注解了-->
<aop:config>
<aop:pointcut expression="execution(public * com.service.*.*(..))" id="pc"/>
<!-- <aop:pointcut id="pc" expression="execution(public * com.dao.*.*(..))" /> <!–把事务控制在Dao层–>-->
<aop:advisor pointcut-ref="pc" advice-ref="userTxAdvice" />
<aop:aspect ref="dataSourceAspect">
<!-- 拦截所有service方法，在dao层添加注解 -->
<aop:pointcut expression="execution(* com.dao..*.*(..))" id="dataSourcePointcut"/>
<aop:before method="intercept" pointcut-ref="dataSourcePointcut"/>
</aop:aspect>
</aop:config>
测试执行效果：
----------------------mysql---------------------------
2020-06-22 11:05:15 [http-nio-8080-exec-4] DEBUG SqlSessionUtils:97 - Creating a new SqlSession
2020-06-22 11:05:15 [http-nio-8080-exec-4] DEBUG SqlSessionUtils:148 - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a9c057] was not registered for synchronization because synchronization is not active
2020-06-22 11:05:15 [http-nio-8080-exec-4] DEBUG DataSourceUtils:114 - Fetching JDBC Connection from DataSource
2020-06-22 11:05:18 [http-nio-8080-exec-4] DEBUG SpringManagedTransaction:87 - JDBC Connection [HikariProxyConnection@987002885 wrapping com.mysql.jdbc.JDBC4Connection@627776c6] will not be managed by Spring
2020-06-22 11:05:18 [http-nio-8080-exec-4] DEBUG getAll:145 - ==> Preparing: select * from user
2020-06-22 11:05:18 [http-nio-8080-exec-4] DEBUG getAll:145 - ==> Parameters:
2020-06-22 11:05:18 [http-nio-8080-exec-4] DEBUG getAll:145 - <== Total: 12
2020-06-22 11:05:18 [http-nio-8080-exec-4] DEBUG SqlSessionUtils:191 - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4a9c057]
2020-06-22 11:05:18 [http-nio-8080-exec-4] DEBUG DataSourceUtils:340 - Returning JDBC Connection to DataSource
User{id=1, username='小一', password='1234', idCardNo='null', birthday='null', phone='null', email='null', sex='null', age=null, createTime=null, updateTime=null, provinceCode='null', cityCode='null', districtCode='null', address='null', detailAddress='null', desp='null', type=null, orForbidden=null, orDel=null}
User{id=2, username='小二', password='1234', idCardNo='null', birthday='null', phone='null', email='null', sex='null', age=null, createTime=null, updateTime=null, provinceCode='null', cityCode='null', districtCode='null', address='null', detailAddress='null', desp='null', type=null, orForbidden=null, orDel=null}
User{id=3, username='小三', password='1234', idCardNo='null', birthday='null', phone='null', email='null', sex='null', age=null, createTime=null, updateTime=null, provinceCode='null', cityCode='null', districtCode='null', address='null', detailAddress='null', desp='null', type=null, orForbidden=null, orDel=null}
User{id=4, username='小四', password='1234', idCardNo='null', birthday='null', phone='null', email='null', sex='null', age=null, createTime=null, updateTime=null, provinceCode='null', cityCode='null', districtCode='null', address='null', detailAddress='null', desp='null', type=null, orForbidden=null, orDel=null}
User{id=5, username='小五', password='1234', idCardNo='null', birthday='null', phone='null', email='null', sex='null', age=null, createTime=null, updateTime=null, provinceCode='null', cityCode='null', districtCode='null', address='null', detailAddress='null', desp='null', type=null, orForbidden=null, orDel=null}
User{id=6, username='小六', password='1234', idCardNo='null', birthday='null', phone='null', email='null', sex='null', age=null, createTime=null, updateTime=null, provinceCode='null', cityCode='null', districtCode='null', address='null', detailAddress='null', desp='null', type=null, orForbidden=null, orDel=null}
User{id=7, username='小七', password='1234', idCardNo='null', birthday='null', phone='null', email='null', sex='null', age=null, createTime=null, updateTime=null, provinceCode='null', cityCode='null', districtCode='null', address='null', detailAddress='null', desp='null', type=null, orForbidden=null, orDel=null}
User{id=8, username='小八', password='1234', idCardNo='null', birthday='null', phone='null', email='null', sex='null', age=null, createTime=null, updateTime=null, provinceCode='null', cityCode='null', districtCode='null', address='null', detailAddress='null', desp='null', type=null, orForbidden=null, orDel=null}
User{id=9, username='小九', password='1234', idCardNo='null', birthday='null', phone='null', email='null', sex='null', age=null, createTime=null, updateTime=null, provinceCode='null', cityCode='null', districtCode='null', address='null', detailAddress='null', desp='null', type=null, orForbidden=null, orDel=null}
User{id=10, username='小十', password='1234', idCardNo='null', birthday='null', phone='null', email='null', sex='null', age=null, createTime=null, updateTime=null, provinceCode='null', cityCode='null', districtCode='null', address='null', detailAddress='null', desp='null', type=null, orForbidden=null, orDel=null}
User{id=11, username='小去fadsf', password='1234', idCardNo='null', birthday='null', phone='null', email='null', sex='男', age=null, createTime=null, updateTime=null, provinceCode='null', cityCode='null', districtCode='null', address='null', detailAddress='null', desp='null', type=null, orForbidden=null, orDel=null}
User{id=12, username='小去fadsf', password='1234', idCardNo='null', birthday='null', phone='null', email='null', sex='男', age=null, createTime=null, updateTime=null, provinceCode='null', cityCode='null', districtCode='null', address='null', detailAddress='null', desp='null', type=null, orForbidden=null, orDel=null}
----------------------oralce---------------------------
2020-06-22 11:05:18 [http-nio-8080-exec-4] DEBUG SqlSessionUtils:97 - Creating a new SqlSession
2020-06-22 11:05:18 [http-nio-8080-exec-4] DEBUG SqlSessionUtils:148 - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@38c5c7c9] was not registered for synchronization because synchronization is not active
2020-06-22 11:05:18 [http-nio-8080-exec-4] DEBUG DataSourceUtils:114 - Fetching JDBC Connection from DataSource
2020-06-22 11:05:19 [http-nio-8080-exec-4] DEBUG SpringManagedTransaction:87 - JDBC Connection [HikariProxyConnection@24572899 wrapping oracle.jdbc.driver.T4CConnection@79232d37] will not be managed by Spring
2020-06-22 11:05:19 [http-nio-8080-exec-4] DEBUG getAll:145 - ==> Preparing: select * from "sys_user"
2020-06-22 11:05:20 [http-nio-8080-exec-4] DEBUG getAll:145 - ==> Parameters:
2020-06-22 11:05:20 [http-nio-8080-exec-4] DEBUG getAll:145 - <== Total: 5
2020-06-22 11:05:20 [http-nio-8080-exec-4] DEBUG SqlSessionUtils:191 - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@38c5c7c9]
2020-06-22 11:05:20 [http-nio-8080-exec-4] DEBUG DataSourceUtils:340 - Returning JDBC Connection to DataSource
{birthday=2020-10-26, password=1234, createtime=2020-06-08 08:59:22.0, address=地址, phone=15255710558, userane=小一, sex=女, id=1}
{birthday=2020-10-26, password=1234, createtime=2020-06-08 08:59:22.0, address=地址, phone=15255710558, userane=小二, sex=女, id=2}
{birthday=2020-10-26, password=1234, createtime=2020-06-08 08:59:22.0, address=地址, phone=15255710558, userane=小三, sex=女, id=3}
{birthday=2020-10-26, password=1234, createtime=2020-06-08 08:59:22.0, address=地址, phone=15255710558, userane=小四, sex=女, id=4}
{birthday=2020-10-26, password=1234, createtime=2020-06-08 08:59:22.0, address=地址, phone=15255710558, userane=小五, sex=女, id=5}
2020-06-22 11:05:20 [http-nio-8080-exec-4] DEBUG RequestResponseBodyMethodProcessor:267 - Using 'application/json;q=0.8', given [text/html, application/xhtml+xml, image/webp, image/apng, application/signed-exchange;v=b3, application/xml;q=0.9, */*;q=0.8] and supported [application/json, application/*+json]
2020-06-22 11:05:20 [http-nio-8080-exec-4] DEBUG RequestResponseBodyMethodProcessor:297 - Nothing to write: null body
2020-06-22 11:05:20 [http-nio-8080-exec-4] DEBUG DispatcherServlet:1123 - Completed 200 OK