如何编写Java代码动态生成带多条件的SQL查询语句？

核心实现思路 #

根据参数命名规则，按以下逻辑动态构建SQL条件：

• 优先处理带_START和_END后缀的成对参数，生成BETWEEN区间条件
• 处理剩余无_START/_END后缀的单个参数，生成IN条件
• 用StringBuilder高效拼接SQL，通过标记集合避免重复处理成对参数

代码实现 #

import java.util.*;

public class DynamicSqlGenerator {
    private static final String BASE_SQL = "select * from ABC";
    private static final String SUFFIX_START = "_START";
    private static final String SUFFIX_END = "_END";

    public static String generateSql(Map<String, Object> params) {
        if (params == null || params.isEmpty()) {
            return BASE_SQL + ";";
        }

        StringBuilder sqlBuilder = new StringBuilder(BASE_SQL);
        Set<String> processedParams = new HashSet<>();
        List<String> conditions = new ArrayList<>();

        // 处理成对的_START/_END参数
        for (String paramKey : params.keySet()) {
            if (processedParams.contains(paramKey)) {
                continue;
            }

            if (paramKey.endsWith(SUFFIX_START)) {
                String baseName = paramKey.substring(0, paramKey.length() - SUFFIX_START.length());
                String endParamKey = baseName + SUFFIX_END;

                if (params.containsKey(endParamKey) && !processedParams.contains(endParamKey)) {
                    Object startValue = params.get(paramKey);
                    Object endValue = params.get(endParamKey);
                    conditions.add(String.format("%s BETWEEN '%s' AND '%s'",
                            baseName, startValue.toString(), endValue.toString()));
                    processedParams.add(paramKey);
                    processedParams.add(endParamKey);
                }
            }
        }

        // 处理剩余单个参数
        for (String paramKey : params.keySet()) {
            if (!processedParams.contains(paramKey)) {
                Object value = params.get(paramKey);
                conditions.add(String.format("%s IN '%s'", paramKey, value.toString()));
                processedParams.add(paramKey);
            }
        }

        // 拼接完整SQL
        if (!conditions.isEmpty()) {
            sqlBuilder.append(" where ").append(String.join(" AND ", conditions));
        }
        sqlBuilder.append(" ;");

        return sqlBuilder.toString();
    }

    // 测试示例
    public static void main(String[] args) {
        // 示例1：仅DATE和TIME区间参数
        Map<String, Object> params1 = new HashMap<>();
        params1.put("DATE_START", 0);
        params1.put("DATE_END", 20230101);
        params1.put("TIME_START", 0);
        params1.put("TIME_END", 173000);
        System.out.println("示例1生成SQL：");
        System.out.println(generateSql(params1));

        // 示例2：新增单个参数和自定义区间参数
        Map<String, Object> params2 = new HashMap<>(params1);
        params2.put("YEAR1", 2015);
        params2.put("YEAR2", 2016);
        params2.put("ACDAT_START", 20200101);
        params2.put("ACDAT_END", 20200202);
        System.out.println("\n示例2生成SQL：");
        System.out.println(generateSql(params2));
    }
}

输出结果 #

运行main方法后输出：

示例1生成SQL：
select * from ABC where DATE BETWEEN '0' AND '20230101' AND TIME BETWEEN '0' AND '173000' ;

示例2生成SQL：
select * from ABC where DATE BETWEEN '0' AND '20230101' AND TIME BETWEEN '0' AND '173000' AND YEAR1 IN '2015' AND YEAR2 IN '2016' AND ACDAT BETWEEN '20200101' AND '20200202' ;

重要注意事项 #

1. SQL注入风险：当前代码直接拼接参数值到SQL，存在注入漏洞。生产环境建议改用PreparedStatement结合占位符（?），示例调整逻辑：

   // 生成带占位符的条件，同时收集参数值
   conditions.add(String.format("%s BETWEEN ? AND ?", baseName));
   paramValues.add(startValue);
   paramValues.add(endValue);
   

2. 值类型处理：代码直接调用toString()转换参数值，实际使用时需根据日期、数字等类型调整格式。
3. 空值处理：需补充参数值为空的判断逻辑，避免生成无效条件。

内容的提问来源于stack exchange，提问作者user10591810