JDBC ResultSet closed in Java after several iterations

Question

I am having a problem with a ResultSet being closed. What confuses me is that it works for a portion of the data and then closes. At first I thought it might be because of connection timeout but that doesn't seem the case.

This portion of the program pertains to comparing an .xlsx workbook to an already present SQL database and for lack of a better term merges/updates it.

First, in my CompareDatabase class I am calling a search function that searches an SQLite database for a specific string every 6 iterations.

int columnCount = 6;
dataPoint = dataPoint.replaceAll("Detail", "");
String[] temp = dataPoint.trim().split("\\s+");
System.out.println(Arrays.toString(temp));
for (String tempDataPoint : temp) {
    if ( columnCount == 6) {
         System.out.println(search(tempDataPoint, connection));
    }
    columnCount = 0;
    } else {
         columnCount++;
    }
}

This search function (also in the CompareDatabase class is then supposed to search for the value and return a String (was originally a Boolean but I wanted to see the output).

private String search (String searchValue, Connection connection) throws SQLException {
    PreparedStatement pStatement = null;
    pStatement = connection.prepareStatement("SELECT * FROM lotdatabase where (Vehicle) = (?)");
    pStatement.setString(1, searchValue);

    try (ResultSet resultSet = pStatement.executeQuery()){
        return resultSet.getString(1);
    }finally {
        close(pStatement);
    }
}

At the end you can see that the PreparedStatement is closed. The ResultSet should also be closed automatically (I read somewhere) but JDBC could possibly be being unreliable.

The Connection however is still open as it will be searching some 200+ strings and opening and closing that many times did not seem like a good idea.

These functions are called by my main class here: One is commented out since it will error out because of primary key violation.

public static void main(String[] args) {
    SQLDatabase sqlDatabase = new SQLDatabase();
    //sqlDatabase.convertToSQL("Database1.xlsx");
    sqlDatabase.compare("Database2.xlsx");
}

I have a suspicion that I am going about a bunch of this wrong (on the aspect of managing connections an such) and I would appreciate a reference to where I can learn to do it properly.

Also, being that PreparedStatement can only handle one ResultSet I don't see that being my issue since I close it every iteration in the for loop.

If more code or explanation is required please let me know and I will do my best to assist.

Thank you for taking the time to read this.

It calls the close function which in of itself is basically just `pStatement.close()` but it just makes it look a little prettier since I close many statements and all. — user2999980, Oct 16 '18 at 02:19
You are using `try-with-resources` so you shouldn't `close` yourself. — Scary Wombat, Oct 16 '18 at 02:22
True. Before though; even without the `try-with-resources` it would still close when I attempted to use `if` logic on the result set. Is there a proper way to do this? — user2999980, Oct 16 '18 at 02:32
Without knowing what your exceptions stacktrace looks like I can only guess. Consider using a DBCP for the connection and *close* it each time. — Scary Wombat, Oct 16 '18 at 02:34

score 0 · Answer 1 · answered Oct 16 '18 at 19:14

So after a bit more Googling and sleeping on it here is what worked for me.

The search function in compareDatabase changed to this:

private Boolean search (String searchValue, Connection connection) {
    PreparedStatement ps = null;
    try {
        ps = connection.prepareStatement("SELECT * FROM lotdatabase where " +
                "(Vehicle) = (?)");
        ps.setString(1, searchValue);

        ResultSet resultSet = ps.executeQuery();

        //The following if statement checks if the ResultSet is empty.
        if (!resultSet.next()){
            resultSet.close();
            ps.close();
            return false;
        }else{
            resultSet.close();
            ps.close();
            return true;
        }

    } catch (SQLException e) {
        e.printStackTrace();
    }
    return false;
}

And in the other function within compareDatabase I call the search function like this:

if (search(tempDataPoint, connection)) {
      System.out.println("MATCH FOUND: " + tempDataPoint);
}else {
      System.out.println("NOT FOUND: " + tempDataPoint);
}

This allows me to check the ResultSet and also be sure that it is closed.

JDBC ResultSet closed in Java after several iterations

1 Answers1