当前位置：首页 > news >正文

[笔记] 动态 SQL 查询技术解析：构建灵活高效的企业级数据访问层

news 2025/7/15 9:44:27

文章目录

一. 应用场景
二. 使用示例
- 示例1
- 示例2
- 示例3
三. 实现
- 1. 动态表查询构建器，模仿MyBatis-Plus
- 2. mapper
- 3. mapper.xml
- - 功能概述
  - 参数说明
四. 动态 SQL 的优化与风险防控

在企业级应用开发中，数据查询场景往往呈现出复杂多变的特点 —— 从简单的单表筛选到包含嵌套条件的多维度检索，从固定格式的报表查询到用户自定义的灵活搜索。动态 SQL 查询以其 “参数化配置 + 逻辑动态拼接” 的特性，成为构建通用数据访问层的核心技术。以下从应用场景、技术优势及工程实践三个维度，深度解析这一技术的实用价值与优化方向。

一. 应用场景

这种动态 SQL 查询在通用数据访问层中非常实用，例如：

通用的数据查询服务。
支持复杂筛选条件的报表系统。
数据管理后台的高级搜索功能。

二. 使用示例

示例1

在这里插入图片描述

示例2

在这里插入图片描述

示例3

在这里插入图片描述

三. 实现

1. 动态表查询构建器，模仿MyBatis-Plus

