<!-- MyBatis-Plus 依賴 -->
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.4.3.4</version>
</dependency>
<!-- HikariCP 連接池 -->
<dependency>
  <groupId>com.zaxxer</groupId>
  <artifactId>hikari-cp</artifactId>
</dependency>
<!-- MySQL 驅(qū)動(dòng) -->
<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <scope>runtime</scope>
</dependency>

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/enterprise_practical_project
    username: root
    password: qwer123456
    hikari:
      maximum-pool-size: 15
      minimum-idle: 5
      connection-timeout: 30000
mybatis-plus:
  mapper-locations: classpath:cn/jjcoder/mapper/*.xml
  type-aliases-package: cn.jjcoder.entity
  configuration:
    map-underscore-to-camel-case: true

@Configuration
public class MyBatisPlusConfig {


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

    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean factory = new MybatisSqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        factory.setMapperLocations(new PathMatchingResourcePatternResolver()
                                   .getResources("classpath:cn/jjcoder/mapper/*.xml"));
        return factory.getObject();
    }
}

<!-- ShardingSphere-JDBC 核心依賴 -->
<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
<dependency>
  <groupId>org.apache.shardingsphere</groupId>
  <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
  <version>5.1.2</version>
  <exclusions>
    <!-- 排除Druid自動(dòng)配置 -->
    <exclusion>
      <groupId>com.alibaba</groupId>
      <artifactId>druid-spring-boot-starter</artifactId>
    </exclusion>
    <!-- 排除任務(wù)調(diào)度器 -->
    <exclusion>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>shardingsphere-schedule-core</artifactId>
    </exclusion>
  </exclusions>
</dependency>

spring:
    shardingsphere:
    # 公共數(shù)據(jù)源配置（錨點(diǎn)）
    common-ds: &common-ds
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: qwer123456
      hikari:
        minimum-idle: 5
        maximum-pool-size: 15
        idle-timeout: 30000

    datasource:
      names: main_db,ds2025
      main_db:
        <<: *common-ds  # 引用公共配置
        jdbc-url: jdbc:mysql://localhost:3306/enterprise_practical_project
      ds2025:
        <<: *common-ds
        jdbc-url: jdbc:mysql://localhost:3306/shard_db_2025
#      ds2026:
#        <<: *common-ds
#        jdbc-url: jdbc:mysql://localhost:3306/shard_db_2026
    rules:
      sharding:
        tables:
          standing_book:
            actual-data-nodes: ds$->{2025..2026}.standing_book_$->{202501..202512}
            database-strategy:
              standard:
                sharding-column: in_out_date
                sharding-algorithm-name: db-interval
            table-strategy:
              standard:
                sharding-column: in_out_date
                sharding-algorithm-name: table-interval
        sharding-algorithms:
          db-interval:
            type: INTERVAL
            props:
              datetime-pattern: "yyyy-MM-dd"
              datetime-lower: '2025-01-01'
              datetime-upper: '2026-12-31'
              sharding-suffix-pattern: 'yyyy'
              datetime-interval-amount: 1
              datetime-interval-unit: years
          table-interval:
            type: INTERVAL
            props:
              datetime-pattern: "yyyy-MM-dd"
              datetime-lower: '2025-01-01'
              datetime-upper: '2026-12-31'
              sharding-suffix-pattern: 'yyyyMM'
              datetime-interval-amount: 1
              datetime-interval-unit: months
              datetime-format-pattern: ^\d{4}-\d{2}-\d{2}$
    
mybatis-plus:
  mapper-scan:
    base-package: cn.jjcoder.mapper
  global-config:
    db-config:
      logic-delete-field: deleted # 全局邏輯刪除字段名
      logic-delete-value: 1 # 邏輯已刪除值
      logic-not-delete-value: 0 # 邏輯未刪除值
  configuration:
    # 配置下劃線與駝峰之間轉(zhuǎn)換
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath:cn.jjcoder.mapper/*.xml
  type-aliases-package: cn.jjcoder.entity
    

@Configuration
public class MyBatisPlusConfig {

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

    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);// 注入的是 ShardingSphere 的數(shù)據(jù)源
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                                          .getResources("classpath:cn/jjcoder/mapper/*.xml"));
        return sessionFactory.getObject();
    }

    /**
     * 使用 ShardingSphere 時(shí)，事務(wù)管理器需要指向 ShardingSphere 的數(shù)據(jù)源：
     * @param dataSource
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

/**
     * 根據(jù)商品出入庫(kù)生成臺(tái)賬
     * @param list
     * @return
     */
    @Override
    public boolean saveByProductInOrOut(List<ProductVO> list, String userName, boolean isIn) {
        String operationType = isIn ? "入庫(kù)" : "出庫(kù)";

        List<StandingBook> standingBooks = new ArrayList<>();
        for (ProductVO vo : list) {
            StandingBook standingBook = new StandingBook();
            standingBook.setProductId(vo.getId());   //產(chǎn)品id
            standingBook.setProductName(vo.getName()); //產(chǎn)品名稱
            standingBook.setOperationType(operationType);  //操作類型
            standingBook.setOperationNum(vo.getInOrOutCount());  //入庫(kù)or出庫(kù)數(shù)量
            Integer newQuantity=isIn? vo.getInventory()+vo.getInOrOutCount() : vo.getInventory()-vo.getInOrOutCount();
            standingBook.setNewQuantity(newQuantity);  //出入庫(kù)后庫(kù)存總量
            standingBook.setOrperator(userName);
            standingBook.setCreateDatetime(LocalDateTime.now());
            standingBook.setInOutDate(LocalDate.now().toString());  //設(shè)置分片鍵值(shardingsphere會(huì)根據(jù)該字段值進(jìn)行分片，路由到相應(yīng)的庫(kù)表)
            standingBooks.add(standingBook);
        }
        //該 saveOrUpdateBatchCustom 將會(huì)觸發(fā)分庫(kù)分表算法
        return this.saveOrUpdateBatchCustom(standingBooks);

    }

