Spring Boot 3 + MyBatis-Plus Multi-Datasource Architecture: The Complete Guide

后端开发

Why Multi-Datasource Is an Enterprise Must-Have

A single-datasource architecture worked fine in the early days of the internet, but as business scales up, one database simply can't handle all the traffic and complexity. Multi-datasource isn't a "nice-to-have" — it's a "must-do."

Five Core Scenarios

Scenario Typical Need Datasource Relationship Example
Read-Write Splitting Read-heavy, distribute read load One primary, multiple replicas E-commerce product detail page
Business Partitioning Physical isolation between domains Parallel & independent Order DB vs. User DB
Sharding Single table too large Horizontal sharding Log table partitioned by month
Multi-Tenancy Tenant data isolation One DB/Schema per tenant SaaS platform
Heterogeneous Sources Different storage types cooperating MySQL + PG + ES Search + transaction hybrid 
┌──────────────────────────────────────────────────────────┐
│              Single Datasource Architecture (Early)       │
│                                                          │
│    App ──────► MySQL (All reads & writes here)            │
│                                                          │
│    Problems: Read-write contention, table bloat,         │
│              cannot scale horizontally                    │
└──────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────┐
│              Multi-Datasource Architecture (Evolved)      │
│                                                          │
│    App ──┬──► MySQL-Master (Writes)                      │
│           ├──► MySQL-Slave1 (Reads)                      │
│           ├──► MySQL-Slave2 (Reads)                      │
│           ├──► OrderDB (Order Database)                   │
│           ├──► UserDB (User Database)                    │
│           └──► Elasticsearch (Search)                    │
└──────────────────────────────────────────────────────────┘

Real-world case: A social platform with 5M DAU had a single-DB peak QPS of 120K. After read-write splitting + business partitioning, the primary DB QPS dropped to 30K, and overall throughput increased 4x.

Three Multi-Datasource Architecture Patterns

Pattern Comparison Overview

Dimension In-App Multi-Datasource JDBC-Layer Proxy Standalone Proxy Layer
Representative dynamic-datasource ShardingSphere-JDBC ShardingSphere-Proxy / MyCat
Intrusiveness Low (annotations/config) Medium (replace DataSource) None (standalone process)
Ops Complexity Low Medium High
Performance Overhead Minimal Low Medium (network hop)
Feature Richness Basic switching Sharding + RW split + encryption Full-featured
Scale Fit Small-medium projects Medium-large projects Large / polyglot projects
Language Binding Java Java Language-agnostic

Pattern 1: In-App Multi-Datasource

┌─────────────────────────────────────────────┐
│              Spring Boot Application          │
│                                              │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐  │
│  │ DataSource│  │ DataSource│  │ DataSource│  │
│  │  master   │  │  slave_1  │  │  slave_2  │  │
│  └─────┬────┘  └─────┬────┘  └─────┬────┘  │
│        │             │             │        │
│  ┌─────▼─────────────▼─────────────▼────┐   │
│  │     DynamicRoutingDataSource          │   │
│  │  (ThreadLocal + @DS Annotation Route) │   │
│  └──────────────────────────────────────┘   │
└─────────────────────────────────────────────┘

Pattern 2: JDBC-Layer Proxy

┌─────────────────────────────────────────────┐
│              Spring Boot Application          │
│                                              │
│  ┌──────────────────────────────────────┐   │
│  │     ShardingSphere-JDBC (JAR)         │   │
│  │  ┌────────────────────────────────┐  │   │
│  │  │  SQL Parse → Route → Rewrite    │  │   │
│  │  │  → Execute → Merge              │  │   │
│  │  └────────────────────────────────┘  │   │
│  └──────┬──────────┬──────────┬─────────┘   │
│         │          │          │              │
│    ds_0 │     ds_1 │     ds_2 │              │
└─────────┼──────────┼──────────┼──────────────┘
          ▼          ▼          ▼
      MySQL-0    MySQL-1    MySQL-2

Pattern 3: Standalone Proxy Layer

┌────────────┐     ┌──────────────────┐     ┌─────────┐
│  App (Java) │────►│                  │     │ MySQL-0 │
├────────────┤     │  ShardingSphere  │────►├─────────┤
│  App (Go)  │────►│  -Proxy          │────►│ MySQL-1 │
├────────────┤     │  (Standalone)    │     ├─────────┤
│  App (Py)  │────►│  App-transparent │────►│ MySQL-2 │
└────────────┘     └──────────────────┘     └─────────┘

dynamic-datasource-spring-boot-starter in Practice

This is the most popular in-app multi-datasource solution, maintained by the Baomidou team with deep MyBatis-Plus integration.

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>dynamic-datasource-spring-boot3-starter</artifactId>
        <version>4.3.1</version>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
        <version>3.5.7</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

Basic Configuration

