shardingsphere,shardingjdbc按年月时间分库，定时创建库表

 码云地址：

修正版sharding-jdbc-test

介绍

sharding-jdbc-test

shardingjdbc 4.1 分库分表，按关键字段分库，时间月份分表。 springboot2.* , yml格式配置文件。

插入和查询都是按时间分表，困难点是表无法创建。 通过解析配置文件，初始化创建表，还可以定时任务定时创建表。

软件架构

软件架构说明

安装教程

创建两个库， shard1, shard2 创建表语句 SQL: CREATE TABLE insert_month ( finance_no bigint(20) NOT NULL COMMENT '流水号', shop_id int(11) DEFAULT NULL COMMENT '店铺id', create_date_time datetime DEFAULT NULL, remark varchar(255) DEFAULT NULL, PRIMARY KEY (finance_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

根据create_date_time 分表， shop_id 分库

分库 shop_id字段 对应关系 alipay:1,sales:0

分库分表逻辑在包 com.test.sharding.config.sharding

自动创建表在 com.test.sharding.scheduled.TableCreate

使用说明

1. 批量插入 localhost:8092/insert
2. 插入单个 localhost:8092/one
3. 查询 http://localhost:8092/get

原来版本https://gitee.com/liran123/sharding_jdbc_test

 maven

<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-jdbc-spring-namespace</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.22</version>
</dependency>

 

 启动类

@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class})

 

 数据库路由规则类，可以去码云看

DefaultDatabaseRangeShardingAlgorithm
DefaultDatabaseShardingAlgorithm
DefaultTableRangeShardingAlgorithm
DefaultTableShardingAlgorithm

package com.chenfan.finance.config.sharding;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.LinkedHashSet;

/**
 * 分表的自定义规则类(范围)
 *
 * @author lr
 */
public class DefaultTableRangeShardingAlgorithm extends CommonShardDataBase implements RangeShardingAlgorithm<LocalDateTime> {

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames,
                                         RangeShardingValue<LocalDateTime> rangeShardingValue) {
        Range<LocalDateTime> ranges = rangeShardingValue.getValueRange();

        LocalDateTime start = ranges.lowerEndpoint();
        LocalDateTime end = ranges.upperEndpoint();

        int startYear = start.getYear();
        int endYear = end.getYear();

        int startMonth = start.getMonthValue();
        int endMonth = end.getMonthValue();

        Collection<String> tables = new LinkedHashSet<>();
        if (start.getNano() <= end.getNano()) {
            for (String c : availableTargetNames) {
                int cMonth = Integer.parseInt(c.substring(c.length() - 6));
                if (cMonth >= Integer.parseInt("" + startYear + startMonth) && cMonth <= Integer.parseInt("" + endYear + endMonth)) {
                    tables.add(c);
                }
            }
        }
        return tables;
    }
}

package com.chenfan.finance.config.sharding;

import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.Date;

/**
 * @author lr
 * 分表的自定义规则类(精确)
 */
public class DefaultTableShardingAlgorithm extends CommonShardDataBase implements PreciseShardingAlgorithm<LocalDateTime> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<LocalDateTime> preciseShardingValue) {
        LocalDateTime createTime = preciseShardingValue.getValue();
        String timeValue = createTime.format(DateTimeFormatter.ofPattern(DB_SHARD_TIME_FORMAT));
        String columnName = preciseShardingValue.getColumnName();
        // 需要分库的逻辑表
        String table = preciseShardingValue.getLogicTableName();
        if (StringUtils.isBlank(timeValue)) {
            throw new UnsupportedOperationException(columnName + ":列，分表精确分片值为NULL;");
        }
        for (String each : collection) {
            if (each.startsWith(table)) {
                return table + "_" + timeValue;
            }
        }
        return table;
        //  throw new UnsupportedOperationException("table not found by shopId ,please config first ");
    }
}

 

 yml配置文件

 

