Springboot3.0整合shardingJdbc5实现按照年分库按月分表--整合es8实现按月分索引

GgBond-WC

已于 2024-01-25 15:08:31 修改

阅读量808

点赞数 5

分类专栏：后端开发 java spring 文章标签： spring boot idea 算法

于 2023-10-12 11:48:08 首次发布

本文链接：https://blog.csdn.net/qq_42633058/article/details/133787025

版权

后端开发同时被 3 个专栏收录

6 篇文章 0 订阅

订阅专栏

java

6 篇文章 0 订阅

订阅专栏

spring

6 篇文章 0 订阅

订阅专栏

SpringBoot3.0整合shardingJdbc5实现按照时间分库分表

依赖

		<groupId>org.apache.shardingsphere</groupId>
			<artifactId>shardingsphere-jdbc-core</artifactId>
			<version>5.3.2</version>
		</dependency>
		 <dependency>
           <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
             <version>3.0.1</version>
            </dependency>

核心配置

application.yml
spring:
  elasticsearch:
    uris: http://10.168.1.47:32374
    username: elastic
    password: lrvb113Q5c687qsmG74j2dMH
  data:
    elasticsearch:
      repositories:
        enabled: false
  config:
    activate:
      on-profile: dev
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:sharding.yaml




sharding.yaml

dataSources:
  ds_2022:
    driverClassName: com.mysql.cj.jdbc.Driver
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://10.168.1.15:3306/bbq_call_record_2022?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    password: 123456
    username: root
  ds_2023:
    driverClassName: com.mysql.cj.jdbc.Driver
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://10.168.1.15:3306/bbq_call_record_2023?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    password: 123456
    username: root

rules:
  - !SHARDING
    tables:
      call_record:
        actualDataNodes: ds_2022.call_record_2022_0${1..9},ds_2022.call_record_2022_1${0..2},ds_2023.call_record_2023_0${1..9},ds_2023.call_record_2023_1${0..2}
        databaseStrategy: # 分库策略，缺省表示使用默认分库策略，以下的分片策略只能选其一
          standard: # 用于单分片键的标准分片场景
            shardingColumn: call_time
            shardingAlgorithmName: database_inline
        tableStrategy:
          standard:
            shardingColumn: call_time
            shardingAlgorithmName: table_inline
    shardingAlgorithms:
      database_inline:
        type: CLASS_BASED
        props:
          strategy: STANDARD
          algorithmClassName: com.glasssix.bbq.callrecord.common.sharding.MyDbPreciseShardingAlgorithm
      table_inline:
        type: CLASS_BASED
        props:
          strategy: STANDARD
          algorithmClassName: com.glasssix.bbq.callrecord.common.sharding.MyTablePreciseShardingAlgorithm

props:
  sql-show: true

精确分库算法

public class MyDbPreciseShardingAlgorithm implements StandardShardingAlgorithm<LocalDateTime> {
	@Override
	public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<LocalDateTime> shardingValue) {
		LocalDateTime localDateTime = shardingValue.getValue();
		String year = String.valueOf(localDateTime.getYear());
		for (String ds : availableTargetNames) {
			if (ds.endsWith(year)) {
				return ds;
			}
		}
		throw new IllegalArgumentException("库拆分失败");
	}

	@Override
	public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<LocalDateTime> shardingValue) {
		Set<String> list = new HashSet<>();
		Range<LocalDateTime> valueRange = shardingValue.getValueRange();
		LocalDateTime lowerDate = valueRange.lowerEndpoint();
		LocalDateTime upperDate = valueRange.upperEndpoint();
		String lowerSuffix = ShardingUtils.getSuffixByYearMonth(ShardingUtils.formatterYear, lowerDate.toLocalDate());
		String upperSuffix = ShardingUtils.getSuffixByYearMonth(ShardingUtils.formatterYear, upperDate.toLocalDate());
		Set<String> suffixList = ShardingUtils.getSuffixListForRangeDb(lowerSuffix, upperSuffix);
		for (String tableName : availableTargetNames) {
			if (containTableName(suffixList, tableName)) {
				list.add(tableName);
			}
		}
		return list;
	}


	private boolean containTableName(Set<String> suffixList, String tableName) {
		boolean flag = false;
		for (String s : suffixList) {
			if (tableName.endsWith(s)) {
				flag = true;
				break;
			}
		}
		return flag;
	}


	@Override
	public void init(Properties properties) {

	}


}

精确分表算法

public class MyTablePreciseShardingAlgorithm implements StandardShardingAlgorithm<LocalDateTime> {

	@Override
	public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<LocalDateTime> shardingValue) {
		LocalDateTime data = shardingValue.getValue();
		String mon = String.valueOf(data.getMonth().getValue());
		for (String table : availableTargetNames) {
			if (table.endsWith(mon)) {
				return table;
			}
		}
		throw new IllegalArgumentException("表拆分失败");
	}

	@Override
	public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<LocalDateTime> shardingValue) {
		Set<String> list = new HashSet<>();
		Range<LocalDateTime> valueRange = shardingValue.getValueRange();
		LocalDateTime lowerDate = valueRange.lowerEndpoint();
		LocalDateTime upperDate = valueRange.upperEndpoint();
		String lowerSuffix = ShardingUtils.getSuffixByYearMonth(ShardingUtils.formatterYearMonth, lowerDate.toLocalDate());
		String upperSuffix = ShardingUtils.getSuffixByYearMonth(ShardingUtils.formatterYearMonth, upperDate.toLocalDate());
		Set<String> suffixList = ShardingUtils.getSuffixListForRangeTable(lowerSuffix, upperSuffix);
		for (String tableName : availableTargetNames) {
			if (containTableName(suffixList, tableName)) {
				list.add(tableName);
			}
		}
		return list;
	}

	private boolean containTableName(Set<String> suffixList, String tableName) {
		boolean flag = false;
		for (String s : suffixList) {
			if (tableName.endsWith(s)) {
				flag = true;
				break;
			}
		}
		return flag;
	}


	@Override
	public void init(Properties properties) {

	}


}

工具类


package com.glasssix.bbq.callrecord.common.utils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

/**
 * （CPR-DB-0002）DB分表工具类
 *
 * @author zata
 */
public class ShardingUtils {
	public static final String FORMAT_YYYY = "yyyy";
	public static final String FORMAT_YYYY_MM = "yyyy_MM";
	public static SimpleDateFormat sdfYear;
	public static SimpleDateFormat sdfYearMonth;
	public static DateTimeFormatter formatterYear;
	public static DateTimeFormatter formatterYearMonth;

	static {
		sdfYear = new SimpleDateFormat(FORMAT_YYYY);
		formatterYear = DateTimeFormatter.ofPattern(FORMAT_YYYY);
		sdfYearMonth = new SimpleDateFormat(FORMAT_YYYY_MM);
		formatterYearMonth = DateTimeFormatter.ofPattern(FORMAT_YYYY_MM);
	}


	public static Set<String> getSuffixListForRangeDb(String lowerSuffix, String upperSuffix) {
		Set<String> suffixList = new HashSet<>();
		if (lowerSuffix.equals(upperSuffix)) {
			suffixList.add(lowerSuffix);
		} else {
			//上下界不在同一张表  计算间隔的所有表
			String tempSuffix = lowerSuffix;
			while (!tempSuffix.equals(upperSuffix)) {
				suffixList.add(tempSuffix);
				LocalDate tempDate = parse(tempSuffix, sdfYear);
				tempSuffix = ShardingUtils.getSuffixByYearMonth(formatterYear, tempDate.plusYears(1));
			}
			suffixList.add(tempSuffix);
		}
		return suffixList;
	}

	public static Set<String> getSuffixListForRangeTable(String lowerSuffix, String upperSuffix) {
		Set<String> suffixList = new HashSet<>();
		if (lowerSuffix.equals(upperSuffix)) {
			//上下界在同一张表
			suffixList.add(lowerSuffix);
		} else {
			//上下界不在同一张表  计算间隔的所有表
			String tempSuffix = lowerSuffix;
			while (!tempSuffix.equals(upperSuffix)) {
				suffixList.add(tempSuffix);
				LocalDate tempDate = parse(tempSuffix, sdfYearMonth);
				tempSuffix = ShardingUtils.getSuffixByYearMonth(formatterYearMonth, tempDate.plusMonths(1));
			}
			suffixList.add(tempSuffix);
		}
		return suffixList;
	}

	public static String getSuffixByYearMonth(DateTimeFormatter formatter, LocalDate date) {
		return date.format(formatter);
	}

	public static LocalDate parse(String date, SimpleDateFormat sdf) {
		Date dateTime = null;
		try {
			dateTime = sdf.parse(date);
		} catch (ParseException e) {
			throw new RuntimeException(e);
		}
		LocalDate localDate = dateTime.toInstant().atZone(java.time.ZoneId.systemDefault()).toLocalDate();
		return localDate;
	}

}

实体类

package com.glasssix.bbq.callrecord.common.entity;

import com.baomidou.mybatisplus.annotation.*;
import com.baomidou.mybatisplus.annotation.TableName;
import com.google.common.collect.Lists;
import lombok.*;
import com.baomidou.mybatisplus.annotation.TableField;
import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;
import jakarta.validation.constraints.NotNull;
import jakarta.validation.constraints.Size;

import java.io.Serializable;
import java.time.LocalDateTime;

import java.util.Date;

/**
 * @TableName call_record
 */
@TableName(value = "call_record")
@Data
@EqualsAndHashCode(callSuper = false)
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class CallRecord implements Serializable {

	/**
	 * redis缓存KEY
	 */
	public static final String REDIS_KEY = "call_record:";

	/**
	 *
	 */
	@NotNull(message = "[]不能为空")


	@TableId(type = IdType.ASSIGN_ID)
	private Long id;


	/**
	 * 算法id
	 */


	private Long algorithmId;
	/**
	 * 算法名称
	 */
	private String algorithmName;


	/**
	 * 版本id
	 */


	private Long versionId;
	/**
	 * 版本名称
	 */
	private String versionName;


	/**
	 * 用户编号
	 */


	private String userSerial;


	/**
	 * 应用编号
	 */


	private String applicationSerial;


	/**
	 * 操作 ip
	 */
	@Size(max = 32, message = "操作 ip不能超过32")


	private String ip;


	/**
	 * 主动创建时间
	 */
	@TableField(fill = FieldFill.INSERT, value = "create_time")
	@JsonDeserialize(using = LocalDateTimeDeserializer.class)
	@JsonSerialize(using = LocalDateTimeSerializer.class)
	private LocalDateTime createTime;


	/**
	 * 调用时间
	 */
	@JsonDeserialize(using = LocalDateTimeDeserializer.class)
	@JsonSerialize(using = LocalDateTimeSerializer.class)
	private LocalDateTime callTime;

	/**
	 * 0:失败 1:成功
	 */
	private Boolean callStatus;

}

es按月分索引

@Component("dynamicIndexHelper")
public class DynamicIndexHelper {
	private DateTimeFormatter FORMATTER_MONTH = DateTimeFormatter.ofPattern("_yyyy_MM");

	public String indexCallRecordDoc(String index) {
		String date = LocalDate.now().format(FORMATTER_MONTH);
		return index + date;
	}
	public String indexCallRecordDoc(LocalDate localDate,String index) {
		String date = localDate.format(FORMATTER_MONTH);
		return index + date;
	}
}
package com.glasssix.bbq.callrecord.common.entity;

import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;
import jakarta.validation.constraints.Size;
import lombok.*;
import org.springframework.data.annotation.Id;
import org.springframework.data.elasticsearch.annotations.DateFormat;
import org.springframework.data.elasticsearch.annotations.Document;
import org.springframework.data.elasticsearch.annotations.Field;
import org.springframework.data.elasticsearch.annotations.FieldType;

import java.io.Serializable;
import java.time.LocalDateTime;

@Data
@EqualsAndHashCode(callSuper = false)
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Document(indexName = "#{@dynamicIndexHelper.indexCallRecordDoc('call_record')}")
public class CallRecordDoc implements Serializable {

	public static final String INDEX_NAME = "call_record";
	@Id
	@Field(type = FieldType.Long)
	private Long id;


	/**
	 * 算法id
	 */

	@Field(type = FieldType.Long)
	private Long algorithmId;
	/**
	 * 算法名称
	 */
	@Field(type = FieldType.Keyword)
	private String algorithmName;


	/**
	 * 版本id
	 */

	@Field(type = FieldType.Long)
	private Long versionId;
	/**
	 * 版本名称
	 */
	@Field(type = FieldType.Keyword)
	private String versionName;


	/**
	 * 用户编号
	 */

	@Field(type = FieldType.Keyword)
	private String userSerial;


	/**
	 * 应用编号
	 */

	@Field(type = FieldType.Keyword)
	private String applicationSerial;



	@Field(type = FieldType.Keyword)
	private String ip;