spring:
  datasource:
    dynamic:
      primary: master
      strict: true
      datasource:
        master:
          url: jdbc:mysql://localhost:3306/db_master?useSSL=false&serverTimezone=Asia/Shanghai
          username: root
          password: master_pwd
          driver-class-name: com.mysql.cj.jdbc.Driver
        slave_1:
          url: jdbc:mysql://localhost:3307/db_slave?useSSL=false&serverTimezone=Asia/Shanghai
          username: root
          password: slave_pwd
          driver-class-name: com.mysql.cj.jdbc.Driver
        slave_2:
          url: jdbc:mysql://localhost:3308/db_slave?useSSL=false&serverTimezone=Asia/Shanghai
          username: root
          password: slave_pwd
          driver-class-name: com.mysql.cj.jdbc.Driver
      hikari:
        minimum-idle: 5
        maximum-pool-size: 20
        idle-timeout: 30000
        max-lifetime: 1800000
        connection-timeout: 30000
        pool-name: DynamicHikariCP

Annotation-Based Switching with @DS

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @DS("master")
    @Override
    public void createUser(User user) {
        userMapper.insert(user);
    }

    @DS("slave_1")
    @Override
    public User getUserById(Long id) {
        return userMapper.selectById(id);
    }

    @DS("slave_2")
    @Override
    public List<User> listUsers(int pageNum, int pageSize) {
        Page<User> page = new Page<>(pageNum, pageSize);
        return userMapper.selectPage(page, null).getRecords();
    }
}

Programmatic Switching

Annotation-based switching isn't flexible enough for complex scenarios. Programmatic switching lets you decide the datasource at runtime:

@Service
public class OrderServiceImpl implements OrderService {

    @Autowired
    private OrderMapper orderMapper;

    @Override
    public Order getOrderWithDynamicDs(String tenantId) {
        String dsKey = "tenant_" + tenantId;
        return DynamicDataSourceContextHolder.push(dsKey, () -> {
            return orderMapper.selectById(tenantId);
        });
    }

    @Override
    public List<Order> batchQuery(List<String> tenantIds) {
        List<Order> result = new ArrayList<>();
        for (String tenantId : tenantIds) {
            DynamicDataSourceContextHolder.push("tenant_" + tenantId, () -> {
                result.addAll(orderMapper.selectList(null));
            });
        }
        return result;
    }
}

Custom Dynamic Datasource Routing Strategy

@Component
public class CustomDsProcessor extends DsProcessor {

    @Autowired
    private TenantDataSourceManager tenantDsManager;

    @Override
    public String determineDsKey(MethodInvocation invocation, String key) {
        if (key.startsWith("tenant#")) {
            String tenantId = extractTenantId(invocation);
            return tenantDsManager.resolveDsKey(tenantId);
        }
        return key;
    }

    private String extractTenantId(MethodInvocation invocation) {
        Object[] args = invocation.getArguments();
        for (Object arg : args) {
            if (arg instanceof TenantAware tenantAware) {
                return tenantAware.getTenantId();
            }
        }
        throw new IllegalStateException("Unable to extract tenant ID");
    }
}

AOP-Based Automatic Read-Write Splitting

@Aspect
@Component
@Order(Ordered.HIGHEST_PRECEDENCE)
public class ReadWriteSplitAspect {

    private static final Set<String> READ_METHOD_PREFIXES =
        Set.of("get", "query", "find", "list", "count", "page", "search");

    @Around("execution(* com.example..service.impl.*ServiceImpl.*(..))")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        String methodName = point.getSignature().getName();
        boolean isRead = READ_METHOD_PREFIXES.stream()
            .anyMatch(methodName::startsWith);

        if (isRead) {
            DynamicDataSourceContextHolder.push("slave_1");
        } else {
            DynamicDataSourceContextHolder.push("master");
        }

        try {
            return point.proceed();
        } finally {
            DynamicDataSourceContextHolder.poll();
        }
    }
}

MyBatis-Plus Multi-Datasource Configuration

Cross-Datasource Join Queries

MyBatis-Plus doesn't support cross-datasource JOINs natively. You need to assemble data at the application layer:

@Service
public class OrderDetailServiceImpl implements OrderDetailService {

    @Autowired
    private OrderMapper orderMapper;

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private ProductMapper productMapper;

    @DS("order_db")
    public OrderDetailVO getOrderDetail(Long orderId) {
        Order order = orderMapper.selectById(orderId);

        User user = DynamicDataSourceContextHolder.push("user_db", () -> {
            return userMapper.selectById(order.getUserId());
        });

        Product product = DynamicDataSourceContextHolder.push("product_db", () -> {
            return productMapper.selectById(order.getProductId());
        });

        return OrderDetailVO.builder()
                .order(order)
                .user(user)
                .product(product)
                .build();
    }
}

Optimized Batch Cross-Datasource Queries

@Service
public class BatchCrossDsService {

    @Autowired
    private OrderMapper orderMapper;

    @Autowired
    private UserMapper userMapper;

