Spring + mybais实现多数据库读写分离

dagecao

于 2021-06-05 12:58:50 发布

阅读量279

点赞数

分类专栏： spring

本文链接：https://blog.csdn.net/dagecao/article/details/117592039

版权

spring 专栏收录该内容

22 篇文章 1 订阅

订阅专栏

本文介绍了在Spring和MyBatis项目中如何实现动态数据源切换，包括单数据源配置、简单读写分离配置以及通过AOP和自定义注解实现的动态数据源切换。通过这种方式，可以在运行时灵活地选择读库或写库，适应多数据源需求。

摘要由CSDN通过智能技术生成

简介

项目中我们经常会遇到多数据源的问题，尤其是数据同步或定时任务等项目更是如此。多数据源让人最头痛的，不是配置多个数据源，而是如何能灵活动态的切换数据源。例如在一个spring和mybais的框架的项目中，我们在spring配置中往往是配置一个dataSource来连接数据库，然后绑定给sessionFactory，在dao层代码中再指定sessionFactory来进行数据库操作。

正如上图所示，每一块都是指定绑死的，如果是多个数据源，也只能是下图中那种方式。

可看出在Dao层代码中写死了两个SessionFactory，这样日后如果再多一个数据源，还要改代码添加一个SessionFactory，显然这并不符合开闭原则。

那么正确的做法应该是下面这样的

单个数据源

我们先来看单个数据源的时候的配置：

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

    <!--开启注解扫描 -->
    <context:annotation-config/>

    <context:component-scan base-package="com.muxi.sample.spring"/>

    <!--引入JDBC的配置文件 -->
    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:jdbc.properties</value>
            </list>
        </property>
    </bean>
    <!-- 配置数据源-->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="DataSource"/>
        <property name="mapperLocations" value="spring/mapper/*Mapper.xml"/>
    </bean>

    <bean  class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.muxi.sample.spring.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
    </bean>  
</beans>

上面的配置是单个数据源的时候，这样配置好后我们就可以使用Mapper接口访问数据库了

多数据源配置方式一

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

    <!--开启注解扫描 -->
    <context:annotation-config/>

    <context:component-scan base-package="com.muxi.sample.spring"/>

    <!--引入JDBC的配置文件 -->
    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:jdbc.properties</value>
                <value>classpath:nais-jdbc.properties</value>
            </list>
        </property>
    </bean>
    <!-- 配置write数据源-->
    <bean id="dataSourceW" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
    </bean>

     <!--配置read数据源 -->
    <bean id="DataSourceR" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${nais.driver}"/>
        <property name="url" value="${nais.url}"/>
        <property name="username" value="${nais.username}"/>
        <property name="password" value="${nais.password}"/>
    </bean>


    <bean id="sqlSessionFactoryR" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="DataSourceR"/>
        <property name="mapperLocations" value="spring/mapper/read/*Mapper.xml"/>
    </bean>

    <bean id="SqlSessionFactoryW" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSourceW"/>
        <property name="mapperLocations" value="spring/mapper/write/*Mapper.xml"/>
    </bean>

    <bean  class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--注意这里对于不同的数据源需要使用不同的包，否则会导致数据源映射错误 -->
        <property name="basePackage" value="com.muxi.sample.spring.dao.read"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactoryR"></property>
    </bean>

    <bean  class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.muxi.sample.spring.dao.write"/>
        <property name="sqlSessionFactoryBeanName" value="SqlSessionFactoryW"></property>
    </bean>
</beans>

上面的这种配置方式能够实现简单的读写分离的场景，只能适应于一读一写的场景，并且要求将

负责写的Mapper 和负责读的Mapper分包，不能实现在同一个Mapper中实现读写分离很不灵活，所以不推荐,那我们现在来看看下面的方式二

多数据源配置方式二

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

    <!--开启注解扫描 -->
    <context:annotation-config/>

    <context:component-scan base-package="com.muxi.sample.spring"/>

    <!--引入JDBC的配置文件 -->
    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:jdbc.properties</value>
                <value>classpath:nais-jdbc.properties</value>
            </list>
        </property>
    </bean>
    <!-- 配置write数据源 -->
    <bean id="dataSourceW" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
    </bean>

     <!--配置read数据源 -->
    <bean id="dataSourceR" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${nais.driver}"/>
        <property name="url" value="${nais.url}"/>
        <property name="username" value="${nais.username}"/>
        <property name="password" value="${nais.password}"/>
    </bean>

    <bean id="multipleDataSource" class="com.muxi.sample.spring.comment.MultipleDataSource">
        <property name="defaultTargetDataSource" ref="dataSourceW"/>
        <property name="targetDataSources">
            <map>
                <entry key="write" value-ref="dataSourceW"/>
                <entry key="read" value-ref="dataSourceR"/>
            </map>
        </property>
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="multipleDataSource"/>
        <property name="mapperLocations" value="spring/mapper/*/*Mapper.xml"/>
    </bean>

    <bean  class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--注意这里对于不同的数据源需要使用不同的包，否则会导致数据源映射错误 -->
        <property name="basePackage" value="com.muxi.sample.spring.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
    </bean>
</beans>

定义一个org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource的子类，并实现它的determineCurrentLookupKey()

public class MultipleDataSource extends AbstractRoutingDataSource {

