1. 目标与前置条件

目标：基于 JSQLParser + FreeMarker + Vue3 构建一套“可配置的数据透视报表”能力，实现从任意基础 SQL/视图出发，按维度/指标灵活聚合、筛选、排序、分页、导出，并支持钻取、联动、TopN、同比环比等常见分析操作。

前置条件（结合你的技术栈）

运行环境：Java 21 / Spring Boot 3.x / Spring Data / Shiro
数据库：MySQL 8.x（支持窗口函数、CTE、ROLLUP 等特性）
前端：Vue3 + Element Plus（也可替换任意 UI 组件库）
模板存储：DB（公共宏 + 业务模板），启动时预加载 + 变更热更新

2. 总体架构（高层）

Vue3（报表设计器/查看器）│  REST/JSON▼
Pivot API（Controller） —— 鉴权（Shiro） —— 限流/审计▼
PivotService（组装查询）├─ TemplateRegistry（FreeMarker 宏/模板加载与渲染）├─ SqlPipeline（JSQLParser 操作：包裹子查询、注入条件、生成聚合）├─ QueryEngine（JdbcTemplate/EntityManager 执行 + 数据权限）└─ CacheLayer（聚合结果缓存/预聚合物化）▼MySQL 8（基础明细表/视图）

关键思想：

任意 SQL → 子查询：把用户/模板提供的基础 SQL 用 JSQLParser 包装成 SELECT ... FROM ( <base_sql> ) t，统一在外层做维度聚合与筛选。
模板只关心“表达式”：维度/指标的 SQL 片段通过宏/模板生成，避免手写大量 if/else 拼接。
强安全：白名单列/表校验、参数化绑定、限时/限量、敏感字段脱敏、SQL 审计日志。

3. 配置模型（后端/前端统一）

{"baseSqlId": "sales_order_detail",             "dimensions": [                                  { "expr": "date_format(order_date,'%Y-%m-%d')", "alias": "d" },{ "expr": "shop_code", "alias": "shop" }],"measures": [                                    { "func": "sum", "field": "amount", "alias": "gmv", "fmt": "currency" },{ "func": "count", "field": "order_id", "alias": "orders" }],"filters": {                                     "where": [{ "expr": "order_date >= :from" },{ "expr": "order_date < :to" },{ "expr": "shop_code in (:shops)" }],"having": [ { "expr": "sum(amount) > :minGmv" } ]},"sort": [ { "by": "gmv", "dir": "desc" }, { "by": "d", "dir": "asc" } ],"limit": 100,"offset": 0,"topn": { "by": "gmv", "n": 10 },           "time": { "grain": "day" },"compare": { "yoy": true, "wow": false },     "drill": { "enabled": true, "key": "order_id" },"export": { "type": "csv" },"params": { "from": "2025-08-01", "to": "2025-09-01", "shops": ["S1","S2"], "minGmv": 1000 }
}

说明：baseSqlId 映射到一段受控的基础 SQL（或视图），在 TemplateRegistry/DB 中维护；前后端均以该 JSON 做协议。

4. FreeMarker 模板设计

4.1 宏（公共库：pivot-macros.ftl）

<#-- 维度渲染 -->
<#macro renderDimensions dims><#list dims as d>${d.expr} AS `${d.alias}`<#if d?has_next>,</#if></#list>
</#macro><#-- 指标渲染（支持 func(field) as alias） -->
<#macro renderMeasures ms><#list ms as m>${m.func}(${m.field}) AS `${m.alias}`<#if m?has_next>,</#if></#list>
</#macro><#-- ORDER BY -->
<#macro renderOrder sort><#if sort?? && (sort?size>0)>ORDER BY<#list sort as s>`${s.by}` ${s.dir?upper_case}<#if s?has_next>,</#if></#list></#if>
</#macro>

4.2 聚合模板（外层）：pivot-aggregate.ftl

<#import "pivot-macros.ftl" as p />
SELECT<@p.renderDimensions dims=dimensions/><#if dimensions?size > 0 && measures?size > 0>,</#if><@p.renderMeasures ms=measures/>
FROM (${baseSql}
) t
<#-- WHERE 只作用于内层，通常由 SqlPipeline 注入。HAVING 用于外层聚合后过滤 -->
<#if filters?? && filters.having?? && (filters.having?size>0)>
HAVING<#list filters.having as h>${h.expr}<#if h?has_next> AND </#if></#list>
</#if>
<#if dimensions?size > 0>
GROUP BY<#list dimensions as d>${d.alias}<#if d?has_next>,</#if></#list>
</#if>
<@p.renderOrder sort=sort/>
<#if limit??> LIMIT ${limit} </#if>
<#if offset??> OFFSET ${offset} </#if>