    public List<OrderWithUserVO> batchQuery(List<Long> orderIds) {
        List<Order> orders = DynamicDataSourceContextHolder.push("order_db", () -> {
            return orderMapper.selectBatchIds(orderIds);
        });

        List<Long> userIds = orders.stream()
                .map(Order::getUserId)
                .distinct()
                .collect(Collectors.toList());

        Map<Long, User> userMap = DynamicDataSourceContextHolder.push("user_db", () -> {
            return userMapper.selectBatchIds(userIds).stream()
                    .collect(Collectors.toMap(User::getId, Function.identity()));
        });

        return orders.stream().map(order -> {
            OrderWithUserVO vo = new OrderWithUserVO();
            vo.setOrder(order);
            vo.setUser(userMap.get(order.getUserId()));
            return vo;
        }).collect(Collectors.toList());
    }
}

Dynamic Datasource Addition and Removal

In SaaS scenarios, tenants register dynamically and need runtime datasource provisioning:

@Component
public class TenantDataSourceManager {

    @Autowired
    private DynamicRoutingDataSource dynamicRoutingDataSource;

    @Autowired
    private DataSourceProperty defaultProperty;

    private final Map<String, DataSourceProperty> tenantDsMap = new ConcurrentHashMap<>();

    public synchronized void addTenantDs(String tenantId, String url, String username, String password) {
        String dsKey = "tenant_" + tenantId;

        DataSourceProperty property = new DataSourceProperty();
        property.setUrl(url);
        property.setUsername(username);
        property.setPassword(password);
        property.setDriverClassName(defaultProperty.getDriverClassName());
        property.setLazy(true);

        HikariDataSource dataSource = property.getDataSource()
                .orElseThrow(() -> new RuntimeException("Failed to create datasource"));
        dataSource.setMaximumPoolSize(10);
        dataSource.setMinimumIdle(2);

        dynamicRoutingDataSource.addDataSource(dsKey, dataSource);
        tenantDsMap.put(dsKey, property);

        log.info("Tenant [{}] datasource added: {}", tenantId, dsKey);
    }

    public synchronized void removeTenantDs(String tenantId) {
        String dsKey = "tenant_" + tenantId;
        dynamicRoutingDataSource.removeDataSource(dsKey);
        tenantDsMap.remove(dsKey);
        log.info("Tenant [{}] datasource removed: {}", tenantId, dsKey);
    }

    public String resolveDsKey(String tenantId) {
        String dsKey = "tenant_" + tenantId;
        if (!tenantDsMap.containsKey(dsKey)) {
            throw new IllegalStateException("Tenant datasource not found: " + dsKey);
        }
        return dsKey;
    }

    public Set<String> getAllTenantDsKeys() {
        return Collections.unmodifiableSet(tenantDsMap.keySet());
    }
}

Tenant Datasource Auto-Registration Listener

@Component
@Slf4j
public class TenantDataSourceInitializer implements ApplicationRunner {

    @Autowired
    private TenantDataSourceManager tenantDsManager;

    @Autowired
    private TenantConfigRepository tenantConfigRepository;

    @Override
    public void run(ApplicationArguments args) {
        List<TenantConfig> tenants = tenantConfigRepository.findAllActive();
        for (TenantConfig tenant : tenants) {
            try {
                tenantDsManager.addTenantDs(
                    tenant.getTenantId(),
                    tenant.getDbUrl(),
                    tenant.getDbUsername(),
                    tenant.getDbPassword()
                );
            } catch (Exception e) {
                log.error("Tenant [{}] datasource initialization failed", tenant.getTenantId(), e);
            }
        }
        log.info("Initialized {} tenant datasources", tenants.size());
    }
}

Read-Write Splitting: From Configuration to Implementation

Complete Read-Write Splitting Architecture

                    ┌─────────────────┐
                    │   Application    │
                    │  @DS("master")   │──Write
                    │  @DS("slave")    │──Read
                    └────────┬────────┘
                             │
              ┌──────────────┼──────────────┐
              │              │              │
        ┌─────▼─────┐ ┌─────▼─────┐ ┌─────▼─────┐
        │  Master    │ │  Slave-1  │ │  Slave-2  │
        │  (RW)      │ │  (RO)     │ │  (RO)     │
        └─────┬─────┘ └───────────┘ └───────────┘
              │
        ┌─────▼─────┐
        │  Binlog    │
        │  Replication│
        └───────────┘

Read-Write Splitting YAML Configuration

spring:
  datasource:
    dynamic:
      primary: master
      strict: true
      datasource:
        master:
          url: jdbc:mysql://mysql-master:3306/app_db?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8mb4
          username: app_writer
          password: ${MASTER_DB_PWD}
        slave_1:
          url: jdbc:mysql://mysql-slave-1:3306/app_db?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8mb4
          username: app_reader
          password: ${SLAVE1_DB_PWD}
        slave_2:
          url: jdbc:mysql://mysql-slave-2:3306/app_db?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8mb4
          username: app_reader
          password: ${SLAVE2_DB_PWD}

Custom Load Balancing Strategy

The default round-robin strategy isn't flexible enough. Production environments need dynamic routing based on replica lag:

@Component
public class SlaveLoadBalanceStrategy implements LoadBalanceStrategy {

    @Autowired
    private MySQLReplicationMonitor replicationMonitor;

