Spring boot + durid + mybatis + docker for mysql read-write separation-2

Spring boot + durid + mybatis + docker for mysql read-write separation-2


In the previous article, we built a mysql replication cluster of primary and secondary, and now we separate the read and write through the test project

Project structure

Code interpretation

Data source configuration

    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://
    username: test
    password: test
      url: jdbc:mysql://
      username: test
      password: test
      url: jdbc:mysql://
      username: test
      password: test

Configure three data sources corresponding to one master and two slaves


Define an enumeration class to declare the key value of the data source

public enum DataSourceType {


To switch the key classes of data source, we use ThreadLocal to hold the above enumeration variables, and use facet (described later) to switch the slave database data source for query operation. When ThreadLocal is empty, the master source is used by default

public class DBContextHolder {

    private static ThreadLocal<DataSourceType> threadLocal = new ThreadLocal<>();

    private static void set(DataSourceType dataSourceType) {

    public static DataSourceType get() {
        return threadLocal.get() == null ? DataSourceType.MASTER : threadLocal.get();

    public static void remove() {

    public static void slave() {
        SecureRandom secureRandom = new SecureRandom();
        int i = secureRandom.nextInt(DataSourceType.values().length - 1) + 1;
        log.info("Use slave Library" +i+ "read");


The key of multi data source switching, inheriting AbstractRoutingDataSource and implementing determineCurrentLookupKey method

public class RouteDataSource extends AbstractRoutingDataSource {

    protected Object determineCurrentLookupKey() {
        return DBContextHolder.get();


Data source configuration: register all data sources in the configuration file into the container. Here, you need to register one more targetDataSource, which is a configuration for multiple data sources. A small scheduling algorithm is used for the switch of slave

public class DataSourceConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource master() {
        return new DruidDataSource();

    @ConfigurationProperties(prefix = "spring.datasource.slave1")
    public DataSource slave1() {
        return new DruidDataSource();

    @ConfigurationProperties(prefix = "spring.datasource.slave2")
    public DataSource slave2() {
        return new DruidDataSource();

    public DataSource targetDataSource(
            @Qualifier("master") DataSource master
            , @Qualifier("slave1") DataSource slave1
            , @Qualifier("slave2") DataSource slave2
            ) {

        Map<Object, Object> dataSources = new HashMap<>();
        dataSources.put(DataSourceType.MASTER, master);
        dataSources.put(DataSourceType.SLAVE1, slave1);
        dataSources.put(DataSourceType.SLAVE2, slave2);

        RouteDataSource routeDataSource = new RouteDataSource();

        return routeDataSource;


Under multiple data sources, we need to manually register SqlSessionFactoryConfig, in which targetDataSource is the dataSource bean we registered before. Related configurations of mybatis will not be described in detail

public class SqlSessionFactoryConfig {

    private DataSource targetDataSource;

    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        // Location of entity class
        // XML configuration of mybatis
        return bean.getObject();

    public DataSourceTransactionManager dataSourceTransactionManager() {
        return new DataSourceTransactionManager(targetDataSource);


Annotation class, which is used to mark the class that performs the read operation, and switch the data source with tangent

public @interface ReadOnly {


Tangent processing class, the key to data source switching. For the method of adding ReadOnly annotation (i.e. performing read operation), switch the data source to slave, and finally clear the ThreadLocal variable to avoid memory leakage (even if ThreadLocal is a weakrecurrence variable, it is also a good habit)

public class ReadOnlyAop {

    @Pointcut(value = "@annotation(com.xsn.anno.ReadOnly)")
    public void pointCut() {


    @Around(value = "pointCut()")
    public  Object changeDb(ProceedingJoinPoint proceedingJoinPoint) {
        try {
            return proceedingJoinPoint.proceed();
        } catch (Throwable e) {
            log.error(e.getMessage(), e);
        } finally {

        return null;

mapper related classes

Automatically generated through maven plug-in, no more details, and the source address will be given later


We add @ ReadOnly registration ID to get user information method. This query is executed by the slave library


New users

View all databases and add them successfully. The log console does not print and switch from database information. That is to say, the write operation is implemented by the master data source

Get user information

Similarly, getting user information, you will find

Randomly switch data source to read from library


At this point, we have achieved a simple separation of reading and writing. Although the switching of data sources is actually controlled by the programmer himself (through annotation), it is not difficult to avoid the situation of missing and wrong annotation.

Previous: Spring boot + durid + mybatis + docker for mysql read-write separation-1

Source address

Configuration center address

Keywords: MySQL Spring Mybatis JDBC

Added by prakash on Wed, 17 Jun 2020 08:59:58 +0300