Multi-data source scheme some thinking

Microservices are so popular today that most architectures have moved to single service and single library to decouple the business relationships of the data sources to the greatest extent. However, there are still a few scenarios where multiple data sources are required. Furthermore, aside from microservices, single SpringBoot services are more common in our development with multiple data sources. This article will focus on the solutions and implementations of multiple data sources.

Mainstream multi-data source solutions

At present, the mainstream multi-data source schemes can be divided into two types from a macro perspective:

  1. Based on subcontracting
    • We divide XXMapper files for multiple data sources into different packages, and then inject a DataSource and SqlSessionFactory instance for each DataSource into the Spring container. If there are four data sources, Spring will end up with four separate DataSource and four separate SqlSessionFactory instances in the container. This design ensures that the data source is completely isolated in the DAO layer, which is the most straightforward and error-free approach.
  2. AOP – based aspect interception
    • There are many implementations of AOP, but most of them have a single SqlSessionFactory instance with multiple SqlSessionFactory instances. If you need to switch, switch to the SqlSessionFactory’s DataSource on the current thread.
    • Here are some ways:
      • Convention based: Define the mapper specification, such as what to start with, and then let AOP intercept those classes or methods to get the data source information from the class name or method name.
      • Annotation-based: Add custom data source annotations where needed, and use AOP to intercept these annotated classes and methods to switch data sources.

The advantages and disadvantages

The subcontract
  • advantages
    • The implementation is simple, straightforward, and certainly error-free.
    • It is easy to see at a glance which library the methods of this SQL are executed on.
  • disadvantages
    • Not flexible enough, i.e. I need to do extra work on Mapper subfolders.
    • The Mapper class file will be bloated, and we’ll probably have a lot of Mapper classes to manage.
    • In some scenarios, this approach is not desirable at all (or, simply using subcontracting is not desirable, as we’ll see below).
AOP
  • advantages
    • Flexible, basically all scenarios (as long as you need a scenario, AOP can be built to suit it)
  • disadvantages
    • It is troublesome to add a note, or a marker, to every place you use it, and there is no guarantee that you will not forget or miswrite it.
    • It’s so flexible, it works well, but if you don’t use it properly, the code is disgusting. (From practical work experience, with this thing, a lot of people write code do not pay attention to quality, SQL disorderly, data sources disorderly cut, code extremely disorderly)
    • Very intrusive to the code. That’s one of the things we don’t like about him. Some methods of annotation require adding data source annotations everywhere in the code, which makes no sense to the business development itself, or even switching data sources within the code logic. You don’t want to look at the business code logic of a project and suddenly be given a piece of code just to switch data sources, and Spring provides us with a number of mechanisms that are intended to be minimally invasive for writing our own code.

How to choice

First, look at the data source scenario you are using. My personal advice is not to use AOP when you can. If you have multiple data sources that are independent of each other and have no correlation at all, then I recommend subcontracting. (There is no correlation here, for example, you need order library, user library, product library, these business attributes are different from each other.) This way, although you need to install the package classification, it makes the code structure very clear, and SQL management is easy.

If you have a large number of data sources, say 20 or 30 on a project, and you might find subcontracting cumbersome, consider switching when using AOP. But think carefully about how confusing the SQL and Mapper files and data sources will be in later use. The subcontracting is heavy, but it’s clear and 100% problem-free (I’ve encountered real development where people often cut the wrong data source).

The only reason I feel I have to use the AOP approach is because the project depends on multiple libraries that have roughly the same table structure or are completely master-slave (which is the biggest disadvantage of subcontracting). For this type of library, splitting into multiple packages can result in a large number of duplicated SQL. Code redundancy is high. Switching is convenient with AOP. (For example, a taxi company divides its database according to the city. The specific data source used is the city ID. Each city has a database. And then the logic of the code inside each package is the same, right? This is obviously not desirable, and this scenario can be solved with AOP in a particularly elegant way).

It is also possible that when integrating certain frameworks, we will have to use subcontracting. For example, when we use SpringBatch, SpringBatch internally forces us to add transactions, causing the Connection to be cached by the transaction manager when switching data sources. The AOP approach just doesn’t work (at least I haven’t found an easy solution yet).

implementation

The subcontract

Define a Primary data source: the @primary annotation.

@Configuration
@MapperScan(basePackages = MasterXXXDsConfig.MAPPER_PKG, sqlSessionFactoryRef = MasterXXXDsConfig.SOURCE_SQL_SESSION_FACTORY)
public class MasterXXXConfig {

  	// DataSource DataSource Bean name
    public static final String SOURCE_NAME = "MasterXXXDs";
    // SqlSessionFactory for the data source
    public static final String SOURCE_SQL_SESSION_FACTORY = "MasterXXXSqlSessionFactory";
  	// The transaction manager for the data source
    public static final String SOURCE_TX_MANAGER = "MasterXXXTransactionManager";
    // The data source subcontracts the mapper's package fully qualified class name
    public static final String MAPPER_PKG = "xx.mapper.MasterXXX";
    // Data source mapper corresponding XML path
    public static final String MAPPER_LOCATION = "classpath:mapper/MasterXXX/*Mapper.xml";

  	/ / inject the DataSource
    @Bean(SOURCE_NAME)
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.MasterXXX")
    public DataSource dataSource(a) {
        return DataSourceBuilder.create().build();
    }

