构建一个安全且带有Trace的MyBatis-Plus通用查询模块

0. 前言#

其实在有LLM的时代，做一个通用查询工具模块似乎比较多余。

出于对代码掌控以及编写方式的一些个人的要求，我将查询分为两类，一类是比较简单的单表以及复杂度不高的多表查询，另一类是需要用到.xml的复杂查询。

前者我觉得如果能遵循某一类范式作为Context，无论是复用者还是LLM，都能很好地遵循一个规则，在这个规则下写出更好审查以及追踪的代码，这就是使用通用查询工具的意义。

而后者本身在业务中占比不多，需要用到的时候肯定也会充分设计，在此基础上可以零星地用到通用查询的工具（比如说统计与传入字段约束），核心功能交由.xml去实现，我觉得是比较合理的实现。

注：本文不考虑纯粹的SQL拼接，完全依靠安全的MyBatis-Plus的QueryWrapper拼接。

1. 拟定通用查询数据结构：`BasePageRequest`#

一般而言，前端想要做一个查询，无非就是：

排序方式
字段+条件+查询值
页码
是否分页
分页大小

出于安全角度考虑，我们还得想到：

最大分页大小限制
最大排序字段限制（避免过于复杂的查询）

对于这种通用的查询数据结构，字段+条件+查询值必然需要声明约束，这个约束应该放在哪先不管，不过很明显我们可以定义一个泛型先顶着，于是便有了下面的BasePageRequest类：

1
@Data
2
public class BasePageRequest<T> {
3
    public static final int MAX_ORDERS = 5;
4
    public static final int MAX_UNPAGED_SIZE = 1000;
5
    // 枚举化排序，避免输错
6
    public enum OrderDirection {
7
        ASC,
8
        DESC,
9
        NATURAL;
10

11
        public static OrderDirection from(String value) {
12
            if (value == null || value.isBlank()) {
60 collapsed lines
13
                return ASC;
14
            }
15
            return OrderDirection.valueOf(value.trim().toUpperCase());
16
        }
17
    }
18

19
    @Data
20
    public static class BaseOrderItem {
21
        @Schema(description = "排序字段")
22
        @NotBlank(message = "排序字段column不能为空")
23
        private String column;
24

25
        @Schema(description = "排序方式", example = "ASC/DESC/NATURAL")
26
        @NotNull(message = "排序方式direction不能为空")
27
        private OrderDirection direction = OrderDirection.ASC;
28

29
        public BaseOrderItem(String column, OrderDirection direction) {
30
            this.column = column;
31
            this.direction = direction;
32
        }
33

34
        public static BaseOrderItem asc(String column) {
35
            return new BaseOrderItem(column, OrderDirection.ASC);
36
        }
37

38
        public static BaseOrderItem desc(String column) {
39
            return new BaseOrderItem(column, OrderDirection.DESC);
40
        }
41

42
        public void setDirection(String direction) {
43
            this.direction = OrderDirection.from(direction);
44
        }
45
    }
46

47
    @Schema(description = "当前页码", example = "1~n | -1(不分页)")
48
    @Min(value = -1, message = "当前页码page必须>=-1")
49
    @NotNull(message = "当前页码page不能为空")
50
    private Integer page = 1;
51

52
    @Schema(description = "是否不分页（优先级高于 page=-1）")
53
    private Boolean noPage = false;
54

55
    @Schema(description = "分页大小", example = "10")
56
    @Min(value = 1, message = "每页最少1条")
57
    @Max(value = 100, message = "每页最多100条")
58
    @NotNull(message = "当前分页大小size不能为空")
59
    private Integer size = 10;
60

61
    @Schema(description = "排序规则列表")
62
    @Valid
63
    @Size(max = MAX_ORDERS, message = "排序字段最多支持5个")
64
    private List<BaseOrderItem> orders;
65

66
    @Schema(description = "动态查询条件")
67
    @Valid
68
    private T query;
69

70
    public boolean isUnpaged() {
71
        if (Boolean.TRUE.equals(noPage)) {
72
            return true;
73
        }
74
        return page != null && page < 0;
75
    }
76
}

2. 拟定数据库查询条件枚举类：`FilterOp`#

这个倒是没什么好说的，主要就是统一管理+收敛所有查询条件为枚举类。

1
public enum FilterOp {
2
    EQ,
3
    NE,
4
    GT,
5
    GE,
6
    LT,
7
    LE,
8
    LIKE,
9
    NOT_LIKE,
10
    LIKE_LEFT,
11
    LIKE_RIGHT,
12
    NOT_LIKE_LEFT,
13
    NOT_LIKE_RIGHT,
14
    BETWEEN,
15
    NOT_BETWEEN,
16
    IN,
17
    NOT_IN,
18
    IS_NULL,
19
    IS_NOT_NULL
20
}

3. BasePageRequest中泛型的基本数据结构：`FilterCondition`#

也就是字段+条件+查询值，一个最小可用的查询atomic结构，通用于所有查询字段的同时也能跟最终的映射解耦。

出于约束角度考虑，字段与查询条件都不能为空，但值的话必须可以为空，因为有IS_NULL，IS_NOT_NULL之类的无值字段查询。

1
@Data
2
public class FilterCondition {
3
    @NotBlank(message = "查询字段不能为空")
4
    private String field;
5
    @NotNull(message = "查询条件不能为空")
6
    private FilterOp op;
7
    private Object value;
8
}

4. 树状查询表达式：AND/OR分组（`QueryLogic`枚举与`QueryGroup`）#

满足 (A OR B) AND (C OR D)这种复杂查询。

当然，也就到此为止，再复杂一些的直接扔给.xml去解决，不要浪费力气在通用结构上，容易陷入过度抽象的陷阱。

QueryLogic#

1
public enum QueryLogic {
2
    AND,
3
    OR
4
}

QueryGroup#

其实这个filters与group限制在20与10其实只是遵循JSR-303 约束，按照自己业务情况来吧。

1
@Data
2
public class QueryGroup {
3
    public static final int MAX_GROUPS = 10;
4

5
    private QueryLogic logic = QueryLogic.AND;
6

7
    @Valid
8
    @Size(max = QueryFilters.MAX_FILTERS, message = "过滤条件最多支持20个")
9
    private List<FilterCondition> filters;
10

11
    @Valid
12
    @Size(max = MAX_GROUPS, message = "分组最多支持10个")
13
    private List<QueryGroup> groups;
14
}

5. 查询请求入口：`QueryFilters`#

其实它本质上跟QueryGroup是一个东西，但掌管的领域不同，QueryGroup是给树状结构查询定义的，而这个类是作为入口直接跟前端接触的类，这个在后面其它类做处理的时候会有所体现。

为什么 QueryFilters 与 QueryGroup 分开

QueryFilters 是请求入口：其承载“简单列表”和“树状分组”，并约束 filters/group 互斥。
QueryGroup 是递归节点：只负责 logic + filters + groups，用于表达 (A OR B) AND (C OR D) 之类复杂结构。的

分离的好处：简单查询不需要树结构，复杂查询才进入递归，避免所有请求都背负树模型成本。

1
@Data
2
public class QueryFilters {
3
    public static final int MAX_FILTERS = 20;
4

5
    @Valid
6
    @Size(max = MAX_FILTERS, message = "过滤条件最多支持20个")
7
    private List<FilterCondition> filters;
8

9
    @Valid
10
    private QueryGroup group;
11
}

6. 声明约束：`QueryFieldSpec`#

定义：

某个字段允许的操作符
列名映射（多表查询时需要将某个字段映射成表名alias.字段这样的格式）
自动模糊（SmartLike=%value%)
值转换器（比如说，前端传timestamp需要转成OffsetDateTime落库，当然，这个肯定会做通用处理，只是说是这么种用法）

1
public class QueryFieldSpec {
2
    private final String column;
3
    private final EnumSet<FilterOp> allowedOps;
4
    private final boolean autoLike;
5
    private final Function<Object, Object> valueConverter;
6

7
    public QueryFieldSpec(String column,
8
                          EnumSet<FilterOp> allowedOps,
9
                          boolean autoLike,
10
                          Function<Object, Object> valueConverter) {
11
        this.column = Objects.requireNonNull(column, "column");
12
        this.allowedOps = Objects.requireNonNull(allowedOps, "allowedOps");
41 collapsed lines
13
        this.autoLike = autoLike;
14
        this.valueConverter = valueConverter;
15
    }
16

17
    public QueryFieldSpec(String column, EnumSet<FilterOp> allowedOps, boolean autoLike) {
18
        this(column, allowedOps, autoLike, null);
19
    }
20

21
    public static QueryFieldSpec of(String column, EnumSet<FilterOp> allowedOps) {
22
        return new QueryFieldSpec(column, allowedOps, false);
23
    }
24

25
    public static QueryFieldSpec of(String column, EnumSet<FilterOp> allowedOps, boolean autoLike) {
26
        return new QueryFieldSpec(column, allowedOps, autoLike);
27
    }
28

29
    public static QueryFieldSpec of(String column,
30
                                    EnumSet<FilterOp> allowedOps,
31
                                    boolean autoLike,
32
                                    Function<Object, Object> valueConverter) {
33
        return new QueryFieldSpec(column, allowedOps, autoLike, valueConverter);
34
    }
35

36
    public static QueryFieldSpec of(String column,
37
                                    EnumSet<FilterOp> allowedOps,
38
                                    Function<Object, Object> valueConverter) {
39
        return new QueryFieldSpec(column, allowedOps, false, valueConverter);
40
    }
41

42
    public String getColumn() {
43
        return column;
44
    }
45

46
    public EnumSet<FilterOp> getAllowedOps() {
47
        return allowedOps;
48
    }
49

50
    public boolean isAutoLike() {
51
        return autoLike;
52
    }
53

54
    public Function<Object, Object> getValueConverter() {
55
        return valueConverter;
56
    }
57
}

