springboot整合sharding-jdbc实现动态分表

1.需求,根据月来分表,逻辑表order,根据create_time字段来分表,order_202201,order_202202 ...

2.sharding是不支持自动建表的,所以我们要先用定时任务创建表,然后再根据字段插入分表:

@Slf4j
@Component
public class CreateTablesTask {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static DateFormat millisdf = new SimpleDateFormat("yyyyMMddHHmmssSS");

    //@Scheduled(cron = "0/5 * * * * ?")
    public Object createSentinelTable() throws Exception {
        BaseResult result = new BaseResult();
        Connection conn = null;
        ResultSet rs = null;
        try {
            String tableName = "order";
            String ym = getLogID().substring(0, 6);
            String table = tableName.concat("_").concat(ym);
            conn = jdbcTemplate.getDataSource().getConnection();
            rs = conn.getMetaData().getTables(null, null, table, null);
            if (rs.next()) {
                log.info("table is exist!");
            } else {
                String c_sql = "创建表的sql语句";
                jdbcTemplate.execute(c_sql);
                log.info("create table success!");
            }
            result.setSuccess(true);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            result.setSuccess(false);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return result;

    }
    public static String getLogID() {
        return getmillisTime() + ((int) (Math.random() * 1000) + 1000);
    }

    public static String getmillisTime() {
        Calendar c = Calendar.getInstance();
        c.setTime(new Date());
        c.add(Calendar.MONTH, +1);
        Date m = c.getTime();
        return millisdf.format(m);
    }
}
3.sharding分表的算法有很多种,可以百度,根据自己的实际需求来选,我用的是精确分片算法:

public class OrderShardingAlgorithmConfig implements PreciseShardingAlgorithm<Date> {

    /**
     * 精确分片算法
     *
     * @param availableTargetNames 所有配置的库列表
     * @param shardingValue        分片值，也就是save_time_com的值
     * @return 所匹配库的结果
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
        //对于库的分片collection存放的是所有的库的列表，目前只有ds0
        //配置的分片的sharding-column对应的值
        Date date = shardingValue.getValue();
        int month = date.getMonth() + 1;
        int year = date.getYear() + 1900;
        if (month < 10) {
            //按月路由
            return shardingValue.getLogicTableName() + "_" + year + "0" + month;
        }
        //按月路由
        return shardingValue.getLogicTableName() + "_" + year + month;

    }
}
4.yml配置:

spring:
  main:
    allow-bean-definition-overriding: true

transaction:
    ### 数据库事务，失败回滚配置
    rollback-on-commit-failure: true
  shardingsphere:
    props:
      sql:
        show: false
    datasource:
      names: ds0
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name=com: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order_db?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: admin
        maxTotal: 40
        maxIdle: 20
    sharding:
      tables:
        # 逻辑表名
        order:
          # 数据节点: 逻辑表名 $ -> {202201,999912}
          actual-data-nodes: ds0.order_$->{(202201..999912).collect{t ->t.toString().padLeft(6,'0')}}
          # 主键生成采用雪花算法
          key-generator:
            column: id
          　type: SNOWFLAKE
          # 分表策略
          table-strategy:
            standard:
              # 分片字段
              sharding-column: create_time
              # 精确分片算法类名称，用于=和IN。。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
              precise-algorithm-className: com.xiaoxiao.order.config.OrderShardingAlgorithmConfig

mybatis:
  config-location: ...
  mapper-locations: ...
5.pom相关的依赖:

<!--sharding-jdbc-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-core-common</artifactId>
    <version>4.1.1</version>
</dependency>