# mybatis-plus生成的sql
2025-05-20 20:06:48.308 DEBUG --- [onPool-worker-9] c.j.mapper.StandingBookMapper.insert     : ==>  Preparing: INSERT INTO standing_book ( standing_book_id, product_id, product_name, operation_type, in_out_date, orperator, operation_num, new_quantity, create_datetime ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? )
2025-05-20 20:06:48.316 DEBUG --- [onPool-worker-9] c.j.mapper.StandingBookMapper.insert     : ==> Parameters: f1feb93be82375630ce8f4c3a8e006f8(String), 1dbcf56794cc1cc21ea12719ea7b7a86(String), 6(String), 入庫(kù)(String), 2025-05-20(String), root(String), 1(Integer), 35(Integer), 2025-05-20T20:06:48.307(LocalDateTime)

                                                                                                                                                                                                                                                                                                             # 被ShardingSphere接管()
                                                                                                                                                                                                                                                                                                             # 邏輯sql
2025-05-20 20:06:48.322  INFO --- [onPool-worker-9] ShardingSphere-SQL                       : Logic SQL: INSERT INTO standing_book  ( standing_book_id,
                                                                                                                                       product_id,
                                                                                                                                       product_name,

                                                                                                                                       operation_type,
                                                                                                                                       in_out_date,
                                                                                                                                       orperator,
                                                                                                                                       operation_num,
                                                                                                                                       new_quantity,
                                                                                                                                       create_datetime )  VALUES  ( ?,
?,
?,

?,
?,
?,
?,
?,
? )
2025-05-20 20:06:48.322  INFO --- [onPool-worker-9] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
# 實(shí)際執(zhí)行sql
2025-05-20 20:06:48.322  INFO --- [onPool-worker-9] ShardingSphere-SQL                       : Actual SQL: ds2025 ::: INSERT INTO standing_book_202505  ( standing_book_id,
                                                                                                                                                          product_id,
                                                                                                                                                          product_name,

                                                                                                                                                          operation_type,
                                                                                                                                                          in_out_date,
                                                                                                                                                          orperator,
                                                                                                                                                          operation_num,
                                                                                                                                                          new_quantity,
                                                                                                                                                          create_datetime )  VALUES  (?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [f1feb93be82375630ce8f4c3a8e006f8, 1dbcf56794cc1cc21ea12719ea7b7a86, 6, 入庫(kù), 2025-05-20, root, 1, 35, 2025-05-20T20:06:48.307]

ShardingSphereAutoConfiguration
  → 解析配置文件
  → 創(chuàng)建多個(gè) HikariDataSource 實(shí)例
  → 根據(jù)分片規(guī)則創(chuàng)建 ShardingRule
  → 將數(shù)據(jù)源和規(guī)則組裝為 ShardingDataSource
  → 注冊(cè) ShardingDataSource 到 Spring 容器

MybatisPlusAutoConfiguration
  → 從 Spring 容器獲取唯一的 DataSource Bean（即 ShardingDataSource）
  → 創(chuàng)建 MybatisSqlSessionFactoryBean
  → 設(shè)置數(shù)據(jù)源、映射文件路徑等配置
  → 初始化 SqlSessionFactory
  → 注冊(cè) MyBatis 的 MapperScanner

@Configuration
public class DataSourceConfig {
    // 假設(shè)存在多個(gè)數(shù)據(jù)源 Bean
    @Bean
    @Primary // 指定 ShardingSphere 的數(shù)據(jù)源為主要數(shù)據(jù)源
    public DataSource shardingDataSource() throws SQLException {
        // 從 ShardingSphere 配置構(gòu)建數(shù)據(jù)源
        return ShardingSphereDataSourceFactory.createDataSource(createShardingRuleConfiguration());
    }
    // 其他數(shù)據(jù)源 Bean（非主要）
    @Bean
    public DataSource anotherDataSource() {
        // 配置其他數(shù)據(jù)源
        return DataSourceBuilder.create().build();
    }
    // 構(gòu)建 ShardingSphere 分片規(guī)則配置
    private ShardingRuleConfiguration createShardingRuleConfiguration() {
        // 配置分片規(guī)則...
    }
}

spring:
  shardingsphere:
    props:
      xa-transaction-manager-type: atomikos  # 使用 Atomikos XA 事務(wù)管理器

spring:
  shardingsphere:
    props:
      sql-show: true  # 打印 SQL
      metrics-name: prometheus  # 集成 Prometheus 監(jiān)控

@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
    sessionFactory.setDataSource(dataSource);
    sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                                      .getResources("classpath:cn/jjcoder/mapper/*.xml"));
    return sessionFactory.getObject();
}

spring:
 shardingsphere:
   props:
     xa-transaction-manager-type: atomikos # 啟用 XA 事務(wù)

@Configuration
public class TransactionConfig {
    @Bean
    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        // 使用 Atomikos 分布式事務(wù)管理器
        JtaTransactionManager transactionManager = new JtaTransactionManager();
        transactionManager.setTransactionManager(new UserTransactionManager());
        transactionManager.setUserTransaction(new UserTransactionImp());
        return transactionManager;
    }
}

spring:
   shardingsphere:
    # 公共數(shù)據(jù)源配置（錨點(diǎn)）
    common-ds: &common-ds
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: qwer123456
      hikari:
        maximum-pool-size: 30 # 增加最大連接數(shù)
        minimum-idle: 10 # 增加最小空閑連接數(shù)
        idle-timeout: 30000 # 空閑連接超時(shí)時(shí)間
        max-lifetime: 1800000 # 連接最大生命周期