    @Override
    public String determineDsKey(List<String> slaveDsKeys, String masterDsKey) {
        List<String> availableSlaves = slaveDsKeys.stream()
                .filter(key -> {
                    long delay = replicationMonitor.getReplicationDelay(key);
                    return delay < 1000;
                })
                .collect(Collectors.toList());

        if (availableSlaves.isEmpty()) {
            log.warn("All replicas lagging too far, falling back to primary for reads");
            return masterDsKey;
        }

        int index = (int) (System.currentTimeMillis() % availableSlaves.size());
        return availableSlaves.get(index);
    }
}

Replication Lag Monitoring

@Component
@Slf4j
public class MySQLReplicationMonitor {

    private final Map<String, Long> replicationDelayMap = new ConcurrentHashMap<>();

    @Scheduled(fixedDelay = 5000)
    public void monitorReplicationDelay() {
        List<String> slaveKeys = List.of("slave_1", "slave_2");
        for (String slaveKey : slaveKeys) {
            DynamicDataSourceContextHolder.push(slaveKey, () -> {
                try (Connection conn = dataSource.getConnection();
                     Statement stmt = conn.createStatement();
                     ResultSet rs = stmt.executeQuery("SHOW SLAVE STATUS")) {
                    if (rs.next()) {
                        long secondsBehindMaster = rs.getLong("Seconds_Behind_Master");
                        replicationDelayMap.put(slaveKey, secondsBehindMaster * 1000);
                        if (secondsBehindMaster > 5) {
                            log.warn("Replica [{}] lag: {} seconds", slaveKey, secondsBehindMaster);
                        }
                    }
                } catch (SQLException e) {
                    replicationDelayMap.put(slaveKey, Long.MAX_VALUE);
                    log.error("Replica [{}] status check failed", slaveKey, e);
                }
            });
        }
    }

    public long getReplicationDelay(String slaveKey) {
        return replicationDelayMap.getOrDefault(slaveKey, 0L);
    }
}

Force-Primary Annotation

Some scenarios (e.g., read-after-write) must hit the primary:

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@DS("master")
public @interface MasterOnly {
}

@Service
public class PaymentServiceImpl implements PaymentService {

    @Autowired
    private PaymentMapper paymentMapper;

    @DS("master")
    @Override
    public Payment createPayment(PaymentDTO dto) {
        Payment payment = new Payment();
        BeanUtils.copyProperties(dto, payment);
        paymentMapper.insert(payment);
        return payment;
    }

    @MasterOnly
    @Override
    public Payment getPaymentAfterCreate(Long paymentId) {
        return paymentMapper.selectById(paymentId);
    }
}

Sharding: Deep Integration with ShardingSphere-JDBC

Sharding Architecture

┌──────────────────────────────────────────────────────┐
│                   Application Layer                    │
│                                                       │
│  ┌─────────────────────────────────────────────────┐ │
│  │           ShardingSphere-JDBC                    │ │
│  │                                                  │ │
│  │  SQL: SELECT * FROM t_order WHERE user_id=1001  │ │
│  │         │                                       │ │
│  │    ┌────▼────┐                                  │ │
│  │    │ SQL Parse│                                  │ │
│  │    └────┬────┘                                  │ │
│  │    ┌────▼────┐                                  │ │
│  │    │ Routing  │──► ds_0.t_order_1               │ │
│  │    └────┬────┘                                  │ │
│  │    ┌────▼────┐                                  │ │
│  │    │ Rewrite  │──► SELECT * FROM t_order_1 ...  │ │
│  │    └────┬────┘                                  │ │
│  │    ┌────▼────┐                                  │ │
│  │    │  Merge   │                                  │ │
│  │    └─────────┘                                  │ │
│  └─────────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────────┘

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>shardingsphere-jdbc-core</artifactId>
        <version>5.5.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
        <version>5.5.1</version>
    </dependency>
</dependencies>

Sharding Configuration

spring:
  shardingsphere:
    mode:
      type: Standalone
      repository:
        type: JDBC
    datasource:
      names: ds_0,ds_1
      ds_0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/shard_db_0?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: ds0_pwd
      ds_1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/shard_db_1?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: ds1_pwd
    rules:
      sharding:
        tables:
          t_order:
            actual-data-nodes: ds_${0..1}.t_order_${0..15}
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order-table-inline
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-db-mod
            key-generate-strategy:
              column: order_id
              key-generator-name: snowflake
          t_order_item:
            actual-data-nodes: ds_${0..1}.t_order_item_${0..15}
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order-item-table-inline
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-db-mod
        sharding-algorithms:
          order-table-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_${order_id % 16}
          order-item-table-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_item_${order_id % 16}
          order-db-mod:
            type: MOD
            props:
              sharding-count: 2
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1
    props:
      sql-show: true

Custom Sharding Algorithm

Standard modulo algorithms require data migration during scaling. Consistent hashing minimizes migration:

@Component
public class ConsistentHashShardingAlgorithm implements StandardShardingAlgorithm<Long> {