	/**
	 * 主动创建时间
	 */
	@Field(type = FieldType.Date, format = DateFormat.date_hour_minute_second)
	@JsonDeserialize(using = LocalDateTimeDeserializer.class)
	@JsonSerialize(using = LocalDateTimeSerializer.class)
	private LocalDateTime createTime;


	/**
	 * 调用时间
	 */
	@Field(type = FieldType.Date, format = DateFormat.date_hour_minute_second)
	@JsonDeserialize(using = LocalDateTimeDeserializer.class)
	@JsonSerialize(using = LocalDateTimeSerializer.class)
	private LocalDateTime callTime;


	/**
	 * 0:失败 1:成功
	 */

	@Field(type = FieldType.Boolean)
	private Boolean callStatus;


}



public interface CallRecordDocEsService  {

	void insertData(CallRecord record);

	List<CallRecordVO> esList(CallRecordVO callRecordVO);


	/**
	 * 获取对象索引名称
	 */
	String[] getTDocIndexNameQuery(LocalDateTime startTime, LocalDateTime endTime);

	IPage<CallRecordVO> esListIPage(CallRecordPageVO callRecordPageVO);

	List<CallRecordVO> esData(CallRecordPageVO callRecordPageVO);
}
@Service
@AllArgsConstructor
public class CallRecordDocEsServiceImpl implements CallRecordDocEsService {
	private ElasticsearchTemplate elasticsearchTemplate;
	private DynamicIndexHelper dynamicIndexHelper;
	private CallRecordDocRepository esRepository;

	@Override
	public void insertData(CallRecord record) {
		CallRecordDoc callRecordDoc = CallRecordUtil.toCallRecordDoc(record);
		IndexQuery indexQuery = new IndexQueryBuilder().withObject(callRecordDoc).build();
		elasticsearchTemplate.index(indexQuery, IndexCoordinates.of(dynamicIndexHelper.indexCallRecordDoc(callRecordDoc.getCallTime().toLocalDate(), CallRecordDoc.INDEX_NAME)));
	}

	@Override
	public List<CallRecordVO> esList(CallRecordVO callRecordVO) {
		List<CallRecordDoc> all = esRepository.findAll();
		List<CallRecordVO> callRecordVOS = CallRecordUtil.toCallESRecordVOList(all);
		return callRecordVOS;
	}

	@Override
	public String[] getTDocIndexNameQuery(LocalDateTime startTime, LocalDateTime endTime) {
		String indexName = CallRecordDoc.INDEX_NAME;
		if (ObjectUtils.isNotEmpty(startTime) && ObjectUtils.isNotEmpty(endTime)) {
			return EsUtils.getMonthBetween(startTime, endTime, indexName);
		}
		startTime = LocalDateTime.of(2022, 1, 1, 0, 0, 0);
		return EsUtils.getMonthBetween(startTime, LocalDateTime.now(), indexName);
	}

	@Override
	public IPage<CallRecordVO> esListIPage(CallRecordPageVO callRecordPageVO) {
		IPage<CallRecordVO> page = new Page<>(callRecordPageVO.getOffset(), callRecordPageVO.getLimit());
		return page(callRecordPageVO.getColumns(), callRecordPageVO.getOrder(), page, callRecordPageVO, getTDocIndexNameQuery(callRecordPageVO.getStartTime(), callRecordPageVO.getEndTime()));
	}

