Spring MVC + Oracle LOBs + Streaming

Question

I have to send a binary stream of a blob by means of a ServletOutputStream.

I'm using the following technologies and software: Oracle 11, WebSphere 7, Springframework 2.5.5, Hibernate 3.3.SP1.

There are two Oracle databases. The first one contains tables for description of documents I have to transfer, and the second one - content of the documents.

I also have configured support for XA datasources in WebSphere and JtaTransactionManager in spring.

I obtain a reference to a document and content itself in one transaction.

JDBC specification tells us that LOBs are transactional objects and portable applications should use such objects within transactions.

And I have the following questions:

1. Is it legal to retrieve BLOB's input stream within transactional method and pass it to the top-level non-transactional method? Something like this:

    @Transactional
    public InputStream getContent(Long docId) {
        Blob blob = getBlob(...);
        return blob.getBinaryStream();
    }

    public ModelAndView handleRequestInternal(HttpServletRequest req, HttpServletResponse resp) {
       Long docId = ServlerRequestUtils.getRequiredLongParameter(req);
       InputStream is = service.getContent(docId);
       copy(is, resp.getOutputStream());
       return null;
    }

2. If it is not legal how to transfer BLOB's binary stream to the end user if the content of the BLOB is large enough and there is preconfigured transaction timeout in the application server? Do I have to handle transactions manually and set the timeout to zero (transaction never time out)?

3. What is the best way to transfer BLOB's binary stream to the end user in such a case?

Answer 1

You're right in that returning's the BLOB's stream out of your tx method is not a good idea... it might work under certain circumstances, database depending, but it's risky.

The solution is to turn the problem inside out. Pass the servlet's OutputStream in to your transactional method. This avoid the transaction issue, and keeps your stream handling in one place:

@Transactional
public void getContent(Long docId, OutputStream outputStream) {
    Blob blob = getBlob(...);
    InputStream blobStream = blob.getBinaryStream();
    copy(blobStream, outputStream);
    blobStream.close(); // ignoring the usual stream closing try/catch stuff for brevity
}

---

public ModelAndView handleRequestInternal(HttpServletRequest req, HttpServletResponse resp) {
   Long docId = ServlerRequestUtils.getRequiredLongParameter(req);
   service.getContent(docId, resp.getOutputStream());
   return null;
}