【无标题】

m0_67588944

于 2025-08-19 16:16:58 发布

阅读量207

点赞数 6

CC 4.0 BY-SA版权

文章标签：开发语言 java sql

本文链接：https://blog.csdn.net/m0_67588944/article/details/150527657

Java动态SQL条件构建器：SqlConditionBuilder详解（基于StringBuilder和建造者模式）

简介

本文将深入分析一个高效、灵活的Java动态SQL条件构建器——SqlConditionBuilder，它可以帮助开发者优雅地构建复杂SQL查询条件，提高代码可读性和维护性。

代码实现

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;

import java.util.Collection;
import java.util.function.Consumer;
import java.util.function.Supplier;
import java.util.stream.Collectors;

/**
 * SQL条件构造器
 */
public class SqlConditionBuilder {
    private final StringBuilder builder = new StringBuilder();
    private boolean hasCondition = false;
    private String nextConnector = " AND "; // 默认连接符
    private EmptyValuePolicy emptyValuePolicy = EmptyValuePolicy.FALSE_CONDITION;//如果拼接的条件值为空，拼1=2

    public static SqlConditionBuilder create() {
        return new SqlConditionBuilder();

    }

    /**
     * 添加原始SQL片段
     */
    public SqlConditionBuilder append(String sqlPart) {
        if (StrUtil.isNotBlank(sqlPart)) {
            addConnectorIfNeeded();
            builder.append(sqlPart);
            markConditionAdded();
        }
        return this;
    }

    public SqlConditionBuilder format(String template, Object... params) {
        if (StrUtil.isNotBlank(template)) {
            addConnectorIfNeeded();
            Object[] quotedParams = quoteParams(params);
            builder.append(StrUtil.format(template, quotedParams));
            markConditionAdded();
        }
        return this;
    }

    public SqlConditionBuilder eq(String column, Object value) {
        if (value == null) {
            handleEmptyValue();
            return this;
        }
        addConnectorIfNeeded();
        builder.append(column).append(" = ").append(quoteValue(value));
        markConditionAdded();
        return this;
    }

    public SqlConditionBuilder or() {
        nextConnector = " OR ";
        return this;
    }

    public SqlConditionBuilder and() {
        nextConnector = " AND ";
        return this;
    }

    public SqlConditionBuilder ne(String column, Object value) {
        if (value == null) {
            handleEmptyValue();
            return this;
        }
        addConnectorIfNeeded();
        builder.append(column).append(" <> ").append(quoteValue(value));
        markConditionAdded();
        return this;
    }

    public SqlConditionBuilder like(String column, String value) {
        if (StrUtil.isBlank(value)) {
            handleEmptyValue();
            return this;
        }
        addConnectorIfNeeded();
        builder.append(column).append(" LIKE ").append(quoteValue("%" + value + "%"));
        markConditionAdded();
        return this;
    }

    public SqlConditionBuilder in(String column, Collection<?> values) {
        if (CollUtil.isEmpty(values)) {
            handleEmptyValue();
            return this;
        }
        addConnectorIfNeeded();
        String inValues = values.stream()
                .map(this::quoteValue)
                .collect(Collectors.joining(", "));
        builder.append(column).append(" IN (").append(inValues).append(")");
        markConditionAdded();
        return this;
    }

    public SqlConditionBuilder in(String column, Supplier<Collection<?>> values) {
        return in(column, values.get());
    }

    public <T> SqlConditionBuilder arrayContains(String arrayColumn, Collection<T> values) {
        if (CollUtil.isEmpty(values)) {
            handleEmptyValue();
            return this;
        }
        addConnectorIfNeeded();
        String arrayValues = values.stream()
                .map(this::quoteValue)
                .collect(Collectors.joining(","));
        builder.append("string_to_array(").append(arrayColumn).append(",',') @> ARRAY[")
                .append(arrayValues).append("]");
        markConditionAdded();
        return this;
    }

    /**
     * 设置下一个条件的连接符（AND/OR）
     */
    public SqlConditionBuilder connectWith(String connector) {
        this.nextConnector = " " + connector.trim().toUpperCase() + " ";
        return this;
    }

