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>
<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);
}
}
配置完就可以使用了 提前创建好表就行了 ，动态创建表可以，但节点表没法动态改变，节点表不改变，查询就只会查那配置好的表，有懂的大佬还望告知！
苏州本地的技术开发者社区，在这里可以交流本地的好吃好玩的，可以交流技术，可以交流招聘等等，没啥限制。
更多推荐
0
0- 0
已为社区贡献3条内容
所有评论(0)