注：baseSql 是经过 JSQLParser 处理过、带参数的内层 SQL。

5. JSQLParser 管道（核心）

目标：把任意基础 SQL 统一变为可注入条件的子查询，并做安全校验。

关键步骤：

解析：Statement stmt = CCJSqlParserUtil.parse(baseSql);
规整：移除外层 ORDER BY / LIMIT（由外层模板控制）；
追加 WHERE：把 filters.where 中的条件以 AND 方式追加到内层查询；
包装：SELECT * FROM ( <normalized_sql> ) t；
白名单校验：检查涉及的表/列是否在白名单；
参数绑定：使用 NamedParameterJdbcTemplate 执行，避免字符串拼接。

示例工具类（片段）：

public class SqlPipeline {public String wrapAsSubquery(String baseSql) {Select select = (Select) CCJSqlParserUtil.parse(baseSql);// 1) 清理外层 ORDER BY/LIMITselect.getSelectBody().accept(new OrderByAndLimitCleaner());// 2) 生成包装 SQLString normalized = select.toString();return "SELECT * FROM (" + normalized + ") t";}public String injectWhere(String wrappedSql, List<String> whereExprs) {// whereExprs 如： ["order_date >= :from", "order_date < :to"]PlainSelect ps = (PlainSelect) ((Select) CCJSqlParserUtil.parse(wrappedSql)).getSelectBody();Expression where = ps.getWhere();for (String expr : whereExprs) {Expression e = CCJSqlParserUtil.parseCondExpression(expr);where = (where == null) ? e : new AndExpression(where, e);}ps.setWhere(where);return ps.toString();}
}

生产建议：对 parse 异常做降级（如回退到安全模式），记录审计日志；在注入前对 expr 做黑/白名单校验。

6. 端到端示例

6.1 基础 SQL（注册为 sales_order_detail）

SELECTorder_id,order_date,shop_code,sku_code,qty,amount
FROM sales_order_detail

6.2 前端配置（节选）

{"baseSqlId": "sales_order_detail","dimensions": [{ "expr": "date_format(order_date,'%Y-%m-%d')", "alias": "d" },{ "expr": "shop_code", "alias": "shop" }],"measures": [{ "func": "sum", "field": "amount", "alias": "gmv" },{ "func": "count", "field": "order_id", "alias": "orders" }],"filters": {"where": [ { "expr": "order_date >= :from" }, { "expr": "order_date < :to" } ]},"sort": [ { "by": "gmv", "dir": "desc" } ],"limit": 50,"params": { "from": "2025-08-01", "to": "2025-09-01" }
}

6.3 管道出 SQL（简化展示）

SELECTdate_format(order_date,'%Y-%m-%d') AS `d`,shop_code AS `shop`,sum(amount) AS `gmv`,count(order_id) AS `orders`
FROM (SELECT order_id, order_date, shop_code, sku_code, qty, amountFROM sales_order_detailWHERE order_date >= :from AND order_date < :to
) t
GROUP BY d, shop
ORDER BY `gmv` DESC
LIMIT 50

7. 后端实现（关键类与 API）

7.1 API 契约

POST /api/pivot/preview：入参 PivotConfig，返回渲染后的 SQL（仅开发/调试环境开放）。
POST /api/pivot/run：入参 PivotConfig，返回分页数据（含列定义与格式）。
POST /api/pivot/export：入参 PivotConfig + export.type，流式导出 CSV/Excel。

7.2 DTO（简化）

record Dim(String expr, String alias) {}
record Meas(String func, String field, String alias, String fmt) {}
record Filter(List<String> where, List<String> having) {}
record Sort(String by, String dir) {}record PivotConfig(String baseSqlId,List<Dim> dimensions,List<Meas> measures,Filter filters,List<Sort> sort,Integer limit,Integer offset,Map<String,Object> params
) {}

7.3 Service 关键流程

public PivotResult run(PivotConfig cfg) {String baseSql = templateRegistry.loadBaseSql(cfg.baseSqlId());String inner = pipeline.wrapAsSubquery(baseSql);inner = pipeline.injectWhere(inner, cfg.filters().where());String aggregated = templateRegistry.render("pivot-aggregate.ftl", Map.of("baseSql", inner,"dimensions", cfg.dimensions(),"measures", cfg.measures(),"filters", cfg.filters(),"sort", cfg.sort(),"limit", cfg.limit(),"offset", cfg.offset()));return queryEngine.query(aggregated, cfg.params());
}

7.4 数据权限建议

在注入 WHERE 前，合并 data_scope 表达式（如门店/区域/用户标签范围）；
Shiro Subject → 上下文携带 org_ids/shop_codes；
对外仅暴露经过模板注册的 baseSqlId，禁止用户直接传任意 SQL。

8. 性能与可用性

索引：维度列、时间列、常用过滤列建立联合/覆盖索引；
预聚合：
- 定时生成日/周/月粒度物化表；
- 热点 TopN 结果放入 Redis，配置 ttl 和“参数签名”作为 Key；
分页策略：聚合后分页（LIMIT/OFFSET）；统计总条数使用二次查询：
```
SELECT COUNT(1) FROM (-- 把上面 GROUP BY 的 SELECT 去掉 ORDER BY/LIMIT
) x
```
大结果导出：使用 fetchSize + ResultSet.TYPE_FORWARD_ONLY 流式写 CSV；
并发：常见报表使用“参数签名”做结果缓存（如 60s）；

ROLLUP：可选支持：

SELECT d, shop, SUM(amount) gmv
FROM t
GROUP BY d, shop WITH ROLLUP

9. 高级功能示例

9.1 TopN（对店铺按 GMV 取 Top10，其它归为 OTHER）

WITH ranked AS (SELECT shop_code, SUM(amount) gmvFROM (${baseSqlWithWhere}) tGROUP BY shop_code
), r AS (SELECT shop_code, gmv, DENSE_RANK() OVER (ORDER BY gmv DESC) rnk FROM ranked
)
SELECT IF(rnk<=10, shop_code, 'OTHER') AS shop,SUM(gmv) gmv
FROM r
GROUP BY shop

也可在外层聚合后，用窗口函数 + CASE WHEN 分桶。

9.2 同比/环比（以日粒度为例）

SELECT d,SUM(amount) AS gmv,LAG(SUM(amount)) OVER (ORDER BY d) AS gmv_prev,(SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY d)) / NULLIF(LAG(SUM(amount)) OVER (ORDER BY d),0) AS wow
FROM (SELECT DATE(order_date) d, amount FROM sales_order_detail WHERE order_date BETWEEN :from AND :to
) t
GROUP BY d