sharding:
  create:
    ## 最多创建当前时间前12个月
    maxMonth: 12
    ## 默认创建前几个月的
    beforeDefaultMonth: 2
    ## 默认创建后几个月的
    afterDefaultMonth: 0
    tables:
      202004: toc_trade_refund_order_header,toc_trade_refund_order_detail,toc_sales_order_header,
              toc_sales_order_detail,toc_sales_deliver_header,toc_sales_deliver_detail,toc_trade_order_header,toc_trade_order_details,
              toc_stockin_order_header,toc_stockin_order_detail,reference_table
      202011: toc_charge

spring:
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      #数据库名称（可以与数据库中的库名不一致）
      names: alipay,sales,order,eopfinance
      alipay:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.228.84.47:3306/toc_alipay_dev?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456

      sales:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.228.84.47:3306/tmp_toc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456

      order:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.228.84.47:3306/tmp_toc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456

      eopfinance:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.228.81.19:38309/eop_finance_dev?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: Chenfan@123.com.cn..


    sharding:
      default-data-source-name: eopfinance
      binding-tables: toc_trade_refund_order_header,toc_trade_refund_order_detail,toc_sales_order_header,
                      toc_sales_order_detail,toc_sales_deliver_header,toc_sales_deliver_detail,toc_alipay_origin,
                      toc_alipay_mapping_income,toc_alipay_mapping_expend,toc_u8_detail,toc_u8_header,reference_table
                      toc_charge,toc_stockin_order_detail,toc_stockin_order_header   

     toc_alipay_origin:
          actual-data-nodes: alipay.toc_alipay_origin_$->{2020..2021}0$->{1..9}, alipay.toc_alipay_origin_$->{2020..2021}$->{10..12}
          table-strategy:
            standard:
              range-algorithm-class-name: com.chenfan.finance.config.sharding.DefaultTableRangeShardingAlgorithm
              precise-algorithm-class-name: com.chenfan.finance.config.sharding.DefaultTableShardingAlgorithm
              sharding-column: account_date

 

自动创建表的，默认创建前一个月的，其他的可以在配置文件里面配置。

 

package com.chenfan.finance.scheduled;

import com.xxl.job.core.biz.model.ReturnT;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.env.OriginTrackedMapPropertySource;
import org.springframework.core.env.MutablePropertySources;
import org.springframework.core.env.PropertySource;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;
import org.springframework.web.context.support.StandardServletEnvironment;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.Map;

/**
 * @author liran
 */
@Slf4j
@Setter
@Getter
@Component
@ConfigurationProperties(prefix = "sharding.create")
public class TableCreate {

    @Resource
    private ShardingDataSource dataSource;
    @Autowired
    StandardServletEnvironment env;
    Map<String, Object> allTable = new HashMap<>();
    Map<String, Object> dbAndTable = new HashMap<>();
    Map<String, Object> createdTables = new HashMap<>();
    private Map<String, String> tables;
    private Integer maxMonth;
    private Integer beforeDefaultMonth;
    private Integer afterDefaultMonth;

    @PostConstruct
    public void init() {
        String v1 = "actual-data-nodes";
        String v2 = "spring.shardingsphere.sharding.tables";
        MutablePropertySources propertySources = env.getPropertySources();
        for (PropertySource<?> propertySource : propertySources) {
            if (propertySource instanceof OriginTrackedMapPropertySource) {
                OriginTrackedMapPropertySource originTrackedMapPropertySource = (OriginTrackedMapPropertySource) propertySource;
                String[] propertyNames = originTrackedMapPropertySource.getPropertyNames();
                for (String propertyName : propertyNames) {
                    if (propertyName.startsWith(v2)) {
                        Object property = originTrackedMapPropertySource.getProperty(propertyName);
                        allTable.put(propertyName, property);
                    }
                }
            }
        }
        String finalSp = "$";
        allTable.forEach((k, v) -> {
            if (k.contains(v1) && String.valueOf(v).contains(finalSp)) {
                String table = StringUtils.substringBetween(k, v2 + ".", "." + v1);
                String dbname = StringUtils.substringBefore(String.valueOf(v), "." + table);
                table = table.trim();
                dbname = dbname.trim();
                dbAndTable.put(table, dbname);
            }
        });
        dbAndTable.forEach((k, v) -> {
            createTable(k, String.valueOf(v));
        });
        createTablePreMonths();
    }

