【Java】通用统计接口设计v1

我设计完感觉哪不对，这不变成间接的前端写SQL了……

不过客户要求的就是在前端能自定义各种统计，而且需要兼容所有的表格页面……

下面代码仅供参考。

目前实现了
1 sum
2 avg
3 count 支持选择是否去重
4 group by

请求参数

@Data
public class CountField {
    private String field; // 字段名
    private boolean distinct; // 是否去重
}

import java.util.List;
@Data
public class SummaryRequest {
    private String tableName; // 表名
    // 要求和的字段
    private List<String> sumField;
    // 要求平均的字段
    private List<String> avgField;
    // 要求计数的字段
    private List<CountField> countField;
    // 用于分组的字段
    private List<String> groupField;
}

响应参数

public class SummaryResult {
    private Map<String, Object> results; // 动态存储聚合结果
}

处理方法

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

@Component
public class CalculateUtils {

    @Autowired
    private CustomBaseMapper customBaseMapper;

    /**
     * 统计 - 支持求和、求平均值、计数
     * @param request
     * @return
     */
    public List<SummaryResult> calculateSummary(SummaryRequest request) {
        List<SummaryResult> results = new ArrayList<>();

        // 校验表名
        String tableName = request.getTableName();
        if (tableName == null || tableName.trim().isEmpty()) {
            throw new IllegalArgumentException("表名不能为空");
        }

        // 构建 SELECT 子句
        StringBuilder sqlNeed = new StringBuilder("SELECT ");
        boolean hasFields = false;

        // 动态拼接 SUM 字段
        List<String> sumFields = request.getSumField();
        if (sumFields != null && !sumFields.isEmpty()) {
            for (String field : sumFields) {
                if (hasFields) sqlNeed.append(", ");
                sqlNeed.append("SUM(").append(field).append(") AS ").append(field).append("_sum");
                hasFields = true;
            }
        }

        // 动态拼接 AVG 字段
        List<String> avgFields = request.getAvgField();
        if (avgFields != null && !avgFields.isEmpty()) {
            for (String field : avgFields) {
                if (hasFields) sqlNeed.append(", ");
                sqlNeed.append("AVG(").append(field).append(") AS ").append(field).append("_avg");
                hasFields = true;
            }
        }

        // 动态拼接 COUNT 字段
        List<CountField> countFields = request.getCountField();
        if (countFields != null && !countFields.isEmpty()) {
            for (CountField countField : countFields) {
                if (hasFields) sqlNeed.append(", ");
                if (countField.isDistinct()) {
                    sqlNeed.append("COUNT(DISTINCT ").append(countField.getField()).append(") AS ")
                            .append(countField.getField()).append("_count_distinct");
                } else {
                    sqlNeed.append("COUNT(").append(countField.getField()).append(") AS ")
                            .append(countField.getField()).append("_count");
                }
                hasFields = true;
            }
        }

        // 如果没有任何聚合字段，则抛出异常
        if (!hasFields) {
            throw new IllegalArgumentException("至少需要一个聚合字段（SUM、AVG 或 COUNT）");
        }

        // 动态拼接 GROUP BY 子句
        List<String> groupFields = request.getGroupField();
        if (groupFields != null && !groupFields.isEmpty()) {
            sqlNeed.append(", ").append(String.join(", ", groupFields)); // 将分组字段添加到 SELECT 子句
            sqlNeed.append(" FROM ").append(tableName);
            sqlNeed.append(" GROUP BY ").append(String.join(", ", groupFields));
        } else {
            sqlNeed.append(" FROM ").append(tableName);
        }

        // 构建完整的 SQL
        // String sql = sqlNeed.append(" FROM ").append(tableName).toString();
        String sql = sqlNeed.toString();

        System.out.println("Generated SQL: " + sql);

        // 使用 MyBatis-Plus 执行原生 SQL 查询。正常情况只有一条数据。List是为了兼容后期添加Group等条件查询
        List<SummaryResult> result = customBaseMapper.selectByNativeSql(sql);
        results.addAll(result);
        return results;
    }
}

SQL

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface CustomBaseMapper<T> extends BaseMapper<T> {
    @Select("${sql}")
    List<Map<String, Object>> selectByNativeSql(String sql);
}

接口

@RestController
@RequestMapping("/erp/common")
public class ErpCommonController {
    @Autowired
    private CalculateUtils calculateUtils;
    @ApiOperation("统计数据")
    @RequestMapping(value = "/calc", method = RequestMethod.POST)
    public Result calcData(@RequestBody SummaryRequest summaryRequest){
        return Result.ok(calculateUtils.calculateSummary(summaryRequest));
    }
}

前端

 mounted() {
    // 等待 DOM 更新完成后执行 reCalcData
    this.$nextTick(() => {
      this.reCalcData();
    });
  },
  methods: {
    reCalcData() {
      calcData({
        tableName: "ts_time_card",
        sumField: ["cost", "std_hours", "ot_hours", "rate_std_hours"],
        avgField: ["cost", "std_hours", "ot_hours", "rate_std_hours"],
        countField: [{ field: "user_id", distinct: true }, { field: "activity_id", distinct: true }]
        // groupField: ["project_id"]
      }).then(res => {
        if (res.success) {
          this.statistics.totalCost = res.result[0]["cost_sum"];
          this.statistics.totalStdHours = res.result[0]["rate_std_hours_sum"];
          this.statistics.totalUser = res.result[0]["user_id_count_distinct"];
          this.statistics.totalActivity = res.result[0]["activity_id_count_distinct"];
        }
      });
    },
}

em

const calcData = params => axios({
  url: "/erp/common/calc",
  method: "post",
  data: params
});

效果