/*** 动态表查询构建器，模仿MyBatis-Plus** @author 鲁子狄* @since 2025/07/11 10:43*/
@Data
public class DynamicQueryWrapper {private final Map<String, Object> param = new HashMap<>();private final List<QueryCondition> conditions = new ArrayList<>();private final List<String> notNullColumns = new ArrayList<>();private final List<String> nullColumns = new ArrayList<>();private String tableName;private String selectColumns;private Object year;private Object versionId;private String groupByField;private String orderByField;private String sort = "asc";/*** 设置表名*/public DynamicQueryWrapper table(String tableName) {this.tableName = tableName;param.put("bmc", tableName);return this;}/*** 设置要查询的字段*/public DynamicQueryWrapper select(String columns) {selectColumns = columns;param.put("bzd", columns);return this;}/*** 设置年份*/public DynamicQueryWrapper year(Object year) {this.year = year;param.put("year", year);return this;}/*** 设置版本ID*/public DynamicQueryWrapper versionId(Object versionId) {this.versionId = versionId;param.put("versionId", versionId);return this;}/*** 等于条件*/public DynamicQueryWrapper eq(boolean condition, String column, Object val) {if (condition && column != null && val != null) {conditions.add(new QueryCondition(column, "=", val));}return this;}/*** 等于条件*/public DynamicQueryWrapper eq(String column, Object val) {return eq(true, column, val);}/*** 不等于条件*/public DynamicQueryWrapper ne(boolean condition, String column, Object val) {if (condition && column != null && val != null) {conditions.add(new QueryCondition(column, "<>", val));}return this;}/*** 不等于条件*/public DynamicQueryWrapper ne(String column, Object val) {return ne(true, column, val);}/*** 大于条件*/public DynamicQueryWrapper gt(boolean condition, String column, Object val) {if (condition && column != null && val != null) {conditions.add(new QueryCondition(column, ">", val));}return this;}/*** 大于条件*/public DynamicQueryWrapper gt(String column, Object val) {return gt(true, column, val);}/*** 小于条件*/public DynamicQueryWrapper lt(boolean condition, String column, Object val) {if (condition && column != null && val != null) {conditions.add(new QueryCondition(column, "<", val));}return this;}/*** 小于条件*/public DynamicQueryWrapper lt(String column, Object val) {return lt(true, column, val);}/*** 大于等于条件*/public DynamicQueryWrapper ge(boolean condition, String column, Object val) {if (condition && column != null && val != null) {conditions.add(new QueryCondition(column, ">=", val));}return this;}/*** 大于等于条件*/public DynamicQueryWrapper ge(String column, Object val) {return ge(true, column, val);}/*** 小于等于条件*/public DynamicQueryWrapper le(boolean condition, String column, Object val) {if (condition && column != null && val != null) {conditions.add(new QueryCondition(column, "<=", val));}return this;}/*** 小于等于条件*/public DynamicQueryWrapper le(String column, Object val) {return le(true, column, val);}/*** 模糊查询*/public DynamicQueryWrapper like(boolean condition, String column, Object val) {if (condition && column != null && val != null) {conditions.add(new QueryCondition(column, "LIKE", val, true));}return this;}/*** 模糊查询*/public DynamicQueryWrapper like(String column, Object val) {return like(true, column, val);}/*** 左模糊查询*/public DynamicQueryWrapper likeLeft(boolean condition, String column, Object val) {if (condition && column != null && val != null) {conditions.add(new QueryCondition(column, "LIKE_LEFT", val, true));}return this;}/*** 左模糊查询*/public DynamicQueryWrapper likeLeft(String column, Object val) {return likeLeft(true, column, val);}/*** 右模糊查询*/public DynamicQueryWrapper likeRight(boolean condition, String column, Object val) {if (condition && column != null && val != null) {conditions.add(new QueryCondition(column, "LIKE_RIGHT", val, true));}return this;}/*** 右模糊查询*/public DynamicQueryWrapper likeRight(String column, Object val) {return likeRight(true, column, val);}/*** 不包含查询*/public DynamicQueryWrapper notLike(boolean condition, String column, Object val) {if (condition && column != null && val != null) {conditions.add(new QueryCondition(column, "NOT LIKE", val, true));}return this;}/*** 不包含查询*/public DynamicQueryWrapper notLike(String column, Object val) {return notLike(true, column, val);}/*** IN查询*/public DynamicQueryWrapper in(boolean condition, String column, Collection<?> values) {if (condition && column != null && values != null && !values.isEmpty()) {conditions.add(new QueryCondition(column, "IN", new ArrayList<>(values)));}return this;}/*** IN查询*/public DynamicQueryWrapper in(String column, Collection<?> values) {return in(true, column, values);}/*** NOT IN查询*/public DynamicQueryWrapper notIn(boolean condition, String column, Collection<?> values) {if (condition && column != null && values != null && !values.isEmpty()) {conditions.add(new QueryCondition(column, "NOT IN", new ArrayList<>(values)));}return this;}/*** NOT IN查询*/public DynamicQueryWrapper notIn(String column, Collection<?> values) {return notIn(true, column, values);}/*** BETWEEN查询*/public DynamicQueryWrapper between(boolean condition, String column, Object val1, Object val2) {if (condition && column != null && val1 != null && val2 != null) {List<Object> values = new ArrayList<>();values.add(val1);values.add(val2);conditions.add(new QueryCondition(column, "BETWEEN", values));}return this;}/*** BETWEEN查询*/public DynamicQueryWrapper between(String column, Object val1, Object val2) {return between(true, column, val1, val2);}/*** NOT BETWEEN查询*/public DynamicQueryWrapper notBetween(boolean condition, String column, Object val1, Object val2) {if (condition && column != null && val1 != null && val2 != null) {List<Object> values = new ArrayList<>();values.add(val1);values.add(val2);conditions.add(new QueryCondition(column, "NOT BETWEEN", values));}return this;}/*** NOT BETWEEN查询*/public DynamicQueryWrapper notBetween(String column, Object val1, Object val2) {return notBetween(true, column, val1, val2);}/*** 字段为NULL*/public DynamicQueryWrapper isNull(boolean condition, String column) {if (condition && column != null) {nullColumns.add(column);}return this;}/*** 字段为NULL*/public DynamicQueryWrapper isNull(String column) {return isNull(true, column);}/*** 字段不为NULL*/public DynamicQueryWrapper isNotNull(boolean condition, String column) {if (condition && column != null) {notNullColumns.add(column);}return this;}/*** 字段不为NULL*/public DynamicQueryWrapper isNotNull(String column) {return isNotNull(true, column);}/*** 分组*/public DynamicQueryWrapper groupBy(String field) {groupByField = field;return this;}/*** 排序*/public DynamicQueryWrapper orderBy(String field, boolean isAsc) {orderByField = field;sort = isAsc ? "asc" : "desc";return this;}/*** 正序排列*/public DynamicQueryWrapper orderByAsc(String field) {return orderBy(field, true);}/*** 倒序排列*/public DynamicQueryWrapper orderByDesc(String field) {return orderBy(field, false);}/*** OR条件*/public DynamicQueryWrapper or(boolean condition, Consumer<DynamicQueryWrapper> consumer) {if (condition) {DynamicQueryWrapper nestedWrapper = new DynamicQueryWrapper();consumer.accept(nestedWrapper);// 创建OR组ConditionGroup orGroup = new ConditionGroup();orGroup.getConditions().addAll(nestedWrapper.conditions);orGroup.setLogic("OR");conditions.add(orGroup);}return this;}/*** OR条件*/public DynamicQueryWrapper or(Consumer<DynamicQueryWrapper> consumer) {return or(true, consumer);}/*** AND条件*/public DynamicQueryWrapper and(boolean condition, Consumer<DynamicQueryWrapper> consumer) {if (condition) {DynamicQueryWrapper nestedWrapper = new DynamicQueryWrapper();consumer.accept(nestedWrapper);// 创建AND组ConditionGroup andGroup = new ConditionGroup();andGroup.getConditions().addAll(nestedWrapper.conditions);andGroup.setLogic("AND");conditions.add(andGroup);}return this;}/*** AND条件*/public DynamicQueryWrapper and(Consumer<DynamicQueryWrapper> consumer) {return and(true, consumer);}/*** 查询条件*/@Datapublic static class QueryCondition {private final String column;private final String operator;private final Object value;private final List<QueryCondition> children = new ArrayList<>();private boolean isLike = false;QueryCondition(String column, String operator, Object value) {this.column = column;this.operator = operator;this.value = value;}QueryCondition(String column, String operator, Object value, boolean isLike) {this.column = column;this.operator = operator;this.value = value;this.isLike = isLike;}// 辅助方法，避免在XML中直接比较字符串public boolean isEqualOperator() {return "=".equals(operator);}public boolean isNotEqualOperator() {return "<>".equals(operator);}public boolean isGreaterThanOperator() {return ">".equals(operator);}public boolean isLessThanOperator() {return "<".equals(operator);}public boolean isGreaterEqualOperator() {return ">=".equals(operator);}public boolean isLessEqualOperator() {return "<=".equals(operator);}public boolean isLikeOperator() {return "LIKE".equals(operator);}public boolean isLeftLikeOperator() {return "LIKE_LEFT".equals(operator);}public boolean isRightLikeOperator() {return "LIKE_RIGHT".equals(operator);}public boolean isNotLikeOperator() {return "NOT LIKE".equals(operator);}public boolean isInOperator() {return "IN".equals(operator);}public boolean isNotInOperator() {return "NOT IN".equals(operator);}public boolean isBetweenOperator() {return "BETWEEN".equals(operator);}public boolean isNotBetweenOperator() {return "NOT BETWEEN".equals(operator);}public boolean isComparisonOperator() {return isEqualOperator() || isNotEqualOperator() || isGreaterThanOperator() || isLessThanOperator()|| isGreaterEqualOperator() || isLessEqualOperator();}public boolean isGroup() {return false;}}/*** 条件组（用于处理括号内的AND/OR组合）*/@Getter@Setterpublic static class ConditionGroup extends QueryCondition {private final List<QueryCondition> conditions = new ArrayList<>();private String logic = "AND";ConditionGroup() {super(null, null, null);}// 添加辅助方法，判断逻辑类型public boolean isAndLogic() {return "AND".equals(logic);}public boolean isOrLogic() {return "OR".equals(logic);}@Overridepublic boolean isGroup() {return true;}}
}

2. mapper

