Java数据库查询ResultSet出现Invalid column index异常求助

问题详情 #

循环查询结果集ResultSet逐个获取邮箱以发送邮件时，遇到如下错误：

Caused by: java.sql.SQLException: Invalid column index

查询相关代码：

public List<UserDto> getEmail() {
    
    Connection connection = null;
    
    PreparedStatement preparedStatement = null;
    
    ResultSet searchResultSet = null;
    
    try {
    
        connection = getConnection();
    
        preparedStatement = connection.prepareStatement(
                        "SELECT EMAIL FROM USER WHERE USER.U_SEQ IN ('1','650')");
                
        searchResultSet = preparedStatement.executeQuery();
    
        return getEmail(searchResultSet);
    
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        try {
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}


private List<UserDto> getEmail(ResultSet searchResultSet) throws SQLException {
    List<UserDto> result = new ArrayList<UserDto>();

    UserDto userDto = null;
    int index = 1;
    while (searchResultSet.next()) {
        userDto = new UserDto();

        userDto.setEmailAddress(searchResultSet.getString(index));
        result.add(userDto);
        index++;
     }
     return result;
}

调用代码：

Delegate delegate = new Delegate();

UserDto userDto = new UserDto();

List<UserDto> users = delegate.getEmail();

delegate.sendNotification("****", "****", users.toString(), "", "",
                   "", body);

问题原因 #

SQL语句仅查询了EMAIL一列，但getEmail方法中每次循环都会让index自增。第一次循环index=1能正常取值，第二次循环index=2时，结果集不存在第二列，直接触发Invalid column index异常。

修复方案 #

方案1：固定使用列索引1 #

移除index的自增逻辑，每次循环都用索引1获取唯一的EMAIL列：

private List<UserDto> getEmail(ResultSet searchResultSet) throws SQLException {
    List<UserDto> result = new ArrayList<UserDto>();

    UserDto userDto = null;
    while (searchResultSet.next()) {
        userDto = new UserDto();
        userDto.setEmailAddress(searchResultSet.getString(1));
        result.add(userDto);
     }
     return result;
}

方案2：使用列名获取（推荐） #

直接通过列名EMAIL取值，代码可读性更强，也不会因为列顺序变化出错：

private List<UserDto> getEmail(ResultSet searchResultSet) throws SQLException {
    List<UserDto> result = new ArrayList<UserDto>();

    UserDto userDto = null;
    while (searchResultSet.next()) {
        userDto = new UserDto();
        userDto.setEmailAddress(searchResultSet.getString("EMAIL"));
        result.add(userDto);
     }
     return result;
}

额外优化：资源关闭的空指针防护 #

原代码的finally块中，如果preparedStatement初始化失败（比如连接获取失败），调用close()会触发空指针异常，建议增加null判断：

finally {
    try {
        if (searchResultSet != null) {
            searchResultSet.close();
        }
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (connection != null) {
            connection.close();
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

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