7. 值通用转换: `QueryValueConverters`#

将前端 value 转为后端真实类型（时间、枚举、数字），就是前面说的值转换通用处理。

其实就是干脏活的。

1
public final class QueryValueConverters {
2
    private static final DateTimeFormatter DEFAULT_DATETIME_FORMATTER =
3
            DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
4

5
    private QueryValueConverters() {
6
    }
7

8
    public static Function<Object, Object> toLong() {
9
        return value -> {
10
            if (value instanceof Long) {
11
                return value;
12
            }
132 collapsed lines
13
            if (value instanceof Number number) {
14
                return number.longValue();
15
            }
16
            if (value instanceof String text) {
17
                String trimmed = text.trim();
18
                if (trimmed.isEmpty()) {
19
                    return null;
20
                }
21
                return Long.parseLong(trimmed);
22
            }
23
            throw new IllegalArgumentException("Unsupported value type for Long: " + value.getClass().getName());
24
        };
25
    }
26

27
    public static Function<Object, Object> toInteger() {
28
        return value -> {
29
            if (value instanceof Integer) {
30
                return value;
31
            }
32
            if (value instanceof Number number) {
33
                return number.intValue();
34
            }
35
            if (value instanceof String text) {
36
                String trimmed = text.trim();
37
                if (trimmed.isEmpty()) {
38
                    return null;
39
                }
40
                return Integer.parseInt(trimmed);
41
            }
42
            throw new IllegalArgumentException("Unsupported value type for Integer: " + value.getClass().getName());
43
        };
44
    }
45

46
    public static Function<Object, Object> toBigDecimal() {
47
        return value -> {
48
            if (value instanceof BigDecimal) {
49
                return value;
50
            }
51
            if (value instanceof Number number) {
52
                return BigDecimal.valueOf(number.doubleValue());
53
            }
54
            if (value instanceof String text) {
55
                String trimmed = text.trim();
56
                if (trimmed.isEmpty()) {
57
                    return null;
58
                }
59
                return new BigDecimal(trimmed);
60
            }
61
            throw new IllegalArgumentException("Unsupported value type for BigDecimal: " + value.getClass().getName());
62
        };
63
    }
64

65
    public static Function<Object, Object> toBoolean() {
66
        return value -> {
67
            if (value instanceof Boolean) {
68
                return value;
69
            }
70
            if (value instanceof String text) {
71
                String trimmed = text.trim();
72
                if (trimmed.isEmpty()) {
73
                    return null;
74
                }
75
                return Boolean.parseBoolean(trimmed);
76
            }
77
            throw new IllegalArgumentException("Unsupported value type for Boolean: " + value.getClass().getName());
78
        };
79
    }
80

81
    public static Function<Object, Object> toOffsetDateTime() {
82
        return value -> {
83
            if (value instanceof OffsetDateTime) {
84
                return value;
85
            }
86
            if (value instanceof LocalDateTime localDateTime) {
87
                return localDateTime.atZone(ZoneId.systemDefault()).toOffsetDateTime();
88
            }
89
            if (value instanceof Number number) {
90
                return OffsetDateTime.ofInstant(Instant.ofEpochMilli(number.longValue()), ZoneId.systemDefault());
91
            }
92
            if (value instanceof String text) {
93
                String trimmed = text.trim();
94
                if (trimmed.isEmpty()) {
95
                    return null;
96
                }
97
                if (trimmed.chars().allMatch(Character::isDigit)) {
98
                    long timestamp = Long.parseLong(trimmed);
99
                    return OffsetDateTime.ofInstant(Instant.ofEpochMilli(timestamp), ZoneId.systemDefault());
100
                }
101
                try {
102
                    LocalDateTime ldt = LocalDateTime.parse(trimmed, DEFAULT_DATETIME_FORMATTER);
103
                    return ldt.atZone(ZoneId.systemDefault()).toOffsetDateTime();
104
                } catch (DateTimeParseException ex) {
105
                    return OffsetDateTime.parse(trimmed);
106
                }
107
            }
108
            throw new IllegalArgumentException("Unsupported value type for OffsetDateTime: " + value.getClass().getName());
109
        };
110
    }
111

112
    public static <E extends Enum<E>> Function<Object, Object> enumByName(Class<E> enumType) {
113
        Objects.requireNonNull(enumType, "enumType");
114
        return value -> {
115
            if (value == null) {
116
                return null;
117
            }
118
            if (enumType.isInstance(value)) {
119
                return value;
120
            }
121
            if (value instanceof String text) {
122
                String trimmed = text.trim();
123
                if (trimmed.isEmpty()) {
124
                    return null;
125
                }
126
                return Enum.valueOf(enumType, trimmed);
127
            }
128
            throw new IllegalArgumentException("Unsupported value type for enum: " + value.getClass().getName());
129
        };
130
    }
131

132
    public static <E> Function<Object, Object> enumByResolver(Function<String, E> resolver) {
133
        Objects.requireNonNull(resolver, "resolver");
134
        return value -> {
135
            if (value == null) {
136
                return null;
137
            }
138
            if (value instanceof String text) {
139
                String trimmed = text.trim();
140
                if (trimmed.isEmpty()) {
141
                    return null;
142
                }
143
                return resolver.apply(trimmed);
144
            }
145
            throw new IllegalArgumentException("Unsupported value type for enum resolver: " + value.getClass().getName());
146
        };
147
    }
148
}

8. 查询条件应用拼接：`QueryFilterApplier`#

既然有了约束，就会有校验，校验完了后就要应用到QueryWrapper上。

这个类主要实现以下功能：

校验字段是否在白名单内。
between/in/like这种并非单值的情况处理。
普通单值条件处理
group条件处理
使用QueryWrapper拼接查询条件

在这里写可以做到查询逻辑集中化，避免各个业务重复写拼装的逻辑。

然后呢，则是可以做一个统一的异常处理与拒绝条件。

对于group的处理很简单

nested：保证分组语义。例如 (A AND B) OR (C AND D)，必须用 nested 包住子组。

and/or：用于连接已有条件与新条件的关系。如果在第一个子组上直接用 and，可能丢失括号导致语义错误。

当前实现：首子组用 nested，后续子组根据 logic 使用 and/or，保证 OR 分组不被展开。

1
public final class QueryFilterApplier {
2
    private QueryFilterApplier() {}
3

4
    public static <T, W extends AbstractWrapper<T, String, W>> void apply(W wrapper,
5
                                                                         List<FilterCondition> filters,
6
                                                                         Map<String, QueryFieldSpec> fieldSpecs) {
7
        if (filters == null || filters.isEmpty()) {
8
            return;
9
        }
10
        if (filters.size() > QueryFilters.MAX_FILTERS) {
11
            throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "filters.size");
12
        }
201 collapsed lines
13
        for (FilterCondition filter : filters) {
14
            if (filter == null || filter.getField() == null || filter.getOp() == null) {
15
                continue;
16
            }
17
            QueryFieldSpec spec = fieldSpecs.get(filter.getField());
18
            if (spec == null) {
19
                throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_NOT_SUPPORT, filter.getField());
20
            }
21
            if (!spec.getAllowedOps().contains(filter.getOp())) {
22
                throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_NOT_SUPPORT, filter.getOp());
23
            }
24
            applySingle(wrapper, spec, filter);
25
        }
26
    }
27

28
    public static <T, W extends AbstractWrapper<T, String, W>> void apply(W wrapper,
29
                                                                         QueryFilters query,
30
                                                                         Map<String, QueryFieldSpec> fieldSpecs) {
31
        if (query == null) {
32
            return;
33
        }
34
        if (query.getGroup() != null && query.getFilters() != null && !query.getFilters().isEmpty()) {
35
            throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "filters.group.conflict");
36
        }
37
        if (query.getGroup() != null) {
38
            applyGroup(wrapper, query.getGroup(), fieldSpecs);
39
            return;
40
        }
41
        apply(wrapper, query.getFilters(), fieldSpecs);
42
    }
43

