分表-526互联

  shardingsphere:    
    datasource:
      name: test
      test:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.124.12:3306/zjjs?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8
        username: root
        password: root
        driver-class-name: com.mysql.cj.jdbc.Driver
    default-data-source-name: test  
    sharding:
      tables:
        t_rpayrecord:
          actual-data-nodes: test.t_rpayrecord_$->{2022..2023}
          tableStrategy:
          standard:
            sharding-column: createtime
            precise-algorithm-class-name: com.mrefuel.util.PreciseRangeShardingAlgorithm # 精确分片算法类名称，用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
            range-algorithm-class-name: com.mrefuel.util.PreciseRangeShardingAlgorithm #范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
           # 主键
          key-generator:
            column: id
            type: SNOWFLAKE

package com.mrefuel.util;

import com.google.common.collect.Range;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Collection;
import java.util.LinkedHashSet;
import java.util.Set;
@Slf4j
public class PreciseRangeShardingAlgorithm implements PreciseShardingAlgorithm<String>, RangeShardingAlgorithm<String> {
    /**
     *  RangeShardingAlgorithm的重写  根据传入的分片健的值，对所有待选择的表中 根据自己的业务逻辑进行判断，选择符合条件的表返回
     * @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();

        String flag = "year";
        for (String tableName : tableNameList) {
            if (tableName.startsWith("month_table")) {
                flag = "month";
                break;
            }

        }

        if ("year".equals(flag)) {
            int lowInt = Integer.parseInt(rangeValue.lowerEndpoint().substring(0,5).replaceAll("-",""));
            int upperInt = Integer.parseInt(rangeValue.upperEndpoint().substring(0,5).replaceAll("-",""));

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

            }
        } else if ("month".equals(flag)) {

            int lowInt = Integer.parseInt(rangeValue.lowerEndpoint().substring(0,7).replaceAll("-",""));
            int upperInt = Integer.parseInt(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;
    }



    /** PreciseShardingAlgorithm的重写 */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        String s = buildShardingTable(preciseShardingValue.getLogicTableName(), preciseShardingValue.getValue());
        return s;
    }

    /**
     * 构建分片后的表名
     * @param logicTableName
     * @param date
     * @return
     */
    private String buildShardingTable(String logicTableName, String date) {

        StringBuffer stringBuffer = new StringBuffer(logicTableName).append("_").append(date, 0, 4);
        if (logicTableName.startsWith("month_table") ) {
            // 月分表
            stringBuffer = new StringBuffer(logicTableName).append("_").append(date, 0, 4)
                    .append(date, 5, 7);
        }
        return stringBuffer.toString();
    }
}

package com.mrefuel.util;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.actuate.autoconfigure.jdbc.DataSourceHealthContributorAutoConfiguration;
import org.springframework.boot.actuate.health.AbstractHealthIndicator;
import org.springframework.boot.actuate.jdbc.DataSourceHealthIndicator;
import org.springframework.boot.jdbc.metadata.DataSourcePoolMetadataProvider;
import org.springframework.context.annotation.Configuration;
import org.springframework.util.StringUtils;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
public class DataSourceHealthConfig extends DataSourceHealthContributorAutoConfiguration {
    public DataSourceHealthConfig(Map<String, DataSource> dataSources, ObjectProvider<DataSourcePoolMetadataProvider> metadataProviders) {
        super(dataSources, metadataProviders);
    }

    @Override
    protected AbstractHealthIndicator createIndicator(DataSource source) {
        DataSourceHealthIndicator indicator = (DataSourceHealthIndicator) super.createIndicator(source);
        if (!StringUtils.hasText(indicator.getQuery())) {
            indicator.setQuery("select 1");
        }
        return indicator;
    }
}

<!--shardingjdbc分片，和Druid不兼容，如果不使用sha   rding则需要注释-->
<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
   <version>4.1.1</version>
</dependency>

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid</artifactId>
   <version>1.2.15</version>
</dependency>