多数据源读写分离改造

多数据源读写分离改造

一、使用背景

 现有项目需要将生成的业务记录数据，按照一定的规则从oracle数据库插入到sqlserver数据库中。

二、改造方式

1、数据源jdbc.properties 新增sqlserver 配置

sqlserver.driver=com.mysql.jdbc.Driver
#datasource.url=jdbc:mysql://localhost:3306/wechat
sqlserver.url=jdbc:mysql://localhost:3306/wechat
sqlserver.username=wechat
sqlserver.password=123456

2、applicationContext.xml 修改

(1)、添加sqlserver连接

 添加连接，指定mybatis xml路径

    <!--SQLServer 数据库连接池 -->
    <bean id="sqlserverSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="${sqlserver.driver}" />
        <property name="url" value="${sqlserver.url}" />
        <property name="username" value="${sqlserver.username}" />
        <property name="password" value="${sqlserver.password}" />
    </bean>
    <bean id="sqlserverSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="sqlserverSource"></property>
        <property name="configLocation" value="classpath:mybatis-sqlserver.xml" />
    </bean>
    <bean id="sqlserverMapper" class="com.github.abel533.sql.SqlMapper"
        scope="prototype">
        <constructor-arg ref="sqlserverSession" />
    </bean>

    <bean id="sqlserverSession" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg index="0" ref="sqlserverSessionFactory" />
    </bean>

(2)、修改MapperScannerConfigurer注册方式

 添加id，和sqlSessionFactoryBeanName（必须通过sqlSessionFactoryBeanName来指定不同的sqlSessionFactory）

    <bean id="oracle" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="eemp.*" />
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>
    <bean id ="sqlserver" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="sqlserver.*" />
        <property name="sqlSessionFactoryBeanName" value="sqlserverSessionFactory"/>
    </bean>

(3)、修改bean扫描配置，添加sqlserver目录

    <!-- 扫描指定package下所有带有如@controller,@services,@resource,并把所注释的注册为Spring Beans -->
    <context:component-scan base-package="eemp.*,sqlserver.*">
        <!--<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/> -->
    </context:component-scan>

(4)、新增sqlserver事务配置

<!-- sqlserver拦截器方式配置事物 -->
    <bean id="transactionManagerSqlserver" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">   
        <property name="dataSource" ref="sqlserverSource" />
    </bean>
    <tx:advice id="transactionAdviceSqlserver" transaction-manager="transactionManagerSqlserver"> 
        <tx:attributes> 
            <tx:method name="add*" propagation="REQUIRED" /> 
            <tx:method name="append*" propagation="REQUIRED" /> 
            <tx:method name="insert*" propagation="REQUIRED" /> 
            <tx:method name="save*" propagation="REQUIRED" /> 
            <tx:method name="update*" propagation="REQUIRED" /> 
            <tx:method name="modify*" propagation="REQUIRED" /> 
            <tx:method name="edit*" propagation="REQUIRED" /> 
            <tx:method name="delete*" propagation="REQUIRED" /> 
            <tx:method name="remove*" propagation="REQUIRED" /> 
            <tx:method name="import*" propagation="REQUIRED" /> 
        </tx:attributes> 
    </tx:advice> 
    <aop:config> 
        <aop:pointcut id="transactionPointcutSqlserver" expression="execution(* sqlserver.*.service..*.*(..))   or execution(* sqlserver.*.*.service..*.*(..))  or execution(* sqlserver.*.*.*.service..*.*(..))" /> 
        <aop:advisor pointcut-ref="transactionPointcutSqlserver" advice-ref="transactionAdviceSqlserver" /> 
    </aop:config> 

3、新增mybatis-sqlserver.xml 配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 这个配置使全局的映射器启用或禁用缓存 -->
    <!--声明数据库 oracle -->
    <properties>
        <property name="dialect" value="sqlserver" />
    </properties>
    <!--基本设置 -->
    <settings>
        <!-- 全局启用或禁用延迟加载。当禁用时，所有关联对象都会即时加载 -->
        <setting name="lazyLoadingEnabled" value="false" />
    </settings>

    <!-- 别名定义 -->
    <typeAliases>
            
    </typeAliases>
    <mappers>
        <!--sqlserver测试 -->
        <mapper resource="sqlservermapper/fjkkx/FjsbMapper.xml" />      
    </mappers>
</configuration>        

4、src 目录下新建sqlserver文件夹

5、resourc目录新增sqlservermapper目录

三、优缺点分析

1、优点：不改变现有框架结构，现有内容不需做任何改动。

2、缺点：不支持跨数据库事务，在一次事务中不支持同时操作两个数据库。