44
    private static <T, W extends AbstractWrapper<T, String, W>> void applySingle(W wrapper, QueryFieldSpec spec, FilterCondition filter) {
45
        Object value = normalizeValue(spec, filter.getOp(), filter.getValue());
46
        FilterOp op = filter.getOp();
47
        String column = spec.getColumn();
48

49
        if (value == null && op != FilterOp.IS_NULL && op != FilterOp.IS_NOT_NULL) {
50
            if (op == FilterOp.IN) {
51
                throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "in.empty");
52
            }
53
            if (op == FilterOp.NOT_IN) {
54
                throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "notIn.empty");
55
            }
56
            return;
57
        }
58

59
        switch (op) {
60
            case EQ -> wrapper.eq(column, value);
61
            case NE -> wrapper.ne(column, value);
62
            case GT -> wrapper.gt(column, value);
63
            case GE -> wrapper.ge(column, value);
64
            case LT -> wrapper.lt(column, value);
65
            case LE -> wrapper.le(column, value);
66
            case LIKE -> wrapper.like(column, normalizeLikeValue(spec, op, value));
67
            case NOT_LIKE -> wrapper.notLike(column, normalizeLikeValue(spec, op, value));
68
            case LIKE_LEFT -> wrapper.likeLeft(column, value);
69
            case LIKE_RIGHT -> wrapper.likeRight(column, value);
70
            case NOT_LIKE_LEFT -> wrapper.notLikeLeft(column, value);
71
            case NOT_LIKE_RIGHT -> wrapper.notLikeRight(column, value);
72
            case BETWEEN -> {
73
                List<?> values = normalizeToList(value);
74
                if (values == null || values.size() != 2) {
75
                    throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "between");
76
                }
77
                wrapper.between(column, values.get(0), values.get(1));
78
            }
79
            case NOT_BETWEEN -> {
80
                List<?> values = normalizeToList(value);
81
                if (values == null || values.size() != 2) {
82
                    throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "notBetween");
83
                }
84
                wrapper.notBetween(column, values.get(0), values.get(1));
85
            }
86
            case IN -> {
87
                List<?> values = normalizeToList(value);
88
                if (values == null || values.isEmpty()) {
89
                    throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "in.empty");
90
                }
91
                wrapper.in(column, values);
92
            }
93
            case NOT_IN -> {
94
                List<?> values = normalizeToList(value);
95
                if (values == null || values.isEmpty()) {
96
                    throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "notIn.empty");
97
                }
98
                wrapper.notIn(column, values);
99
            }
100
            case IS_NULL -> wrapper.isNull(column);
101
            case IS_NOT_NULL -> wrapper.isNotNull(column);
102
        }
103
    }
104

105
    private static <T, W extends AbstractWrapper<T, String, W>> void applyGroup(W wrapper,
106
                                                                               QueryGroup group,
107
                                                                               Map<String, QueryFieldSpec> fieldSpecs) {
108
        if (group == null) {
109
            return;
110
        }
111
        QueryLogic logic = group.getLogic() == null ? QueryLogic.AND : group.getLogic();
112
        List<FilterCondition> filters = group.getFilters();
113
        if (filters != null && filters.size() > QueryFilters.MAX_FILTERS) {
114
            throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "filters.size");
115
        }
116
        int clauses = 0;
117
        if (filters != null) {
118
            for (FilterCondition filter : filters) {
119
                if (filter == null || filter.getField() == null || filter.getOp() == null) {
120
                    continue;
121
                }
122
                QueryFieldSpec spec = fieldSpecs.get(filter.getField());
123
                if (spec == null) {
124
                    throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_NOT_SUPPORT, filter.getField());
125
                }
126
                if (!spec.getAllowedOps().contains(filter.getOp())) {
127
                    throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_NOT_SUPPORT, filter.getOp());
128
                }
129
                if (logic == QueryLogic.OR && clauses > 0) {
130
                    wrapper.or();
131
                }
132
                applySingle(wrapper, spec, filter);
133
                clauses++;
134
            }
135
        }
136

137
        List<QueryGroup> groups = group.getGroups();
138
        if (groups != null && groups.size() > QueryGroup.MAX_GROUPS) {
139
            throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "groups.size");
140
        }
141
        if (groups != null) {
142
            for (QueryGroup child : groups) {
143
                if (child == null) {
144
                    continue;
145
                }
146
                if (clauses == 0) {
147
                    wrapper.nested(w -> applyGroup(w, child, fieldSpecs));
148
                } else if (logic == QueryLogic.OR) {
149
                    wrapper.or(w -> applyGroup(w, child, fieldSpecs));
150
                } else {
151
                    wrapper.and(w -> applyGroup(w, child, fieldSpecs));
152
                }
153
                clauses++;
154
            }
155
        }
156
    }
157

158
    private static Object normalizeValue(QueryFieldSpec spec, FilterOp op, Object value) {
159
        if (value == null) {
160
            return null;
161
        }
162
        Function<Object, Object> converter = spec.getValueConverter();
163
        if (converter == null) {
164
            return value;
165
        }
166
        try {
167
            if (op == FilterOp.IN || op == FilterOp.NOT_IN
168
                    || op == FilterOp.BETWEEN || op == FilterOp.NOT_BETWEEN) {
169
                List<?> values = normalizeToList(value);
170
                if (values == null) {
171
                    return value;
172
                }
173
                List<Object> converted = new ArrayList<>(values.size());
174
                for (Object item : values) {
175
                    converted.add(converter.apply(item));
176
                }
177
                return converted;
178
            }
179
            return converter.apply(value);
180
        } catch (RuntimeException ex) {
181
            throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "value.convert");
182
        }
183
    }
184

185
    private static Object normalizeLikeValue(QueryFieldSpec spec, FilterOp op, Object value) {
186
        if (!spec.isAutoLike() || !(value instanceof String)) {
187
            return value;
188
        }
189
        if (op != FilterOp.LIKE && op != FilterOp.NOT_LIKE) {
190
            return value;
191
        }
192
        String raw = (String) value;
193
        return "%" + raw + "%";
194
    }
195

196
    private static List<?> normalizeToList(Object value) {
197
        if (value == null) {
198
            return null;
199
        }
200
        if (value instanceof List<?> list) {
201
            return list;
202
        }
203
        if (value instanceof Collection<?> collection) {
204
            return List.copyOf(collection);
205
        }
206
        if (value.getClass().isArray()) {
207
            int length = Array.getLength(value);
208
            Object[] array = new Object[length];
209
            for (int i = 0; i < length; i++) {
210
                array[i] = Array.get(value, i);
211
            }
212
            return List.of(array);
213
        }
214
        return null;
215
    }
216
}

9. Query模块Exception: `QueryException`#

前面抛出了这么个异常但没定义，这里补上，其实就是一个常规的异常而已。

1
@Getter
2
public class QueryException extends RuntimeException {
3
    private final int code;
4
    private final Object data;
5

6
    public QueryException(ResponseCode responseCode, Object data) {
7
        super(responseCode.getMessage());
8
        this.code = responseCode.getCode();
9
        this.data = data;
10
    }
11

12
    public QueryException(ResponseCode responseCode, String message, Object data) {
13
        super(message);
14
        this.code = responseCode.getCode();
15
        this.data = data;
16
    }
17
}

10. 分页排序与构建: `QueryPageBuilder`#

虽然前面的入口做了点处理，但这边再做一次也没什么。

主要做分页边界控制、orders数量、排序字段、排序逻辑做统一处理与校验。

1
public final class QueryPageBuilder {
2
    private QueryPageBuilder() {}
3

4
    public static <T> IPage<T> buildPage(BasePageRequest<?> request, Map<String, String> orderColumns) {
5
        List<BasePageRequest.BaseOrderItem> orders = request.getOrders();
6
        if (orders != null && orders.size() > BasePageRequest.MAX_ORDERS) {
7
            // orders数量约束
8
            throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "orders.size");
9
        }
10
        long size = resolveSize(request);
11
        long current = resolveCurrent(request);
12
        Page<T> page = new Page<>(current, size);
49 collapsed lines
13
        if (request.isUnpaged()) {
14
            page.setSearchCount(false);
15
        }
16
        if (orders == null || orders.isEmpty()) {
17
            return page;
18
        }
19
        for (BasePageRequest.BaseOrderItem order : orders) {
20
            BasePageRequest.OrderDirection direction = order.getDirection();
21
            if (direction == null) {
22
                throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "null");
23
            }
24
            if (direction == BasePageRequest.OrderDirection.NATURAL) {
25
                continue;
26
            }
27
            String column = resolveOrderColumn(orderColumns, order.getColumn());
28
            if (column == null) {
29
                throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_NOT_SUPPORT, order.getColumn());
30
            }
31
            switch (direction) {
32
                case ASC -> page.orders().add(OrderItem.asc(column));
33
                case DESC -> page.orders().add(OrderItem.desc(column));
34
                default -> throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, direction);
35
            }
36
        }