  	/ / injection SqlSessionFactory
    @Bean(SOURCE_SQL_SESSION_FACTORY)
    @Primary
    public SqlSessionFactory sqlSessionFactory(a) throws Exception {
      	/ / if use Mybatis - Plus, pay attention to the need to use MP here own definition MybatisSqlSessionFactoryBean, or SQL could not find the corresponding binding
      	// If not MP, use Mybatis' own SqlSessionFactoryBean
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        GlobalConfig globalConfig = GlobalConfigUtils.defaults();
        globalConfig.setBanner(false);
        sqlSessionFactoryBean.setGlobalConfig(globalConfig);
        sqlSessionFactoryBean.setDataSource(dataSource());
      	// Specify the XML path for mapper
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return sqlSessionFactoryBean.getObject();
    }
		
  	// Inject the transaction manager
    @Bean(SOURCE_TX_MANAGER)
    @Primary
    public DataSourceTransactionManager dataSourceTransactionManager(a) {
        return newDataSourceTransactionManager(dataSource()); }}Copy the code

Then other data source definitions are similar, except that the @primary annotation is no longer required. Such as:

@Configuration
@MapperScan(basePackages = YYYYConfig.MAPPER_PKG, sqlSessionFactoryRef = YYYYConfig.SOURCE_SQL_SESSION_FACTORY)
public class YYYYConfig {
 		/ /...
    @Bean(SOURCE_NAME)
    @ConfigurationProperties(prefix = "spring.datasource.YYYY")
    public DataSource dataSource(a) {
      / /...
    }

    @Bean(SOURCE_SQL_SESSION_FACTORY)
    public SqlSessionFactory sqlSessionFactory(a) throws Exception {
      / /...
    }

    @Bean(SOURCE_TX_MANAGER)
    public DataSourceTransactionManager dataSourceTransactionManager(a) {
      / /...}}Copy the code

Also note in the configuration:

spring:
  datasource:
    XXXMaster:
      type: com.alibaba.druid.pool.DruidDataSource
      You need to configure jdbc-URL instead of URL
      jdbc-url: jdbc:mysql://..........
      username: xxxx
      password: xxxx
Copy the code

This is what we call a multi-data source scenario, because the container has multiple datasources that work independently of each other.

AOP

The AOP approach is mostly referred to as dynamic data sources. Because they usually use a custom DataSource that defines a Map inside, When obtaining the Connection, AOP intercepts the current required DataSource, pulls the corresponding DataSource from the Map, and then getConnection gives the Connection to achieve the switch DataSource operation.

The custom

The general process is as follows:

  1. Customize a DataSource and inherit itAbstractDataSourceClass to replace SpringBoot’s automatically configured DataSource. It stores a Map of all DataSource objects.
  2. There’s a lot of different ways that we can get the right DataSource, based on the DataSource, okay
  3. And then we define the Aspect,

There are many ways to implement AOP, but I’ll give you an example of a basic implementation here :(just examples)

  • A user-defined dynamic data source needs to be configured to replace the data source automatically configured by SpringBoot

    public class DynamicDataSource extends AbstractRoutingDataSource{
    private Map<String, DataSource> dataSources = new HashMap<>();
	@Override
	protected Object determineCurrentLookupKey(a) {
		return DynamicDataSourceDsHolder.getDataSourceType();
	}
    // Get the desired DataSource based on the ThreadLocal value
    public DataSource getDataSource(a){
        returndataSources.get(determineCurrentLookupKey()); }}Copy the code

  • Define a ThreadLocalHolder to hold which data source is being used by the current thread.

    public class DynamicDataSourceDsHolder {
	private static final ThreadLocal<String> dsHolder = new ThreadLocal<String>();
    public static void setDataSourceType(String dataSourceType) {
		contextHolder.set(dataSourceType);
	}
	public static String getDataSourceType(a) {
		return contextHolder.get();
	}
	public static void clearDataSourceType(a) { contextHolder.remove(); }}Copy the code

  • Define an annotation to indicate that a data source switch is required to use the method

    @Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@Documented
public @interface DS {
	String value(a);
}
Copy the code

  • Define a section

    @Aspect
@Order(-1)
@Component
public class DynamicDataSourceAspect {
	private final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);
	@Before("execution(xxxx)")
	public void before(JoinPoint point) { Object target = point.getTarget(); String method = point.getSignature().getName(); Class<? >[] classz = target.getClass().getInterfaces(); Class<? >[] parameterTypes = ((MethodSignature) point.getSignature()).getMethod().getParameterTypes();try {
			Method m = classz[0].getMethod(method, parameterTypes);
			if(m ! =null&& m.isAnnotationPresent(TargetDataSource.class)) { TargetDataSource data = m.getAnnotation(TargetDataSource.class); DynamicDataSourceDsHolder.setDataSourceType(data.value()); }}catch(Exception e) { e.printStackTrace(); }}@After("execution(xxxx)")
	public void after(JoinPoint point) { DynamicDataSourceDsHolder.clearDataSourceType(); }}Copy the code

  • use

    @DS("master")
public void service(a){
    //xxxxx
}
Copy the code

This simple version of the multi-data source method, there are many problems in the actual use, but the general idea is there, we can use the production of open source solution: Beans dynamic data source solution.

Corn beans

We can use the dynamic data source scheme provided by Corn. It is an AOP+ThreadLocal approach that uses annotations to tag methods or classes for switching data sources, and supports many other features that can be read in the official documentation.

thinking

Actually the subcontract and AOP can be used in combination, the subcontract way faster thing is certain, because they don’t need a lot of switching operation of data source), but this performance, I can through the AOP interception different package, and then do different data source switch, also can realize the way of equivalent in the subcontract data section. In fact, we can customize many of the most suitable scenarios for our own scenarios through AOP.