<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version> <!-- 請(qǐng)使用最新穩(wěn)定版本 -->
</dependency>
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

spring:
  shardingsphere:
    datasource:
      names: ds0
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0
        username: root
        password: 123456
    rules:
      sharding:
        tables:
          t_order: # 邏輯表名
            actual-data-nodes: ds0.t_order_$->{0..1} # 實(shí)際數(shù)據(jù)節(jié)點(diǎn)，ds0庫(kù)下t_order_0, t_order_1兩張表
            table-strategy:
              standard:
                sharding-column: order_id # 分片字段
                sharding-algorithm-name: table-inline # 分表算法名稱
        sharding-algorithms:
          table-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_$->{order_id % 2} # 分片算法表達(dá)式，按order_id取模分到兩個(gè)表
    props:
      sql-show: true # 打印SQL，方便調(diào)試

@Autowired
private JdbcTemplate jdbcTemplate;
public void demo() {
    // 插入一條order_id為123的訂單，根據(jù) 123 % 2 = 1，會(huì)路由到 t_order_1 表
    String sql = "INSERT INTO t_order (order_id, user_id, amount) VALUES (?, ?, ?)";
    jdbcTemplate.update(sql, 123L, 1000L, 200.00);
    // 查詢order_id為123的訂單，同樣會(huì)路由到 t_order_1 表
    List<Map<String, Object>> orders = jdbcTemplate.queryForList("SELECT * FROM t_order WHERE order_id = ?", 123L);
}

spring:
  shardingsphere:
    datasource:
      names: ds0, ds1 # 定義兩個(gè)數(shù)據(jù)源
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0
        username: root
        password: 123456
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds1
        username: root
        password: 123456
    rules:
      sharding:
        tables:
          t_order:
            # 實(shí)際數(shù)據(jù)節(jié)點(diǎn)，兩個(gè)庫(kù)，每個(gè)庫(kù)一張表（也可每個(gè)庫(kù)多張表）
            actual-data-nodes: ds$->{0..1}.t_order
            database-strategy: # 分庫(kù)策略
              standard:
                sharding-column: user_id # 分庫(kù)字段
                sharding-algorithm-name: database-inline # 分庫(kù)算法名稱
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 2} # 按user_id取模分庫(kù)
    props:
      sql-show: true

@Autowired
private JdbcTemplate jdbcTemplate;
public void demo() {
    // 插入一條user_id為1001的訂單，根據(jù) 1001 % 2 = 1，會(huì)路由到 ds1 庫(kù)的 t_order 表
    String sql = "INSERT INTO t_order (order_id, user_id, amount) VALUES (?, ?, ?)";
    jdbcTemplate.update(sql, 456L, 1001L, 300.00);
    // 查詢user_id為1001的訂單，同樣會(huì)路由到 ds1 庫(kù)
    List<Map<String, Object>> orders = jdbcTemplate.queryForList("SELECT * FROM t_order WHERE user_id = ?", 1001L);
}

spring:
  shardingsphere:
    datasource:
      names: ds0
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0
        username: root
        password: 123456
    rules:
      sharding:
        tables:
          t_order:
            actual-data-nodes: ds0.t_order_$->{202410..202412} # 假設(shè)配置未來(lái)幾個(gè)月的表
            table-strategy:
              standard:
                sharding-column: create_time # 分片字段為創(chuàng)建時(shí)間
                sharding-algorithm-name: table-time-month # 使用自定義的時(shí)間按月分表算法
        sharding-algorithms:
          table-time-month:
            type: CLASS_BASED # 使用自定義算法
            props:
              strategy: standard
              algorithmClassName: com.yourpackage.algorithm.TimeMonthShardingAlgorithm # 自定義算法類(lèi)
    props:
      sql-show: true

import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
public class TimeMonthShardingAlgorithm implements StandardShardingAlgorithm<LocalDateTime> {
    private final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMM");
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<LocalDateTime> shardingValue) {
        // 處理精確分片，如 =, IN
        String logicTableName = shardingValue.getLogicTableName();
        LocalDateTime createTime = shardingValue.getValue();
        String actualTableSuffix = formatter.format(createTime); // 根據(jù)時(shí)間生成表后綴，如202410
        String actualTableName = logicTableName + "_" + actualTableSuffix; // 拼接實(shí)際表名，如t_order_202410
        // 檢查計(jì)算出的表是否存在配置中
        for (String each : availableTargetNames) {
            if (each.equals(actualTableName)) {
                return actualTableName;
            }
        }
        throw new IllegalArgumentException("No actual table found for: " + actualTableName);
    }
    @Override
    public Properties getProps() {
        return new Properties();
    }
    @Override
    public void init(Properties properties) {
        // 初始化操作，如果需要
    }
    // 注意：ShardingSphere 5.x 及以上版本，可能需要實(shí)現(xiàn)其他方法，如 `getType`。請(qǐng)根據(jù)實(shí)際版本調(diào)整。
}

@Autowired
private JdbcTemplate jdbcTemplate;
public void demo() {
    // 插入一條創(chuàng)建時(shí)間為當(dāng)前的訂單，會(huì)路由到對(duì)應(yīng)月份的表，如 t_order_202410
    String sql = "INSERT INTO t_order (order_id, user_id, amount, create_time) VALUES (?, ?, ?, ?)";
    jdbcTemplate.update(sql, 789L, 1002L, 400.00, LocalDateTime.now());
    // 查詢特定時(shí)間范圍的訂單，自定義算法中的doSharding方法會(huì)被調(diào)用
    List<Map<String, Object>> orders = jdbcTemplate.queryForList("SELECT * FROM t_order WHERE create_time BETWEEN ? AND ?", 
        LocalDateTime.of(2024, 10, 1, 0, 0), 
        LocalDateTime.of(2024, 10, 31, 23, 59));
}