Trying to query database using PreparedStatement,

问题:

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;
}

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;

}

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;
}

Where query equals "SELECT * FROM contacts WHERE account_name = '" + 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.

回答1:

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.

回答2:

I recommend you don't do this.

Instead of returning your ResultSet, you should immediately loop through it and pull all the results out and hand them over to something else. It should then be closed.

Passing around things like Statements, ResultSets, and Connections is BOUND to cause a resource leak eventually, not to mention creating unneeded complexity in your code trying to avoid those leaks. They should all be opened, used, and immediately closed in a finally block to avoid any leaks. Anything returned from such a method should NOT be tied to some DB object with a lifecycle and associated resources.

You don't have to take this advice, but I truly believe your life will be easier if you do.

回答3:

Unless you've already tried them and they don't have the features you need, I recommend against using JDBC directly. Instead just get Hibernate, iBatis (retired, but still functional) or myBatis(follow on to iBatis) and let them handle the JDBC stuff. It is likely that these ORM tools will do a better job of managing the resources than a homespun project.