    private ConsistentHash<String> consistentHash;

    @Override
    public void init(Properties props) {
        int virtualNodes = Integer.parseInt(props.getProperty("virtual-nodes", "160"));
        List<String> actualDataNodes = Arrays.stream(props.getProperty("actual-data-nodes").split(","))
                .collect(Collectors.toList());

        HashFunction hashFunction = new Murmur3HashFunction();
        consistentHash = new ConsistentHash<>(hashFunction, virtualNodes, actualDataNodes);
    }

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        return consistentHash.get(shardingValue.getValue());
    }

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
        return availableTargetNames;
    }

    @Override
    public String getType() {
        return "CONSISTENT_HASH";
    }
}

MyBatis-Plus + ShardingSphere Integration Notes

@Configuration
@MapperScan("com.example.mapper")
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

    @Bean
    public IdentifierGenerator identifierGenerator() {
        return new CustomIdGenerator();
    }
}

public class CustomIdGenerator implements IdentifierGenerator {
    @Override
    public Number nextId(Object entity) {
        return ShardingSphereIdGenerator.nextId();
    }
}

Multi-Tenant Data Isolation: Schema-Level vs. Table-Level

Isolation Mode Comparison

Dimension Schema-Level Isolation Table-Level Isolation
Isolation Level Higher Lower
Resource Overhead Independent Schema per tenant Shared Schema, table name distinction
Ops Complexity Medium Low
Data Leak Risk Low Medium (strict filtering required)
Use Case Enterprise / Financial SMB / SaaS
Scalability Limited Schema count Limited table count

Schema-Level Isolation Implementation

@Component
public class SchemaTenantInterceptor implements InnerInterceptor {

    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
        String tenantId = TenantContextHolder.getTenantId();
        if (tenantId != null) {
            DynamicDataSourceContextHolder.push("tenant_" + tenantId);
        }
    }
}

Table-Level Isolation (MyBatis-Plus Tenant Plugin)

@Configuration
public class TenantConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(TenantLineInnerInterceptor tenantInterceptor) {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(tenantInterceptor);
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

    @Bean
    public TenantLineInnerInterceptor tenantLineInnerInterceptor() {
        return new TenantLineInnerInterceptor(new TenantLineHandler() {
            @Override
            public Expression getTenantId() {
                Long tenantId = TenantContextHolder.getTenantId();
                if (tenantId == null) {
                    throw new IllegalStateException("Tenant ID must not be null");
                }
                return new LongValue(tenantId);
            }

            @Override
            public boolean ignoreTable(String tableName) {
                return Set.of("sys_config", "sys_dict", "sys_menu")
                        .contains(tableName);
            }

            @Override
            public String getTenantIdColumn() {
                return "tenant_id";
            }
        });
    }
}

Tenant Context Management

public class TenantContextHolder {

    private static final ThreadLocal<Long> TENANT_ID = new ThreadLocal<>();

    public static void setTenantId(Long tenantId) {
        TENANT_ID.set(tenantId);
    }

    public static Long getTenantId() {
        return TENANT_ID.get();
    }

    public static void clear() {
        TENANT_ID.remove();
    }
}

Web-Layer Tenant ID Injection

@Component
@WebFilter(urlPatterns = "/*")
public class TenantFilter implements Filter {

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
            throws IOException, ServletException {
        HttpServletRequest httpRequest = (HttpServletRequest) request;
        String tenantHeader = httpRequest.getHeader("X-Tenant-Id");

        if (tenantHeader != null) {
            try {
                TenantContextHolder.setTenantId(Long.parseLong(tenantHeader));
            } catch (NumberFormatException e) {
                throw new ServletException("Invalid tenant ID: " + tenantHeader);
            }
        }

        try {
            chain.doFilter(request, response);
        } finally {
            TenantContextHolder.clear();
        }
    }
}

Transaction Management: Local / Seata Distributed / MQ Eventual Consistency

Transaction Approach Comparison

Approach Consistency Performance Complexity Use Case
Local (@DSTransactional) Strong High Low Single DS or tolerates brief inconsistency
Seata AT Mode Strong Medium High Cross-DB strong consistency required
Seata TCC Mode Strong Medium-Low Very High Core business: funds, inventory
MQ Eventual Consistency Eventual High Medium Async scenarios / latency-tolerant
Saga Pattern Eventual High High Long-running process orchestration

Local Transactions: @DSTransactional

dynamic-datasource provides @DSTransactional to maintain transactions across datasource switches:

@Service
public class OrderServiceImpl implements OrderService {

    @Autowired
    private OrderMapper orderMapper;

    @Autowired
    private OrderItemMapper orderItemMapper;

    @DSTransactional
    @DS("master")
    @Override
    public void createOrder(OrderCreateDTO dto) {
        Order order = new Order();
        order.setOrderNo(generateOrderNo());
        order.setUserId(dto.getUserId());
        order.setStatus(OrderStatus.CREATED);
        orderMapper.insert(order);

        for (OrderItemDTO itemDTO : dto.getItems()) {
            OrderItem item = new OrderItem();
            item.setOrderId(order.getId());
            item.setProductId(itemDTO.getProductId());
            item.setQuantity(itemDTO.getQuantity());
            orderItemMapper.insert(item);
        }
    }
}