37
        return page;
38
    }
39
  // orders字段白名单排查
40
    private static String resolveOrderColumn(Map<String, String> orderColumns, String field) {
41
        if (field == null) {
42
            return null;
43
        }
44
        if (orderColumns == null || orderColumns.isEmpty()) {
45
            return field;
46
        }
47
        return orderColumns.get(field);
48
    }
49

50
    private static long resolveCurrent(BasePageRequest<?> request) {
51
        if (request.isUnpaged()) {
52
            return 1L;
53
        }
54
        Integer page = request.getPage();
55
        return page == null ? 1L : page;
56
    }
57
  // size约束，MAX_UNPAGED_SIZE为不分页时最大返回数量。
58
    private static long resolveSize(BasePageRequest<?> request) {
59
        if (request.isUnpaged()) {
60
            return BasePageRequest.MAX_UNPAGED_SIZE;
61
        }
62
        Integer size = request.getSize();
63
        return size == null ? 10L : size;
64
    }
65
}

11. Query模块异常追踪：`QueryTraceService/QueryTraceSupport`#

对查询条件数量、排序数量、不分页语义做指标与 tag。

做这么个东西，也是为了方便查询出问题的时候方便我们定位出异常源。

同时也方便对查询进行统计，对后续优化与审计做数据支持。

QueryTraceService#

1
package com.zwlh.powergrid.contract.common.query.unified;
2

3
import io.micrometer.tracing.Span;
4
import io.micrometer.tracing.Tracer;
5
import org.springframework.stereotype.Component;
6

7
@Component
8
public class QueryTraceService {
9

10
    private final Tracer tracer;
11
    private static final int MAX_COUNT_TAG = 5;
12

42 collapsed lines
13
    public QueryTraceService(Tracer tracer) {
14
        this.tracer = tracer;
15
    }
16

17
    public void tagFilters(int count) {
18
        Span span = tracer.currentSpan();
19
        if (span == null) {
20
            return;
21
        }
22
        span.tag("mp.filters.count", normalizeCount(count));
23
    }
24

25
    public void tagOrders(int count) {
26
        Span span = tracer.currentSpan();
27
        if (span == null) {
28
            return;
29
        }
30
        span.tag("mp.orders.count", normalizeCount(count));
31
    }
32

33
    public void tagUnpaged(boolean unpaged) {
34
        Span span = tracer.currentSpan();
35
        if (span == null) {
36
            return;
37
        }
38
        span.tag("mp.query.unpaged", String.valueOf(unpaged));
39
    }
40

41
    public void tagReject(String reason) {
42
        Span span = tracer.currentSpan();
43
        if (span == null) {
44
            return;
45
        }
46
        span.tag("mp.query.reject", reason == null ? "unknown" : reason);
47
    }
48

49
    private String normalizeCount(int count) {
50
        if (count < 0) {
51
            return "0";
52
        }
53
        if (count > MAX_COUNT_TAG) {
54
            return MAX_COUNT_TAG + "+";
55
        }
56
        return String.valueOf(count);
57
    }
58
}

QueryTraceSupport#

1
public final class QueryTraceSupport {
2
    private static final int DEFAULT_LIMIT = 8;
3

4
    private QueryTraceSupport() {
5
    }
6

7
    public static int count(Collection<?> items) {
8
        return items == null ? 0 : items.size();
9
    }
10

11
    public static String summarizeFilterOpssummarizeFilterFields(List<FilterCondition> filters) {
12
        return summarize(filters, FilterCondition::getField);
23 collapsed lines
13
    }
14

15
    public static String summarizeFilterOps(List<FilterCondition> filters) {
16
        return summarize(filters, filter -> filter.getOp() == null ? null : filter.getOp().name());
17
    }
18

19
    public static String summarizeOrderColumns(List<BasePageRequest.BaseOrderItem> orders) {
20
        return summarize(orders, BasePageRequest.BaseOrderItem::getColumn);
21
    }
22

23
    public static String summarizeOrderDirections(List<BasePageRequest.BaseOrderItem> orders) {
24
        return summarize(orders, order -> order.getDirection() == null ? null : order.getDirection().name());
25
    }
26

27
    private static <T> String summarize(List<T> items, Function<T, String> mapper) {
28
        if (items == null || items.isEmpty()) {
29
            return "";
30
        }
31
        return items.stream()
32
                .map(mapper)
33
                .filter(Objects::nonNull)
34
                .distinct()
35
                .limit(DEFAULT_LIMIT)
36
                .collect(Collectors.joining(","));
37
    }
38
}

12. Query模块错误管理: QueryExceptionHandler#

老规矩，每个模块自己有自己的exception handler，也避免跟别的模块杂糅在一起处理。

1
@RestControllerAdvice
2
@RequiredArgsConstructor
3
@Slf4j
4
public class QueryExceptionHandler {
5
    private final QueryTraceService queryTraceService;
6

7
    @ExceptionHandler(QueryException.class)
8
    public ResponseEntity<Result<?>> handleQueryException(QueryException ex) {
9
        queryTraceService.tagReject(String.valueOf(ex.getData()));
10
        log.warn("Query异常: code={}, msg={}, data={}", ex.getCode(), ex.getMessage(), ex.getData());
11
        return ResponseEntity.status(HttpStatus.OK.value())
12
                .body(Result.error(ex.getCode(), ex.getMessage(), ex.getData()));
13
    }
14
}

13. 使用示例#

Controller#

1
@PostMapping("/approval/process/page")
2
public IPage<ApprovalProcess> page(@RequestBody BasePageRequest<QueryFilters> req) {
3
    return approvalProcessService.page(req);
4
}

Repository#

先定义查询字段的白名单过滤字段以及排序字段。

1
private static final Map<String, QueryFieldSpec> PROCESS_FILTERS = Map.of(
2
    "processName", QueryFieldSpec.of("process_name", EnumSet.of(FilterOp.EQ, FilterOp.LIKE), true),
3
    "isActive", QueryFieldSpec.of("is_active", EnumSet.of(FilterOp.EQ), QueryValueConverters.toBoolean())
4
);
5

6
private static final Map<String, String> PROCESS_ORDERS = Map.of(
7
    "processName", "process_name",
8
    "createdTime", "created_time",
9
    "updatedTime", "updated_time",
10
    "isActive", "is_active"
11
);

当然，有人可能会想着把这些声明逻辑藏在某个DTO里面，直接在字段上面加上注解，我选择在Repository里面写主要是考虑到以下因素：

其实在哪里写都是要写的，放DTO里面的话，我们还要做个Aspect，也没有省多少功夫的同时还引入多一个AOP，并没有降低工作量。
同一个DTO可能会碰到以下场景：在A 接口允许模糊查，在 B 接口只允许精确查，难不成要为了一个字段又创建一个DTO？
Repository本来就是干脏累活的地方，而且现在有LLM了让它给你生成就是，跟生成糊糊代码本质的不同点就在于这坨糊糊的逻辑清晰，本质上只是声明式内容，比起你显式解析一堆入参还要校验来说，这点东西LLM几乎可以说几乎没有幻觉的同时，代码人类也完全看得懂。

不过这么写很容易出错，我们可以加个Lambda解析：

1
public final class MpColumnUtil {
2
    private MpColumnUtil() {
3
    }
4

5
    public static <T> String col(SFunction<T, ?> func) {
6
        return resolveColumn(func);
7
    }
8

9
    public static <T> String col(String alias, SFunction<T, ?> func) {
10
        if (alias == null || alias.isBlank()) {
11
            return resolveColumn(func);
12
        }
17 collapsed lines
13
        return alias + "." + resolveColumn(func);
14
    }
15

16
    private static <T> String resolveColumn(SFunction<T, ?> func) {
17
        if (func == null) {
18
            throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "lambda.null");
19
        }
20
        LambdaMeta meta = LambdaUtils.extract(func);
21
        String fieldName = PropertyNamer.methodToProperty(meta.getImplMethodName());
22
        Class<?> entityClass = meta.getInstantiatedClass();
23
        Map<String, ColumnCache> columnMap = LambdaUtils.getColumnMap(entityClass);
24
        if (columnMap == null) {
25
            throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "tableInfo.missing");
26
        }
27
        ColumnCache cache = columnMap.get(LambdaUtils.formatKey(fieldName));
28
        if (cache == null) {
29
            throw new QueryException(ResponseCode.SYSTEM_QUERY_CONDITION_ERROR, "column.missing");
30
        }
31
        return cache.getColumn();
32
    }
33
}

其实就是包个语法糖，调用方式变动了一下而已：

1
private static final Map<String, QueryFieldSpec> PROCESS_FILTERS = Map.of(
2
            "processName", QueryFieldSpec.of(MpColumnUtil.col(ApprovalProcess::getProcessName), EnumSet.of(FilterOp.EQ, FilterOp.LIKE), true),
3
            "isActive", QueryFieldSpec.of(MpColumnUtil.col(ApprovalProcess::getIsActive), EnumSet.of(FilterOp.EQ), QueryValueConverters.toBoolean())
4
    );
