文章目录
在企业级应用开发中,数据查询场景往往呈现出复杂多变的特点 —— 从简单的单表筛选到包含嵌套条件的多维度检索,从固定格式的报表查询到用户自定义的灵活搜索。动态 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>
功能概述
- 基础查询:从指定表(
${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("非法表名查询");
}