package com.xiaopeng.itinerary.utils;
|
|
import lombok.extern.slf4j.Slf4j;
|
|
import java.util.ArrayList;
|
import java.util.List;
|
|
/**
|
* 分表查询辅助工具类
|
* 提供分表查询的SQL构建和批量查询支持
|
*
|
* @author auto-generated
|
*/
|
@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 ItineraryQueryBuilder {
|
|
/**
|
* 根据用户ID查询操作日志
|
*/
|
public static String querySysOperLogByUserId(Long userId, String additionalWhere) {
|
String tableName = ShardingTableUtil.getSysOperLogTableName(userId);
|
StringBuilder sql = new StringBuilder("SELECT * FROM ").append(tableName);
|
sql.append(" WHERE userId = ").append(userId);
|
|
if (additionalWhere != null && !additionalWhere.trim().isEmpty()) {
|
sql.append(" AND ").append(additionalWhere);
|
}
|
|
return sql.toString();
|
}
|
|
/**
|
* 根据用户ID查询协议用户信息
|
*/
|
public static String queryAgreementAppUserByUserId(Long appUserId) {
|
String tableName = ShardingTableUtil.getAgreementAppUserTableName(appUserId);
|
return "SELECT * FROM " + tableName + " WHERE app_user_id = " + appUserId;
|
}
|
|
/**
|
* 根据电子围栏区域ID查询点位信息
|
*/
|
public static String queryPointByElectronicAreaId(Long electronicAreaId) {
|
String tableName = ShardingTableUtil.getPointTableName(electronicAreaId);
|
return "SELECT * FROM " + tableName + " WHERE electronic_area_id = " + electronicAreaId;
|
}
|
|
/**
|
* 查询所有操作日志（全表扫描）
|
*/
|
public static String queryAllSysOperLogs(String whereClause, String orderBy, Integer limit) {
|
return ShardingQueryHelper.buildUnionAllQuery("sys_oper_log", 10, whereClause, orderBy, limit);
|
}
|
|
/**
|
* 查询所有协议用户（全表扫描）
|
*/
|
public static String queryAllAgreementAppUsers(String whereClause, String orderBy, Integer limit) {
|
return ShardingQueryHelper.buildUnionAllQuery("xp_agreement_app_user", 10, whereClause, orderBy, limit);
|
}
|
|
/**
|
* 查询所有点位（全表扫描）
|
*/
|
public static String queryAllPoints(String whereClause, String orderBy, Integer limit) {
|
return ShardingQueryHelper.buildUnionAllQuery("xp_point", 10, 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 countAllSysOperLogs(String whereClause) {
|
return buildCountQuery("sys_oper_log", 10, whereClause);
|
}
|
|
/**
|
* 统计所有协议用户数量
|
*/
|
public static String countAllAgreementAppUsers(String whereClause) {
|
return buildCountQuery("xp_agreement_app_user", 10, whereClause);
|
}
|
|
/**
|
* 统计所有点位数量
|
*/
|
public static String countAllPoints(String whereClause) {
|
return buildCountQuery("xp_point", 10, whereClause);
|
}
|
}
|
|
/**
|
* 打印查询示例
|
*/
|
public static void printQueryExamples() {
|
log.info("========== Sharding Query Examples ==========");
|
log.info("1. Query sys_oper_log by userId 12345:");
|
log.info(" {}", ItineraryQueryBuilder.querySysOperLogByUserId(12345L, "status = 1"));
|
log.info("");
|
log.info("2. Query agreement_app_user by userId 67890:");
|
log.info(" {}", ItineraryQueryBuilder.queryAgreementAppUserByUserId(67890L));
|
log.info("");
|
log.info("3. Query all sys_oper_logs (UNION ALL):");
|
log.info(" {}", ItineraryQueryBuilder.queryAllSysOperLogs("status = 1", "create_time DESC", 100));
|
log.info("");
|
log.info("4. Count all agreement_app_users:");
|
log.info(" {}", ShardingCountHelper.countAllAgreementAppUsers(null));
|
log.info("=============================================");
|
}
|
}
|