5

6
    private static final Map<String, String> PROCESS_ORDERS = Map.of(
7
            "processName", MpColumnUtil.col(ApprovalProcess::getProcessName),
8
            "createdTime", MpColumnUtil.col(ApprovalProcess::getCreatedTime),
9
            "updatedTime", MpColumnUtil.col(ApprovalProcess::getUpdatedTime),
10
            "isActive", MpColumnUtil.col(ApprovalProcess::getIsActive)
11
    );

再进一步的builder构建似乎就没什么必要了，有兴趣可以自己实现。

然后应用过滤与分页：

1
QueryWrapper<ApprovalProcess> wrapper = new QueryWrapper<>();
2
QueryFilterApplier.apply(wrapper, req.getQuery(), PROCESS_FILTERS);
3
IPage<ApprovalProcess> page = QueryPageBuilder.buildPage(req, PROCESS_ORDERS);
4
return approvalProcessMapper.selectPage(page, wrapper);

完整的Repository示例如下：

1
@Repository
2
@RequiredArgsConstructor
3
public class ApprovalProcessQueryRepositoryImpl implements ApprovalProcessQueryRepository {
4
    private static final Map<String, QueryFieldSpec> PROCESS_FILTERS = Map.of(
5
            "processName", QueryFieldSpec.of("process_name", EnumSet.of(FilterOp.EQ, FilterOp.LIKE), true),
6
            "isActive", QueryFieldSpec.of("is_active", EnumSet.of(FilterOp.EQ), QueryValueConverters.toBoolean())
7
    );
8

9
    private static final Map<String, String> PROCESS_ORDERS = Map.of(
10
            "processName", "process_name",
11
            "createdTime", "created_time",
12
            "updatedTime", "updated_time",
18 collapsed lines
13
            "isActive", "is_active"
14
    );
15

16
    private final ApprovalProcessMapper approvalProcessMapper;
17

18
    @Override
19
    public IPage<ApprovalProcess> findPage(BasePageRequest<QueryFilters> basePageRequest) {
20
        QueryWrapper<ApprovalProcess> queryWrapper = new QueryWrapper<>();
21
        List<FilterCondition> filters = null;
22
        if (basePageRequest.getQuery() != null) {
23
            filters = basePageRequest.getQuery().getFilters();
24
            QueryFilterApplier.apply(queryWrapper, basePageRequest.getQuery(), PROCESS_FILTERS);
25
        }
26
        IPage<ApprovalProcess> iPage = QueryPageBuilder.buildPage(basePageRequest, PROCESS_ORDERS);
27
        return approvalProcessMapper.selectPage(iPage, queryWrapper);
28
    }
29

30
    @Override
31
    public ApprovalProcess findByProcessName(String processName) {
32
        return approvalProcessMapper.selectOne(new QueryWrapper<ApprovalProcess>().eq("process_name", processName));
33
    }
34
}

Service#

1
  /**
2
     * 获取审批流程pages
3
     * @param basePageRequest page基本请求体
4
     * @return IPage<ApprovalProcess>
5
     */
6
    public IPage<ApprovalProcess> page(BasePageRequest<QueryFilters> basePageRequest) {
7
        List<FilterCondition> filters = null;
8
        if (basePageRequest.getQuery() != null) {
9
            filters = basePageRequest.getQuery().getFilters();
10
        }
11
        if (QueryTraceSupport.count(filters) > 0) {
12
            queryTraceService.tagFilters(
13
                    QueryTraceSupport.count(filters),
14
            );
15
        }
16
        List<BasePageRequest.BaseOrderItem> orders = basePageRequest.getOrders();
17
        if (QueryTraceSupport.count(orders) > 0) {
18
            queryTraceService.tagOrders(
19
                    QueryTraceSupport.count(orders)
20
            );
21
        }
22
        queryTraceService.tagUnpaged(basePageRequest.isUnpaged());
23
        return approvalProcessQueryRepository.findPage(basePageRequest);
24
    }

当然，每个Service层都要这么写查询追踪是非常繁琐以及没有意义的事情，我们来做个Aspect吧：

定义：

1
/**
2
 * Query 请求追踪标记
3
 * 用于统一记录查询过滤/排序/不分页等指标
4
 */
5
@Target({ElementType.METHOD, ElementType.TYPE})
6
@Retention(RetentionPolicy.RUNTIME)
7
@Documented
8
public @interface QueryTrace {
9
}

实现：

1
@Aspect
2
@Component
3
@RequiredArgsConstructor
4
public class QueryTraceAspect {
5
    private final QueryTraceService queryTraceService;
6

7
    @Before("@annotation(queryTrace)")
8
    public void trace(JoinPoint joinPoint, QueryTrace queryTrace) {
9
        BasePageRequest<?> request = findBasePageRequest(joinPoint.getArgs());
10
        if (request == null) {
11
            return;
12
        }
27 collapsed lines
13
        List<FilterCondition> filters = null;
14
        Object query = request.getQuery();
15
        if (query instanceof QueryFilters queryFilters) {
16
            filters = queryFilters.getFilters();
17
        }
18
        if (QueryTraceSupport.count(filters) > 0) {
19
            queryTraceService.tagFilters(
20
                    QueryTraceSupport.count(filters)
21
            );
22
        }
23
        List<BasePageRequest.BaseOrderItem> orders = request.getOrders();
24
        if (QueryTraceSupport.count(orders) > 0) {
25
            queryTraceService.tagOrders(
26
                    QueryTraceSupport.count(orders)
27
            );
28
        }
29
        queryTraceService.tagUnpaged(request.isUnpaged());
30
    }
31

32
    private BasePageRequest<?> findBasePageRequest(Object[] args) {
33
        if (args == null) {
34
            return null;
35
        }
36
        for (Object arg : args) {
37
            if (arg instanceof BasePageRequest<?> request) {
38
                return request;
39
            }
40
        }
41
        return null;
42
    }
43
}

直接在方法上面加上注解，这样Service层就没那么多干扰噪音：

1
@QueryTrace
2
    public IPage<ApprovalProcess> approvalProcessPage(BasePageRequest<ApprovalProcessPageDTO> basePageRequest) throws IllegalAccessException {
3
        return approvalProcessQueryRepository.findPage(basePageRequest);
4
    }

14. 引用MyBatis-Plus-Join插件+Alias别名映射解决多表查询问题#

以UserQueryRepositoryImpl为例：

注意！并非银弹，当查询复杂度超过一定程度后，请发挥MyBatis-Plus自身拥有的优势，在.xml中直接写SQL，不要本末倒置！

1
@Repository
2
@RequiredArgsConstructor
3
public class UserQueryRepositoryImpl implements UserQueryRepository {
4
    private static final Map<String, QueryFieldSpec> USER_FILTERS = Map.of(
5
            "name", QueryFieldSpec.of(MpColumnUtil.col("uai", UserAdditionalInfo::getName), EnumSet.of(FilterOp.EQ, FilterOp.LIKE), true),
6
            "phone", QueryFieldSpec.of(MpColumnUtil.col("u", User::getPhone), EnumSet.of(FilterOp.LIKE), true),
7
            "registerType", QueryFieldSpec.of(MpColumnUtil.col("uai", UserAdditionalInfo::getRegisterType), EnumSet.of(FilterOp.EQ), QueryValueConverters.toInteger()),
8
            "identity", QueryFieldSpec.of(MpColumnUtil.col("uai", UserAdditionalInfo::getIdentity), EnumSet.of(FilterOp.EQ, FilterOp.LIKE)),
9
            "discriminator", QueryFieldSpec.of(MpColumnUtil.col("uai", UserAdditionalInfo::getDiscriminator), EnumSet.of(FilterOp.EQ), QueryValueConverters.toInteger())
10
    );
11

12
    private static final Map<String, String> USER_ORDERS = Map.of(
13
            "id", MpColumnUtil.col("u", User::getId),
14
            "phone", MpColumnUtil.col("u", User::getPhone),
15
            "createdTime", MpColumnUtil.col("u", User::getCreatedTime),
16
            "name", MpColumnUtil.col("uai", UserAdditionalInfo::getName),
17
            "registerType", MpColumnUtil.col("uai", UserAdditionalInfo::getRegisterType),
18
            "discriminator", MpColumnUtil.col("uai", UserAdditionalInfo::getDiscriminator)
19
    );
20

21
    @Override
22
    public IPage<UserPageVo> findUserPage(BasePageRequest<UserPageDTO> basePageRequest) throws IllegalAccessException {
23
        MPJQueryWrapper<User> queryWrapper = JoinWrappers.query(User.class);
24
        QueryFilterApplier.apply(queryWrapper, basePageRequest.getQuery(), USER_FILTERS);
25
        IPage<UserPageVo> page = QueryPageBuilder.buildPage(basePageRequest, USER_ORDERS);
26
        return userMapper.getUsersPage(page, queryWrapper);
27
    }
28
}

