Stream the data content directly from database to the HTTP

Question

Right now we are holding file in our postgresql database and mapping that content using byte[] field in our entity. I need to investigate if we could stream the content data direct from the database to the HTTP output stream, and do the same thing in opposite way so stream binary data from HTTP into database using jpa Blob data type. I know that Blob has methods getBinaryStream and setBinaryStream so its may work, and we do not need hold data into memory.

What I am concern are database transaction, because we are mapping entity into DTO, and the second thing is broken Http request and data may be lost in some point.

Is there are anybody who had any experience with that solution ?

Maybe this http://stackoverflow.com/questions/42950938/proper-hibernate-mapping-for-lob-in-hibernate-pojo-we-are-using-hibernate-mapp/42951098#42951098 can help you. Angelo — Angelo Immediata, May 11 '17 at 10:23
I believe that should be possible. Because Spring-data supports Streaming of queryOperation (https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-streaming) and you can stream your httpResponse as well (http://stackoverflow.com/questions/15283347/stream-directly-to-response-output-stream-in-handler-method-of-spring-mvc-3-1-co) — pvpkiran, May 11 '17 at 10:24

score 3 · Answer 1 · answered Feb 05 '18 at 13:48

Solution for stream-reading data from BLOBs:

Existing BLOB data are streamed by passing OutputStream (provided by the servlet container) into transactional method which writes entity blob data to the stream from inside transaction. Note that the content type of response is set before writing the data.

Entity class:

public class Attachment {
   private java.sql.Blob data;
   public java.sql.Blob getData() { return data; }
}

Service method:

@Transactional(readOnly = true)  
public void copyContentsTo(long attachmentId, OutputStream outputStream) throws IOException {
  Attachment dbAttachment = attachmentRepository.findOne(attachmentId);

  try (InputStream is = dbAttachment.getData().getBinaryStream()) {
    IOUtils.copy(is, outputStream);

  } catch (SQLException e) {
    throw new ParameterException("Cannot extract BLOB for attachment #" + attachmentId, e);
  }
}

REST API Spring Controller method:

@GetMapping(value = "/api/project-attachment/{attachment-id}/content")
@ResponseStatus(HttpStatus.OK)
public void getAttachmentContent(
    @PathVariable("attachment-id") long attachmentId,
    HttpServletResponse response, 
    OutputStream stream) throws IOException {

    response.setContentType(getMime(attachmentId));
    attachmentService.copyContentsTo(attachmentId, stream);
}

Paul Warren · Answer 2 · 2018-03-29T16:09:20.963

Lucasz, Spring Content for JPA does exactly what you are asking. Designed to make it really easy to create Spring applications that handle content (documents, images, video's etc). It supports a range of backend stores one of which being relational DBs and obviously they uses BLOBs.

This JPA module will stream uploaded files from the request input stream directly to the database and vice versa thus it never stores the entire file in-memory which would obviously cause problems with very large files.

It would save you from having to write ANY of the code in @tequilacat's answer.

Probably worth a look.

Stream the data content directly from database to the HTTP

2 Answers2

Entity class:

Service method:

REST API Spring Controller method: