CODEWITHSUNDEEP
javamultithreadingexceptionresultset
OldCurmudgeon
OldCurmudgeon

Reputation: 65859

RuntimeException - Exhausted Resultset - How can it happen?

Code:

    // Start the query.
    final ResultSet r = prepared.executeQuery();
    try {
      // Returning false from oneRow will stop the process.
      boolean goOn = true;
      while (r.next() && goOn) {
        log.trace("Result: {}", toString(r));
        // We did do at least one.
        ran = true;
        goOn = oneRow(r);
      }
    } finally {
      try {
        // Always remember to close the ResultSet.
        r.close();
      } catch (SQLException ex) {
        log.error("Close failed", ex);
      }
    }

    // Handle one row.
    public boolean oneRow(ResultSet r) throws Exception {
      String xml1 = r.getString(1);
      String xml2 = r.getString(2);
      if (xml1 == null && xml2 == null) {
        // Probably compressed.
        xml1 = decompress(r, 3);
        xml2 = decompress(r, 4);
      }
      return false;
    }

  private static String decompress(ResultSet rs, int col) throws SQLException {
    // Exception gets thrown here!!! ???
    final InputStream compressed = rs.getBinaryStream(col);
    ...

As you can see this is not the obvious problem of not calling ResultSet.next(). Not only that but I have called getString in the ResultSet twice already, it is the third time that breaks it.

Note that this is an occasional issue, this code works fine most of the time.

The query looks something like:

"SELECT P1.XML XML1, "
      + "P2.XML XML2, "
      + "P1.CompressedXML CompressedXML1, "
      + "P2.CompressedXML CompressedXML2 "
      + "FROM Table1 P1 "
      + "LEFT JOIN Table2 T2 ON T2.ID = P1.ID "
      + "LEFT JOIN Table1 P2 ON P2.ID = T2.Item_Code "
      + "WHERE P1.ID = ?"

I realise this is a rather incestuous query but as I stated - this all works fine most of the time.

Added

Prompted by the answer posted by user1933888 it occurs to me that since I am using a home-grown connection-pool is it possible for a prepared statement to interfere with itself between two threads sharing different connections?

I am confident that the same connection will never be in use by two threads at the same time but the prepared statement could be shared as it should reside in the database.

Upvotes: 0

Views: 477

Answers (1)

beny23
beny23

Reputation: 35048

As your PreparedStatement is linked to a connection, if you share the PreparedStatement between threads you would end up sharing a connection and that definitely will cause problems.

It is true that statements are generally cached in the DB to avoid having to reparse it but you'd still need one PreparedStatement object per thread to avoid confusing the DB driver as to what thread wants what.

In general, I would avoid a home brew connection pool considering that in general AppServers do the pooling for you or (if you're not in an AppServer) there are plenty of connection pool libs out there, so why reinvent the wheel.

In general:

  • you can shared DataSources between concurrent threads
  • you should NOT share Connection, PreparedStatement or ResultSet objects between concurrent threads

The connection pool will ensure that non-concurrent threads can reuse Connections and the JDBC driver will internally cache Statements

Hope that makes sense.

Upvotes: 1

Related Questions