15. 前端请求示例：#

1
{
2
  "page": 1,
3
  "size": 20,
4
  "orders": [{ "column": "createdTime", "direction": "DESC" }],
5
  "query": {
6
    "filters": [
7
      { "field": "processName", "op": "LIKE", "value": "合同" },
8
      { "field": "isActive", "op": "EQ", "value": true }
9
    ]
10
  }
11
}

16. 总结与注意事项#

流程：#

Controller 接收 BasePageRequest<QueryFilters>。
QueryFilterApplier 校验并写入 QueryWrapper。
QueryPageBuilder 生成 IPage（分页 + 排序）。
Mapper 执行 selectPage。
相对简单的多表查询采用MyBatis-Plus-Join插件+Alias别名映射方案。
Service层使用@QueryTrace注解收集信息。
若字段/查询条件非法，抛出 QueryException，由 QueryExceptionHandler 统一返回错误并打tag。

1
┌─────────────┐     ┌──────────────────┐     ┌─────────────────┐
2
│ Controller  │ ──▶ │ QueryFilterApplier│ ──▶ │ QueryWrapper    │
3
└─────────────┘     └──────────────────┘     └─────────────────┘
4
       │                                              │
5
       ▼                                              ▼
6
┌─────────────┐                              ┌─────────────────┐
7
│ QueryPage   │ ────────────────────────────▶│ Mapper.selectPage│
8
│ Builder     │                              └─────────────────┘
9
└─────────────┘

所有通用性方案都会有自己的边界，通用性方案解决的是通用性的事，不要试图让通用性方案继续去解决不通用的事。

一些缺失类的说明：#

`ResponseCode`:#

这个类其实就是一个普通的枚举类，没说明是因为每个人的项目都不一样，应该是一看就明白的东西吧…

1
@AllArgsConstructor
2
@Getter
3
public enum ResponseCode {
4
    SYSTEM_QUERY_CONDITION_ERROR(10004, "查询条件解析失败"),
5
    SYSTEM_QUERY_CONDITION_NOT_SUPPORT(10005,"不支持的查询条件"),
6
    ....
7
 }

`Result`:#

通用的返回给前端的数据解构：

1
@Data
2
public class Result<T> {
3
    private Integer code;
4
    private String message;
5
    private T data;
6
    ....
7
    public static <T> Result<T> error(Integer responseCode, String message, T data) {
8
       Result<T> result = new Result<>();
9
       result.setData(data);
10
       result.setCode(responseCode);
11
       result.setMessage(message);
12
       return result;
13
    }
14
}

查询成本概览：#

OR 与深层嵌套：会显著增加 SQL 复杂度与优化器成本，容易触发全表扫描或与预期不一样的执行方式。
模糊（LIKE '%x'/NOT LIKE '%x'）：大表基本无法走索引，应尽量限制字段或条件数量。
IN 大集合：集合太大会拖慢解析与执行，建议限制长度或落表再 join。
Join + count：分页时 count(*) 与 join 组合开销很大，noPage 场景已经通过阈值限制。
排序字段：尽量使用索引列作为默认排序，避免 join 后排序放大代价。
分页相关：通过 MAX_UNPAGED_SIZE 控制返回量，避免一次拉取全部数据。

不过我并没有定义MAX_GROUP_DEPTH，像模糊、IN大集合之类的也不好在代码层面上限制。

我觉得主要还是代码审查的问题，不管是新人还是老人，代码提交前，都应该给LLM，或者给公司内部专门做代码审查的LLM检查，如果可以的话，再集中做人工代码评审。

另外要区分导出与不分页的差别，这个工具本质上是一个查询工具而不是导出工具，导出工具应该自己去实现SQL，不要依赖这个工具，这也是一直强调的工具边界问题。

17. 单元测试#

`QueryFilterApplier`:#

1
class QueryFilterApplierTest {
2

3
    @Test
4
    void apply_buildsExpectedSqlSegment() {
5
        // 正常路径：EQ + BETWEEN 应转换为 SQL 片段。
6
        QueryWrapper<Object> wrapper = new QueryWrapper<>();
7
        Map<String, QueryFieldSpec> specs = Map.of(
8
                "status", QueryFieldSpec.of("status", EnumSet.of(FilterOp.EQ)),
9
                "createdTime", QueryFieldSpec.of("created_time", EnumSet.of(FilterOp.BETWEEN))
10
        );
11

12
        FilterCondition status = new FilterCondition();
203 collapsed lines
13
        status.setField("status");
14
        status.setOp(FilterOp.EQ);
15
        status.setValue("pending");
16

17
        FilterCondition created = new FilterCondition();
18
        created.setField("createdTime");
19
        created.setOp(FilterOp.BETWEEN);
20
        created.setValue(List.of("2026-01-01", "2026-01-31"));
21

22
        QueryFilterApplier.apply(wrapper, List.of(status, created), specs);
23

24
        String sql = wrapper.getCustomSqlSegment();
25
        assertTrue(sql.contains("status"));
26
        assertTrue(sql.contains("created_time"));
27
        assertTrue(sql.toUpperCase().contains("BETWEEN"));
28
    }
29

30
    @Test
31
    void apply_rejectsUnsupportedOp() {
32
        // 不在字段规格中的操作符应被拒绝。
33
        QueryWrapper<Object> wrapper = new QueryWrapper<>();
34
        Map<String, QueryFieldSpec> specs = Map.of(
35
                "status", QueryFieldSpec.of("status", EnumSet.of(FilterOp.EQ))
36
        );
37

38
        FilterCondition status = new FilterCondition();
39
        status.setField("status");
40
        status.setOp(FilterOp.LIKE);
41
        status.setValue("pending");
42

43
        assertThrows(QueryException.class,
44
                () -> QueryFilterApplier.apply(wrapper, List.of(status), specs));
45
    }
46

47
    @Test
48
    void apply_rejectsFiltersAndGroupConflict() {
49
        // filters 与 group 不能同时存在。
50
        QueryWrapper<Object> wrapper = new QueryWrapper<>();
51
        Map<String, QueryFieldSpec> specs = Map.of(
52
                "status", QueryFieldSpec.of("status", EnumSet.of(FilterOp.EQ))
53
        );
54

55
        QueryFilters query = new QueryFilters();
56
        query.setFilters(List.of(filter("status", FilterOp.EQ, "pending")));
57
        query.setGroup(new QueryGroup());
58

59
        assertThrows(QueryException.class,
60
                () -> QueryFilterApplier.apply(wrapper, query, specs));
61
    }
62

63
    @Test
64
    void apply_rejectsTooManyFilters() {
65
        // 超过最大 filters 数量应被拒绝。
66
        QueryWrapper<Object> wrapper = new QueryWrapper<>();
67
        Map<String, QueryFieldSpec> specs = Map.of(
68
                "status", QueryFieldSpec.of("status", EnumSet.of(FilterOp.EQ))
69
        );
70

71
        List<FilterCondition> filters = new ArrayList<>();
72
        for (int i = 0; i < QueryFilters.MAX_FILTERS + 1; i++) {
73
            filters.add(filter("status", FilterOp.EQ, "pending"));
74
        }
75

76
        assertThrows(QueryException.class,
77
                () -> QueryFilterApplier.apply(wrapper, filters, specs));
78
    }
79

80
    @Test
81
    void apply_rejectsTooManyGroups() {
82
        // 超过最大 group 数量应被拒绝。
83
        QueryWrapper<Object> wrapper = new QueryWrapper<>();
84
        Map<String, QueryFieldSpec> specs = Map.of(
85
                "status", QueryFieldSpec.of("status", EnumSet.of(FilterOp.EQ))
86
        );
87

88
        QueryGroup group = new QueryGroup();
89
        List<QueryGroup> groups = new ArrayList<>();
90
        for (int i = 0; i < QueryGroup.MAX_GROUPS + 1; i++) {
91
            groups.add(new QueryGroup());
92
        }
93
        group.setGroups(groups);
94

95
        QueryFilters query = new QueryFilters();
96
        query.setGroup(group);
97

98
        assertThrows(QueryException.class,
99
                () -> QueryFilterApplier.apply(wrapper, query, specs));
100
    }
101

102
    @Test
103
    void apply_rejectsEmptyIn() {
104
        // 严格模式下 IN 不能为空。
105
        QueryWrapper<Object> wrapper = new QueryWrapper<>();
106
        Map<String, QueryFieldSpec> specs = Map.of(
107
                "id", QueryFieldSpec.of("id", EnumSet.of(FilterOp.IN))
108
        );
109

110
        FilterCondition condition = filter("id", FilterOp.IN, List.of());
111
        assertThrows(QueryException.class,
112
                () -> QueryFilterApplier.apply(wrapper, List.of(condition), specs));
113
    }
114

115
    @Test
116
    void apply_rejectsEmptyNotIn() {
117
        // 严格模式下 NOT_IN 不能为空。
118
        QueryWrapper<Object> wrapper = new QueryWrapper<>();
119
        Map<String, QueryFieldSpec> specs = Map.of(
120
                "id", QueryFieldSpec.of("id", EnumSet.of(FilterOp.NOT_IN))
121
        );
122

123
        FilterCondition condition = filter("id", FilterOp.NOT_IN, List.of());
124
        assertThrows(QueryException.class,
125
                () -> QueryFilterApplier.apply(wrapper, List.of(condition), specs));
126
    }
127

128
    @Test
129
    void apply_rejectsInvalidBetween() {
130
        // BETWEEN 必须提供两个值。
131
        QueryWrapper<Object> wrapper = new QueryWrapper<>();
132
        Map<String, QueryFieldSpec> specs = Map.of(
133
                "createdTime", QueryFieldSpec.of("created_time", EnumSet.of(FilterOp.BETWEEN))
134
        );
135

136
        FilterCondition condition = filter("createdTime", FilterOp.BETWEEN, List.of("only-one"));
137
        assertThrows(QueryException.class,
138
                () -> QueryFilterApplier.apply(wrapper, List.of(condition), specs));
139
    }
140

141
    @Test
142
    void apply_wrapsAutoLikeValue() {
143
        // autoLike 需要包裹为 %...%。
144
        QueryWrapper<Object> wrapper = new QueryWrapper<>();
145
        Map<String, QueryFieldSpec> specs = Map.of(
146
                "name", QueryFieldSpec.of("name", EnumSet.of(FilterOp.LIKE), true)
147
        );
148

149
        FilterCondition condition = filter("name", FilterOp.LIKE, "neo");
150
        QueryFilterApplier.apply(wrapper, List.of(condition), specs);
151

152
        boolean matched = wrapper.getParamNameValuePairs().values().stream()
153
                .anyMatch(value -> "%neo%".equals(value));
154
        assertTrue(matched);
155
    }
156

157
    @Test
158
    void apply_supportsNotLikeLeftRight() {
159
        // NOT_LIKE_LEFT/RIGHT 需生成 NOT LIKE 条件。
160
        QueryWrapper<Object> wrapper = new QueryWrapper<>();
161
        Map<String, QueryFieldSpec> specs = Map.of(
162
                "name", QueryFieldSpec.of("name", EnumSet.of(FilterOp.NOT_LIKE_LEFT, FilterOp.NOT_LIKE_RIGHT))
163
        );
164

165
        QueryFilterApplier.apply(wrapper, List.of(
166
                filter("name", FilterOp.NOT_LIKE_LEFT, "neo"),
167
                filter("name", FilterOp.NOT_LIKE_RIGHT, "neo")
168
        ), specs);
169

170
        String sql = wrapper.getCustomSqlSegment();
171
        assertTrue(sql.toUpperCase().contains("NOT LIKE"));
172
    }
173

174
    @Test
175
    void apply_buildsOrGroupWithNestedParentheses() {
176
        // OR 组应保留每个子组的括号嵌套。
177
        QueryWrapper<Object> wrapper = new QueryWrapper<>();
178
        Map<String, QueryFieldSpec> specs = Map.of(
179
                "processId", QueryFieldSpec.of("process_id", EnumSet.of(FilterOp.EQ)),
180
                "status", QueryFieldSpec.of("status", EnumSet.of(FilterOp.EQ)),
181
                "applicantId", QueryFieldSpec.of("applicant_id", EnumSet.of(FilterOp.EQ)),
182
                "createdTime", QueryFieldSpec.of("created_time", EnumSet.of(FilterOp.GE))
183
        );
184

185
        QueryGroup group1 = new QueryGroup();
186
        group1.setLogic(QueryLogic.AND);
187
        group1.setFilters(List.of(
188
                filter("processId", FilterOp.EQ, 1),
189
                filter("status", FilterOp.EQ, "pending")
190
        ));
191

192
        QueryGroup group2 = new QueryGroup();
193
        group2.setLogic(QueryLogic.AND);
194
        group2.setFilters(List.of(
195
                filter("applicantId", FilterOp.EQ, 2),
196
                filter("createdTime", FilterOp.GE, "2026-01-01")
197
        ));
198

199
        QueryGroup root = new QueryGroup();
200
        root.setLogic(QueryLogic.OR);
201
        root.setGroups(List.of(group1, group2));
202

203
        QueryFilters query = new QueryFilters();
204
        query.setGroup(root);
205

206
        QueryFilterApplier.apply(wrapper, query, specs);
207

208
        String sql = wrapper.getCustomSqlSegment();
209
        assertTrue(sql.matches("(?is).*\\(.*process_id.*and.*status.*\\)\\s*or\\s*\\(.*applicant_id.*and.*created_time.*\\).*"));
210
    }
211

212
    private FilterCondition filter(String field, FilterOp op, Object value) {
213
        FilterCondition condition = new FilterCondition();
214
        condition.setField(field);
215
        condition.setOp(op);
216
        condition.setValue(value);
217
        return condition;
218
    }
219
}

