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

【Java】通用统计接口设计v1
https://www.cnblogs.com/zwj/p/18818674/java-sql-calc-utils-v1

对比V1，V2的改变，主要是：

将 SQL 动态拼接逻辑从 Java 代码中移至 MyBatis XML 文件中，提高了代码的可读性和可维护性。
充分利用了 MyBatis 的动态 SQL 功能（如 和 标签）。
使 CalculateUtils 更加简洁，专注于业务逻辑。

请求参数

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

逻辑代码

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

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

@Component
public class CalculateUtils {

    @Autowired
    private CustomBaseMapper customBaseMapper;

    /**
     * 统计 - 支持求和、求平均值、计数
     * @param request 请求参数
     * @return 统计结果
     */
    public List<SummaryResult> calculateSummary(SummaryRequest request) {
        // 校验表名
        String tableName = request.getTableName();
        if (tableName == null || tableName.trim().isEmpty()) {
            throw new IllegalArgumentException("表名不能为空");
        }

        // 校验是否有聚合字段
        if ((request.getSumField() == null || request.getSumField().isEmpty()) &&
                (request.getAvgField() == null || request.getAvgField().isEmpty()) &&
                (request.getCountField() == null || request.getCountField().isEmpty())) {
            throw new IllegalArgumentException("至少需要一个聚合字段（SUM、AVG 或 COUNT）");
        }

        // 调用 MyBatis 动态 SQL 查询
        List<Map<String, Object>> resultMapList = customBaseMapper.dynamicSummaryQuery(
                tableName,
                request.getSumField(),
                request.getAvgField(),
                request.getCountField(),
                request.getGroupField()
        );

        // 将结果封装为 SummaryResult 列表
        List<SummaryResult> results = new ArrayList<>();
        for (Map<String, Object> resultMap : resultMapList) {
            SummaryResult result = new SummaryResult();
            result.setResults(resultMap);
            results.add(result);
        }

        return results;
    }
}

mapper

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

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

public interface CustomBaseMapper<T> extends BaseMapper<T> {
    /**
     * 动态 SQL 查询
     * @param tableName 表名
     * @param sumFields 求和字段列表
     * @param avgFields 求平均值字段列表
     * @param countFields 计数字段列表
     * @param groupFields 分组字段列表
     * @return 查询结果
     */
    List<Map<String, Object>> dynamicSummaryQuery(
            @Param("tableName") String tableName,
            @Param("sumFields") List<String> sumFields,
            @Param("avgFields") List<String> avgFields,
            @Param("countFields") List<CountField> countFields,
            @Param("groupFields") List<String> groupFields
    );

    // 定义 CountField 类用于传递计数字段及其去重标志
    class CountField {
        private String field;
        private boolean distinct;

        public CountField(String field, boolean distinct) {
            this.field = field;
            this.distinct = distinct;
        }

        public String getField() {
            return field;
        }

        public boolean isDistinct() {
            return distinct;
        }
    }
}

xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.保密哦.erp.mapper.CustomBaseMapper">

    <select id="dynamicSummaryQuery" resultType="map">
        SELECT
        <trim suffixOverrides=",">
            <!-- SUM 字段 -->
            <foreach collection="sumFields" item="field" separator=",">
                SUM(${field}) AS ${field}_sum
            </foreach>

            <!-- AVG 字段 -->
            <if test="avgFields != null and avgFields.size() > 0">
                ,
                <foreach collection="avgFields" item="field" separator=",">
                    AVG(${field}) AS ${field}_avg
                </foreach>
            </if>

            <!-- COUNT 字段 -->
            <if test="countFields != null and countFields.size() > 0">
                ,
                <foreach collection="countFields" item="countField" separator=",">
                    <if test="countField.distinct">
                        COUNT(DISTINCT ${countField.field}) AS ${countField.field}_count_distinct
                    </if>
                    <if test="!countField.distinct">
                        COUNT(${countField.field}) AS ${countField.field}_count
                    </if>
                </foreach>
            </if>

            <!-- GROUP BY 字段 -->
            <if test="groupFields != null and groupFields.size() > 0">
                ,
                ${@java.lang.String@join(', ', groupFields)}
            </if>
        </trim>
        FROM ${tableName}
        <where>
            <!-- 可以在此处添加 WHERE 条件 -->
        </where>
        <if test="groupFields != null and groupFields.size() > 0">
            GROUP BY ${@java.lang.String@join(', ', groupFields)}
        </if>
    </select>
</mapper>

响应

import lombok.Data;

import java.util.Map;

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

前端

主要是返回值需要注意一下，怎么去取，其他的都不是什么问题

  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 }]
      }).then(res => {
        if (res.success) {
          this.statistics.totalCost = res.result[0]["results"]["cost_sum"];
          this.statistics.totalStdHours = res.result[0]["results"]["rate_std_hours_sum"];
          this.statistics.totalUser = res.result[0]["results"]["user_id_count_distinct"];
          this.statistics.totalActivity = res.result[0]["results"]["activity_id_count_distinct"];
        }
      });
    },