SharDingJDBC-4.0.0-RC1按月水平分表

废话不多说，上代码！

         <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

关键配置yml

spring:
  #shardingjdbc主要配置
  main:
    allow-bean-definition-overriding: true #Bean覆盖，srpingboot2.1以上默认是false,一个实体类对应多张表，水平分表必须加
  shardingsphere:
    datasource:
      names: h1 #单数据源，多个以逗号隔开
      h1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://rm-.mysql.cn-chengdu.rds.aliyuncs.com:3306/chen
        username: chen
        password: chen
    sharding:
     # binding-tables: hss_history #绑定表，需要关联查询的时候配置
      tables:
        hss_history: #逻辑表  下面是节点表,分表后还有数据在原来的表，所有查询节点需要加上原来的表
          actual-data-nodes: h1.hss_history,h1.hss_history_202$->{201..204}
          key-generator: #主键生成策略 雪花算法，
            column: id
            type: SNOWFLAKE
          table-strategy:
            standard:  #分片策略 以创建时候分表，实现类计算
              sharding-column: create_time
              #对应下面的分表策略类
              precise-algorithm-class-name: work.chen.history.config.TableShardingAlgorithm
              range-algorithm-class-name: work.chen.history.config.TableShardingAlgorithm
      #多数据源指定默认数据源，两个数据源其中一个数据源不参于分表可以设置
      #default-data-source-name: h1
    props:
      sql: #展示SQL 测试的时候可以改为true
        show: false

actual-data-nodes: h1.hss_history,h1.hss_history_202$->{201…204}
这代表可以查询的表，有多少表就配多少，原始表hss_history，还有hss_history_202201-202204
如果这里配的节点表，数据库中不存在，那么查询的时候就会报错 找不到表，需要提前创建好表，
配了shardingjdbc 有些sql语句就不支持了，
SQL支持查询
解决这个办法，只有分多个数据源，不参于分表的数据源改为默认的就不会使用sharding的数据源

分表具体策略

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 work.chenshuang.history.service.impl.HssHistoryService;
import work.chenshuang.history.utils.DateUtil;

import java.awt.image.ImageConsumer;
import java.util.Collection;
import java.util.Date;
import java.util.List;


// 分表规则
//按单月分表
public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Date>,RangeShardingAlgorithm<Long> {
    //@Autowired不能注入
    private static HssHistoryService hssHistory = (HssHistoryService) SpringUtil.getBean(HssHistoryService.class);

    //精确插入表名  单表
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> preciseShardingValue) {
        StringBuffer tableName = new StringBuffer();
        //表名精确匹配，表名加上截取的年月
        tableName.append(preciseShardingValue.getLogicTableName())
                //时间戳转成date类型
                .append("_").append(DateUtil.date2Str(preciseShardingValue.getValue(), DateUtil.YEAR_MONTH_NUMBER));
        // System.out.println("表名为:"+tableName.toString());
        // System.out.println("逻辑表名"+preciseShardingValue.getLogicTableName());
        System.out.println("时间"+preciseShardingValue.getValue());
        //availableTargetNames存的是节点表名字，可以查询的表
        //单数据源这里就会有问题，sql有问题 @Select("select table_name from information_schema.tables where TABLE_SCHEMA=(select database())")
        //List<String> tableNames = hssHistory.queryTableNames();
        //创建表
        //hssHistory.createTable(tableName.toString());
        return tableName.toString();
    }

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
        return null;
    }
}

时间工具类

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;

import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.XMLGregorianCalendar;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;

@Slf4j
public class DateUtil {

    public static final String DATE_FORMAT_DEFAULT = "yyyy-MM-dd HH:mm:ss";
    public static final String DATE_FORMAT_NUMBER = "yyyyMMddHHmmss";
    public static final String YEAR_MONTH_DAY_NUMBER = "yyyyMMdd";
    public static final String YEAR_MONTH_NUMBER = "yyyyMM";
    public static final String DATE_FORMAT_DAY_PATTERN = "yyyy-MM-dd";
    public static final String YEAR_MONTH_DAY_EN_SECOND = "yyyy/MM/dd HH:mm:ss";
    public static final String YEAR_MONTH_DAY_CN_SECOND = "yyyy年MM月dd日 HH时mm分ss秒";
    public static final String YEAR_MONTH_DAY_CN = "yyyy年MM月dd日";