`QueryPageBuilder`:#

1
class QueryPageBuilderTest {
2

3
    @Test
4
    void buildPage_resolvesOrderColumns() {
5
        // 排序字段应通过白名单映射。
6
        BasePageRequest<QueryFilters> request = new BasePageRequest<>();
7
        request.setPage(1);
8
        request.setSize(10);
9
        request.setOrders(List.of(
10
                BasePageRequest.BaseOrderItem.desc("createdTime"),
11
                BasePageRequest.BaseOrderItem.asc("status")
12
        ));
117 collapsed lines
13

14
        IPage<Object> page = QueryPageBuilder.buildPage(request, Map.of(
15
                "createdTime", "created_time",
16
                "status", "status"
17
        ));
18

19
        List<OrderItem> orders = page.orders();
20
        assertEquals(2, orders.size());
21
        assertEquals("created_time", orders.get(0).getColumn());
22
        assertEquals("status", orders.get(1).getColumn());
23
    }
24

25
    @Test
26
    void buildPage_rejectsUnknownOrderField() {
27
        // 未知排序字段应被拒绝。
28
        BasePageRequest<QueryFilters> request = new BasePageRequest<>();
29
        request.setPage(1);
30
        request.setSize(10);
31
        request.setOrders(List.of(BasePageRequest.BaseOrderItem.asc("unknown")));
32

33
        assertThrows(QueryException.class,
34
                () -> QueryPageBuilder.buildPage(request, Map.of("status", "status")));
35
    }
36

37
    @Test
38
    void buildPage_unpagedWhenNoPageTrue() {
39
        // noPage=true 应触发不分页。
40
        BasePageRequest<QueryFilters> request = new BasePageRequest<>();
41
        request.setPage(2);
42
        request.setSize(10);
43
        request.setNoPage(true);
44

45
        IPage<Object> page = QueryPageBuilder.buildPage(request, Map.of("id", "id"));
46

47
        assertEquals(1L, page.getCurrent());
48
        assertEquals(BasePageRequest.MAX_UNPAGED_SIZE, page.getSize());
49
        assertFalse(page.isSearchCount());
50
    }
51

52
    @Test
53
    void buildPage_unpagedWhenPageIsNegative() {
54
        // page < 0 应触发不分页。
55
        BasePageRequest<QueryFilters> request = new BasePageRequest<>();
56
        request.setPage(-1);
57
        request.setSize(10);
58

59
        IPage<Object> page = QueryPageBuilder.buildPage(request, Map.of("id", "id"));
60

61
        assertEquals(1L, page.getCurrent());
62
        assertEquals(BasePageRequest.MAX_UNPAGED_SIZE, page.getSize());
63
        assertFalse(page.isSearchCount());
64
    }
65

66
    @Test
67
    void buildPage_allowsMaxUnpagedSizeInPagedMode() {
68
        // 分页模式下允许 MAX_UNPAGED_SIZE。
69
        BasePageRequest<QueryFilters> request = new BasePageRequest<>();
70
        request.setPage(1);
71
        request.setSize(BasePageRequest.MAX_UNPAGED_SIZE);
72

73
        IPage<Object> page = QueryPageBuilder.buildPage(request, Map.of("id", "id"));
74

75
        assertEquals(1L, page.getCurrent());
76
        assertEquals(BasePageRequest.MAX_UNPAGED_SIZE, page.getSize());
77
        assertTrue(page.isSearchCount());
78
    }
79

80
    @Test
81
    void buildPage_skipsNaturalOrder() {
82
        // NATURAL 方向应被忽略。
83
        BasePageRequest<QueryFilters> request = new BasePageRequest<>();
84
        request.setPage(1);
85
        request.setSize(10);
86
        request.setOrders(List.of(new BasePageRequest.BaseOrderItem("status", BasePageRequest.OrderDirection.NATURAL)));
87

88
        IPage<Object> page = QueryPageBuilder.buildPage(request, Map.of("status", "status"));
89
        assertTrue(page.orders().isEmpty());
90
    }
91

92
    @Test
93
    void buildPage_rejectsEmptyOrderMapping() {
94
        // 排序白名单为空时应拒绝排序。
95
        BasePageRequest<QueryFilters> request = new BasePageRequest<>();
96
        request.setPage(1);
97
        request.setSize(10);
98
        request.setOrders(List.of(BasePageRequest.BaseOrderItem.asc("status")));
99

100
        assertThrows(QueryException.class,
101
                () -> QueryPageBuilder.buildPage(request, Map.of()));
102
    }
103

104
    @Test
105
    void buildPage_rejectsNullDirection() {
106
        // 排序方向为 null 应被拒绝。
107
        BasePageRequest<QueryFilters> request = new BasePageRequest<>();
108
        request.setPage(1);
109
        request.setSize(10);
110
        BasePageRequest.BaseOrderItem item = new BasePageRequest.BaseOrderItem("status", BasePageRequest.OrderDirection.ASC);
111
        item.setDirection((BasePageRequest.OrderDirection) null);
112
        request.setOrders(List.of(item));
113

114
        assertThrows(QueryException.class,
115
                () -> QueryPageBuilder.buildPage(request, Map.of("status", "status")));
116
    }
117

118
    @Test
119
    void buildPage_rejectsTooManyOrders() {
120
        // 超过最大排序数量应被拒绝。
121
        BasePageRequest<QueryFilters> request = new BasePageRequest<>();
122
        request.setPage(1);
123
        request.setSize(10);
124
        List<BasePageRequest.BaseOrderItem> orders = new ArrayList<>();
125
        for (int i = 0; i < BasePageRequest.MAX_ORDERS + 1; i++) {
126
            orders.add(BasePageRequest.BaseOrderItem.asc("status"));
127
        }
128
        request.setOrders(orders);
129

130
        assertThrows(QueryException.class,
131
                () -> QueryPageBuilder.buildPage(request, Map.of("status", "status")));
132
    }
133
}

