[笔记] 动态 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);
	}

	/**
	 * 查询条件
	 */
	@Data
	public 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
	@Setter
	public 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);
		}

		@Override
		public 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">
        SELECT
        CAST(id as char) as sjid, ${wrapper.param.bzd}, #{wrapper.param.year} as year, version_id as versionId
        FROM
        ${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>

功能概述

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