    /**
     * 采用 ThreadLocal 避免 SimpleDateFormat 非线程安全的问题
     * <p>
     * Key —— 时间格式
     * Value —— 解析特定时间格式的 SimpleDateFormat
     */
    private static ThreadLocal<Map<String, SimpleDateFormat>> sThreadLocal = new ThreadLocal<>();


    /**
     * 获取解析特定时间格式的 SimpleDateFormat
     *
     * @param pattern 时间格式
     */
    private static SimpleDateFormat getDateFormat(String pattern) {
        if (StringUtils.isBlank(pattern)) {
            pattern = DATE_FORMAT_DEFAULT;
        }
        Map<String, SimpleDateFormat> strDateFormatMap = sThreadLocal.get();
        if (strDateFormatMap == null) {
            strDateFormatMap = new HashMap<>();
        }

        SimpleDateFormat simpleDateFormat = strDateFormatMap.get(pattern);
        if (simpleDateFormat == null) {
            simpleDateFormat = new SimpleDateFormat(pattern, Locale.getDefault());
            strDateFormatMap.put(pattern, simpleDateFormat);
            sThreadLocal.set(strDateFormatMap);
        }
        return simpleDateFormat;
    }


    public static Date toDate(LocalDateTime dateTime) {
        if (dateTime == null) {
            return null;
        }
        return Date.from(dateTime.atZone(ZoneId.systemDefault()).toInstant());
    }

    public static String format(LocalDateTime dateTime, String format) {
        if (dateTime == null) {
            return null;
        }
        return dateTime.format(DateTimeFormatter.ofPattern(format));
    }

    public static LocalDateTime parse(String dateTimeStr, String format) {
        if (StringUtils.isBlank(format)) {
            format = DATE_FORMAT_DEFAULT;
        }
        if (dateTimeStr == null) {
            return null;
        }
        DateTimeFormatter sf = DateTimeFormatter.ofPattern(format);
        LocalDateTime dateTime = LocalDateTime.parse(dateTimeStr, sf);
        return dateTime;
    }

    public static String format(LocalDateTime dateTime) {
        return format(dateTime, DATE_FORMAT_DEFAULT);
    }

    public static LocalDateTime parse(String dateTimeStr) {
        return parse(dateTimeStr, DATE_FORMAT_DEFAULT);
    }

    // #############################################################################

    /**
     * 时间格式转换为字符串格式
     *
     * @param date   时间
     * @param format 格式 如("yyyy-MM-dd hh:mm:ss")
     * @return String
     */
    public static String date2Str(Date date, String format) {
        if (StringUtils.isBlank(format)) {
            format = DATE_FORMAT_DEFAULT;
        }
        if (date == null) {
            return null;
        }
        return getDateFormat(format).format(date);
    }

    /**
     * 字符串格式转换为时间格式
     *
     * @param dateStr 字符串
     * @param format  格式 如("yyyy-MM-dd HH:mm:ss")
     * @return Date
     */
    public static Date str2Date(String dateStr, String format) {
        if (StringUtils.isBlank(format)) {
            format = DATE_FORMAT_DEFAULT;
        }
        if (StringUtils.isBlank(dateStr)) {
            return null;
        }
        try {
            return getDateFormat(format).parse(dateStr);
        } catch (ParseException pe) {
            log.error("str2Date ParseException error", pe);
        }
        return null;
    }

    public static Date dateFormat(Date date, String format) {
        if (StringUtils.isBlank(format)) {
            format = DATE_FORMAT_DEFAULT;
        }
        if (date == null) {
            return null;
        }
        return str2Date(date2Str(date, format), format);
    }

    /**
     * @param timestamp：时间
     */
    public static Date parse(long timestamp) {
        return new Date(timestamp);
    }

    /**
     * 获取当前日期
     *
     * @param pattern 格式如:yyyy-MM-dd
     * @return
     */
    public static String getCurrentDate(String pattern) {
        return getDateFormat(pattern).format(new Date());
    }

    /**
     * get cuurent Date return java.util.Date type
     */
    public static Date getCurrentDate() {
        return new Date();
    }

    /**
     * get current Date return java.sql.Date type
     *
     * @return
     */
    public static java.sql.Date getNowSqlDate() {
        return new java.sql.Date(System.currentTimeMillis());
    }

