尝试使用PreparedStatement查询数据库时，ResultSet在不应该关闭时又重新关闭 [英] Trying to query database using PreparedStatement, ResultSet is coming back closed when it ought not to

问题描述

我正在尝试使用准备好的语句查询数据库.最初，我有这个:

I am trying to query my database using a prepared statement. Initially, I had this:

public ResultSet preparedQueryContactsWhereAccount(String accountName) throws SQLException {
    PreparedStatement statment = null;
    ResultSet rs = null;
    String statString = "SELECT * FROM contacts WHERE account_name = ?";

    try {
        statment = mConn.prepareStatement(statString);
        statment.setString(1, accountName);
        rs = statment.executeQuery();
    } catch(SQLException e) {
        e.printStackTrace();
    }
    if(statment != null) {
        statment.close();
    }
    return rs;
}

但是，这将返回一个关闭的ResultSet.我做了一些阅读，发现有时候关闭一条语句时，其ResultSets也可以关闭.所以我尝试了这个:

However, this would return a closed ResultSet. I did some reading and found that sometimes when a statement is closed its ResultSets can be closed too. So I tried this:

public MasterEntry preparedQueryContactsWhereAccount(String accountName, MasterEntry entry) throws SQLException, IllegalAccessException {
    PreparedStatement prepStat = null;
    ResultSet rs = null;
    String statString = "SELECT * FROM contacts WHERE account_name = ?";

    try {
        prepStat = mConn.prepareStatement(statString);
        prepStat.setString(1, accountName);
        rs = prepStat.executeQuery();
    } catch(Exception e) {
        e.printStackTrace();
        System.err.println("Statement failed.");
    }
    if(rs.isClosed()) {
        System.out.println("fail");
        throw new IllegalAccessException("closed");
    }
    else {
        entry.setmFirstName(rs.getString("first_name"));
        entry.setmLastName(rs.getString("last_name"));
        entry.setmEmail(rs.getString("email_address"));
        rs.close();
        prepStat.close();
    }
    return entry;

}

只是稍微重新组织了一下流程，所以ResultSet数据都可以在方法内部进行处理.这将引发您在此处看到的IllegalAccessException，并且ResultSet已关闭.

Just reorganized the process a bit so the ResultSet data can all be handled inside the method. This throws that IllegalAccessException you can see there, the ResultSet is closed.

我尝试以更简单的方式进行相同的查询(这种查询不太安全，无法处理撇号等字符):

I tried doing this same query the simpler way (which isn't as secure and can't handle characters such as apostrophes):

public ResultSet makeQuery(String query) {
    ResultSet rs = null;
    try {
        rs = mStat.executeQuery(query);
    } catch (Exception e) {
        e.printStackTrace();
        System.out.println("Problem in query: " + query);
    }
    return rs;
}

查询等于"SELECT * FROM contacts WHERE account_name = '" + accountName + "'"

与我在prepareQuery方法中使用的accountName字符串相同.这种方式可以正常工作，当然任何带有撇号的accountName都会失败.我真的很想用准备好的语句来做到这一点，但我只是想不通.有什么建议吗?非常感谢.

Same accountName String as I used in the preparedQuery method. This way works fine, except of course any accountName with an apostrophe in it will fail. I would really like to do this with a prepared statement but I just can't figure it out. Any advice? Thanks a lot.

推荐答案

看起来您的代码很好，但是您缺少rs.next()命令.您正在尝试读取尚未设置为特定行的结果集，因此您将收到IllegalStateException.

It looks like your code is fine, however you are missing the rs.next() command. You are attempting to read a result set that has not been set to a specific row, and thus you are receiving an IllegalStateException.

这篇关于尝试使用PreparedStatement查询数据库时，ResultSet在不应该关闭时又重新关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！