	/*** selectList 使用QueryWrapper进行查询** @param wrapper 自定义查询条件* @return java.util.List<java.util.Map < java.lang.String, java.lang.String>>*/List<Map<String, String>> selectDataList(@Param("wrapper") DynamicQueryWrapper wrapper);

3. mapper.xml

<select id="selectDataList" resultType="java.util.Map">SELECTCAST(id as char) as sjid, ${wrapper.param.bzd}, #{wrapper.param.year} as year, version_id as versionIdFROM${wrapper.param.bmc}WHERE 1=1<if test="wrapper.param.versionId != null and wrapper.param.versionId != ''">AND version_id = #{wrapper.param.versionId}</if><!-- 处理所有条件 --><if test="wrapper.conditions != null and wrapper.conditions.size() > 0"><foreach collection="wrapper.conditions" item="condition" index="index"><choose><!-- 处理条件组（带括号的AND/OR条件） --><when test="condition.isGroup()">AND (<trim prefixOverrides="AND|OR"><foreach collection="condition.conditions" item="groupItem">${condition.logic}<choose><when test="groupItem.comparisonOperator">${groupItem.column} ${groupItem.operator} #{groupItem.value}</when><when test="groupItem.likeOperator and groupItem.isLike">${groupItem.column} LIKE CONCAT('%', #{groupItem.value}, '%')</when><when test="groupItem.leftLikeOperator and groupItem.isLike">${groupItem.column} LIKE CONCAT('%', #{groupItem.value})</when><when test="groupItem.rightLikeOperator and groupItem.isLike">${groupItem.column} LIKE CONCAT(#{groupItem.value}, '%')</when><when test="groupItem.notLikeOperator and groupItem.isLike">${groupItem.column} NOT LIKE CONCAT('%', #{groupItem.value}, '%')</when><when test="groupItem.inOperator">${groupItem.column} IN<foreach collection="groupItem.value" item="val" open="(" separator="," close=")">#{val}</foreach></when><when test="groupItem.notInOperator">${groupItem.column} NOT IN<foreach collection="groupItem.value" item="val" open="(" separator="," close=")">#{val}</foreach></when><when test="groupItem.betweenOperator">${groupItem.column} BETWEEN #{groupItem.value[0]} AND #{groupItem.value[1]}</when><when test="groupItem.notBetweenOperator">${groupItem.column} NOT BETWEEN #{groupItem.value[0]} AND #{groupItem.value[1]}</when></choose></foreach></trim>)</when><!-- 处理普通条件 --><otherwise><choose><when test="condition.comparisonOperator">AND ${condition.column} ${condition.operator} #{condition.value}</when><when test="condition.likeOperator and condition.isLike">AND ${condition.column} LIKE CONCAT('%', #{condition.value}, '%')</when><when test="condition.leftLikeOperator and condition.isLike">AND ${condition.column} LIKE CONCAT('%', #{condition.value})</when><when test="condition.rightLikeOperator and condition.isLike">AND ${condition.column} LIKE CONCAT(#{condition.value}, '%')</when><when test="condition.notLikeOperator and condition.isLike">AND ${condition.column} NOT LIKE CONCAT('%', #{condition.value}, '%')</when><when test="condition.inOperator">AND ${condition.column} IN<foreach collection="condition.value" item="val" open="(" separator="," close=")">#{val}</foreach></when><when test="condition.notInOperator">AND ${condition.column} NOT IN<foreach collection="condition.value" item="val" open="(" separator="," close=")">#{val}</foreach></when><when test="condition.betweenOperator">AND ${condition.column} BETWEEN #{condition.value[0]} AND #{condition.value[1]}</when><when test="condition.notBetweenOperator">AND ${condition.column} NOT BETWEEN #{condition.value[0]} AND #{condition.value[1]}</when></choose></otherwise></choose></foreach></if><!-- 处理NULL条件 --><if test="wrapper.nullColumns != null and wrapper.nullColumns.size() > 0"><foreach collection="wrapper.nullColumns" item="column">AND ${column} IS NULL</foreach></if><!-- 处理非空条件 --><if test="wrapper.notNullColumns != null and wrapper.notNullColumns.size() > 0"><foreach collection="wrapper.notNullColumns" item="column">AND ${column} IS NOT NULL AND ${column} != ''</foreach></if><!-- 处理分组 --><if test="wrapper.groupByField != null and wrapper.groupByField != ''">GROUP BY ${wrapper.groupByField}</if><!-- 处理排序 --><if test="wrapper.orderByField != null and wrapper.orderByField != ''">ORDER BY ${wrapper.orderByField} ${wrapper.sort}</if></select>

功能概述

基础查询：从指定表（${wrapper.param.bmc}）中选取数据，并返回一个 Map 结果集。
动态条件过滤：能根据不同的条件动态生成 WHERE 子句，支持多种比较操作，像等值比较、LIKE 模糊查询、IN/NOT IN 集合查询以及 BETWEEN 范围查询等。
条件分组：支持用括号将条件分组，以此来实现复杂的逻辑组合，例如 (A AND B) OR C。
空值判断：可以筛选出 NULL 或非 NULL 的字段。
分组和排序：支持 GROUP BY 和 ORDER BY 子句，能对查询结果进行分组和排序。

参数说明

查询使用了一个名为wrapper的参数对象，该对象包含以下属性：

param.bzd：要查询的字段列表。
param.bmc：要查询的表名。
param.year：年份参数。
param.versionId：版本 ID，用于筛选特定版本。
conditions：条件列表，包含简单条件和条件组。
nullColumns：需要判断为 NULL 的字段列表。
notNullColumns：需要判断为非 NULL 的字段列表。
groupByField：用于分组的字段。
orderByField和sort：用于排序的字段和排序方向。

四. 动态 SQL 的优化与风险防控

现系统不用

对表名（${wrapper.param.bmc}）、字段名（${wrapper.param.bzd}）等拼接参数进行白名单校验，避免恶意用户注入非法表名（如user; DROP TABLE data）。

// 表名白名单校验
private static final Set<String> TABLE_WHITELIST = new HashSet<>(Arrays.asList("user", "order", "product"));
if (!TABLE_WHITELIST.contains(wrapper.getParam().getBmc())) {throw new SecurityException("非法表名查询");
}