YoY 可用 DATE_SUB(d, INTERVAL 1 YEAR) 关联对比或双区间查询再 JOIN。

9.3 钻取

规则：每条聚合行返回一个 drillKey（如 d|shop），前端点击发起 /api/pivot/drill?key=...，后端映射回内层 WHERE + 原始明细查询，限制 LIMIT 1000。

10. 前端（Vue3）

10.1 组件划分

PivotDesigner：左侧维度/指标/过滤器选择区；
PivotTable：结果展示 + 汇总行 + 导出；
FieldCatalog：基础 SQL 字段字典（从后端 /api/pivot/fields?baseSqlId=... 取）；

10.2 关键代码（示例，Composition API）

// usePivot.ts
import { ref } from 'vue'
import axios from 'axios'export function usePivot(){const config = ref({ baseSqlId: '', dimensions: [], measures: [], filters: { where:[], having:[] }, sort:[], limit:50, offset:0, params:{} })const loading = ref(false)const data = ref({ columns:[], rows:[], total:0 })async function run(){loading.value = truetry{const res = await axios.post('/api/pivot/run', config.value)data.value = res.data} finally { loading.value = false }}return { config, data, loading, run }
}

<!-- PivotTable.vue -->
<template><div><el-space><el-button :loading="loading" @click="run">运行</el-button><el-button @click="exportCsv">导出CSV</el-button></el-space><el-table :data="data.rows" style="width: 100%" :border="true"><el-table-column v-for="c in data.columns" :key="c.prop" :prop="c.prop" :label="c.label" :fixed="c.fixed" :width="c.width" /></el-table><el-paginationv-model:current-page="page"v-model:page-size="size":total="data.total"@current-change="onPageChange"@size-change="onSizeChange"/></div>
</template>
<script setup lang="ts">
import { ref, watch } from 'vue'
import { usePivot } from './usePivot'
const { config, data, loading, run } = usePivot()
const page = ref(1); const size = ref(50)
function onPageChange(p:number){ config.value.offset = (p-1)*size.value; run() }
function onSizeChange(s:number){ size.value = s; config.value.limit = s; config.value.offset = 0; run() }
function exportCsv(){ window.open('/api/pivot/export?type=csv') }
watch(()=>config.value.baseSqlId, run)
</script>

