Details on MyBatis multi data source configuration of read write separation

  • 2020-05-27 05:27:50
  • OfStack

MyBatis multi-data source configuration (read-write separation)

First of all, the configuration in this article USES the most straightforward approach and can be cumbersome to use in practice.

In practice, there may be a variety of combinations, you can understand the meaning of this article, do not rigid use.

Possibilities for multiple data sources

1. The master-slave

Generally, it is the case of master and slave of MySQL1. The example in this paper is the case of master and slave, but there are only two data sources, so it is not too troublesome to adopt direct configuration, but it is not conducive to the subsequent expansion. It is mainly used as an example to illustrate the actual operation, please consider carefully.

2. The depots

When the business independence is strong and the data volume is large, in order to improve the concurrency, the tables may be partitioned. After the partitioning, each database needs to configure one data source.

This situation can be referred to in this article, but it is important to note that Mapper for each database is easily distinguished and configured under different packages.

In addition, there will be a master and slave in the case of sub-library, if you have too many databases from the library, you can refer to the method provided above, or find other ways to solve.

Mapper subcontract

In the case of a repository, Mapper1 for different databases must be placed under different packages.

In the case of master and slave, the same Mapper will be read and write at the same time. It is not appropriate to create two, just use the same one. However, in this case, it is important to note that Spring automatically generates the same name for Mapper and has the same type, so it is not possible to directly inject the Mapper interface. It needs to be solved by SqlSession.

Spring base configuration

applicationContext.xml


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

  <context:component-scan base-package="com.isea533.mybatis.service"/>
  <context:property-placeholder location="classpath:config.properties"/>
  <aop:aspectj-autoproxy/>

  <import resource="spring-datasource-master.xml"/>
  <import resource="spring-datasource-slave.xml"/>
</beans>

This file is mainly introduced by spring-datasource-master.xml and spring-datasource-slave.xml.

