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（基础明细表/视图）

关键思想：

1. 任意 SQL → 子查询：把用户/模板提供的基础 SQL 用 JSQLParser 包装成 SELECT ... FROM ( <base_sql> ) t，统一在外层做维度聚合与筛选。

2. 模板只关心“表达式”：维度/指标的 SQL 片段通过宏/模板生成，避免手写大量 if/else 拼接。

3. 强安全：白名单列/表校验、参数化绑定、限时/限量、敏感字段脱敏、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 统一变为可注入条件的子查询，并做安全校验。

关键步骤：

1. 解析：Statement stmt = CCJSqlParserUtil.parse(baseSql);

2. 规整：移除外层 ORDER BY / LIMIT（由外层模板控制）；

3. 追加 WHERE：把 filters.where 中的条件以 AND 方式追加到内层查询；

4. 包装：SELECT * FROM ( <normalized_sql> ) t；

5. 白名单校验：检查涉及的表/列是否在白名单；

6. 参数绑定：使用 NamedParameterJdbcTemplate 执行，避免字符串拼接。

示例工具类（片段）：

public class SqlPipeline {
  public String wrapAsSubquery(String baseSql) {
    Select select = (Select) CCJSqlParserUtil.parse(baseSql);
    // 1) 清理外层 ORDER BY/LIMIT
    select.getSelectBody().accept(new OrderByAndLimitCleaner());

    // 2) 生成包装 SQL
    String 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）

SELECT
  order_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（简化展示）

SELECT
  date_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, amount
  FROM sales_order_detail
  WHERE 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. 性能与可用性

1. 索引：维度列、时间列、常用过滤列建立联合/覆盖索引；

2. 预聚合：

   • 定时生成日/周/月粒度物化表；

   • 热点 TopN 结果放入 Redis，配置 ttl 和“参数签名”作为 Key；

3. 分页策略：聚合后分页（LIMIT/OFFSET）；统计总条数使用二次查询：

   SELECT COUNT(1) FROM (
     -- 把上面 GROUP BY 的 SELECT 去掉 ORDER BY/LIMIT
   ) x
   

4. 大结果导出：使用 fetchSize + ResultSet.TYPE_FORWARD_ONLY 流式写 CSV；

5. 并发：常见报表使用“参数签名”做结果缓存（如 60s）；

6. 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) gmv
  FROM (
    ${baseSqlWithWhere}
  ) t
  GROUP 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 = true
    try{
      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-pagination
      v-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;
}

常见坑与规避

• 内层对时间列做函数导致走不到索引 → 把函数放到外层显示，内层只做区间过滤；

• 维度别名与保留字冲突（如 date、group）→ 统一反引号包裹；

• COUNT(*) 与 COUNT(col) 语义差异；

• ORDER BY 字段不在 SELECT 中的 MySQL 行为差异；

• 导出超大文件内存膨胀 → 必须流式 + 临时文件。