Spring Mybatis 实现主从数据库/多数据源切换配置

开发项目用到数据库  db2 某个功能需要从SQL server数据库取数据 对比  

在此把代码整理 发上来  不然 都忘了

一、配置文件

注意: 数据库连接池 看项目不同 做对应调整即可

<!-- 启动AspectJ支持   只对扫描过的bean有效-->
    <aop:aspectj-autoproxy proxy-target-class="true" />

 <!-- base dataSource -->
    <bean name="baseDataSource" class="org.apache.commons.dbcp.BasicDataSource"
          destroy-method="close">
        <property name="initialSize" value="${jdbc.initialSize}"/>
        <property name="minIdle" value="${jdbc.minIdle}"/>
        <property name="maxIdle" value="${jdbc.maxIdle}"/>
        <property name="maxActive" value="${jdbc.maxActive}"/>
        <property name="maxWait" value="${jdbc.maxWait}"/>
        <property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}"/>
        <property name="removeAbandoned" value="${jdbc.removeAbandoned}"/>
        <property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}"/>
        <property name="testWhileIdle" value="${jdbc.testWhileIdle}"/>
        <property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}"/>
        <property name="numTestsPerEvictionRun" value="${jdbc.numTestsPerEvictionRun}"/>
        <property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}"/>
    </bean>
    <!-- 主库 DB2 -->
    <bean name="master-dataSource" parent="baseDataSource">
        <property name="driverClassName" value="${jdbc.db2.driver}"/>
        <property name="url" value="${jdbc.db2.url}" />
        <property name="username" value="${jdbc.db2.username}" />
        <property name="password" value="${jdbc.db2.password}" />
    </bean>
    <!-- 从库 SQL Server -->
    <bean name="slave-dataSource" parent="baseDataSource">
        <property name="driverClassName" value="${jdbc.sqlserver.driver}"/>
        <property name="url" value="${jdbc.sqlserver.url}" />
        <property name="username" value="${jdbc.sqlserver.username}" />
        <property name="password" value="${jdbc.sqlserver.password}" />
    </bean>
    <!--主从库选择 -->
    <bean id="dynamicDataSource" class="com.icss.barcode.monitoring.common.datasource.DynamicDataSource">
        <property name="master" ref="master-dataSource" />
        <property name="slaves" ref="slave-dataSource"/>
    </bean>

    <!-- 定义事务 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dynamicDataSource" />
    </bean>

    <!-- 配置 Annotation 驱动，扫描@Transactional注解的类定义事务  -->
    <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true"/>

配置文件设置

#============================================================================
# 通用配置
#============================================================================
jdbc.initialSize=5
jdbc.minIdle=5
jdbc.maxIdle=20
jdbc.maxActive=100
jdbc.maxWait=100000
jdbc.defaultAutoCommit=false
jdbc.removeAbandoned=true
jdbc.removeAbandonedTimeout=600
jdbc.testWhileIdle=true
jdbc.timeBetweenEvictionRunsMillis=60000
jdbc.numTestsPerEvictionRun=20
jdbc.minEvictableIdleTimeMillis=300000

#============================================================================
# DB2
#============================================================================
jdbc.db2.driver=com.ibm.db2.jcc.DB2Driver

##本地
jdbc.db2.url=jdbc:db2://localhost:50000/barcode
jdbc.db2.username=db2inst1
jdbc.db2.password=db2admin


#============================================================================
# MS SQL Server
#============================================================================
jdbc.sqlserver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.sqlserver.url=jdbc:sqlserver://127.0.0.1:1401
jdbc.sqlserver.username=sa
jdbc.sqlserver.password=gptrbrt5683RR

数据库安装:

docker 安装db2

docker 安装 SQL  server

 

注解 实现类

package com.icss.barcode.monitoring.common.datasource;

import com.icss.barcode.monitoring.common.annotation.DataSourceChange;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

/**
 * aop 注解 选择数据库
 * @author Haiming
 * @date 2020/8/7 11:40 AM
 */
@Aspect
@Component
public class DataSourceAspect {
    private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceAspect.class);

    @Around("@annotation(dataSourceChange)")
    public Object doAround(ProceedingJoinPoint pjp, DataSourceChange dataSourceChange) {
        Object retVal = null;
        boolean selectedDataSource = false;
        try {
            if (null != dataSourceChange) {
                selectedDataSource = true;
                if (dataSourceChange.slave()) {
                    DynamicDataSource.useSlave();
                } else {
                    DynamicDataSource.useMaster();
                }
            }
            retVal = pjp.proceed();
        } catch (Throwable e) {
            LOGGER.warn("数据源切换错误", e);
            throw new RuntimeException("数据源切换错误", e);
        } finally {
            if (selectedDataSource) {
                DynamicDataSource.reset();
            }
        }
        return retVal;
    }
}