    /**
     * get the current timestamp return java.sql.Timestamp
     *
     * @return
     */
    public static Timestamp getNowTimestamp() {
        return new Timestamp(System.currentTimeMillis());
    }

    public static String timestamp2Str(Timestamp time, String format) {
        return getDateFormat(format).format(time);
    }

    public static Date timestamp2Date(Timestamp time, String format) throws ParseException {
        return str2Date(timestamp2Str(time, format), format);
    }

    public static Timestamp str2Timestamp(Timestamp time, String format) throws ParseException {
        Date date = str2Date(timestamp2Str(time, format), format);
        return new Timestamp(date.getTime());
    }

    public static Timestamp date2Timestamp(Date date, String format) {
        Date result = str2Date(date2Str(date, format), format);
        return new Timestamp(result.getTime());
    }

    /**
     * time1>time2 返回正数
     *
     * @param time1
     * @param time2
     * @return
     */
    public static long getOffsetBetweenTimes(String time1, String time2) {
        return str2Date(time1, DATE_FORMAT_DEFAULT).getTime() - str2Date(time2, DATE_FORMAT_DEFAULT).getTime();
    }


    /**
     * 返回时间差
     *
     * @param start
     * @param end
     * @param timeUnit 天d, 时h, 分m, 秒s
     * @return 根据timeUnit返回
     */
    public static long getOffsetBetweenDate(Date start, Date end, char timeUnit) {
        long startT = start.getTime(); //定义上机时间
        long endT = end.getTime();  //定义下机时间

        long ss = (startT - endT) / (1000); //共计秒数
        int mm = (int) ss / 60;   //共计分钟数
        int hh = (int) ss / 3600;  //共计小时数
        int dd = hh / 24;   //共计天数
        System.out.println("######################## 共" + dd + "天 准确时间是：" + hh + " 小时 " + mm + " 分钟" + ss + " 秒 共计：" + ss * 1000 + " 毫秒");

        long result = 0L;
        if ('d' == timeUnit) {
            result = dd;
        } else if ('h' == timeUnit) {
            result = hh;
        } else if ('m' == timeUnit) {
            result = mm;
        } else if ('s' == timeUnit) {
            result = ss;
        }
        return result;
    }

