Jersey - Returning stream from ResultSet

Question

I have a table with a CLOB field (MySQL MediumText).

I want to return an input stream to that CLOB. My resource code looks like this:

@GET
public StreamingOutput getAsStream(int id) {
  try {
    // prepare the statement object
    ResultSet rs = stmt.executeQuery();
    if (rs.next()) return new StreamingOutput() {
      public void write(OutputStream outputStream) throws ... {
        copy(rs.getBinaryStream(1), outputStream);
      }
    }
  }
  finally {
    rs.close();
    stmt.close();
    connection.close();
  }
}

Which doesn't look right. The code closes the db resources (resultset, statement and connection) before Jersey has a chance to write the stream to the response.

I can close the db resources in the StreamingOutput.write method. But it also does not feel right - I'm letting some outside container close my resources.

The last idea I can think of is to read the entire stream into memory and then send it. I don't want to do it of course.

So, anyone got any better idea?

I checked Return a file using Java Jersey which didn't help much.

Thanks, Doron

score 0 · Accepted Answer · answered Feb 19 '12 at 10:28

0

Yes, your code as shown will not work. You created an anonymous inner class in your method and then handed it as the return object to the container. The write object in the new class doesn't actually get invoked until sometime after your method has already completed, at which time of course you have already closed up all your connections.

Conceptually, trying to stream directly from the database is probably not a good idea. You don't want to hold open a connection for the amount of time the client decides to take in consuming the input stream. The best thing to do is read the data into memory, and then stream that data to the client.

answered Feb 19 '12 at 10:28

Perception

79,279
19
185
195

Of course my code shouldn't work, that's why I've posted the question here :-). BTW, the code does work. I'm guessing its because I'm using a connection pool so the connection doesn't really close. Anyway, streaming blobs sounded as a common enough operation to me. I thought it might be supported by Jersey. But I guess your argument about keeping the connection open for a period that actually depends on the client wins the case. – daramasala Feb 22 '12 at 09:39
@Perception, but what if the data from database is a huge dataset, say around a million records, then I guess streaming directly from the database will be good ? – Abdullah Shaikh Apr 30 '12 at 07:52
Returning millions of records in one shot is just not a use case for any system I've ever written. ***But*** lets say you run into that case where you have to, then you should page through the data (using limit) as opposed to trying to stream it all at once. – Perception Apr 30 '12 at 10:34

Jersey - Returning stream from ResultSet

1 Answers1