• spring+mybatis+多数据源(mysql+oracle)配置

    1.自定义注解

    1. @Target({ElementType.TYPE,ElementType.METHOD})
    2. @Retention(RetentionPolicy.RUNTIME)
    3. public @interface DataSource {
    4.     String value();
    5. }

    2.

    1. package com.utils;
    2.  
    3. public class DbContextHolder {
    4.     private static final ThreadLocal<String>THREAD_DATA_SOURCE =new ThreadLocal<>();
    5.     /**
    6.      * 设置当前数据库
    7.      */
    8.     public static void setDataSource(String dataSource) {
    9.         THREAD_DATA_SOURCE.set(dataSource);
    10.     }
    11.     /**
    12.      * 取得当前数据库
    13.      */
    14.     public static String getDataSource() {
    15.         return THREAD_DATA_SOURCE.get();
    16.     }
    17.  
    18.     /**
    19.      * 清除上下文数据
    20.      */
    21.     public static void clearDataSource() {
    22.         THREAD_DATA_SOURCE.remove();
    23.     }
    24. }

    3.

    1. package com.utils;
    2.  
    3. import java.lang.reflect.Method;
    4.  
    5. import org.aspectj.lang.JoinPoint;
    6. import org.aspectj.lang.reflect.MethodSignature;
    7.  
    8. public class DataSourceAspect {
    9.     //	拦截目标方法，获取由@DataSource指定的数据源标识，设置到线程存储中以便切换数据源
    10.     public void intercept(JoinPoint point) throws Exception{
    11.         Class<?> target = point.getTarget().getClass();
    12.         MethodSignature signature=(MethodSignature)point.getSignature();
    13.         // 默认使用目标类型的注解，如果没有则使用其实现接口的注解
    14.  
    15.         for (Class<?> clazz : target.getInterfaces()) {
    16.             resolveDataSource(clazz, signature.getMethod());
    17.         }
    18.         resolveDataSource(target, signature.getMethod());
    19.     }
    20.     /**
    21.      * 提取目标对象方法注解和类型注解中的数据源标识
    22.      */
    23.  
    24.     public void resolveDataSource(Class<?>clazz,Method method) {
    25.         try {
    26.             Class<?>[]types=method.getParameterTypes();
    27. //			默认使用类型注解
    28.             if (clazz.isAnnotationPresent(DataSource.class)) {
    29.                 DataSource source = clazz.getAnnotation(DataSource.class);
    30.                 DbContextHolder.setDataSource(source.value());
    31.             }
    32. //			方法注解可以覆盖类型注解
    33.             Method m=clazz.getMethod(method.getName(), types);
    34.             if (m!=null && m.isAnnotationPresent(DataSource.class)) {
    35.                 DataSource source = m.getAnnotation(DataSource.class);
    36.                 DbContextHolder.setDataSource(source.value());
    37.             }
    38.         } catch (Exception e) {
    39.             System.out.println(clazz+":"+e.getMessage());
    40.         }
    41.     }
    42. }

    4.

    1. package com.utils;
    2.  
    3. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    4.  
    5. public class MultipleDataSource extends AbstractRoutingDataSource {
    6.     @Override
    7.     protected Object determineCurrentLookupKey() {
    8.         // TODO Auto-generated method stub
    9.         return DbContextHolder.getDataSource();
    10.     }
    11. }

    1.   <!-- 将多个配置文件读取到容器中，交给Spring管理 -->
    2.     <bean id="propertyConfigurer"
    3.           class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    4.         <property name="locations">
    5.             <list>
    6.                 <!-- 这里支持多种寻址方式：classpath和file -->
    7.                 <value>classpath:db.properties</value>
    8.             </list>
    9.         </property>
    10.     </bean>
    11.     <bean id="dataSource_mysql" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    12.         <property name="username" value="${user}"/>
    13.         <property name="password" value="${password}"/>
    14.         <property name="driverClassName" value="${driverClassName}"/>
    15.         <property name="jdbcUrl" value="${jdbcUrl}"/>
    16.         <!--<property name="jdbcUrl" value="jdbc:mysql://47.96.75.158:3306/a?characterEncoding=UTF-8"/>-->
    17.     </bean>
    18.     <bean id="dataSource_oracle" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    19.         <property name="driverClassName" value="${jdbc.dataSourceDriver}" />
    20.         <property name="jdbcUrl" value="${jdbc.dataSourceUrl}" />
    21.         <property name="username" value="${jdbc.dataSourceUser}" />
    22.         <property name="password" value="${jdbc.dataSourcePasswd}" />
    23.     </bean>
    24.     <!-- 下面的是切换数据库的自定义类 -->
    25.     <bean id="dataSource" class="com.utils.MultipleDataSource">
    26.         <!-- 默认使用mysql数据库 -->
    27.         <property name="defaultTargetDataSource" ref="dataSource_mysql"></property>
    28.         <property name="targetDataSources">
    29.             <map>
    30.                 <entry key="dataSource_mysql" value-ref="dataSource_mysql"></entry>
    31.                 <entry key="dataSource_oracle" value-ref="dataSource_oracle"></entry>
    32.             </map>
    33.         </property>
    34.     </bean>
    35.  
    36.  
    37.     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    38.         <property name="dataSource" ref="dataSource" />
    39.         <property name="mapperLocations" value="classpath:mapper/*.xml" />
    40.          <!--注意其他配置-->
    41.         <property name="plugins">
    42.             <array>
    43.                 <bean class="com.github.pagehelper.PageInterceptor">
    44.                     <property name="properties">
    45.                         <!--使用下面的方式配置参数，一行配置一个 -->
    46.                         <value>
    47.                             rowBoundsWithCount=true
    48.                             PageRowBounds=true
    49.                             offsetAsPageNum=true
    50.                             pageSizeZero=true
    51.                             reasonable=true
    52.                         </value>
    53.                     </property>
    54.                 </bean>
    55.             </array>
    56.         </property>
    57.         <property name="configLocation" value="classpath:mybatis_configer.xml"></property><!-- 配置mybatis的日志记录 -->
    58.  
    59.     </bean>
    60.     <!--扫描依赖注入关联接口-->
    61.     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    62.         <property name="basePackage" value="com.dao"/>
    63.         <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    64.     </bean>
    65.  
    66.  
    67.     <!-- ================================事务相关控制=================================================    -->
    68.     <bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    69.         <property name="dataSource" ref="dataSource"></property>
    70.     </bean>
    71.  
    72.     <tx:advice id="userTxAdvice" transaction-manager="transactionManager">
    73.         <tx:attributes>
    74.             <tx:method name="delete*" propagation="REQUIRED" read-only="false"
    75.                        rollback-for="java.lang.Exception" no-rollback-for="java.lang.RuntimeException"/>
    76.             <tx:method name="insert*" propagation="REQUIRED" read-only="false"
    77.                        rollback-for="java.lang.RuntimeException" />
    78.             <tx:method name="update*" propagation="REQUIRED" read-only="false"
    79.                        rollback-for="java.lang.Exception" />
    80.  
    81.             <tx:method name="find*" propagation="SUPPORTS"/>
    82.             <tx:method name="get*" propagation="SUPPORTS"/>
    83.             <tx:method name="select*" propagation="SUPPORTS"/>
    84.         </tx:attributes>
    85.     </tx:advice>
    86.     <!-- 切面 -->
    87.     <aop:aspectj-autoproxy></aop:aspectj-autoproxy>
    88.     <bean id="dataSourceAspect" class="com.utils.DataSourceAspect"></bean>
    89.     <!--@Transactional(rollbackFor = Exception.class)使用下面的aop就不用每个类，或者方法使用注解了-->
    90.     <aop:config>
    91.         <aop:pointcut expression="execution(public * com.service.*.*(..))" id="pc"/>
    92.         <!--        <aop:pointcut id="pc" expression="execution(public * com.dao.*.*(..))" /> &lt;!&ndash;把事务控制在Dao层&ndash;&gt;-->
    93.         <aop:advisor pointcut-ref="pc" advice-ref="userTxAdvice" />
    94.  
    95.         <aop:aspect ref="dataSourceAspect">
    96.             <!-- 拦截所有service方法，在dao层添加注解 -->
    97.             <aop:pointcut expression="execution(* com.dao..*.*(..))" id="dataSourcePointcut"/>
    98.             <aop:before method="intercept" pointcut-ref="dataSourcePointcut"/>
    99.         </aop:aspect>
    100.     </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

  • 相关阅读:
    王道数据结构——树
    冲刺学习-MySQL-基础
    AQS 组件
    开源与闭源：AI模型发展的两条路径
    MySql学习之慢SQL优化和慢SQL案例
    uni-app —— 下拉刷新 & 上拉加载
    Docker配置nginx
    【IDEA配置】IDEA配置
    【网站项目】学习资料销售平台 小程序
    【力扣每日一题】2023.9.15 宝石补给
  • 原文地址：https://blog.csdn.net/qq_38310446/article/details/106897888