Seata AT Mode Integration

┌──────────┐    ┌──────────┐    ┌──────────┐
│  Service A│───►│  Service B│───►│  Service C│
│  (Order)  │    │  (Stock)  │    │ (Account) │
└─────┬────┘    └─────┬────┘    └─────┬────┘
      │               │               │
      └───────────────┼───────────────┘
                      │
              ┌───────▼───────┐
              │  Seata Server  │
              │  (TC Coordinator)│
              └───────────────┘

<dependency>
    <groupId>io.seata</groupId>
    <artifactId>seata-spring-boot-starter</artifactId>
    <version>2.2.0</version>
</dependency>
<dependency>
    <groupId>com.alibaba.cloud</groupId>
    <artifactId>spring-cloud-starter-alibaba-seata</artifactId>
    <version>2023.0.3.2</version>
</dependency>

seata:
  enabled: true
  application-id: order-service
  tx-service-group: my_test_tx_group
  service:
    vgroup-mapping:
      my_test_tx_group: default
  registry:
    type: nacos
    nacos:
      server-addr: localhost:8848
      namespace: seata
      group: SEATA_GROUP
  config:
    type: nacos
    nacos:
      server-addr: localhost:8848
      namespace: seata
      group: SEATA_GROUP

@Service
public class BusinessServiceImpl implements BusinessService {

    @Autowired
    private OrderService orderService;

    @Autowired
    private StockService stockService;

    @Autowired
    private AccountService accountService;

    @GlobalTransactional(name = "create-order-tx", rollbackFor = Exception.class)
    @Override
    public void purchase(PurchaseDTO dto) {
        orderService.createOrder(dto);
        stockService.deductStock(dto.getProductId(), dto.getQuantity());
        accountService.deductBalance(dto.getUserId(), dto.getAmount());
    }
}

MQ Eventual Consistency

@Service
@Slf4j
public class OrderMQService {

    @Autowired
    private RocketMQTemplate rocketMQTemplate;

    @Autowired
    private OrderMapper orderMapper;

    @Autowired
    private TransactionLogMapper transactionLogMapper;

    @Transactional
    public void createOrderWithMQ(OrderCreateDTO dto) {
        Order order = new Order();
        BeanUtils.copyProperties(dto, order);
        order.setStatus(OrderStatus.CREATED);
        orderMapper.insert(order);

        TransactionLog log = new TransactionLog();
        log.setTransactionId(UUID.randomUUID().toString());
        log.setBizType("CREATE_ORDER");
        log.setBizId(order.getId());
        log.setPayload(JSON.toJSONString(dto));
        transactionLogMapper.insert(log);
    }

    @TransactionalEventListener(phase = TransactionPhase.AFTER_COMMIT)
    public void onOrderCreated(OrderCreatedEvent event) {
        rocketMQTemplate.asyncSend(
            "order-create-topic",
            MessageBuilder.withPayload(event)
                .setHeader("KEYS", event.getOrderNo())
                .build(),
            new SendCallback() {
                @Override
                public void onSuccess(SendResult sendResult) {
                    log.info("Order message sent successfully: {}", event.getOrderNo());
                }

                @Override
                public void onException(Throwable e) {
                    log.error("Order message send failed: {}", event.getOrderNo(), e);
                }
            }
        );
    }
}

Message Compensation Scheduled Task

@Component
@Slf4j
public class TransactionLogCompensator {

    @Autowired
    private TransactionLogMapper transactionLogMapper;

    @Autowired
    private RocketMQTemplate rocketMQTemplate;

    @Scheduled(fixedDelay = 30000)
    public void compensate() {
        List<TransactionLog> pendingLogs = transactionLogMapper.selectList(
            new LambdaQueryWrapper<TransactionLog>()
                .eq(TransactionLog::getStatus, 0)
                .lt(TransactionLog::getRetryCount, 5)
                .le(TransactionLog::getCreateTime, LocalDateTime.now().minusMinutes(1))
        );

        for (TransactionLog log : pendingLogs) {
            try {
                rocketMQTemplate.syncSend("order-create-topic", log.getPayload());
                log.setStatus(1);
                transactionLogMapper.updateById(log);
            } catch (Exception e) {
                log.setRetryCount(log.getRetryCount() + 1);
                transactionLogMapper.updateById(log);
                log.warn("Transaction log [{}] compensation failed, retry count: {}", log.getTransactionId(), log.getRetryCount());
            }
        }
    }
}

Monitoring & Operations: Datasource Health Checks + Micrometer Metrics

Datasource Health Check

@Component
@Slf4j
public class DataSourceHealthChecker {

    @Autowired
    private DynamicRoutingDataSource dynamicRoutingDataSource;