    private void createTablePreMonths() {
        tables.forEach((date, d) -> {
            createPreTables(date, d);
        });
    }

    private void createPreTables(String date, String tables) {
        String[] tabs = tables.split(",");
        for (String table : tabs) {
            table = table.trim();
            if (dbAndTable.containsKey(table)) {
                String db = (String) dbAndTable.get(table);
                for (int j = 1; j <= maxMonth; j++) {
                    String localDateString = getLocalDateString(LocalDateTime.now().plusMonths(-j), table);
                    if (localDateString.contains(date)) {
                        createNeedTime(table, db, localDateString);
                        break;
                    }
                    createNeedTime(table, db, localDateString);
                }
            }
        }
    }

    private void createTable(String table, String db) {
        if (afterDefaultMonth != null && afterDefaultMonth > 0) {
            createNeedTime(table, db, getLocalDateString(LocalDateTime.now(), table));
            for (int i = 1; i <= afterDefaultMonth; i++) {
                createNeedTime(table, db, getLocalDateString(LocalDateTime.now().plusMonths(i), table));
            }
        }
        if (beforeDefaultMonth != null) {
            for (int i = 1; i <= beforeDefaultMonth; i++) {
                createNeedTime(table, db, getLocalDateString(LocalDateTime.now().plusMonths(-i), table));
            }
        }
    }

    private void createNeedTime(String table, String db, String create) {
        DataSource dataSource = this.dataSource.getDataSourceMap().get(db);
        String sql = "SHOW CREATE TABLE " + table;
        String existSql = "select * from information_schema.tables where table_name ='" + table + "'; ";
        doCreate(dataSource, sql, existSql, create, db, table);
    }

    private void doCreate(DataSource dataSource, String sql, String existSql, String create, String db, String table) {
        String msg = " create table: " + create + "  origin table: " + table + "  db: " + db;
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            ResultSet resultSet = stmt.executeQuery(existSql);
            Assert.isTrue(resultSet.next(), msg + "初始化表不存在");

            ResultSet resTable = stmt.executeQuery(sql);
            Assert.isTrue(resTable.next(), msg + "初始化表不存在");
            String existTableName = resTable.getString(1);
            String createSqlOrigin = resTable.getString(2);
            // log.info(existTableName, createSqlOrigin);

            String existSqlNew = StringUtils.replaceOnce(existSql, existTableName, create);
            ResultSet executeQuery = stmt.executeQuery(existSqlNew);
            if (executeQuery.next()) {
                log.info("table exist :" + msg);
            } else {
                String creatsql = StringUtils.replaceOnce(createSqlOrigin, existTableName, create);
                if (0 == stmt.executeUpdate(creatsql)) {
                    log.info(msg + "success ！");

                    createdTables.put(create, db);
                } else {
                    log.error(msg + "fail ！");
                }
            }
        } catch (Exception e) {
            log.error("create  table fail  error : {} ", e.getMessage());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    log.error("SQLException", e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error("SQLException", e);
                }
            }
        }
    }

    private String getLocalDateString(LocalDateTime now, String table) {
        int startYear = now.getYear();
        int startMonth = now.getMonthValue();
        int month = 10;
        if (startMonth < month) {
            return table + "_" + startYear + "0" + startMonth;
        }
        return table + "_" + startYear + startMonth;
    }

    /**
     * 定时建表
     *
     * @return
     * @throws SQLException
     */
    // @Scheduled(cron = "0/60 * * * * ?")
    public ReturnT<String> cfWdtRdCalculateTask() throws SQLException {
        init();
        return ReturnT.SUCCESS;
    }


}

 

 

 官方文档：

shardingsphere,shardingjdbc按年月时间分库，定时创建库表

• 不使用Spring
  • 引入Maven依赖
  • 基于Java编码的规则配置
  • 基于Yaml的规则配置
  • 使用原生JDBC
• 使用Spring
  • 引入Maven依赖
  • 基于Spring boot的规则配置
  • 基于Spring命名空间的规则配置
  • 在Spring中使用DataSource