Spring Boot enables you to get started with multiple data sources

1. Why do you need multiple data sources?

Multiple data sources are dynamic data sources, and project development is gradually expanding, and a single data source and a single data source can no longer meet the support needs of the demand project. In this paper, dynamic-datasource-spring-boot-starter is used to implement multiple data sources.

Key features:

• In the tank Grouping of data sources It is suitable for a variety of scenarios, such as pure multi-database, read/write splitting, and one master, many-slave mixed mode.
• Sensitive configuration information in the database is supported encrypt ENC()。
• Each database can independently initialize the schema and database.
• Support no data source start, support lazy loading data source (create a connection when needed).
• In the tank Custom annotations DS (3.2.0+) needs to be inherited.
• Provides and simplifies rapid integration with Druid, HikariCp, BeeCp, Dbcp2.
• Provide integration solutions for Mybatis-Plus, Quartz, ShardingJdbc, P6sy, Jndi and other components.
• offer Custom data source sources Scenarios (e.g., all from the database).
• Provide after the project starts Dynamically add and remove data sources Scheme.
• Available in the Mybatis environment Pure read/write splitting Scheme.
• Provision of use SPEL dynamic parameters Parse the data source schema. Built-in spel, session, header, support customization.
• In the tank Nested switching of multi-tier data sources 。 （ServiceA >>> ServiceB >>> ServiceC）。
• Provide **SETA-based distributed transaction scheme.
• offer On-premises multi-data source transaction scenarios

2. Environment construction

In this article, two MySQL data sources are used, and the tables are all structured in the same way

The first database

docker run --name docker-mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysql

The second database

docker run --name docker-mysql-2 -e MYSQL_ROOT_PASSWORD=123456 -p 3307:3306 -d mysql

Initialize the data

create database demo;
create table user_info
(
user_id     varchar(64)          not null primary key,
username    varchar(100)         null ,
age         int(3)               null ,
gender      tinyint(1)           null ,
remark      varchar(255)         null ,
create_time datetime             null ,
create_id   varchar(64)          null ,
update_time datetime             null ,
update_id   varchar(64)          null ,
enabled     tinyint(1) default 1 null
);

Note description

msyql account：root
mysql password：123456

3. Code engineering

Objective: Switch data sources

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>springboot-demo</artifactId>
        <groupId>com.et</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>
    <artifactId>dynamic-datasource</artifactId>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- dynamic-datasource -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
        <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.31</version>
        </dependency>
    </dependencies>
</project>

Code generates CRUD operations

package com.et.dynamic.datasource;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.exceptions.MybatisPlusException;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.InjectionConfig;
import com.baomidou.mybatisplus.generator.config.*;
import com.baomidou.mybatisplus.generator.config.po.TableInfo;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
import com.baomidou.mybatisplus.generator.engine.FreemarkerTemplateEngine;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class GeneratorCode {
    /**
     * database connect
     * */
    private static final String dbUrl = "jdbc:mysql://localhost:3306/demo?useUnicode=true&useSSL=false&characterEncoding=utf8";
    /**
     * username
     * */
    private static final String username = "root";
    /**
     * pasword
     * */
    private static final String password = "123456";
    /**
     * moduleName
     * */
    private static final String moduleName = "/dynamic-datasource";
    /**
     * <p>
     * read console content
     * @param
     * </p>
     */
    public static String scanner(String tip) {
        Scanner scanner = new Scanner(System.in);
        StringBuilder help = new StringBuilder();
        help.append("please input:" + tip + "：");
        System.out.println(help.toString());
        if (scanner.hasNext()) {
            String ipt = scanner.next();
            if (StringUtils.isNotBlank(ipt)) {
                return ipt;
            }
        }
        throw new MybatisPlusException("please right conntent:" + tip + "！");
    }
    public static void main(String[] args) {
        // Code Generateor
        AutoGenerator mpg = new AutoGenerator();
        String module = scanner(" module");
        // GlobalCOnfig
        GlobalConfig gc = new GlobalConfig();
        //D:\IdeaProjects\ETFramework
        String basedir ="D:/IdeaProjects/ETFramework/";
        String projectPath = basedir+moduleName;
        System.out.println(projectPath);
        //OutputDir
        gc.setOutputDir(projectPath+"/src/main/java");
        gc.setAuthor("stopping");
        //some generate rule
        gc.setMapperName("%sMapper");
        gc.setServiceName("%sService");
        gc.setServiceImplName("%sServiceImp");
        gc.setControllerName("%sController");
        gc.setXmlName("%sMapper");
        gc.setIdType(IdType.AUTO);
        gc.setOpen(false);
        //IsOverride
        gc.setFileOverride(true);
        //isSwagger2
        gc.setSwagger2(false);
        mpg.setGlobalConfig(gc);
        //datasource
        DataSourceConfig dsc = new DataSourceConfig();
        dsc.setUrl(dbUrl);
        dsc.setDriverName("com.mysql.cj.jdbc.Driver");
        dsc.setUsername(username);
        dsc.setPassword(password);
        mpg.setDataSource(dsc);
        // PackageConfig
        PackageConfig pc = new PackageConfig();
        //package path
        pc.setParent("com.et.dynamic.datasource");
        //subpackage path
        pc.setMapper("mapper."+module);
        pc.setController("controller."+module);
        pc.setService("service."+module);
        pc.setServiceImpl("service."+module+".imp");
        pc.setEntity("model.entity");
        pc.setXml("Mapper");
        mpg.setPackageInfo(pc);
        //custom config
        InjectionConfig cfg = new InjectionConfig() {
            @Override
            public void initMap() {
                // to do nothing
            }
        };
        //  freemarker
        String templatePath = "/templates/mapper.xml.ftl";
        // FileOutConfig
        List<FileOutConfig> focList = new ArrayList<>();
        focList.add(new FileOutConfig(templatePath) {
            @Override
            public String outputFile(TableInfo tableInfo) {
                // Mapper
                String xmlUrl = projectPath + "/src/main/resources/mapper/" + module
                        + "/" + tableInfo.getEntityName() + "Mapper" + StringPool.DOT_XML;
                System.out.println("xml path:"+xmlUrl);
                return xmlUrl;
            }
        });
        cfg.setFileOutConfigList(focList);
        mpg.setCfg(cfg);
        // templateConfig
        TemplateConfig templateConfig = new TemplateConfig();
        templateConfig.setXml(null);
        mpg.setTemplate(templateConfig);
        // StrategyConfig
        StrategyConfig strategy = new StrategyConfig();
        strategy.setNaming(NamingStrategy.underline_to_camel);
        strategy.setColumnNaming(NamingStrategy.underline_to_camel);
        strategy.setEntityLombokModel(true);
        strategy.setRestControllerStyle(true);
        // common file
        //strategy.setSuperEntityColumns("id");
        strategy.setInclude(scanner("tablename，multi can be seperated ,").split(","));
        strategy.setControllerMappingHyphenStyle(true);
        strategy.setTablePrefix(pc.getModuleName() + "_");
        //isAnnotationEnable
        strategy.setEntityTableFieldAnnotationEnable(true);
        mpg.setStrategy(strategy);
        mpg.setTemplateEngine(new FreemarkerTemplateEngine());
        mpg.execute();
    }
}

