动态SQL查询中的参数化与调试技巧

// 假设 searchTerm 已经根据类型判断并设置了相应的条件
// ...
// String FETCH_USER_BY_SEARCHTERM = "SELECT * FROM SignUpTable WHERE ? = ? "; // 原始查询字符串

// 在条件分支中设置参数
if (searchTerm.contains(".com") && searchTerm.contains("@")) {
    // System.out.println("In email check");
    psmt.setString(1, "EMAIL_ID"); // 尝试将列名作为参数
    psmt.setString(2, searchTerm);
} else if (numresult == true){ // numresult 假定为之前判断手机号的结果
    // System.out.println("In number check");
    psmt.setString(1, "MOBILE_NUMBER"); // 尝试将列名作为参数
    psmt.setString(2, searchTerm);
} else if (uresult == true || alphanumeic == true) { // uresult/alphanumeic 假定为之前判断用户名的结果
    // System.out.println("In username check");
    psmt.setString(1, "USER_NAME"); // 尝试将列名作为参数
    psmt.setString(2, searchTerm);
}

rs = psmt.executeQuery(); // 执行查询
// ... 处理结果集

String FETCH_USER_BY_SEARCHTERM = "SELECT * FROM SignUpTable WHERE ? = ? ";

SELECT * FROM SignUpTable WHERE 'EMAIL_ID' = 'user@example.com';

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserSearchService {

    private Connection connection; // 假设connection已初始化

    public UserSearchService(Connection connection) {
        this.connection = connection;
    }

    public HomeVo findUserBySearchTerm(String searchTerm, boolean numresult, boolean uresult, boolean alphanumeric) throws SQLException {
        String columnToSearch;

        // 1. 根据搜索词类型确定要查询的列名
        if (searchTerm.contains(".com") && searchTerm.contains("@")) {
            columnToSearch = "EMAIL_ID";
        } else if (numresult) { // 假设numresult为true表示是手机号
            columnToSearch = "MOBILE_NUMBER";
        } else if (uresult || alphanumeric) { // 假设uresult或alphanumeric为true表示是用户名
            columnToSearch = "USER_NAME";
        } else {
            // 如果无法判断类型，可以抛出异常或设置默认列
            throw new IllegalArgumentException("无法识别的搜索词类型");
        }

        // 2. 安全地构建SQL查询字符串
        // 确保 columnToSearch 只能是预定义的合法列名，防止SQL注入
        // 这是一个简单的白名单检查，更复杂的应用可能需要更严格的验证
        if (!isValidColumn(columnToSearch)) {
            throw new IllegalArgumentException("非法的查询列名: " + columnToSearch);
        }

        String sql = "SELECT ID, FIRST_NAME, LAST_NAME, EMAIL_ID, MOBILE_NUMBER, QUALIFICATION, STATE, GENDER, USER_NAME, DOB FROM SignUpTable WHERE " + columnToSearch + " = ?";
        PreparedStatement psmt = null;
        ResultSet rs = null;
        HomeVo vo = null;

        try {
            psmt = connection.prepareStatement(sql);
            psmt.setString(1, searchTerm); // 现在searchTerm作为值被参数化

            // 调试：打印预处理语句，查看实际执行的SQL
            System.out.println("Executing SQL: " + psmt.toString());

            rs = psmt.executeQuery();

            if (rs.next()) { // 使用if而不是while，因为通常只期望返回一个用户
                vo = new HomeVo();
                vo.setId(rs.getInt("ID"));
                vo.setFirstName(rs.getString("FIRST_NAME"));
                vo.setLastName(rs.getString("LAST_NAME"));
                vo.setEmailId(rs.getString("EMAIL_ID"));
                vo.setNumber(rs.getString("MOBILE_NUMBER"));
                vo.setQualification(rs.getString("QUALIFICATION"));
                vo.setState(rs.getString("STATE"));
                vo.setGender(rs.getString("GENDER"));
                vo.setUserName(rs.getString("USER_NAME"));
                vo.setDob(rs.getString("DOB"));
            }
        } finally {
            // 关闭资源
            if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
            if (psmt != null) try { psmt.close(); } catch (SQLException ignore) {}
        }
        return vo;
    }

    // 辅助方法：校验列名是否合法，防止SQL注入
    private boolean isValidColumn(String column) {
        return "EMAIL_ID".equals(column) || "MOBILE_NUMBER".equals(column) || "USER_NAME".equals(column);
    }

    // 假设 HomeVo 类已经定义
    static class HomeVo {
        private int id;
        private String firstName;
        private String lastName;
        private String emailId;
        private String number;
        private String qualification;
        private String state;
        private String gender;
        private String userName;
        private String dob;

        // Getters and Setters
        public int getId() { return id; }
        public void setId(int id) { this.id = id; }
        public String getFirstName() { return firstName; }
        public void setFirstName(String firstName) { this.firstName = firstName; }
        public String getLastName() { return lastName; }
        public void setLastName(String lastName) { this.lastName = lastName; }
        public String getEmailId() { return emailId; }
        public void setEmailId(String emailId) { this.emailId = emailId; }
        public String getNumber() { return number; }
        public void setNumber(String number) { this.number = number; }
        public String getQualification() { return qualification; }
        public void setQualification(String qualification) { this.qualification = qualification; }
        public String getState() { return state; }
        public void setState(String state) { this.state = state; }
        public String getGender() { return gender; }
        public void setGender(String gender) { this.gender = gender; }
        public String getUserName() { return userName; }
        public void setUserName(String userName) { this.userName = userName; }
        public String getDob() { return dob; }
        public void setDob(String dob) { this.dob = dob; }
    }
}