Jsqlparser + Freemarker + Vue3 數據透視報表設計方案

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;
}