Spring + mybais realizes multi-database read-write separation

Introduction

In projects, we often encounter problems with multiple data sources, especially for projects such as data synchronization or timed tasks. The biggest headache of multiple data sources is not to configure multiple data sources, but how to switch data sources flexibly and dynamically. For example, in a spring and mybais framework project, we often configure a dataSource to connect to the database in the spring configuration, and then bind it to the sessionFactory, and then specify the sessionFactory in the dao layer code to perform database operations.

As shown in the figure above, each piece is designated to be bound. If there are multiple data sources, it can only be in the way shown in the figure below.

It can be seen that two SessionFactory are written dead in the Dao layer code, so if there is another data source in the future, the code will need to be changed to add a SessionFactory. Obviously, this does not conform to the open and close principle.

Then the correct approach should be as follows

Single data source

Let's first look at the configuration of a single data source:

<?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>

When the above configuration is for a single data source, after configuration, we can use the Mapper interface to access the database

Multi-data source configuration method one

<?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>

The above configuration method can realize a simple read-write separation scenario, which can only be adapted to the one-read-write scenario, and requires the

The Mapper responsible for writing and the Mapper responsible for reading are subpackaged. It is inflexible to achieve separation of reading and writing in the same Mapper, so it is not recommended. Then let’s take a look at the second method below.

Multiple data source configuration method two

<?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>

Define a subclass of org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource and implement its 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();    }}

Customize an enumeration class to specify the read and write mode

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;    }}

test

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);      }}

The above method needs to specify the database source before each execution of the service method. It is very inflexible to use, so is there a more flexible way? Of course, we can achieve the purpose of dynamically switching data sources through springAOP + custom annotations. The specific implementation is as follows:

Multi-data source configuration mode three

<?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>

Custom annotation

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

@[email protected](1000001)@Componentpublic 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();    }}

Annotate the service class and specify the data source type

@Servicepublic class UserService {     @Autowired    private UserMapper userMapper;     // @DynamicDataSourceType("read")    public User selectById(Long id){        return userMapper.selectById(id);    }}

test

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);      }}

The above three methods, the third method is more recommended, because it is more flexible, and can achieve multiple write and read multiple scenarios through custom routing tests. The specific implementation is to rewrite afterPropertiesSet(), which will not be explained in detail here.

Note: The Spring version here must be 5.1.6.RELEASE and above, otherwise the AOP of Mapper will not take effect.

Reference: https://blog.csdn.net/wuyongde_0922/article/details/70655185

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