ShardingJdbc 分库分表简单实现

自己整理学习了一下基于shardingjdbc实现mysql 分表分库的实现，自己的代码在github上，有需要的同学请自行下载学习，欢迎指教！

https://github.com/lixiao04/ShardingExample.git

1-核心代码是实现分片规则的代码
PreciseTableShardingAlgorithm.java (主要用于对分片字段使用=和in条件时的规则设置)

public class PreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    /**
     * 精确分片算法
     *
     * @param availableTargetNames 所有配置的表列表，这里代表所匹配到库的所有表
     * @param shardingValue        分片值，也就是create_time的值
     * @return                     所匹配表的结果
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        System.out.println("精准查询返回表名");
        long value = shardingValue.getValue();

        if (value <= 0) throw new UnsupportedOperationException("preciseShardingValue is null");

        final String yearJoinMonthStr = DateUtil.getYearJoinMonthByMillisecond(value);
       //动态策略
        StringBuffer tableName = new StringBuffer();


        tableName.append(shardingValue.getLogicTableName()).append("_"+yearJoinMonthStr);
        return tableName.toString();
    }
}

RangeTableShardingAlgorithm.java (用于对分片字段使用between and 条件时的规则实现)

public class RangeTableShardingAlgorithm  implements RangeShardingAlgorithm<Long> {
    private static  SimpleDateFormat dateformat=new SimpleDateFormat("yyyyMM");
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> rangeShardingValue) {
        System.out.println("查找某个范围的数据时 返回表名");
        final ArrayList<String> result = new ArrayList<>();
        final Range<Long> range = rangeShardingValue.getValueRange();
        long startMillisecond = range.lowerEndpoint();
        long endMillisecond = range.upperEndpoint();

        //动态策略
        Date startTime=new Date(startMillisecond);
        Date endTime=new Date(endMillisecond);
        Date now = new Date();
        if (startTime.after(now)){
            startTime = now;
        }
        if (endTime.after(now)){
            endTime = now;
        }
        Collection<String> tables = getRoutTable(rangeShardingValue.getLogicTableName(), startTime, endTime);

        if (tables != null && tables.size() >0) {
            result.addAll(tables);
        }
        return result;


    }
	//动态获取表名方法-----------------------------
	private Collection<String> getRoutTable(String logicTableName, Date startTime, Date endTime) {
	    Set<String> rouTables = new HashSet<>();
	    if (startTime != null && endTime != null) {
	        List<String> rangeNameList = getRangeNameList(startTime, endTime);
	        for (String YearMonth : rangeNameList) {
	            rouTables.add(logicTableName +"_"+ YearMonth);
	        }
	    }
	    return rouTables;
	}

    private static List<String> getRangeNameList(Date startTime, Date endTime) {
        List<String> result = Lists.newArrayList();
        // 定义日期实例
        Calendar dd = Calendar.getInstance();

        dd.setTime(startTime);

        while(dd.getTime().before(endTime)) {
            result.add(dateformat.format(dd.getTime()));
            // 进行当前日期按月份 + 1
            dd.add(Calendar.MONTH, 1);
        }
        return result;
    }

}

2-后期基于初始化表比较麻烦，所以设计了动态实现分表的策略
核心代码类在ShardingTableRuleActualTablesRefreshSchedule.java 中

@Component
@EnableScheduling
@EnableConfigurationProperties(DynamicTablesProperties.class)
@Slf4j
public class ShardingTableRuleActualTablesRefreshSchedule implements InitializingBean {

    @Autowired
    private DynamicTablesProperties dynamicTablesProperties;

    @Autowired
    private DataSource dataSource;

    private RedisTemplate<Object,Object> redisTemplate;

    private List<String> tnames = new ArrayList<>();
      //构造代码块，在构造函数调用前调用
    {
        tnames.add("t_order_202106");
        tnames.add("t_order_202107");
    }


    public ShardingTableRuleActualTablesRefreshSchedule() {
    }

    @Scheduled(cron = "0 28 16 9 6 *")
    public void actualTablesRefresh() throws NoSuchFieldException, IllegalAccessException {
        System.out.println("开始动态创建表---------------------------------");
        ShardingDataSource dataSource = (ShardingDataSource) this.dataSource;
        if (dynamicTablesProperties.getNames() == null || dynamicTablesProperties.getNames().length == 0) {
            return;
        }
        for (int i = 0; i < dynamicTablesProperties.getNames().length; i++) {
            TableRule tableRule = null;
            try {
                tableRule = dataSource.getShardingContext().getShardingRule().getTableRule(dynamicTablesProperties.getNames()[i]);
                System.out.println(tableRule);
            } catch (ShardingConfigurationException e) {
                 log.error("逻辑表：{},不存在配置！", dynamicTablesProperties.getNames()[i]);
            }

            List<DataNode> dataNodes = tableRule.getActualDataNodes();
            Field actualDataNodesField = TableRule.class.getDeclaredField("actualDataNodes");
            Field modifiersField = Field.class.getDeclaredField("modifiers");
            modifiersField.setAccessible(true);
            modifiersField.setInt(actualDataNodesField, actualDataNodesField.getModifiers() & ~Modifier.FINAL);
            actualDataNodesField.setAccessible(true);

            LocalDateTime localDateTime = LocalDateTime.of(2019, 1, 1, 0, 0, new Random().nextInt(59));
            LocalDateTime now = LocalDateTime.now();
            int nowYear = now.getYear();
             now =LocalDateTime.of(nowYear, 12, 31, 0, 0, new Random().nextInt(59));
            String dataSourceName = dataNodes.get(0).getDataSourceName();
            String logicTableName = tableRule.getLogicTable();
            System.out.println("数据库名：="+dataSourceName+"表名前缀："+logicTableName);

            StringBuilder stringBuilder = new StringBuilder(10).append(dataSourceName).append(".").append(logicTableName);
            final int length = stringBuilder.length();
            List<DataNode> newDataNodes = new ArrayList<>();
            while (true) {
                stringBuilder.setLength(length);
                stringBuilder.append("_"+localDateTime.format(DateTimeFormatter.ofPattern("yyyyMM")));
                DataNode dataNode = new DataNode(stringBuilder.toString());
                String table = createTable(dataNode.getTableName());
                System.out.println("新加的表："+table);
                newDataNodes.add(dataNode);
                localDateTime = localDateTime.plusMonths(1L);
                if (localDateTime.isAfter(now)) {
                    break;
                }
            }
            actualDataNodesField.set(tableRule, newDataNodes);
            System.out.println("新的分表节点"+newDataNodes);
            Set<String> actualTables = Sets.newHashSet();
            Map<DataNode, Integer> dataNodeIntegerMap = Maps.newHashMap();
            AtomicInteger a = new AtomicInteger(0);
            newDataNodes.forEach((dataNode -> {
                actualTables.add(dataNode.getTableName());
                if (a.intValue() == 0){
                    a.incrementAndGet();
                    dataNodeIntegerMap.put(dataNode, 0);
                }else {
                    dataNodeIntegerMap.put(dataNode, a.intValue());
                    a.incrementAndGet();
                }
            }));

            //动态刷新：actualTables
            Field actualTablesField = TableRule.class.getDeclaredField("actualTables");
            actualTablesField.setAccessible(true);
            actualTablesField.set(tableRule, actualTables);
            //动态刷新：dataNodeIndexMap
            Field dataNodeIndexMapField = TableRule.class.getDeclaredField("dataNodeIndexMap");
            dataNodeIndexMapField.setAccessible(true);
            dataNodeIndexMapField.set(tableRule, dataNodeIntegerMap);
            log.info("-----------------end----------------");
        }

    }
    public String createTable(String tb){
        if(tnames.contains(tb)){
            System.out.println(tb);
        }else{
            tnames.add(tb);
            String creatsql = String.format("CREATE TABLE if not exists `%s` (" +
                    "`order_id`  bigint(20) NOT NULL ,\n" +
                    "`price`  decimal(10,2) NULL DEFAULT NULL ,\n" +
                    "`user_id`  bigint(20) NULL DEFAULT NULL ,\n" +
                    "`status`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,\n" +
                    "`create_time`  bigint(20) NULL DEFAULT NULL ,\n" +
                    "PRIMARY KEY (`order_id`)\n" +
                    ")\n" +
                    "ENGINE=InnoDB\n" +
                    "DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci\n" +
                    "ROW_FORMAT=DYNAMIC",tb);

            Connection conn = null;
            try {
                conn = dataSource.getConnection();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            //执行创建表
            System.out.println("//创建表");
            try{
                Statement stmt = conn.createStatement();
                if(0 == stmt.executeUpdate(creatsql)) {
                    System.out.println("成功创建表！");
                } else {
                    System.out.println("创建表失败！");
                }
                stmt.close();
                conn.close();
                System.out.println("//关闭资源");
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return tb;
    }
    @Override
    public void afterPropertiesSet() throws Exception {
        actualTablesRefresh();
    }
}

3-动态的缺点是：目前项目启动会创建所有的表，以及动态节点的刷新也会有问题，比如单独启动测试类无法获取动态的节点列表导致会报t_order表不存在的问题，还待优化

参考视频学习：https://edu.csdn.net/course/play/26238/325867