如何简化多条件查询场景下大量IF语句的使用

问题背景 #

我有一段代码，允许用户从5个下拉菜单中选择不同参数，选项包括"None"、"Name"、"Manager"、"AccessLevel"、"Inhabitant"和"Location"，这些参数会与JTextField中的输入值匹配。目前通过大量嵌套IF语句组合参数生成对应的SQL查询语句，比如同时选择Name、Manager和Location时，生成的SQL语句为：

query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND propertyManager = ? AND propertyLocation = ?";

这种方式导致代码充斥繁杂的判断逻辑，需要编写数百行代码处理不同组合，且后续扩展新参数时成本极高。以下是部分现有代码：

if (searchingForName == true) {
    if (searchingForManager == true) {
        if (searchingForAccessLevel == true) { // final lvl 1
             query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND propertyManager = ? AND accessLevelRequired = ?"; 
             query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyName = ? AND propertyManager = ? AND accessLevelRequired = ?"; 
        }else if ((searchType1.equals("Inhabitant") || searchType2.equals("Inhabitant") || searchType3.equals("Inhabitant"))) { // final lvl 2
          query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND propertyManager = ? AND propertyInhabitant = ?"; 
          query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyName = ? AND propertyManager = ? AND propertyInhabitant = ?"; 
      }else if ((searchType1.equals("Location") || searchType2.equals("Location") || searchType3.equals("Location"))) { // finaly lvl 3
          query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND propertyManager = ? AND propertyLocation = ?"; 
          query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyName = ? AND propertyManager = ? AND propertyLocation = ?"; 
      }
    }  else if ((searchType1.equals("AccessLevel") || searchType2.equals("AccessLevel") || searchType3.equals("AccessLevel"))) { // sub lvl 2
         if ((searchType1.equals("Inhabitant") || searchType2.equals("Inhabitant") || searchType3.equals("Inhabitant"))) { //final lvl 1
          query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND accessLevelRequired = ? AND propertyInhabitant = ?"; 
          query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyName = ? AND accessLevelRequired = ? AND propertyInhabitant = ?"; 
      }else if ((searchType1.equals("Location") || searchType2.equals("Location") || searchType3.equals("Location"))) { // final lvl 2
          query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND accessLevelRequired = ? AND propertyLocation = ?"; 
          query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyName = ? AND accessLevelRequired = ? AND propertyLocation = ?"; 
      }
    } else if ((searchType1.equals("Inhabitant") || searchType2.equals("Inhabitant") || searchType3.equals("Inhabitant"))) { // sub lvl 3
         if ((searchType1.equals("Location") || searchType2.equals("Location") || searchType3.equals("Location"))) { // final lvl 1
          query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND propertyInhabitant = ? AND propertyLocation = ?"; 
          query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyName = ? AND propertyInhabitant = ? AND propertyLocation = ?"; 
    }
         
//}
}else if ((searchType1.equals("Manager") || searchType2.equals("Manager") || searchType3.equals("Manager"))) { // main lvl 2
    
    
    if ((searchType1.equals("AccessLevel") || searchType2.equals("AccessLevel") || searchType3.equals("AccessLevel"))) { // sub lvl 1
        
        
      if ((searchType1.equals("Inhabitant") || searchType2.equals("Inhabitant") || searchType3.equals("Inhabitant"))) {// final lvl 1
           query = "SELECT * FROM propertyInfo WHERE propertyManager = ? AND accessLevelRequired = ? AND propertyInhabitant = ?"; 
           query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE   propertyManager = ? AND accessLevelRequired = ? AND propertyInhabitant = ?"; 
           
      }else if ((searchType1.equals("Location") || searchType2.equals("Location") || searchType3.equals("Location"))) { // final lvl 2
          query = "SELECT * FROM propertyInfo WHERE propertyManager = ? AND accessLevelRequired = ? AND propertyLocation = ?"; 
          query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyManager = ? AND accessLevelRequired = ? AND propertyLocation = ?"; 
      }
    }  else if ((searchType1.equals("Inhabitant") || searchType2.equals("Inhabitant") || searchType3.equals("Inhabitant"))) { // sub lvl 2
          if ((searchType1.equals("Location") || searchType2.equals("Location") || searchType3.equals("Location"))) { // final lvl 1
          query = "SELECT * FROM propertyInfo WHERE propertyManager = ? AND propertyInhabitant = ? AND propertyLocation = ?"; 
          query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyManager = ? AND propertyInhabitant = ? AND propertyLocation = ?"; 
      }
    }
  } 
}

简化方案 #

1. 建立参数与数据库字段的映射 #

先定义一个静态映射表，将下拉菜单的选项名称对应到数据库实际字段，避免硬编码字段名：

private static final Map<String, String> PARAM_FIELD_MAP = new HashMap<>();
static {
    PARAM_FIELD_MAP.put("Name", "propertyName");
    PARAM_FIELD_MAP.put("Manager", "propertyManager");
    PARAM_FIELD_MAP.put("AccessLevel", "accessLevelRequired");
    PARAM_FIELD_MAP.put("Inhabitant", "propertyInhabitant");
    PARAM_FIELD_MAP.put("Location", "propertyLocation");
}

2. 收集有效查询参数 #

遍历所有下拉菜单的选择值，过滤掉"None"选项，收集需要参与查询的参数：

// 假设5个下拉菜单的选择值存储在searchTypes列表中
List<String> selectedParams = new ArrayList<>();
for (String type : searchTypes) {
    if (!"None".equals(type)) {
        selectedParams.add(type);
    }
}

3. 动态生成SQL语句 #

根据收集到的参数，拼接WHERE子句，同时准备参数值列表（用于后续PreparedStatement绑定）：

List<String> whereClauses = new ArrayList<>();
List<Object> paramValues = new ArrayList<>();

for (String param : selectedParams) {
    String fieldName = PARAM_FIELD_MAP.get(param);
    whereClauses.add(fieldName + " = ?");
    // 替换为实际获取对应JTextField输入值的逻辑，比如用map存储参数与输入框的对应
    paramValues.add(getInputValueForParam(param)); 
}

// 构建主查询
StringBuilder queryBuilder = new StringBuilder("SELECT * FROM propertyInfo");
if (!whereClauses.isEmpty()) {
    queryBuilder.append(" WHERE ").append(String.join(" AND ", whereClauses));
}
String query = queryBuilder.toString();

// 构建计数查询
StringBuilder countQueryBuilder = new StringBuilder("SELECT COUNT(*) AS recordCount FROM propertyInfo");
if (!whereClauses.isEmpty()) {
    countQueryBuilder.append(" WHERE ").append(String.join(" AND ", whereClauses));
}
String query2 = countQueryBuilder.toString();

4. 绑定参数并执行查询 #

使用PreparedStatement绑定参数值，避免SQL注入风险：

// 执行主查询
PreparedStatement stmt = connection.prepareStatement(query);
for (int i = 0; i < paramValues.size(); i++) {
    stmt.setObject(i + 1, paramValues.get(i));
}
ResultSet rs = stmt.executeQuery();

// 执行计数查询
PreparedStatement countStmt = connection.prepareStatement(query2);
for (int i = 0; i < paramValues.size(); i++) {
    countStmt.setObject(i + 1, paramValues.get(i));
}
ResultSet countRs = countStmt.executeQuery();

方案优势 #

• 代码简洁：彻底消除嵌套IF逻辑，代码量大幅减少
• 易扩展：新增查询参数只需在映射表中添加条目，无需修改核心逻辑
• 安全性：采用PreparedStatement绑定参数，从根源避免SQL注入
• 可维护性：参数与字段的映射集中管理，便于后续修改

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