基于mysq数据库和SqlServer数据共用的记录

给了个需求,以mysql为主数据库然后一个菜单从SqlServer数据库取数据,开始用jdbc做了下,甚是麻烦了点,就改用框架,废话不多说,直接上代码:

项目框架是ssm的:

spring-mybatis.xml文件:

mysql的连接配置就直接写死了,mysql的配置就不多写了,直接用properties文件,

<?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:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/tx
      http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
      http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-4.1.xsd">

    <bean id="mySqlDataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="username" value="${connection.username}"></property>
        <property name="password" value="${connection.password}"></property>
        <property name="url" value="${connection.url}"></property>
        <property name="driverClassName" value="${connection.driverClassName}"></property>
        <property name="maxActive" value="${connection.maxActive}"></property>
        <property name="minIdle" value="${connection.minIdle}"></property>
        <property name="filters" value="${connection.filters}"/>
        <property name="initialSize" value="${connection.initialSize}"/>
        <property name="timeBetweenEvictionRunsMillis" value="${connection.timeBetweenEvictionRunsMillis}"/>
        <property name="minEvictableIdleTimeMillis" value="${connection.minEvictableIdleTimeMillis}"/>
        <property name="maxOpenPreparedStatements" value="${connection.maxOpenPreparedStatements}"/>
        <property name="removeAbandoned" value="${connection.removeAbandoned}"/>
        <property name="removeAbandonedTimeout" value="${connection.removeAbandonedTimeout}"/>
        <property name="logAbandoned" value="${connection.logAbandoned}"/>
    </bean>


    <!-- 配置sqlserver数据源 -->
    <bean id="sqlServerDataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <property name="url" value="jdbc:sqlserver://XXX.168.x.xxx:1433;DatabaseName=数据库名称"/>
        <property name="username" value="用户名"/>
        <property name="password" value="密码"/>
    </bean>


    <!-- aop拦截处理，去掉也能运行对应的 dataSource 改掉-->
    <bean id="dataSource" class="qgs.serviceOperation.util.DbcontextHolder"><!-- 这里写选择数据源的类地址 下面跟着给出-->
        <property name="defaultTargetDataSource" ref="mySqlDataSource"/><!-- 设置默认为此mySqlDataSource数据源-->
        <property name="targetDataSources">
            <map>
                <entry key="mySqlDataSource" value-ref="mySqlDataSource"/>
                <entry key="sqlServerDataSource" value-ref="sqlServerDataSource"/>
            </map>
        </property>
    </bean>
    <!-- myBatis文件 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="mapperLocations" value="classpath*:/mapper/**/*.xml"/>
        <property name="configLocation">
            <value>classpath:spring/mybatis-setting.xml</value>
        </property>
    </bean>
    <!-- mybatis.spring自动映射 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="qgs.serviceOperation.**.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>

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

    <!-- 配置事务管理器 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!-- 注解方式配置事物 -->
    <tx:annotation-driven transaction-manager="transactionManager"/>

</beans> 

创建一个切换数据源的工具类 DbcontextHolder:

package qgs.serviceOperation.util;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DbcontextHolder extends AbstractRoutingDataSource {
    public static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    /**
     * 设置当前数据源
     * @param dbType
     */
    public static void setDbType(String dbType){
        contextHolder.set(dbType);
    }
    /**
     * 获得当前数据源
     * @return
     */
    public static String getDbType(){
        String dbType = (String)contextHolder.get();
        return dbType;
    }
    /**
     *清除上下文
     *
     */
    public void clearContext(){
        contextHolder.remove();
    }
    @Override
    protected Object determineCurrentLookupKey() {
        return DbcontextHolder.getDbType();
    }
}

接下来就是使用了,我是直接在Service的实现类里调用

首先刚开始是mysql数据库,然后调用这个方法的时候就切换下数据源,

@Override
public List<ProjectFollowInfo> getProjectFollowInfo(Integer id) {
    //切换数据源
    DbcontextHolder.setDbType("sqlServerDataSource");
    return customerInfoMapper.getProjectFollowInfo(id);
}

当然,用完SqlServer数据库要去用其他功能之前要记得把数据源给切换回去,希望可以帮到部分小伙伴