    @Scheduled(fixedDelay = 10000)
    public void checkAllDataSources() {
        Map<String, DataSource> dataSources = dynamicRoutingDataSource.getCurrentDataSources();

        for (Map.Entry<String, DataSource> entry : dataSources.entrySet()) {
            String dsKey = entry.getKey();
            try (Connection conn = entry.getValue().getConnection();
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT 1")) {

                if (rs.next() && rs.getInt(1) == 1) {
                    log.debug("Datasource [{}] health check passed", dsKey);
                }
            } catch (SQLException e) {
                log.error("Datasource [{}] health check failed", dsKey, e);
                alertService.sendAlert("Datasource error: " + dsKey, e.getMessage());
            }
        }
    }
}

Micrometer Metrics Collection

@Configuration
public class DataSourceMetricsConfig {

    @Bean
    public MeterDataSourceAspect meterDataSourceAspect(MeterRegistry registry) {
        return new MeterDataSourceAspect(registry);
    }

    public static class MeterDataSourceAspect {

        private final MeterRegistry registry;
        private final Counter dsSwitchCounter;
        private final Timer dsQueryTimer;

        public MeterDataSourceAspect(MeterRegistry registry) {
            this.registry = registry;
            this.dsSwitchCounter = Counter.builder("datasource.switch.count")
                    .description("Datasource switch count")
                    .register(registry);
            this.dsQueryTimer = Timer.builder("datasource.query.duration")
                    .description("Datasource query duration")
                    .tag("type", "dynamic")
                    .register(registry);
        }

        public void recordSwitch(String dsKey) {
            dsSwitchCounter.increment();
            registry.counter("datasource.switch.count", "ds", dsKey).increment();
        }

        public Timer.Sample startQuery() {
            return Timer.start(registry);
        }

        public void endQuery(Timer.Sample sample, String dsKey) {
            sample.stop(Timer.builder("datasource.query.duration")
                    .tag("ds", dsKey)
                    .register(registry));
        }
    }
}

HikariCP Connection Pool Monitoring

spring:
  datasource:
    dynamic:
      hikari:
        register-mbeans: true
        metrics-tracker: true

@Component
public class HikariMetricsExporter {

    @Autowired
    private DynamicRoutingDataSource dynamicRoutingDataSource;

    @Autowired
    private MeterRegistry meterRegistry;

    @Scheduled(fixedDelay = 15000)
    public void exportHikariMetrics() {
        Map<String, DataSource> dataSources = dynamicRoutingDataSource.getCurrentDataSources();

        for (Map.Entry<String, DataSource> entry : dataSources.entrySet()) {
            if (entry.getValue() instanceof HikariDataSource hikari) {
                HikariPoolMXBean pool = hikari.getHikariPoolMXBean();

                Gauge.builder("hikari.active.connections", pool, HikariPoolMXBean::getActiveConnections)
                        .tag("pool", entry.getKey())
                        .description("Active connections")
                        .register(meterRegistry);

                Gauge.builder("hikari.idle.connections", pool, HikariPoolMXBean::getIdleConnections)
                        .tag("pool", entry.getKey())
                        .description("Idle connections")
                        .register(meterRegistry);

                Gauge.builder("hikari.threads.awaiting", pool, HikariPoolMXBean::getThreadsAwaitingConnection)
                        .tag("pool", entry.getKey())
                        .description("Threads awaiting connection")
                        .register(meterRegistry);
            }
        }
    }
}

Prometheus + Grafana Dashboard Key Metrics

Metric Meaning Alert Threshold
hikari.active.connections Current active connections > maxPool * 0.8
hikari.threads.awaiting Threads waiting for connections > 5
datasource.switch.count Datasource switch frequency Abnormal spike
datasource.query.duration Query duration P99 > 1s
hikari.connection.creation Connection creation rate Sustained high frequency

Production-Grade Architecture & Best Practices

Complete Production Architecture

┌─────────────────────────────────────────────────────────────┐
│                       Nginx / Gateway                        │
└──────────────────────────┬──────────────────────────────────┘
                           │
┌──────────────────────────▼──────────────────────────────────┐
│                    Spring Boot Application                    │
│                                                              │
│  ┌──────────────────────────────────────────────────────┐   │
│  │                 Controller Layer                      │   │
│  │        TenantFilter → @DS → @GlobalTransactional     │   │
│  └──────────────────────────┬───────────────────────────┘   │
│                             │                                │
│  ┌──────────────────────────▼───────────────────────────┐   │
│  │                 Service Layer                         │   │
│  │     @DSTransactional / @GlobalTransactional           │   │
│  └──────────────────────────┬───────────────────────────┘   │
│                             │                                │
│  ┌──────────────────────────▼───────────────────────────┐   │
│  │          DynamicRoutingDataSource                     │   │
│  │    ┌─────────┬──────────┬──────────┬──────────┐      │   │
│  │    │ master  │ slave_1  │ order_db │ tenant_X │      │   │
│  │    └────┬────┴────┬─────┴────┬─────┴────┬─────┘      │   │
│  └─────────┼─────────┼──────────┼──────────┼────────────┘   │
└────────────┼─────────┼──────────┼──────────┼────────────────┘
             │         │          │          │
        ┌────▼───┐ ┌──▼───┐ ┌───▼──┐ ┌────▼────┐
        │Master  │ │Slave │ │Order │ │Tenant   │
        │MySQL   │ │MySQL │ │MySQL │ │MySQL    │
        └────────┘ └──────┘ └──────┘ └─────────┘