    /**
     * 对指定日期滚动指定天数,负数时,则往前滚,正数时,则往后滚
     *
     * @param date Date
     * @param days int
     * @return String
     */
    public static String rollDays(Date date, int days) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.DATE, days);
        return date2Str(calendar.getTime(), DATE_FORMAT_DEFAULT);
    }

    /**
     * 对指定日期滚动指定分钟,负数时,则往前滚,正数时,则往后滚
     *
     * @param date    Date
     * @param minutes int
     * @return String
     */
    public static String rollMinutes(Date date, int minutes) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.MINUTE, minutes);
        return date2Str(calendar.getTime(), DATE_FORMAT_DEFAULT);
    }

    /**
     * 对指定日期滚动指定分钟,负数时,则往前滚,正数时,则往后滚
     *
     * @param date    Date
     * @param seconds int
     * @return String
     */
    public static String rollSeconds(Date date, int seconds, String format) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.SECOND, seconds);
        return date2Str(calendar.getTime(), format);
    }

    /**
     * 返回  2013-01-16T06:24:26.829Z 时间
     *
     * @param date
     * @return
     * @throws Exception
     */
    public static XMLGregorianCalendar getXmlDatetime(Date date) throws Exception {
        if (null == date) {
            date = new Date();
        }
        GregorianCalendar nowGregorianCalendar = new GregorianCalendar();
        XMLGregorianCalendar xmlDatetime = DatatypeFactory.newInstance().newXMLGregorianCalendar(nowGregorianCalendar);
        // XMLGregorianCalendar ->GregorianCalendar
        nowGregorianCalendar = xmlDatetime.toGregorianCalendar();
        nowGregorianCalendar.setTime(date);
        return xmlDatetime;
    }

    public static boolean checkDateBettwenBoth(Date checkDate, Date date1, Date date2) {
        boolean temp = false;
        if (checkDate == null || date1 == null || date2 == null) {
            temp = false;
            return temp;
        }

        if (checkDate.equals(date1) || checkDate.equals(date2)) {
            temp = true;
        }

        if (checkDate.after(date1) && checkDate.before(date2)) {
            temp = true;
        }

        return temp;
    }

    public static String getFormatDatetime() throws Exception {
        GregorianCalendar gCalendar = new GregorianCalendar();
        String strDateTime;
        try {
            strDateTime = getDateFormat(DATE_FORMAT_DEFAULT).format(gCalendar.getTime());
        } catch (Exception ex) {
            System.out.println("Error Message:".concat(String.valueOf(String.valueOf(ex.toString()))));
            String s = null;
            return s;
        }
        return strDateTime;
    }


    /**
     * 判断给定的日期是一年中的第几天
     *
     * @param dateTimeStr
     * @return
     */
    public static int dayOfYear(String dateTimeStr) {
        int day = 0;
        if (StringUtils.isBlank(dateTimeStr)) {
            return day;
        }
        Date dateTime = str2Date(dateTimeStr, DATE_FORMAT_DEFAULT);
        return dayOfYear(dateTime);
    }


    /**
     * 判断给定的日期是一年中的第几天
     *
     * @param dateTime
     * @return
     */
    public static int dayOfYear(Date dateTime) {
        int year = 0, month = 0, date = 0, day = 0;
        if (null == dateTime) {
            return day;
        }
        String dateTimeStr = date2Str(dateTime, null);
        LocalDateTime localDateTime = parse(dateTimeStr);
        year = localDateTime.getYear();
        month = localDateTime.getMonthValue();
        date = localDateTime.getDayOfMonth();
        return dayOfYear(year, month, date);
    }


    /**
     * 判断给定的日期是一年中的第几天
     *
     * @param year
     * @param month
     * @param date
     * @return
     */
    public static int dayOfYear(int year, int month, int date) {
        int[] days = {0, 31, 0, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31};
        int day = 0;
        //判断是不是闰年，然后设置二月的天数
        if ((year % 400 == 0) || ((year % 100 != 0) && (year % 4 == 0))) {
            days[2] = 29;
        } else {
            days[2] = 28;
        }
        for (int i = 1; i < month; i++) {
            day += days[i];
        }
        day += date;
        return day;
    }



    /*public static void main(String[] args){
        System.out.print(new Date());
    }*/
}

上面这个工具类不导依赖会报错

  <!-- sfl4j -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.7</version>
        </dependency>

注入工具类

package work.chenshuang.history.config;

import org.springframework.beans.BeansException;
import org.springframework.beans.factory.NoSuchBeanDefinitionException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

@Component
public class SpringUtil implements ApplicationContextAware {
    private static ApplicationContext applicationContext = null;

    public SpringUtil() {
    }

    public void setApplicationContext(ApplicationContext arg0) throws BeansException {
        if (applicationContext == null) {
            applicationContext = arg0;
        }

    }

    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    public static void setAppCtx(ApplicationContext webAppCtx) {
        if (webAppCtx != null) {
            applicationContext = webAppCtx;
        }
    }

    /**
     * 拿到ApplicationContext对象实例后就可以手动获取Bean的注入实例对象
     */
    public static <T> T getBean(Class<T> clazz) {
        return getApplicationContext().getBean(clazz);
    }

    public static <T> T getBean(String name, Class<T> clazz) throws ClassNotFoundException {
        return getApplicationContext().getBean(name, clazz);
    }

    public static final Object getBean(String beanName) {
        return getApplicationContext().getBean(beanName);
    }

    public static final Object getBean(String beanName, String className) throws ClassNotFoundException {
        Class clz = Class.forName(className);
        return getApplicationContext().getBean(beanName, clz.getClass());
    }

    public static boolean containsBean(String name) {
        return getApplicationContext().containsBean(name);
    }

    public static boolean isSingleton(String name) throws NoSuchBeanDefinitionException {
        return getApplicationContext().isSingleton(name);
    }

    public static Class<?> getType(String name) throws NoSuchBeanDefinitionException {
        return getApplicationContext().getType(name);
    }

    public static String[] getAliases(String name) throws NoSuchBeanDefinitionException {
        return getApplicationContext().getAliases(name);
    }
}

配置完就可以使用了 提前创建好表就行了 ，动态创建表可以，但节点表没法动态改变，节点表不改变，查询就只会查那配置好的表，有懂的大佬还望告知！