sharingjdbc5.x多数据源分库分表读写分离自动创表实现（pgsql）(一)

引入相关jar

<dependency>
    <groupId>cn.yibo</groupId>
    <artifactId>micontrol-sharding-service</artifactId>
    <version>${micontrol.version}</version>
</dependency>

yml配置

 

 

 

相关配置类

package cn.yibo.sharding.modules.common.config;

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Primary;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class,
        SpringBootConfiguration.class})
public class DataSourceConfig {
    /**
     * 分表数据源名称
     */
    private static final String SHARDING_DATA_SOURCE_NAME = "sharding";
    /**
     * 动态数据源配置项
     */
    @Autowired
    private DynamicDataSourceProperties properties;

    /**
     * shardingjdbc有四种数据源，需要根据业务注入不同的数据源
     *
     * <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
     * <p>2. 主从数据源: masterSlaveDataSource;
     * <p>3. 脱敏数据源：encryptDataSource;
     * <p>4. 影子数据源：shadowDataSource
     *
     */
    @Lazy
    @Resource
    DataSource shardingDataSource;

    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                // 将 shardingjdbc 管理的数据源也交给动态数据源管理
                dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
                return dataSourceMap;
            }
        };
    }

    /**
     * 将动态数据源设置为首选的
     * 当spring存在多个数据源时, 自动注入的是首选的对象
     * 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
     *
     * @return
     */
    @Primary
    @Bean
    public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
        dataSource.setPrimary(properties.getPrimary());
        dataSource.setStrict(properties.getStrict());
        dataSource.setStrategy(properties.getStrategy());
        dataSource.setProvider(dynamicDataSourceProvider);
        dataSource.setP6spy(properties.getP6spy());
       // dataSource.setSeata(properties.getSeata());
        return dataSource;
    }
}

package cn.yibo.sharding.modules.common.config;

import cn.hutool.core.date.DateUtil;
import com.google.common.collect.Range;
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 org.springframework.stereotype.Component;

import java.text.SimpleDateFormat;
import java.util.*;
@Component
public class DateShardingAlgorithm implements StandardShardingAlgorithm<Long> {
    // 查询使用
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
        collection.forEach(i -> System.out.println("节点配置表名为: " + i));
        // 查询数据库中的表 hss_history
        List<String> tableNames = ShardingAlgorithmTool.getAllTableNameBySchema();
        tableNames.forEach(i -> System.out.println("数据库实时表名: " + i));
        // 查询缓存中的表 hss_history
        HashSet<String> tableNameCache = ShardingAlgorithmTool.cacheTableNames();
        tableNameCache.forEach(i -> System.out.println("缓存中的表名: " + i));
        // 获取查询条件 精确匹配表
        Range<Long> valueRange = rangeShardingValue.getValueRange();
        Long beginLong = valueRange.lowerEndpoint();// 开始条件的时候戳
        Long endLong = valueRange.upperEndpoint();// 结束条件的时候戳
        SimpleDateFormat sf = new SimpleDateFormat("yyyyMM");
        String begin = sf.format(new Date(beginLong * 1000));
        String end = sf.format(new Date(endLong * 1000));
        // 不要匹配节点配置的表，数据库表一旦不存在就会报错
        List<String> queryTables = new ArrayList<>(tableNameCache.size());
        for (String tableName : tableNameCache) {
            if (!tableName.equals("hss_history")) {
                // 截取缓存表名后缀的年月 yyyyMM
                String num = tableName.split("_")[2];
                // 在查询条件范围内才添加
                if (Integer.parseInt(num) >= Integer.parseInt(begin) && Integer.parseInt(num) <= Integer.parseInt(end)) {
                    queryTables.add(tableName);
                }
            }
        }
        // 返回按条件匹配的表名
        return queryTables;
    }

    // 添加使用
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        StringBuilder resultTableName = new StringBuilder();
        String logicTableName = preciseShardingValue.getLogicTableName();
        //表名精确匹配，表名加上截取的时间
        resultTableName.append(logicTableName)
                //时间戳秒级转毫秒级转成date类型
                .append("_").append(DateUtil.format(new Date(preciseShardingValue.getValue().longValue() * 1000), "yyyyMM"));
        System.out.println("插入表名为:" + resultTableName);
        return ShardingAlgorithmTool.shardingTablesCheckAndCreatAndReturn(logicTableName, resultTableName.toString());
    }



    @Override
    public String getType() {
        //　自定义
        return "CLASS_BASED";
    }

    @Override
    public Properties getProps() {
        /*Properties properties=new Properties();
        properties.put("strategy","standard");
        properties.put("algorithmClassName","cn.yibo.sharding.modules.common.config.DateShardingAlgorithm");
        properties.put("write-data-source-name","master");
        properties.put("read-data-source-names","slave");*/
        //return properties;
        return  null;
    }

  /*  @Override
    public void init(Properties properties) {
        properties.put("strategy","standard");
        properties.put("algorithmClassName","cn.yibo.sharding.modules.common.config.DateShardingAlgorithm");
    }*/

    @Override
    public void init() {
        Properties properties=new Properties();
        properties.put("write-data-source-name","master");
        properties.put("read-data-source-names","slave");
    }

    public static void main(String[] args) {
        String dateStr="2023-02-01 12:00:24.222";
        SimpleDateFormat simpleDateFormat;
    }
}