spring-datasource-master.xml


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

  <bean id="dataSourceMaster" class="com.alibaba.druid.pool.DruidDataSource" 
    init-method="init" destroy-method="close">
    <property name="driverClassName" value="${master.jdbc.driverClass}"/>
    <property name="url" value="${master.jdbc.url}"/>
    <property name="username" value="${master.jdbc.user}"/>
    <property name="password" value="${master.jdbc.password}"/>

    <property name="filters" value="stat"/>

    <property name="maxActive" value="20"/>
    <property name="initialSize" value="1"/>
    <property name="maxWait" value="60000"/>
    <property name="minIdle" value="1"/>

    <property name="timeBetweenEvictionRunsMillis" value="60000"/>
    <property name="minEvictableIdleTimeMillis" value="300000"/>

    <property name="validationQuery" value="SELECT 'x'"/>
    <property name="testWhileIdle" value="true"/>
    <property name="testOnBorrow" value="false"/>
    <property name="testOnReturn" value="false"/>
  </bean>

  <bean id="sqlSessionFactory1" 
    class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSourceMaster"/>
    <property name="mapperLocations">
      <array>
        <value>classpath:mapper/*.xml</value>
      </array>
    </property>
  </bean>

  <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.isea533.mybatis.mapper"/>
    <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory1"/>
  </bean>

  <bean id="sqlSessionMaster" class="org.mybatis.spring.SqlSessionTemplate" scope="prototype">
    <constructor-arg index="0" ref="sqlSessionFactory1"/>
  </bean>

  <aop:config>
    <aop:pointcut id="appService" 
      expression="execution(* com.isea533.mybatis.service..*Service*.*(..))"/>
    <aop:advisor advice-ref="txAdvice1" pointcut-ref="appService"/>
  </aop:config>

  <tx:advice id="txAdvice1" transaction-manager="transactionManager1">
    <tx:attributes>
      <tx:method name="select*" read-only="true"/>
      <tx:method name="find*" read-only="true"/>
      <tx:method name="get*" read-only="true"/>
      <tx:method name="*"/>
    </tx:attributes>
  </tx:advice>

  <bean id="transactionManager1" 
   class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSourceMaster"/>
  </bean>
</beans>

spring-datasource-slave.xml

There is little difference between master and id. The main difference is the id name and the data source configuration.


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

  <bean id="dataSourceSlave" class="com.alibaba.druid.pool.DruidDataSource" 
    init-method="init" destroy-method="close">
    <property name="driverClassName" value="${slave.jdbc.driverClass}"/>
    <property name="url" value="${slave.jdbc.url}"/>
    <property name="username" value="${slave.jdbc.user}"/>
    <property name="password" value="${slave.jdbc.password}"/>

    <property name="filters" value="stat"/>

    <property name="maxActive" value="20"/>
    <property name="initialSize" value="1"/>
    <property name="maxWait" value="60000"/>
    <property name="minIdle" value="1"/>

    <property name="timeBetweenEvictionRunsMillis" value="60000"/>
    <property name="minEvictableIdleTimeMillis" value="300000"/>

    <property name="validationQuery" value="SELECT 'x'"/>
    <property name="testWhileIdle" value="true"/>
    <property name="testOnBorrow" value="false"/>
    <property name="testOnReturn" value="false"/>
  </bean>

  <bean id="sqlSessionFactory2" 
    class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSourceSlave"/>
    <property name="mapperLocations">
      <array>
        <value>classpath:mapper/*.xml</value>
      </array>
    </property>
  </bean>

  <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.isea533.mybatis.mapper"/>
    <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory2"/>
  </bean>

  <bean id="sqlSessionSlave" class="org.mybatis.spring.SqlSessionTemplate" scope="prototype">
    <constructor-arg index="0" ref="sqlSessionFactory2"/>
  </bean>


  <aop:config>
    <aop:pointcut id="appService" 
      expression="execution(* com.isea533.mybatis.service..*Service*.*(..))"/>
    <aop:advisor advice-ref="txAdvice2" pointcut-ref="appService"/>
  </aop:config>

  <tx:advice id="txAdvice2" transaction-manager="transactionManager2">
    <tx:attributes>
      <tx:method name="*" read-only="true"/>
    </tx:attributes>
  </tx:advice>

  <bean id="transactionManager2" 
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSourceSlave"/>
  </bean>
</beans>

So notice here < tx:method name="*" read-only="true"/ > It's read-only. If it is not a slave library, it can be configured as a master library.

In the following code:


<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
  <property name="basePackage" value="com.isea533.mybatis.mapper"/>
  <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory2"/>
</bean>

The different sqlSessionFactory must be specified through sqlSessionFactoryBeanName.

config.properties


#  Database configuration  - Master
master.jdbc.driverClass = com.mysql.jdbc.Driver
master.jdbc.url = jdbc:mysql://192.168.1.11:3306/test
master.jdbc.user = root
master.jdbc.password = jj

# - Slave
slave.jdbc.driverClass = com.mysql.jdbc.Driver
slave.jdbc.url = jdbc:mysql://192.168.1.22:3306/test
slave.jdbc.user = root
slave.jdbc.password = jj

Using Mapper

This is set for master and slave. The Mapper of the two configuration scans is like 1, so it cannot be injected directly. Instead, it needs to be injected in the following troublesome way.


@Service
public class DemoService {
  private CountryMapper writeMapper;
  private CountryMapper readMapper;

  @Resource(name = "sqlSessionMaster")
  public void setWriteMapper(SqlSession sqlSession) {
    this.writeMapper = sqlSession.getMapper(CountryMapper.class);
  }
  @Resource(name = "sqlSessionSlave")
  public void setReadMapper(SqlSession sqlSession) {
    this.readMapper = sqlSession.getMapper(CountryMapper.class);
  }

  public int save(Country country){
    return writeMapper.insert(country);
  }

  public List<Country> selectPage(int pageNum, int pageSize) {
    PageHelper.startPage(pageNum, pageSize);
    return readMapper.select(null);
  }
}

Since sqlSession can be distinguished by name, Mapper is obtained from sqlSession here.

In addition, if you need to consider writing and reading in the same transaction, you need to use the same writeMapper so that you can get the latest data in the transaction while reading.

So that's the master-slave case.

In the case of library partitioning, since different Mapper are in different packages, you can directly inject Mapper with @Resource or @Autowired, instead of obtaining sqlSession.

This article is just a reference of multiple data sources. Please consider it according to your own situation when applying it in practice.

Later, I will spare time, in this paper and on the basis of the above two links for MySql multiple data sources, trying to develop plug-in can automatically switch data source, because I'm not very familiar to practical application in this field, so welcome to leave a message to share their own solutions, to understand these, the more the more likely they are to develop the general data source switching plug-in.


Related articles: