Spring Boot and MyBatis realize multi-data source and dynamic data source switching

Project address github.com/helloworlde…

This project uses Spring Boot and MyBatis to realize multi-data source and dynamic data source switching; There are many different ways of implementation. In the process of learning, I found that there was no article to deal with these ways and common problems in a centralized manner, so the common ways and common problems were written in different branches of this project:

Master: Uses RESTful APIS of multiple data sources and Druid to implement dynamic data source switching and read-only data source load balancing at the DAO layer
Dev: Dynamic data source switching in the simplest form of slice and annotation
Druid: Dynamic data source switching using the Druid connection pool via sections and annotations
Aspect_dao: Dynamic data source switching at the DAO layer via facets
Roundrobin: Load balancing for read – only data sources by using the polling mode

The above branches are based on the dev branch modification or expansion, basically covers the common way of multi-data source dynamic switching, the basic principle is the same, are through the section according to different conditions before the implementation of database operation switch data source

In the process of using the basic step through all dynamic data source switching pits, some common pits and solutions are written inIssuesinside

This project uses one writable data source and several read-only data sources. In order to reduce the database pressure, the read-only data source is selected by round-robin method. Considering that there are both read and write operations in a Service, this application uses AOP facets to switch read-only data sources through DAO layer method names. This approach, however, requires consistency between the master and slave data sources. You should avoid querying immediately after a write in the same Service method. If you must read immediately after a write, you should add the @Transactional annotation to the Service method to ensure that the master data source is used

Note that the @Transactional annotation should not be applied at the Service class level after using the DAO layer slice. Instead, it should be applied to methods, as Spring recommends

Dynamic switching of data sources depends on four classes in the Configuration package, which are:

DataSourceRoutingDataSource.java

DataSourceConfigurer.java

DynamicDataSourceContextHolder.java

DynamicDataSourceAspect.java

Add the dependent

dependencies {
    compile('org. Mybatis. Spring. The boot: mybatis - spring - the boot - starter: 1.3.1')
    compile('org.springframework.boot:spring-boot-starter-web')
    compile('org.springframework.boot:spring-boot-starter-aop')
    compile('com. Alibaba: druid - spring - the boot - starter: 1.1.6')
    runtime('mysql:mysql-connector-java')
    testCompile('org.springframework.boot:spring-boot-starter-test')}Copy the code

Create databases and tables

Create databases separatelyproduct_master.product_slave_alpha.product_slave_beta.product_slave_gamma
Create tables in each of the above databasesproductAnd insert different data

DROP DATABASE IF EXISTS product_master;
CREATE DATABASE product_master;
CREATE TABLE product_master.product(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  price DOUBLE(10.2) NOT NULL DEFAULT 0);
INSERT INTO product_master.product (name, price) VALUES('master'.'1');


DROP DATABASE IF EXISTS product_slave_alpha;
CREATE DATABASE product_slave_alpha;
CREATE TABLE product_slave_alpha.product(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  price DOUBLE(10.2) NOT NULL DEFAULT 0);
INSERT INTO product_slave_alpha.product (name, price) VALUES('slaveAlpha'.'1');

DROP DATABASE IF EXISTS product_slave_beta;
CREATE DATABASE product_slave_beta;
CREATE TABLE product_slave_beta.product(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  price DOUBLE(10.2) NOT NULL DEFAULT 0);
INSERT INTO product_slave_beta.product (name, price) VALUES('slaveBeta'.'1');

DROP DATABASE IF EXISTS product_slave_gamma;
CREATE DATABASE product_slave_gamma;
CREATE TABLE product_slave_gamma.product(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  price DOUBLE(10.2) NOT NULL DEFAULT 0);
INSERT INTO product_slave_gamma.product (name, price) VALUES('slaveGamma'.'1');

Copy the code

Configuring a Data Source

application.properties

# Master datasource config
spring.datasource.druid.master.name=master
spring.datasource.druid.master.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.master.url=jdbc:mysql://localhost/product_master?useSSL=false
spring.datasource.druid.master.port=3306
spring.datasource.druid.master.username=root
spring.datasource.druid.master.password=123456

# SlaveAlpha datasource config
spring.datasource.druid.slave-alpha.name=SlaveAlpha
spring.datasource.druid.slave-alpha.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.slave-alpha.url=jdbc:mysql://localhost/product_slave_alpha?useSSL=false
spring.datasource.druid.slave-alpha.port=3306
spring.datasource.druid.slave-alpha.username=root
spring.datasource.druid.slave-alpha.password=123456

# SlaveBeta datasource config
spring.datasource.druid.slave-beta.name=SlaveBeta
spring.datasource.druid.slave-beta.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.slave-beta.url=jdbc:mysql://localhost/product_slave_beta?useSSL=false
spring.datasource.druid.slave-beta.port=3306
spring.datasource.druid.slave-beta.username=root
spring.datasource.druid.slave-beta.password=123456

# SlaveGamma datasource config
spring.datasource.druid.slave-gamma.name=SlaveGamma
spring.datasource.druid.slave-gamma.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.slave-gamma.url=jdbc:mysql://localhost/product_slave_gamma?useSSL=false
spring.datasource.druid.slave-gamma.port=3306
spring.datasource.druid.slave-gamma.username=root
spring.datasource.druid.slave-gamma.password=123456

# Druid dataSource config
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.initial-size=5
spring.datasource.druid.max-active=20
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-wait=60000
spring.datasource.druid.pool-prepared-statements=false
spring.datasource.druid.validation-query=SELECT 1
spring.datasource.druid.validation-query-timeout=30000
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.test-while-idle=true
#spring.datasource.druid.time-between-eviction-runs-millis=
#spring.datasource.druid.min-evictable-idle-time-millis=
#spring.datasource.druid.max-evictable-idle-time-millis=10000

# Druid stat filter config
spring.datasource.druid.filters=stat,wall,log4j
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.web-stat-filter.session-stat-enable=true
spring.datasource.druid.web-stat-filter.session-stat-max-count=10
spring.datasource.druid.web-stat-filter.principal-session-name=user
#spring.datasource.druid.web-stat-filter.principal-cookie-name=
spring.datasource.druid.web-stat-filter.profile-enable=true
spring.datasource.druid.filter.stat.db-type=mysql
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=1000
spring.datasource.druid.filter.stat.merge-sql=true
spring.datasource.druid.filter.wall.enabled=true
spring.datasource.druid.filter.wall.config.delete-allow=true
spring.datasource.druid.filter.wall.config.drop-table-allow=false
spring.datasource.druid.filter.slf4j.enabled=true
# Druid manage page config
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=true
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin
#spring.datasource.druid.stat-view-servlet.allow=
#spring.datasource.druid.stat-view-servlet.deny=
spring.datasource.druid.use-global-data-source-stat=true
# Druid AOP config
spring.datasource.druid.aop-patterns=cn.com.hellowood.dynamicdatasource.service.*
spring.aop.proxy-target-class=true

# MyBatis config
mybatis.type-aliases-package=cn.com.hellowood.dynamicdatasource.mapper
mybatis.mapper-locations=mappers/**Mapper.xml
server.port=9999
Copy the code

Configuring a Data Source

DataSourceKey.java

package cn.com.hellowood.dynamicdatasource.common;

public enum DataSourceKey {
    master,
    slaveAlpha,
    slaveBeta,
    slaveGamma
}

Copy the code

DataSourceRoutingDataSource.java

The class inherits from AbstractRoutingDataSource class, in the access database called when the class of determineCurrentLookupKey () method to obtain the key of the database instance

package cn.com.hellowood.dynamicdatasource.configuration;

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

public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    @Override
    protected Object determineCurrentLookupKey(a) {
        logger.info("Current DataSource is [{}]", DynamicDataSourceContextHolder.getDataSourceKey());
        returnDynamicDataSourceContextHolder.getDataSourceKey(); }}Copy the code

DataSourceConfigurer.java

Data source configuration class in which multiple data source instances are generated and injected into ApplicationContext

package cn.com.hellowood.dynamicdatasource.configuration;

import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

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

@Configuration
public class DataSourceConfigurer {

    /**
     * master DataSource
     * @PrimaryThe annotation is used to identify the default DataSource Bean, since there are five DataSource beans, This annotation can be used for the master * or slave DataSource Bean, but not for the dynamicDataSource Bean, which would result in a loop call to * *@ConfigurationPropertiesThe annotations are used to read the configuration from the application.properties file and set the properties * for the Bean@return data source
     */
    @Bean("master")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.druid.master")
    public DataSource master(a) {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * Slave alpha data source.
     *
     * @return the data source
     */
    @Bean("slaveAlpha")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave-alpha")
    public DataSource slaveAlpha(a) {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * Slave beta data source.
     *
     * @return the data source
     */
    @Bean("slaveBeta")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave-beta")
    public DataSource slaveBeta(a) {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * Slave gamma data source.
     *
     * @return the data source
     */
    @Bean("slaveGamma")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave-gamma")
    public DataSource slaveGamma(a) {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * Dynamic data source.
     *
     * @return the data source
     */
    @Bean("dynamicDataSource")
    public DataSource dynamicDataSource(a) {
        DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
        Map<Object, Object> dataSourceMap = new HashMap<>(4);
        dataSourceMap.put(DataSourceKey.master.name(), master());
        dataSourceMap.put(DataSourceKey.slaveAlpha.name(), slaveAlpha());
        dataSourceMap.put(DataSourceKey.slaveBeta.name(), slaveBeta());
        dataSourceMap.put(DataSourceKey.slaveGamma.name(), slaveGamma());

        // Use the master data source as the default specified data source
        dynamicRoutingDataSource.setDefaultTargetDataSource(master());
        // Use the master and slave data sources as the specified data sources
        dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);

        // Put the data source's key into the data source context's key set, which is used to determine whether the data source is valid when switching
        DynamicDataSourceContextHolder.dataSourceKeys.addAll(dataSourceMap.keySet());

        // Put the Slave data source key in the collection for round robin
        DynamicDataSourceContextHolder.slaveDataSourceKeys.addAll(dataSourceMap.keySet());
        DynamicDataSourceContextHolder.slaveDataSourceKeys.remove(DataSourceKey.master.name());
        return dynamicRoutingDataSource;
    }   
    
    /** * Configure SqlSessionFactoryBean *@ConfigurationPropertiesMyBatis mapper location and persistence interface alias is set to the properties of the Bean. If *. XML is not used, this configuration can be omitted@return the sql session factory bean
     */
    @Bean
    @ConfigurationProperties(prefix = "mybatis")
    public SqlSessionFactoryBean sqlSessionFactoryBean(a) {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        If dynamicDataSource is not used as the data source, the switch cannot be implemented
        sqlSessionFactoryBean.setDataSource(dynamicDataSource());
        return sqlSessionFactoryBean;
    }
    
    / * * * injection DataSourceTransactionManager * / for transaction management
    @Bean
    public PlatformTransactionManager transactionManager(a) {
        return newDataSourceTransactionManager(dynamicDataSource()); }}Copy the code

DynamicDataSourceContextHolder.java

This class is configured for the data source context and is used to switch data sources

package cn.com.hellowood.dynamicdatasource.configuration;


import cn.com.hellowood.dynamicdatasource.common.DataSourceKey;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

public class DynamicDataSourceContextHolder {

    private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);

    /** * is used to ensure that the data source will not be modified by other threads */
    private static Lock lock = new ReentrantLock();

    /** * a round-robin counter */
    private static int counter = 0;

    /** * Maintain variable for every thread, to avoid effect other thread */
    private static final ThreadLocal<Object> CONTEXT_HOLDER = ThreadLocal.withInitial(DataSourceKey.master);


    /** * All DataSource List */
    public static List<Object> dataSourceKeys = new ArrayList<>();

    /** * The constant slaveDataSourceKeys. */
    public static List<Object> slaveDataSourceKeys = new ArrayList<>();

    /**
     * To switch DataSource
     *
     * @param key the key
     */
    public static void setDataSourceKey(String key) {
        CONTEXT_HOLDER.set(key);
    }

    /** * Use master data source. */
    public static void useMasterDataSource(a) {
        CONTEXT_HOLDER.set(DataSourceKey.master);
    }

    /** * Select the data source to use in round robin mode when using read-only data sources */
    public static void useSlaveDataSource(a) {
        lock.lock();

        try {
            int datasourceKeyIndex = counter % slaveDataSourceKeys.size();
            CONTEXT_HOLDER.set(String.valueOf(slaveDataSourceKeys.get(datasourceKeyIndex)));
            counter++;
        } catch (Exception e) {
            logger.error("Switch slave datasource failed, error message is {}", e.getMessage());
            useMasterDataSource();
            e.printStackTrace();
        } finally{ lock.unlock(); }}/**
     * Get current DataSource
     *
     * @return data source key
     */
    public static String getDataSourceKey(a) {
        return CONTEXT_HOLDER.get();
    }

    /** * To set DataSource as default */
    public static void clearDataSourceKey(a) {
        CONTEXT_HOLDER.remove();
    }

    /**
     * Check if give DataSource is in current DataSource list
     *
     * @param key the key
     * @return boolean boolean
     */
    public static boolean containDataSourceKey(String key) {
        returndataSourceKeys.contains(key); }}Copy the code

DynamicDataSourceAspect.java

Dynamic data switching plane, cutting DAO layer, through the DAO layer method name to judge which one to use data source, realize the data source switch can can not set about aspects of the Order, because the lowest @ Transactional, depends on other aspects of setting, And in the org. Springframework. Core. The annotation. AnnotationAwareOrderComparator will reorder

package cn.com.hellowood.dynamicdatasource.configuration;

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

@Aspect
@Component
public class DynamicDataSourceAspect {
    private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);

    private final String[] QUERY_PREFIX = {"select"};

    @Pointcut("execution( * cn.com.hellowood.dynamicdatasource.mapper.*.*(..) )")
    public void daoAspect(a) {}@Before("daoAspect()")
    public void switchDataSource(JoinPoint point) {
        Boolean isQueryMethod = isQueryMethod(point.getSignature().getName());
        if (isQueryMethod) {
            DynamicDataSourceContextHolder.useSlaveDataSource();
            logger.info("Switch DataSource to [{}] in Method [{}]", DynamicDataSourceContextHolder.getDataSourceKey(), point.getSignature()); }}@After("daoAspect())")
    public void restoreDataSource(JoinPoint point) {
        DynamicDataSourceContextHolder.clearDataSourceKey();
        logger.info("Restore DataSource to [{}] in Method [{}]",
                DynamicDataSourceContextHolder.getDataSourceKey(), point.getSignature());
    }

    private Boolean isQueryMethod(String methodName) {
        for (String prefix : QUERY_PREFIX) {
            if (methodName.startsWith(prefix)) {
                return true; }}return false; }}Copy the code

Configure the Product REST API

ProductController.java

package cn.com.hellowood.dynamicdatasource.controller;

import cn.com.hellowood.dynamicdatasource.common.CommonResponse;
import cn.com.hellowood.dynamicdatasource.common.ResponseUtil;
import cn.com.hellowood.dynamicdatasource.modal.Product;
import cn.com.hellowood.dynamicdatasource.service.ProductService;
import cn.com.hellowood.dynamicdatasource.utils.ServiceException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/product")
public class ProductController {

    @Autowired
    private ProductService productService;

    @GetMapping("/{id}")
    public CommonResponse getProduct(@PathVariable("id") Long productId) throws ServiceException {
        return ResponseUtil.generateResponse(productService.select(productId));
    }

    @GetMapping
    public CommonResponse getAllProduct(a) {
        return ResponseUtil.generateResponse(productService.getAllProduct());
    }

    @PutMapping("/{id}")
    public CommonResponse updateProduct(@PathVariable("id") Long productId, @RequestBody Product newProduct) throws ServiceException {
        return ResponseUtil.generateResponse(productService.update(productId, newProduct));
    }

    @DeleteMapping("/{id}")
    public CommonResponse deleteProduct(@PathVariable("id") long productId) throws ServiceException {
        return ResponseUtil.generateResponse(productService.delete(productId));
    }

    @PostMapping
    public CommonResponse addProduct(@RequestBody Product newProduct) throws ServiceException {
        returnResponseUtil.generateResponse(productService.add(newProduct)); }}Copy the code

ProductService.java

package cn.com.hellowood.dynamicdatasource.service;

import cn.com.hellowood.dynamicdatasource.mapper.ProductDao;
import cn.com.hellowood.dynamicdatasource.modal.Product;
import cn.com.hellowood.dynamicdatasource.utils.ServiceException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
public class ProductService {

    @Autowired
    private ProductDao productDao;

    public Product select(long productId) throws ServiceException {
        Product product = productDao.select(productId);
        if (product == null) {
            throw new ServiceException("Product:" + productId + " not found");
        }
        return product;
    }

    @Transactional(rollbackFor = DataAccessException.class)
    public Product update(long productId, Product newProduct) throws ServiceException {

        if (productDao.update(newProduct) <= 0) {
            throw new ServiceException("Update product:" + productId + "failed");
        }
        return newProduct;
    }

    @Transactional(rollbackFor = DataAccessException.class)
    public boolean add(Product newProduct) throws ServiceException {
        Integer num = productDao.insert(newProduct);
        if (num <= 0) {
            throw new ServiceException("Add product failed");
        }
        return true;
    }

    @Transactional(rollbackFor = DataAccessException.class)
    public boolean delete(long productId) throws ServiceException {
        Integer num = productDao.delete(productId);
        if (num <= 0) {
            throw new ServiceException("Delete product:" + productId + "failed");
        }
        return true;
    }

    public List<Product> getAllProduct(a) {
        returnproductDao.getAllProduct(); }}Copy the code

ProductDao.java

package cn.com.hellowood.dynamicdatasource.mapper;

import cn.com.hellowood.dynamicdatasource.modal.Product;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface ProductDao {
    Product select(@Param("id") long id);

    Integer update(Product product);

    Integer insert(Product product);

    Integer delete(long productId);

    List<Product> getAllProduct(a);
}

Copy the code

ProductMapper.xml

/product/1 returns all data in the product table in the product_master database. Product_slave_alpha = product_slave_beta = product_slave_gamma = product_slave_alpha = product_slave_beta = product_slave_gamma Dynamic switching between data sources is valid

Pay attention to

The @Transactional annotation cannot be applied to classes, but only to methods, because the DAO layer is used to switch data sources. Methods with @trasactional annotations do not switch data sources

Spring Boot and MyBatis realize multi-data source and dynamic data source switching

In the process of using the basic step through all dynamic data source switching pits, some common pits and solutions are written inIssuesinside

Add the dependent

Create databases and tables

Configuring a Data Source

Configuring a Data Source

Configure the Product REST API

Pay attention to

Related Posts

Develop required test skills, Junit+Mock+Assert+DevOps

Kibana: Create a link to Drilldown-7.11 version for Dashboard

Leetcode Problem of the Day Series – Range Summation II- “Mathematical Simulation”