    private static final ThreadLocal<String> dataSourceKey = new ThreadLocal<>();

    public static void setDataSourceKey(String key){
        dataSourceKey.set(key);
    }


    @Override
    protected Object determineCurrentLookupKey() {
        return dataSourceKey.get();
    }

    public static void clearDataSource(){
        dataSourceKey.remove();
    }
}

自定义一个枚举类指定读写方式

public enum DynamicDataSourceEnum {
    READ("read", "读"),
    WRITE("write", "写");

    private String value;
    private String desc;

    DynamicDataSourceEnum(String value, String desc) {
        this.value = value;
        this.desc = desc;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    public boolean is(String value) {
        return this.value.equalsIgnoreCase(value) ? true : false;
    }
}

测试

public class ApplicationStarterMulti {

    public static void main(String[] args) {
        ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext-multi.xml");

        MultipleDataSource.setDataSourceKey(DynamicDataSourceEnum.WRITE.getValue());
        QuestionService questionService = applicationContext.getBean("questionService", QuestionService.class);


        QuestionDO questionDO = questionService.selectById(20L);

        System.out.println(questionDO);

        MultipleDataSource.setDataSourceKey(DynamicDataSourceEnum.READ.getValue());
        UserService userService = applicationContext.getBean("userService", UserService.class);

        User user = userService.selectById(103L);

        System.out.println(user);


    }
}

上面的这个方式需要在每次执行service方法之前去指定数据库源，使用上很不灵活，那么有没有更灵活的方式呢？当然有，我们可以通过springAOP + 自定义注解的方式来达到动态切换数据源的目的，具体实现如下：

多数据源配置方式三

<?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:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
            http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
            http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd
            http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">

    <context:component-scan base-package="com.muxi.sample.spring"/>
    <!--开启注解扫描 -->
    <context:annotation-config/>
    <!--基于注解的AOP的实现 -->
    <aop:aspectj-autoproxy proxy-target-class="true"/>

    <!--引入JDBC的配置文件 -->
    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:jdbc.properties</value>
                <value>classpath:nais-jdbc.properties</value>
            </list>
        </property>
    </bean>
    <!-- 配置数据源-->
    <bean id="dataSourceW" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
    </bean>

    <!--配置nais数据源,可以理解为读写分离的场景 -->
    <bean id="dataSourceR" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${nais.driver}"/>
        <property name="url" value="${nais.url}"/>
        <property name="username" value="${nais.username}"/>
        <property name="password" value="${nais.password}"/>
    </bean>

    <bean id="multipleDataSource" class="com.muxi.sample.spring.comment.MultipleDataSource">
        <property name="defaultTargetDataSource" ref="dataSourceW"/>
        <property name="targetDataSources">
            <map>
                <entry key="write" value-ref="dataSourceW"/>
                <entry key="read" value-ref="dataSourceR"/>
            </map>
        </property>
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="multipleDataSource"/>
        <property name="mapperLocations" value="spring/mapper/*/*Mapper.xml"/>
    </bean>

    <bean  class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--注意这里对于不同的数据源需要使用不同的包，否则会导致数据源映射错误 -->
        <property name="basePackage" value="com.muxi.sample.spring.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
    </bean>

</beans>

自定义注解

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

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD,ElementType.TYPE})
public @interface DynamicDataSourceType {

    String value() default "write";
}

自定义切面Aspect

@Aspect
@Order(1000001)
@Component
public class DynamicDataSourceAspect {

    @Pointcut("@annotation(dynamicDataSourceType)")
    public void choose(DynamicDataSourceType dynamicDataSourceType){

    }

    public void pointCut(){};

    @Around("choose(dynamicDataSourceType)")
    public Object aspect(ProceedingJoinPoint joinPoint,DynamicDataSourceType dynamicDataSourceType) throws Throwable {

        String value = dynamicDataSourceType.value();
        if(DynamicDataSourceEnum.WRITE.is(value)){
            MultipleDataSource.setDataSourceKey(DynamicDataSourceEnum.WRITE.getValue());
        }else {
            MultipleDataSource.setDataSourceKey(DynamicDataSourceEnum.READ.getValue());
        }
        return joinPoint.proceed();
    }
}

在service类上加上注解并指定数据源类型

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    // @DynamicDataSourceType("read")
    public User selectById(Long id){
        return userMapper.selectById(id);
    }
}

测试

public class ApplicationStarterAnnotation {

    public static void main(String[] args) {
        ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext-anno.xml");

        QuestionService questionService = applicationContext.getBean("questionService", QuestionService.class);


        QuestionDO questionDO = questionService.selectById(20L);

        System.out.println(questionDO);

        UserService userService = applicationContext.getBean("userService", UserService.class);

        User user = userService.selectById(103L);

        System.out.println(user);


    }
}

以上三种方式，更推荐使用方式三，因为更加的灵活，并且可以通过自定义路由测试可以实现多写多读的场景具体实现是重写afterPropertiesSet(),这里就不在详细说明了

注意：这里的Spring版本要在5.1.6.RELEASE及以上，否则Mapper的AOP无法生效，这个坑货呀

参考：https://blog.csdn.net/wuyongde_0922/article/details/70655185

https://blog.csdn.net/wangpeng047/article/details/8866239