package com.icss.barcode.monitoring.common.datasource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;

/**
 * 配置主从数据源后，根据选择，返回对应的数据源
 * https://blog.csdn.net/itdragons/article/details/52578892
 * @author Haiming
 * @date 2020/8/7 11:01 AM
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    private static final Logger LOGGER = LoggerFactory.getLogger(DynamicDataSource.class);
    // 主库，只允许有一个
    private DataSource master;
    private DataSource slaves;
    private static final String DEFAULT = "master";
    private static final String SLAVE = "slave";

    private Map<Object, Object> dataSources = new HashMap<Object, Object>();

    private static final ThreadLocal<LinkedList<String>> datasourceHolder = new ThreadLocal<LinkedList<String>>() {
        @Override
        protected LinkedList<String> initialValue() {
            return new LinkedList<String>();
        }
    };


    /**
     * 初始化
     */
    @Override
    public void afterPropertiesSet() {
        if (null == master) {
            throw new IllegalArgumentException("Property 'master' is required");
        }
        dataSources.put(DEFAULT, master);
        dataSources.put(SLAVE, slaves);
        this.setDefaultTargetDataSource(master);
        this.setTargetDataSources(dataSources);
        super.afterPropertiesSet();
    }

    /**
     * 选择使用主库，并把选择放到当前ThreadLocal的栈顶
     */
    public static void useMaster() {
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("use datasource :" + datasourceHolder.get());
        }
        LinkedList<String> m = datasourceHolder.get();
        m.offerFirst(DEFAULT);
    }

    /**
     * 选择使用从库，并把选择放到当前ThreadLocal的栈顶
     */
    public static void useSlave() {
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("use datasource :" + datasourceHolder.get());
        }
        LinkedList<String> m = datasourceHolder.get();
        m.offerFirst(SLAVE);
    }

    /**
     * 重置当前栈
     */
    public static void reset() {
        LinkedList<String> m = datasourceHolder.get();
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("reset datasource {}", m);
        }
        if (m.size() > 0) {
            m.poll();
        }
    }

    /**
     * 如果是选择使用从库
     * 计算结果返回AbstractRoutingDataSource
     */
    @Override
    protected Object determineCurrentLookupKey() {
        LinkedList<String> m = datasourceHolder.get();
        String key = m.peekFirst() == null ? DEFAULT : m.peekFirst();
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("currenty datasource :" + key);
        }
        if (null != key) {
            if (DEFAULT.equals(key)) {
                return key;
            } else if (SLAVE.equals(key)) {
                return SLAVE;
            }
            return null;
        } else {
            return null;
        }
    }

    public DataSource getMaster() {
        return master;
    }

    public void setMaster(DataSource master) {
        this.master = master;
    }

    public DataSource getSlaves() {
        return slaves;
    }

    public void setSlaves(DataSource slaves) {
        this.slaves = slaves;
    }
}

package com.icss.barcode.monitoring.common.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 切换数据库注解
 * @author haiming
 */
@Inherited
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSourceChange {
    boolean slave() default false;
}

实际使用 添加注解即可:  @DataSourceChange(slave = true)

    /**
     * 查询S_BILL_BASE信息
     *
     * @param bbId S_BILL_BASEID
     * @return S_BILL_BASE信息
     */
    @Override
    @DataSourceChange(slave = true)
    public SBillBase selectSBillBaseById(Long bbId){
        return sBillBaseMapper.selectSBillBaseById(bbId);
    }

    @Override
    @DataSourceChange(slave = true)
    public List<SBillBase> selectSBillBaseBybbContactNo(List<String> list) {
        return sBillBaseMapper.selectSBillBaseBybbContactNo(list);
    }

    /**
     * 查询S_BILL_BASE列表
     *
     * @param sBillBase S_BILL_BASE信息
     * @return S_BILL_BASE集合
     */
    @Override

    @DataSourceChange(slave = true)
    public List<SBillBase> selectSBillBaseList(SBillBase sBillBase){
        return sBillBaseMapper.selectSBillBaseList(sBillBase);
    }

如有问题 欢迎留言

码云主页:https://gitee.com/TomYule

GitHub: https://github.com/TomYule

如果对您有帮助 欢迎点赞 收藏 转发