package com.xiaopeng.order.utils;
|
|
import lombok.extern.slf4j.Slf4j;
|
|
import java.util.ArrayList;
|
import java.util.List;
|
|
/**
|
* 分表查询辅助工具类
|
* 提供分表查询的SQL构建和批量查询支持
|
*
|
* @author auto-generated
|
* @date 2026-03-05
|
*/
|
@Slf4j
|
public class ShardingQueryHelper {
|
|
/**
|
* 构建单个分表的查询SQL
|
*
|
* @param baseTableName 基础表名
|
* @param shardingValue 分片键值
|
* @param shardingCount 分片数量
|
* @param whereClause WHERE子句（不包含WHERE关键字）
|
* @return 完整的查询SQL
|
*/
|
public static String buildSingleTableQuery(String baseTableName, Long shardingValue,
|
int shardingCount, String whereClause) {
|
String tableName = ShardingTableUtil.getShardingTableName(baseTableName, shardingValue, shardingCount);
|
StringBuilder sql = new StringBuilder("SELECT * FROM ");
|
sql.append(tableName);
|
|
if (whereClause != null && !whereClause.trim().isEmpty()) {
|
sql.append(" WHERE ").append(whereClause);
|
}
|
|
String query = sql.toString();
|
log.debug("Generated single table query: {}", query);
|
return query;
|
}
|
|
/**
|
* 构建全表扫描的UNION查询SQL（适用于需要查询所有分表的场景）
|
*
|
* @param baseTableName 基础表名
|
* @param shardingCount 分片数量
|
* @param whereClause WHERE子句（不包含WHERE关键字）
|
* @param orderBy 排序字句（不包含ORDER BY关键字）
|
* @param limit 限制返回数量，null表示不限制
|
* @return UNION查询SQL
|
*/
|
public static String buildUnionAllQuery(String baseTableName, int shardingCount,
|
String whereClause, String orderBy, Integer limit) {
|
StringBuilder sql = new StringBuilder();
|
|
for (int i = 0; i < shardingCount; i++) {
|
if (i > 0) {
|
sql.append(" UNION ALL ");
|
}
|
|
sql.append("SELECT * FROM ").append(baseTableName).append("_").append(i);
|
|
if (whereClause != null && !whereClause.trim().isEmpty()) {
|
sql.append(" WHERE ").append(whereClause);
|
}
|
}
|
|
// 外层排序和限制
|
if (orderBy != null && !orderBy.trim().isEmpty()) {
|
sql.insert(0, "SELECT * FROM (");
|
sql.append(") AS union_result ORDER BY ").append(orderBy);
|
}
|
|
if (limit != null && limit > 0) {
|
sql.append(" LIMIT ").append(limit);
|
}
|
|
String query = sql.toString();
|
log.debug("Generated union all query for {} tables: {}", shardingCount, query);
|
return query;
|
}
|
|
/**
|
* 获取需要查询的分表索引列表（基于分片键值范围）
|
*
|
* @param startValue 起始分片键值
|
* @param endValue 结束分片键值
|
* @param shardingCount 分片数量
|
* @return 需要查询的表索引列表
|
*/
|
public static List<Integer> getShardingIndexesInRange(Long startValue, Long endValue, int shardingCount) {
|
List<Integer> indexes = new ArrayList<>();
|
|
if (startValue == null || endValue == null || startValue > endValue) {
|
log.warn("Invalid range: start={}, end={}, returning all indexes", startValue, endValue);
|
for (int i = 0; i < shardingCount; i++) {
|
indexes.add(i);
|
}
|
return indexes;
|
}
|
|
// 计算涉及的分表索引
|
for (long value = startValue; value <= endValue; value++) {
|
int index = (int) (value % shardingCount);
|
if (!indexes.contains(index)) {
|
indexes.add(index);
|
}
|
}
|
|
log.debug("Range query from {} to {} covers indexes: {}", startValue, endValue, indexes);
|
return indexes;
|
}
|
|
/**
|
* 构建基于索引列表的UNION查询
|
*
|
* @param baseTableName 基础表名
|
* @param tableIndexes 要查询的表索引列表
|
* @param whereClause WHERE子句
|
* @param orderBy 排序字句
|
* @param limit 限制数量
|
* @return UNION查询SQL
|
*/
|
public static String buildUnionQueryByIndexes(String baseTableName, List<Integer> tableIndexes,
|
String whereClause, String orderBy, Integer limit) {
|
if (tableIndexes == null || tableIndexes.isEmpty()) {
|
log.warn("No table indexes provided, returning empty query");
|
return "";
|
}
|
|
StringBuilder sql = new StringBuilder();
|
|
for (int i = 0; i < tableIndexes.size(); i++) {
|
if (i > 0) {
|
sql.append(" UNION ALL ");
|
}
|
|
int tableIndex = tableIndexes.get(i);
|
sql.append("SELECT * FROM ").append(baseTableName).append("_").append(tableIndex);
|
|
if (whereClause != null && !whereClause.trim().isEmpty()) {
|
sql.append(" WHERE ").append(whereClause);
|
}
|
}
|
|
// 外层排序和限制
|
if (orderBy != null && !orderBy.trim().isEmpty()) {
|
sql.insert(0, "SELECT * FROM (");
|
sql.append(") AS union_result ORDER BY ").append(orderBy);
|
}
|
|
if (limit != null && limit > 0) {
|
sql.append(" LIMIT ").append(limit);
|
}
|
|
String query = sql.toString();
|
log.debug("Generated union query for indexes {}: {}", tableIndexes, query);
|
return query;
|
}
|
|
/**
|
* 构建订单相关表的批量查询SQL示例
|
*/
|
public static class OrderQueryBuilder {
|
|
/**
|
* 根据用户ID查询订单
|
*/
|
public static String queryOrderByUserId(Long appUserId, String additionalWhere) {
|
String tableName = ShardingTableUtil.getOrderTableName(appUserId);
|
StringBuilder sql = new StringBuilder("SELECT * FROM ").append(tableName);
|
sql.append(" WHERE app_user_id = ").append(appUserId);
|
|
if (additionalWhere != null && !additionalWhere.trim().isEmpty()) {
|
sql.append(" AND ").append(additionalWhere);
|
}
|
|
return sql.toString();
|
}
|
|
/**
|
* 根据订单ID查询订单详情
|
*/
|
public static String queryOrderDetailByOrderId(Long orderId) {
|
String tableName = ShardingTableUtil.getOrderDetailTableName(orderId);
|
return "SELECT * FROM " + tableName + " WHERE order_id = " + orderId;
|
}
|
|
/**
|
* 根据订单ID查询订单支付信息
|
*/
|
public static String queryOrderPaymentByOrderId(Long orderId) {
|
String tableName = ShardingTableUtil.getOrderPaymentTableName(orderId);
|
return "SELECT * FROM " + tableName + " WHERE order_id = " + orderId;
|
}
|
|
/**
|
* 根据订单ID查询订单评价
|
*/
|
public static String queryOrderEvaluateByOrderId(Long orderId) {
|
String tableName = ShardingTableUtil.getOrderEvaluateTableName(orderId);
|
return "SELECT * FROM " + tableName + " WHERE order_id = " + orderId;
|
}
|
|
/**
|
* 根据订单ID查询订单取消记录
|
*/
|
public static String queryOrderCancelByOrderId(Long orderId) {
|
String tableName = ShardingTableUtil.getOrderCancelTableName(orderId);
|
return "SELECT * FROM " + tableName + " WHERE order_id = " + orderId;
|
}
|
|
/**
|
* 查询所有订单（全表扫描）
|
*/
|
public static String queryAllOrders(String whereClause, String orderBy, Integer limit) {
|
return ShardingQueryHelper.buildUnionAllQuery("xp_order", 20, whereClause, orderBy, limit);
|
}
|
|
/**
|
* 查询所有订单详情（全表扫描）
|
*/
|
public static String queryAllOrderDetails(String whereClause, String orderBy, Integer limit) {
|
return ShardingQueryHelper.buildUnionAllQuery("xp_order_detail", 20, whereClause, orderBy, limit);
|
}
|
}
|
|
/**
|
* 分表查询计数工具
|
*/
|
public static class ShardingCountHelper {
|
|
/**
|
* 构建分表计数查询（UNION ALL + SUM）
|
*/
|
public static String buildCountQuery(String baseTableName, int shardingCount, String whereClause) {
|
StringBuilder sql = new StringBuilder("SELECT SUM(cnt) AS total_count FROM (");
|
|
for (int i = 0; i < shardingCount; i++) {
|
if (i > 0) {
|
sql.append(" UNION ALL ");
|
}
|
|
sql.append("SELECT COUNT(*) AS cnt FROM ").append(baseTableName).append("_").append(i);
|
|
if (whereClause != null && !whereClause.trim().isEmpty()) {
|
sql.append(" WHERE ").append(whereClause);
|
}
|
}
|
|
sql.append(") AS count_result");
|
|
String query = sql.toString();
|
log.debug("Generated count query: {}", query);
|
return query;
|
}
|
|
/**
|
* 统计所有订单数量
|
*/
|
public static String countAllOrders(String whereClause) {
|
return buildCountQuery("xp_order", 20, whereClause);
|
}
|
}
|
|
/**
|
* 打印查询示例
|
*/
|
public static void printQueryExamples() {
|
log.info("========== Sharding Query Examples ==========");
|
log.info("1. Query order by userId 12345:");
|
log.info(" {}", OrderQueryBuilder.queryOrderByUserId(12345L, "status = 1"));
|
log.info("");
|
log.info("2. Query order detail by orderId 67890:");
|
log.info(" {}", OrderQueryBuilder.queryOrderDetailByOrderId(67890L));
|
log.info("");
|
log.info("3. Query all orders with status=1 (UNION ALL):");
|
log.info(" {}", OrderQueryBuilder.queryAllOrders("status = 1", "create_time DESC", 100));
|
log.info("");
|
log.info("4. Count all orders:");
|
log.info(" {}", ShardingCountHelper.countAllOrders("status IN (9, 10)"));
|
log.info("=============================================");
|
}
|
}
|