10.3 交互要点

维度/指标拖拽排序即 dimensions、measures 顺序；
过滤器 UI → 生成 expr（提供字段/操作符/占位参数拼装器，避免用户手写表达式）；
支持字段格式化：金额、百分比、千分位等（后端返回 columns[x].fmt）。

11. 安全与风控

只允许调用已注册的 baseSqlId（DB 中维护 SQL 文本与字段白名单）；
所有 :param 走 NamedParameterJdbcTemplate 绑定；
表达式校验：限制可用函数/关键字（例如禁止 ;, --, /* */, sleep 等）；
超时/行数限制：默认 timeout=15s，max-rows=50,000；
审计：记录用户、SQL 摘要（hash）、耗时、扫描行数、命中缓存与否。

12. 测试计划

单测：
- JSQLParser：Where 注入、Order/LIMIT 清理、别名冲突处理；
- 模板渲染：预期 SQL 与实际对比；
集成测试：
- 常见维度组合/TopN/同比/环比/分页/导出；
- 大数据量性能基准（QPS、P95 延迟）；
回归：每次修改模板库后自动跑一批“黄金用例”。

13. 运维与监控

暴露健康检查与关键指标（查询耗时、并发数、失败率、缓存命中率）到 Prometheus；
热更新：DB 模板变更 → 发送 MQ（Artemis）→ 节点刷新本地缓存；
失败熔断：同一用户在短时内多次慢查询/失败，临时降级其配额。

14. 可扩展方向

预聚合引擎：按配置定时物化（增量更新）；
维度字典服务：统一的字段目录/血缘追踪；
多数据源：在 baseSqlId 上绑定数据源路由；
报表版本化：配置与模板版本追踪，可回滚。

15. 落地清单（Checklist）

定义 PivotConfig 协议与前端模型
建立 TemplateRegistry（加载宏/模板/基础 SQL）
实现 SqlPipeline（包装/注入/校验）
渲染聚合模板并执行（QueryEngine）
前端设计器与表格组件
缓存、导出、审计与告警

有了以上骨架，你可以先从“单一基础 SQL + 2 个维度 + 2 个指标”开始最小可用版本（MVP），再迭代加入 TopN、钻取、同比/环比等能力。

16. 模板/基础SQL注册与热更新设计（结合 Artemis）

16.1 表结构（JPA 实体）

// 基础 SQL（白名单入口）
@Entity @Table(name="pivot_base_sql")
public class PivotBaseSql extends AbstractEntity {@Id @GeneratedValue private Long id;@Comment("唯一编码") @FormField(order=1) private String code; // 如 sales_order_detail@Lob @Comment("SQL 文本") @FormField(order=2, type="textarea") private String sqlText;@Comment("启用状态") private Boolean enabled = true;@Comment("允许的字段白名单(JSON)") @Lob private String fieldWhitelistJson; // ["order_id","order_date",...]@Comment("数据源标识") private String datasourceKey; // 多数据源路由@CreateByUser private Long createUserId;@Comment("版本号") private Long version;@Comment("最后修改时间") private Instant updatedAt;
}// 公共宏/模板
@Entity @Table(name="pivot_template")
public class PivotTemplate extends AbstractEntity {@Id @GeneratedValue private Long id;@Comment("类型: macro|aggregate|other") private String type;@Comment("名称") private String name; // pivot-macros.ftl, pivot-aggregate.ftl@Lob @Comment("模板内容") private String content;@Comment("启用") private Boolean enabled = true;private Instant updatedAt;
}