SharedingJDBC4.0实现按月分片demo

最新推荐文章于 2024-01-23 16:06:34 发布

屌丝的程序员

最新推荐文章于 2024-01-23 16:06:34 发布

阅读量175

点赞数

文章标签： spring java spring boot

本文链接：https://blog.csdn.net/ganjing222/article/details/130739829

版权

1.demo实现功能

这里实现了按月分表的新增，修改和范围查询功能，没有实现分库功能。

完整下载地址: https://download.csdn.net/download/ganjing222/87794697

2.pom文件

<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>

3.yml文件配置

spring:
  shardingsphere:
    #配置数据源
    datasource:
      names: ds-master
      ds-master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        #url: jdbc:mysql://192.168.163.128:3306/shardingjdbcdb?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8
        url: jdbc:mysql://127.0.0.1:3306/sharedingdemo?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8
        username: root
        password: 123456
    sharding:
      tables:
        user:
          #配置真实表分布 t_log_$->{2022..2100}0$->{1..9},ds.t_log_$->{2022..2100}1$->{0..2}
          #t_user_$->{2022..2100}0$->{1..9},mydb.t_user_$->{2022..2100}1$->{0..2}
          actual-data-nodes: ds-master.user_$->{2023..2023}0$->{1..9},ds-master.user_$->{2023..2023}1$->{0..2}  #0..1 从0到1
          tableStrategy:
            standard:
              sharding-column: create_time
              #定义精准分片算法
              precise-algorithm-class-name: cn.itcloud.sharedingjdbc.algorithm.MyTableRangeShardingAlgorithm
              #定义范围分片算法
              range-algorithm-class-name: cn.itcloud.sharedingjdbc.algorithm.MyTableRangeShardingAlgorithm

4. 自定义分片算法实现

@Slf4j
public class MyTableRangeShardingAlgorithm implements PreciseShardingAlgorithm<String>,RangeShardingAlgorithm<String> {
    /**
     *  根据传入的分片健的值，对所有待选择的表中 根据自己的业务逻辑进行判断，选择符合条件的表返回
     * @param tableNameList 返回需要查询的表
     * @param shardingValue 传入的分片健的值
     * @return 返回符合条件的表名称
     */
    @Override
    public Collection<String> doSharding(Collection<String> tableNameList, RangeShardingValue<String> shardingValue) {

        System.out.println("[MyTableRangeShardingAlgorithm] shardingValue: [{}]\n"+ shardingValue);
        Set<String> tableNameResultList = new LinkedHashSet<>();
        Range<String> rangeValue = shardingValue.getValueRange();

        int lowInt = Integer.valueOf(rangeValue.lowerEndpoint().substring(0,7).replaceAll("-",""));
        int upperInt = Integer.valueOf(rangeValue.upperEndpoint().substring(0,7).replaceAll("-",""));

        for (String tableNameItem : tableNameList) {
            String substring = tableNameItem.substring(tableNameItem.length() - 6,tableNameItem.length());
            int tableItem = Integer.valueOf(substring);
            if(tableItem >=  lowInt && tableItem <= upperInt ){
                tableNameResultList.add(tableNameItem);
            }

        }
        return tableNameResultList;
    }
    /**
     * 构建分片后的表名
     * @param logicTableName
     * @param date
     * @return
     */
    private String buildShardingTable(String logicTableName, String date) {
        StringBuffer stringBuffer = new StringBuffer(logicTableName).append("_").append(date, 0, 4)
                .append(date, 5, 7);
        return stringBuffer.toString();
    }

    /**
     * 精准分片算法
     * @param collection
     * @param preciseShardingValue
     * @return
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        String s = buildShardingTable(preciseShardingValue.getLogicTableName(), preciseShardingValue.getValue());
        return s;
    }
}

5：数据库对应表名