title: Spring JDBC connection pool and InputStream results
body: <p>I am writing a webservice that allows users to post files and then retrieve them at a URL (basically think of it as the RESTful Amazon S3).  The issue I came across was rather then return a byte[] from my Oracle query (Spring JDBC) I am returning an InputStream and then streaming the data back to the client in chunks.  This (IMO) is a much better idea since I put no size restriction on the file and I don't want 2GB byte arrays in memory.</p>
  
  <p>At first it seemed to work fine, but I ran into a case during heavy load that sometimes a Connection would get reused before the previous servlet could send the file.  It seems after the JDBC call that returned the InputStream, the Connection would be returned to the pool (Spring would call conn.close(), but not clear the associated ResultSet).  So if no other request was given that Connection then the InputStream would still be valid and could be read from, but if the Connection was given to a new request then the InputStream would be null and the previous request would fail.</p>
  
  <p>My solution was to create a subclass of InputStream that also takes a Connection as a constructor arg, and in the overridden <strong>public close()</strong> method also close the Connection.  I had to ditch the Spring JDBC and just make a normal PreparedStatement call, otherwise Spring would always return the connection to the pool.</p>
  
  <pre><code>public class ConnectionInputStream extends InputStream {
  
     private Connection conn;
     private InputStream stream;
  
     public ConnectionInputStream(InputStream s, Connection c) {
        conn = c;
        stream = s;
     }
  
     // all InputStream methods call the same method on the variable stream
  
     @Override
     public void close() throws IOException {
        try {
           stream.close();
        } catch (IOException ioex) {
            //do something
        } finally {
           try {
               conn.close();
           } catch (SQLException sqlex) {
               //ignore
           }
        }
     }
  } 
  </code></pre>
  
  <p>Does anyone have a more elegant solution, or see any glaring problems with my solution?  Also this code wasn't cut/paste from my actual code so if there is a typo just ignore it.</p>