Common Pitfalls Summary

Pitfall Symptom Solution
@DS annotation not working AOP proxy prevents switching Ensure annotation on public methods, avoid same-class internal calls
Switching DS inside transaction Still in original DS transaction Use @DSTransactional instead of @Transactional
ThreadLocal leak DS identifier residue in async threads Clean up DynamicDataSourceContextHolder in finally block
Connection pool exhaustion Connection wait timeout under high concurrency Configure pool size per datasource QPS
Stale reads from replica Can't read just-written data from replica Read-after-write goes to primary or use @MasterOnly
Full table scan without shard key Queries without shard key routed to all shards Require shard key or use ES wide table
Tenant ID not injected SQL missing tenant_id condition Global tenant interceptor + whitelist tables
Distributed tx timeout Seata global transaction timeout rollback Set reasonable timeout, avoid long transactions
Dynamic DS not closed Connection pool not released after tenant deregistration Close pool when calling removeDataSource
HikariCP misconfigured minimumIdle=maximumPoolSize wastes resources Distinguish core vs. peak configuration

Connection Pool Configuration Reference

spring:
  datasource:
    dynamic:
      hikari:
        minimum-idle: 5
        maximum-pool-size: 20
        idle-timeout: 300000
        max-lifetime: 1800000
        connection-timeout: 30000
        connection-test-query: SELECT 1
        pool-name: DynamicHikariCP
      datasource:
        master:
          hikari:
            minimum-idle: 10
            maximum-pool-size: 50
        slave_1:
          hikari:
            minimum-idle: 10
            maximum-pool-size: 30
        slave_2:
          hikari:
            minimum-idle: 10
            maximum-pool-size: 30

Configuration Encryption

spring:
  datasource:
    dynamic:
      datasource:
        master:
          url: jdbc:mysql://mysql-master:3306/app_db
          username: ENC(g2U3x8vKpQ==)
          password: ENC(aB3dE7fG9hJ==)

@Configuration
public class DataSourceEncryptConfig {

    @Bean
    public DataSourcePropertySourceProcessor dataSourcePropertySourceProcessor() {
        return new DataSourcePropertySourceProcessor() {
            @Override
            public String decrypt(String cipherText) {
                if (cipherText.startsWith("ENC(")) {
                    String encrypted = cipherText.substring(4, cipherText.length() - 1);
                    return AesUtil.decrypt(encrypted, getSecretKey());
                }
                return cipherText;
            }
        };
    }
}

Graceful Shutdown

@Configuration
public class GracefulShutdownConfig {

    @Autowired
    private DynamicRoutingDataSource dynamicRoutingDataSource;

    @PreDestroy
    public void gracefulShutdown() {
        log.info("Starting graceful datasource shutdown...");

        Map<String, DataSource> dataSources = dynamicRoutingDataSource.getCurrentDataSources();
        for (Map.Entry<String, DataSource> entry : dataSources.entrySet()) {
            if (entry.getValue() instanceof HikariDataSource hikari) {
                log.info("Closing datasource [{}], active connections: {}", entry.getKey(), hikari.getHikariPoolMXBean().getActiveConnections());
                hikari.close();
            }
        }

        log.info("All datasources closed");
    }
}

Multi-Environment Configuration Management

# application-dev.yml
spring:
  datasource:
    dynamic:
      strict: false
      datasource:
        master:
          url: jdbc:mysql://localhost:3306/dev_db

# application-prod.yml
spring:
  datasource:
    dynamic:
      strict: true
      hikari:
        minimum-idle: 10
        maximum-pool-size: 50
      datasource:
        master:
          url: jdbc:mysql://mysql-master.internal:3306/prod_db
          username: ${DB_MASTER_USER}
          password: ${DB_MASTER_PWD}

Conclusion

Spring Boot 3 + MyBatis-Plus multi-datasource architecture is the standard for enterprise Java applications. Key selection recommendations:

  • Small-medium projects: dynamic-datasource-spring-boot-starter + annotation switching — simple and efficient
  • Medium-large projects: ShardingSphere-JDBC + sharding — feature-rich
  • Polyglot projects: ShardingSphere-Proxy standalone proxy layer
  • Transaction requirements: Single-DB use @DSTransactional, cross-DB use Seata AT, async use MQ eventual consistency
  • Multi-tenancy: Enterprise customers use Schema isolation, SMB use Table isolation + MyBatis-Plus tenant plugin

Multi-datasource is not a silver bullet. Evaluate whether you truly need it before adopting. If a single database suffices, don't over-engineer.

Try these browser-local tools — no sign-up required →

#MyBatis-Plus#多数据源#分库分表#读写分离#ShardingSphere#Spring Boot