	@Override
	public List<CallRecordVO> esData(CallRecordPageVO callRecordVO) {

		if (ObjectUtils.isEmpty(callRecordVO.getStartTime()) || ObjectUtils.isEmpty(callRecordVO.getEndTime())) {
			LocalDateTime now = LocalDateTime.now();
			LocalDateTime sevenDaysAgoEnd = LocalDateTime.of(now.minusDays(6).toLocalDate(), LocalTime.MIN);
			callRecordVO.setStartTime(sevenDaysAgoEnd);
			callRecordVO.setEndTime(now);
		}
		NativeQueryBuilder builder = NativeQuery.builder();
		builder.withAggregation("count",
			Aggregation.of(fn ->
				fn.dateHistogram(
						data -> data.field("callTime")
							.calendarInterval(CalendarInterval.Day)
							.format("yyyy-MM-dd")
					)
					.aggregations("callStatus", Aggregation.of(a -> a.terms(ta -> ta.field("callStatus").size(1000))))
			)
		);
		getQuery(callRecordVO, builder);
		builder = builder.withSort(Sort.by(Sort.Direction.ASC, "callTime"));
		String[] tDocIndexNameQuery = getTDocIndexNameQuery(callRecordVO.getStartTime(), callRecordVO.getEndTime());
		SearchHits<CallRecordDoc> searchHits = elasticsearchTemplate.search(builder.build(), CallRecordDoc.class, IndexCoordinates.of(tDocIndexNameQuery));
		AggregationsContainer<?> aggregations = searchHits.getAggregations();
		Map<String, ElasticsearchAggregation> aggregationMap = ((ElasticsearchAggregations) aggregations).aggregationsAsMap();
		ElasticsearchAggregation elasticsearchAggregation = aggregationMap.get("count");
		Aggregate aggregate = elasticsearchAggregation.aggregation().getAggregate();
		((DateHistogramAggregate) aggregate._get()).buckets().array().forEach(
			data -> {
				System.out.println(data.keyAsString());
				Map<String, Aggregate> aggregations1 = data.aggregations();
				Aggregate callStatus = aggregations1.get("callStatus");
				((LongTermsAggregate)callStatus._get()).buckets().array().forEach(
					callStatusData -> {
						System.out.println(callStatusData.keyAsString());
						System.out.println(callStatusData.docCount());
					}
				);
			}
		);
		return null;
	}

	public IPage<CallRecordVO> page(String columns, String orders, IPage<CallRecordVO> page, CallRecordPageVO callRecordPageVO, String[] indexName) {
		NativeQueryBuilder builder = NativeQuery.builder();
		builder.withPageable(PageRequest.of((int) (page.getCurrent() - 1), (int) page.getSize()));
		getQuery(callRecordPageVO, builder);
		if (ObjectUtils.isNotEmpty(columns) && ObjectUtils.isNotEmpty(orders)) {
			String[] columnList = columns.split(",");
			String[] orderList = orders.split(",");
			for (int x = 0; x < columnList.length; x++) {
				builder = builder.withSort(Sort.by(Sort.Direction.fromString(orderList[x]), columnList[x]));
			}
		} else {
			builder = builder.withSort(Sort.by(Sort.Direction.DESC, "callTime"));
		}

		SearchHits<CallRecordDoc> searchHits = elasticsearchTemplate.search(builder.build(), CallRecordDoc.class, IndexCoordinates.of(indexName));
		long totalHits = searchHits.getTotalHits();
		List<CallRecordDoc> entityList = searchHits.getSearchHits().stream()
			.map(SearchHit::getContent)
			.toList();
		List<CallRecordVO> callRecordVOS = CallRecordUtil.toCallESRecordVOList(entityList);
		page.setTotal(totalHits);
		page.setCurrent(page.getCurrent());
		page.setPages(page.getSize());
		page.setRecords(callRecordVOS);

		return page;

	}

	private static void getQuery(CallRecordPageVO callRecordPageVO, NativeQueryBuilder builder) {
		builder.withQuery(q ->
		{
			q.bool(bool -> {
				if (ObjectUtils.isNotEmpty(callRecordPageVO.getVersionName())) {
					bool.must(much -> much.match(m -> m.field("versionName").query(callRecordPageVO.getVersionName())));
				}
				if (ObjectUtils.isNotEmpty(callRecordPageVO.getAlgorithmName())) {
					bool.must(much -> much.match(m -> m.field("algorithmName").query(callRecordPageVO.getAlgorithmName())));
				}
				if (ObjectUtils.isNotEmpty(callRecordPageVO.getCallStatus())) {
					bool.must(much -> much.match(m -> m.field("callStatus").query(callRecordPageVO.getCallStatus())));
				}
				if (ObjectUtils.isNotEmpty(callRecordPageVO.getStartTime()) && ObjectUtils.isNotEmpty(callRecordPageVO.getEndTime())) {
					bool.must(range -> range.range(r -> r
						.field("callTime")
						.gte(JsonData.of(callRecordPageVO.getStartTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss"))))
						.lte(JsonData.of(callRecordPageVO.getEndTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss"))))
					));
				}
				return bool;
			});
			return q;
		});
	}
}

GgBond-WC

关注

5
点赞
踩
3

收藏

觉得还不错? 一键收藏
2
评论
Springboot3.0整合shardingJdbc5实现按照年分库按月分表--整合es8实现按月分索引

【代码】Springboot3.0整合shardingJdbc5实现按照年分库按月分表--整合es8实现按月分索引。
复制链接

扫一扫