    /**
     * 添加条件组
     */
    public SqlConditionBuilder group(Consumer<SqlConditionBuilder> groupBuilder) {
        SqlConditionBuilder inner = new SqlConditionBuilder();
        inner.setEmptyValuePolicy(this.emptyValuePolicy); // 继承策略
        groupBuilder.accept(inner);
        String innerSql = inner.build();

        if (StrUtil.isNotBlank(innerSql)) {
            addConnectorIfNeeded();
            builder.append("(").append(innerSql).append(")");
            markConditionAdded();
        }
        return this;
    }

    /**
     * 条件判断
     */
    public SqlConditionBuilder when(boolean condition, Consumer<SqlConditionBuilder> action) {
        if (condition) {
            action.accept(this);
        }
        return this;
    }

    /**
     * 构建最终SQL
     */
    public String build() {
        return builder.toString();
    }

    public SqlConditionBuilder setEmptyValuePolicy(EmptyValuePolicy policy) {
        this.emptyValuePolicy = policy;
        return this;
    }
    private void addConnectorIfNeeded() {
        if (hasCondition) {
            builder.append(nextConnector);
        }
    }

    private void markConditionAdded() {
        hasCondition = true;
        resetConnector();
    }

    private void resetConnector() {
        nextConnector = " AND ";
    }

    private Object[] quoteParams(Object[] params) {
        Object[] quoted = new Object[params.length];
        for (int i = 0; i < params.length; i++) {
            quoted[i] = quoteValue(params[i]);
        }
        return quoted;
    }

    private String quoteValue(Object value) {
        if (value == null) return "NULL";
        if (value instanceof Number || value instanceof Boolean) {
            return value.toString();
        }
        return "'" + value.toString().replace("'", "''") + "'";
    }

    private void handleEmptyValue() {
        switch (emptyValuePolicy) {
            case THROW_EXCEPTION:
                throw new IllegalArgumentException("SQL条件值不允许为空");
            case FALSE_CONDITION:
                addConnectorIfNeeded();
                builder.append("1=0");
                markConditionAdded();
                break;
            case IGNORE://如果空置跳过该条件的拼接
            default:
                // 忽略空值，不做任何操作
        }
    }

    public enum EmptyValuePolicy {
        IGNORE,          // 空值跳过该条件
        THROW_EXCEPTION, // 抛出异常
        FALSE_CONDITION  // 返回永假条件(1=0)
    }

示例

条件值空拼接永假条件策略（拼接1=0）

   String sql = SqlConditionBuilder.create()
                .setEmptyValuePolicy(SqlConditionBuilder.EmptyValuePolicy.FALSE_CONDITION)
                .eq("status", 1)
                .ne("create_by", null)
                .build();
        System.out.println(sql);
        //setEmptyValuePolicy可以不设置默认是制造永假条件

打印结果：status = 1 AND 1=0

条件值空抛出异常策略

   String sql = SqlConditionBuilder.create()
                .setEmptyValuePolicy(SqlConditionBuilder.EmptyValuePolicy.THROW_EXCEPTION)
                .eq("status", 1)
                .ne("create_by", null)
                .build();
        System.out.println(sql);
        //抛出：java.lang.IllegalArgumentException: SQL条件值不允许为空

条件值空跳过该条件拼接

   String sql = SqlConditionBuilder.create()
                .setEmptyValuePolicy(SqlConditionBuilder.EmptyValuePolicy.IGNORE)
             .eq("status", 1)
                .ne("create_by", null)
                .build();
  //打印：status = 1

复杂实例

  String sql = SqlConditionBuilder.create()
            .eq("status", 1)
            .ne("create_by", "zhangsan")
            .like("name", "cccc")
            .group(g -> g
                    .eq("role", "admin")
                    .or()
                    .in("departments", Collections.emptyList())
            )
            .build();
    System.out.println(sql);

//打印结果：
status = 1 AND create_by <> ‘zhangsan’ AND name LIKE ‘%cccc%’ AND (role = ‘admin’ OR 1=0)