`QueryValueConverters`#

1
class QueryValueConvertersTest {
2

3
    private enum DemoStatus {
4
        PENDING,
5
        DONE
6
    }
7

8
    @Test
9
    void toLong_convertsNumberAndString() {
10
        // toLong 应处理数字、裁剪字符串，空串返回 null。
11
        Function<Object, Object> converter = QueryValueConverters.toLong();
12
        assertEquals(10L, converter.apply(10));
123 collapsed lines
13
        assertEquals(12L, converter.apply(" 12 "));
14
        assertNull(converter.apply(" "));
15
    }
16

17
    @Test
18
    void toLong_rejectsUnsupportedType() {
19
        // toLong 应拒绝不支持的类型。
20
        Function<Object, Object> converter = QueryValueConverters.toLong();
21
        assertThrows(IllegalArgumentException.class, () -> converter.apply(new Object()));
22
    }
23

24
    @Test
25
    void toInteger_convertsNumberAndString() {
26
        // toInteger 应处理数字、裁剪字符串，空串返回 null。
27
        Function<Object, Object> converter = QueryValueConverters.toInteger();
28
        assertEquals(7, converter.apply(7L));
29
        assertEquals(9, converter.apply(" 9 "));
30
        assertNull(converter.apply(""));
31
    }
32

33
    @Test
34
    void toBigDecimal_convertsNumbersAndStrings() {
35
        // toBigDecimal 应保持整数类精度并支持字符串小数。
36
        Function<Object, Object> converter = QueryValueConverters.toBigDecimal();
37
        assertEquals(new BigDecimal("123"), converter.apply(new BigInteger("123")));
38
        assertEquals(new BigDecimal("42"), converter.apply(42L));
39
        assertEquals(new BigDecimal("12.34"), converter.apply("12.34"));
40
    }
41

42
    @Test
43
    void toBigDecimal_rejectsUnsupportedType() {
44
        // toBigDecimal 应拒绝不支持的类型。
45
        Function<Object, Object> converter = QueryValueConverters.toBigDecimal();
46
        assertThrows(IllegalArgumentException.class, () -> converter.apply(OffsetDateTime.now()));
47
    }
48

49
    @Test
50
    void toBoolean_convertsStringAndHandlesEmpty() {
51
        // toBoolean 应解析字符串，空串返回 null。
52
        Function<Object, Object> converter = QueryValueConverters.toBoolean();
53
        assertEquals(Boolean.TRUE, converter.apply(" true "));
54
        assertNull(converter.apply(" "));
55
    }
56

57
    @Test
58
    void toBoolean_rejectsUnsupportedType() {
59
        // toBoolean 应拒绝不支持的类型。
60
        Function<Object, Object> converter = QueryValueConverters.toBoolean();
61
        assertThrows(IllegalArgumentException.class, () -> converter.apply(1));
62
    }
63

64
    @Test
65
    void toOffsetDateTime_convertsTimestamp() {
66
        // toOffsetDateTime 应将时间戳转换为 OffsetDateTime。
67
        Function<Object, Object> converter = QueryValueConverters.toOffsetDateTime();
68
        long timestamp = 1700000000000L;
69
        OffsetDateTime result = (OffsetDateTime) converter.apply(timestamp);
70
        assertEquals(Instant.ofEpochMilli(timestamp), result.toInstant());
71
    }
72

73
    @Test
74
    void toOffsetDateTime_convertsTimestampString() {
75
        // toOffsetDateTime 应将纯数字字符串视为时间戳。
76
        Function<Object, Object> converter = QueryValueConverters.toOffsetDateTime();
77
        long timestamp = 1700000000000L;
78
        OffsetDateTime result = (OffsetDateTime) converter.apply(String.valueOf(timestamp));
79
        assertEquals(Instant.ofEpochMilli(timestamp), result.toInstant());
80
    }
81

82
    @Test
83
    void toOffsetDateTime_convertsFormattedDateTime() {
84
        // toOffsetDateTime 应解析默认的日期时间格式。
85
        Function<Object, Object> converter = QueryValueConverters.toOffsetDateTime();
86
        String text = "2024-01-02 03:04:05";
87
        LocalDateTime ldt = LocalDateTime.parse(text, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
88
        OffsetDateTime expected = ldt.atZone(ZoneId.systemDefault()).toOffsetDateTime();
89
        assertEquals(expected, converter.apply(text));
90
    }
91

92
    @Test
93
    void toOffsetDateTime_convertsIsoOffsetDateTime() {
94
        // toOffsetDateTime 应解析 ISO 带偏移的时间串。
95
        Function<Object, Object> converter = QueryValueConverters.toOffsetDateTime();
96
        String text = "2024-01-02T03:04:05+08:00";
97
        assertEquals(OffsetDateTime.parse(text), converter.apply(text));
98
    }
99

100
    @Test
101
    void toOffsetDateTime_rejectsUnsupportedType() {
102
        // toOffsetDateTime 应拒绝不支持的类型。
103
        Function<Object, Object> converter = QueryValueConverters.toOffsetDateTime();
104
        assertThrows(IllegalArgumentException.class, () -> converter.apply(new Object()));
105
    }
106

107
    @Test
108
    void enumByName_handlesStringAndEnum() {
109
        // enumByName 应支持枚举实例与字符串名称。
110
        Function<Object, Object> converter = QueryValueConverters.enumByName(DemoStatus.class);
111
        assertEquals(DemoStatus.PENDING, converter.apply("PENDING"));
112
        assertEquals(DemoStatus.DONE, converter.apply(DemoStatus.DONE));
113
        assertNull(converter.apply(" "));
114
    }
115

116
    @Test
117
    void enumByName_rejectsUnsupportedType() {
118
        // enumByName 应拒绝不支持的类型。
119
        Function<Object, Object> converter = QueryValueConverters.enumByName(DemoStatus.class);
120
        assertThrows(IllegalArgumentException.class, () -> converter.apply(1));
121
    }
122

123
    @Test
124
    void enumByResolver_usesResolverAndHandlesEmpty() {
125
        // enumByResolver 应将字符串交给 resolver 处理。
126
        Function<Object, Object> converter = QueryValueConverters.enumByResolver(
127
                value -> "PENDING".equals(value) ? DemoStatus.PENDING : DemoStatus.DONE
128
        );
129
        assertEquals(DemoStatus.PENDING, converter.apply("PENDING"));
130
        assertNull(converter.apply(" "));
131
    }
132

133
    @Test
134
    void enumByResolver_rejectsUnsupportedType() {
135
        // enumByResolver 应拒绝不支持的类型。
136
        Function<Object, Object> converter = QueryValueConverters.enumByResolver(value -> DemoStatus.PENDING);
137
        assertThrows(IllegalArgumentException.class, () -> converter.apply(123));
138
    }
139
}

0. 前言#

1. 拟定通用查询数据结构：BasePageRequest#

2. 拟定数据库查询条件枚举类：FilterOp#

3. BasePageRequest中泛型的基本数据结构：FilterCondition#

4. 树状查询表达式：AND/OR分组（QueryLogic枚举与QueryGroup）#