Database Initialization

Set up three distinct databases: primary_db, replica_a, and replica_b. The primary database will hold the initial records, while the replicas will only contain the schema.

CREATE TABLE `inventory_item` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `sku` varchar(50) NOT NULL,
  `name` varchar(200) NOT NULL,
  `type` varchar(100) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `quantity` int NOT NULL DEFAULT 0,
  `active` tinyint NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sku` (`sku`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert sample rows into primary_db:

INSERT INTO `inventory_item` (`sku`, `name`, `type`, `price`, `quantity`, `active`) VALUES
('SKU-001', 'Advanced Gadget', 'Electronics', 499.00, 120, 1),
('SKU-002', 'Pro Widget', 'Electronics', 299.00, 85, 1),
('SKU-003', 'Mega Tool', 'Hardware', 150.00, 200, 1);

Entity Definition

@Data
@TableName("inventory_item")
public class InventoryItem {
    @TableId(type = IdType.AUTO)
    private Long id;

    @TableField("sku")
    private String sku;

    @TableField("name")
    private String name;

    @TableField("type")
    private String type;

    @TableField("price")
    private BigDecimal price;

    @TableField("quantity")
    private Integer quantity;

    @TableField("active")
    private Integer active;

    @TableField(value = "created_at", fill = FieldFill.INSERT)
    private LocalDateTime createdAt;

    @TableField(value = "updated_at", fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updatedAt;
}

Dependency Setup

Include the necessary starters for Spring Boot 3, MyBatis-Plus, dynamic datasource, and Druid connection pool.

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.2.0</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
    <version>3.5.14</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot3-starter</artifactId>
    <version>4.3.0</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.8</version>
</dependency>

Datasource Configuration

Configure the YAML file to define the primary and replica datasources. The primary attribute sets the default fallback, and strict: false allows fallback to the primary datasource if a specific one is not found.

spring:
  datasource:
    dynamic:
      primary: primary_db
      strict: false
      datasource:
        primary_db:
          url: jdbc:mysql://localhost:3306/primary_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
          username: root
          password: 12345678
          driver-class-name: com.mysql.cj.jdbc.Driver
          druid:
            initial-size: 5
            min-idle: 5
            max-active: 20
            max-wait: 60000
        replica_a:
          url: jdbc:mysql://localhost:3306/replica_a?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
          username: root
          password: 12345678
          driver-class-name: com.mysql.cj.jdbc.Driver
          druid:
            initial-size: 5
            min-idle: 5
            max-active: 20
        replica_b:
          url: jdbc:mysql://localhost:3306/replica_b?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
          username: root
          password: 12345678
          driver-class-name: com.mysql.cj.jdbc.Driver
          druid:
            initial-size: 5
            min-idle: 5
            max-active: 20

SQL Execution Interceptor

To monitor which database executes each query, implement a MyBatis interceptor that logs the connection URL and the executed statement.

@Intercepts({
    @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
public class DatasourceLoggingInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler handler = (StatementHandler) invocation.getTarget();
        MetaObject metaObj = SystemMetaObject.forObject(handler);
        String boundSql = (String) metaObj.getValue("delegate.boundSql.sql");

        if (boundSql != null && !boundSql.trim().isEmpty()) {
            Connection conn = (Connection) invocation.getArgs()[0];
            String url = conn.getMetaData().getURL();
            System.out.println("=== Datasource Interceptor ===");
            System.out.println("Target DB URL: " + url);
            System.out.println("Query: " + boundSql.trim().replaceAll("\\s+", " "));
            System.out.println("===============================");
        }
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        return target instanceof StatementHandler ? Plugin.wrap(target, this) : target;
    }

    @Override
    public void setProperties(Properties properties) {}
}

Register the interceptor in a configuration class:

@Configuration
public class MyBatisInterceptorConfig {
    @Bean
    public DatasourceLoggingInterceptor datasourceLoggingInterceptor() {
        return new DatasourceLoggingInterceptor();
    }
}

Datasource Routing with @DS

Create the mapper interface:

@Mapper
public interface InventoryMapper extends BaseMapper<InventoryItem> {}

The @DS annotation dictates the target datasource at the method or class level. Applying it on a method overrides the class-level declaration.

@Service
@RequiredArgsConstructor
public class InventoryService {
    private final InventoryMapper inventoryMapper;

    public List<InventoryItem> fetchFromPrimary() {
        return inventoryMapper.selectList(null);
    }

    @DS("replica_a")
    public List<InventoryItem> fetchFromReplicaA() {
        return inventoryMapper.selectList(null);
    }
}

AOP Proxy Constraints in Multi-Datasource Operations

When a single transaction requires interacting with multiple datasources, internal method calls within the same class annotated with @DS will fail to switch contexts. Spring AOP relies on proxies, so self-invocation bypasses the proxy, defaulting to the primary database.

Incorrect approach: Calling an annotated method locally causes primary key conflicts because both queries route to primary_db.

// This will NOT switch datasources correctly for insertIntoReplica
public void transferData() {
    List<InventoryItem> records = readFromPrimary();
    insertIntoReplica(records); // Self-invocation bypasses @DS proxy
}

@DS("replica_a")
private void insertIntoReplica(List<InventoryItem> records) {
    inventoryMapper.insert(records.get(0));
}

Correct approach: Delegate the operations to separate service classes to ensure the proxy intercepts the @DS annotations.

@Service
@RequiredArgsConstructor
public class PrimaryDataService {
    private final InventoryMapper inventoryMapper;

    @DS("primary_db")
    public List<InventoryItem> readAll() {
        return inventoryMapper.selectList(null);
    }
}

@Service
@RequiredArgsConstructor
public class ReplicaADataService {
    private final InventoryMapper inventoryMapper;

    @DS("replica_a")
    public void persistFirst(List<InventoryItem> records) {
        inventoryMapper.insert(records.get(0));
    }
}

The orchestrating service injects these dedicated components to execute cross-datasource logic successfully:

@Service
@RequiredArgsConstructor
public class DataMigrationService {
    private final PrimaryDataService primaryDataService;
    private final ReplicaADataService replicaADataService;

    public void executeTransfer() {
        List<InventoryItem> sourceRecords = primaryDataService.readAll();
        if (!sourceRecords.isEmpty()) {
            replicaADataService.persistFirst(sourceRecords);
        }
    }
}