The generated code is as follows

Then modify the service to add the annotation of switching data sources@DS can be annotated on a method or on a class,At the same time, there is the principle of proximity, and the method annotation takes precedence over the class annotation。 noteoutcomeThere is no @DSDefault data source@DS(“dsName”)dsName can be the name of a group or a specific database

package com.et.dynamic.datasource.service.userinfo.imp;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.et.dynamic.datasource.model.entity.UserInfo;
import com.et.dynamic.datasource.mapper.userinfo.UserInfoMapper;
import com.et.dynamic.datasource.service.userinfo.UserInfoService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
/**
 * @author stopping
 * @since 2024-05-13
 */
@Service
@DS("slave_1")
public class UserInfoServiceImp extends ServiceImpl<UserInfoMapper, UserInfo> implements UserInfoService {
    @Resource
    UserInfoMapper userInfoMapper;
    @Override
    @DS("master")
    public List<UserInfo> testQueryWrapper(int age) {
        QueryWrapper<UserInfo> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.ge("age", age);
        List<UserInfo> userList = userInfoMapper.selectList(userQueryWrapper);
        return userList;
    }
}

application.yaml

The following formats are supported

spring:                               spring:                               spring:
  datasource:                           datasource:                           datasource:
    dynamic:                              dynamic:                              dynamic:
      datasource:                           datasource:                           datasource:
        master_1:                             mysql:                                master:
        master_2:                             oracle:                               slave_1:
        slave_1:                              sqlserver:                            slave_2:
        slave_2:                              postgresql:                           oracle_1:
        slave_3:                              h2:                                   oracle_2:

In this example, configure the following table:

server:
  port: 8088
spring:
  datasource:
    dynamic:
      primary: master
      strict: false
      datasource:
        master:
          url: jdbc:mysql://localhost:3306/demo?serverTimezone=Asia/Shanghai
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
        slave_1:
          url: jdbc:mysql://localhost:3307/demo?serverTimezone=Asia/Shanghai
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver

4. Testing

Test insertion

@Test
public void insert()  {
    log.info("your method test Code");
    for(int i =1;i<10;i++) {
        UserInfo ui =  new UserInfo();
        ui.setUserId(i+"id");
        userInfoService.removeById(i+"id");
        ui.setUsername("HBLOG"+i);
        ui.setAge(i);
        userInfoService.save(ui);
    }

Since the class is @DS (“slave_1”), the slave_1mysql library is expected to be inserted.

Test the query

@Test
public void testQueryWrapper()  {
    log.info("your method test Code");
    userInfoService.testQueryWrapper(3).forEach(System.out::println);
}

The @DS (“master”) of the query method annotation, so the data cannot be found on the Mater library at all

5. References

• https://baomidou.com/pages/a61e1b/
• http://www.liuhaihua.cn/archives/710543.html