How to stream large Blob from database to application using JPA?

Question

I have JPA a entity class that contains a blob field like this:

@Entity
public class Report {
    private Long id;
    private byte[] content;

    @Id
    @Column(name = "report_id")
    @SequenceGenerator(name = "REPORT_ID_GENERATOR", sequenceName = "report_sequence_id", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "REPORT_ID_GENERATOR")
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    @Lob
    @Column(name = "content")
    public byte[] getContent() {
        return content;
    }

    public void setContent(byte[] content) {
        this.content = content;
    }
}

and I have some large data (over 3 gigs) inserted on a record of it in database (using a DBMS procedure).

Application users are supposed to be able to download the content of these records, so I have implemented a method that streams the fetched result to clients browsers.

Problem is, since JPQL select queries tend to fetch entire object from DB first and then give it to application, whenever I try to access this record using JPA I get unable to allocate enough memory exception.

I have seen some solution for this problem using JDBC connections that try to stream data from database, but I could not any JPA specific solution for it.

Does anyone have any clue how to solve should I solve this problem?

Answer 1

I solved the problem in following manner, note that this solution may only work on hibernate implementation of JPA.

1. First I acquired a hibernate session from entity manager
2. Then I created a prepared statement for selecting the blob using connection extracted from the session
3. Then I generated a input stream from result set of prepared statement.

Here is the DAO class that is used to stream the content:

@Repository
public class ReportDAO{

private static final Logger logger = LoggerFactory.getLogger(ReportDAO.class);

@PersistenceContext
private  EntityManager entityManager; 

//---streamToWrite is the stream that we used to deliver the content to client
public void streamReportContent(final Long id, final OutputStream streamToWrite) {
        try{
            entityManager=entityManager.getEntityManagerFactory().createEntityManager();
            Session session = entityManager.unwrap(Session.class);
            session.doWork(new Work() {
                @Override
                public void execute(Connection connection) throws SQLException
                {
                    PreparedStatement stmt=connection.prepareStatement("SELECT content FROM report where id=?");
                    stmt.setLong(1,id);
                    ResultSet rs = stmt.executeQuery();
                    rs.next();
                    if(rs != null)
                    {
                        Blob blob = rs.getBlob(1);
                        InputStream input = blob.getBinaryStream();
                        byte[] buffer = new byte[1024];

                        try {
                            while (input.read(buffer) > 0) {
                                String str = new String(buffer, StandardCharsets.UTF_8);
                                streamToWrite.write(buffer);
                            }

                            input.close();

                        } catch (IOException e) {
                            logger.error("Failure in streaming report", e);
                        }



                        rs.close();
                    }

                }
            });
        }
        catch (Exception e){
            logger.error("A problem happened during the streaming problem", e);
        }
}

Answer 2

This is a late answer, but for those still looking for a solution, I found a good article by Thorben Janssen on Thoughts on Java blog. The drawback, it's Hibernate specific, but you seem to use it anyway. Basicly the solution is to use java.sql.Blob data type attributes in your entity

@Entity
public class Book {

    @Id
    @GeneratedValue
    private Long id;

    private String title;

    @Lob
    private Clob content;

    @Lob
    private Blob cover;

    ...
}

And then you use Hibernate’s BlobProxy, which provides an OutputStream. But take a look at the article here

Answer 3

Since you are using relational DB storing a large (gigabyte) data file in a database as a BLOB is not good practice . Instead what common practice is Data itself is stored in form of the file on Server (may be FTP) and metadata (path of file along with server etc.) regarding this is stored in Data Base column . In this case it becomes even easy to stream this data to client.

Answer 4

You should take a look at the community project Spring Content. This project gives you a Spring Data-like approach to content. It is to unstructured data (documents, images, videos, etc), what Spring Data is to structured data. You could add it with something like the following:-

pom.xml (Spring Boot starters also available)

   <!-- Java API -->
   <dependency>          
      <groupId>com.github.paulcwarren</groupId>
      <artifactId>spring-content-jpa</artifactId>
      <version>0.9.0</version>
   </dependency>
   <!-- REST API -->
   <dependency>
      <groupId>com.github.paulcwarren</groupId>
      <artifactId>spring-content-rest</artifactId>
      <version>0.9.0</version>
   </dependency>

Configuration

@Configuration
@EnableJpaStores
@Import("org.springframework.content.rest.config.RestConfiguration.class") <!-- enables REST API)
public class ContentConfig {

   <!-- specify the resource specific to your database --> 
   @Value("/org/springframework/content/jpa/schema-drop-h2.sql")
   private ClasspathResource dropBlobTables;

   <!-- specify the resource specific to your database --> 
   @Value("/org/springframework/content/jpa/schema-h2.sql")
   private ClasspathResource createBlobTables;

   @Bean
   DataSourceInitializer datasourceInitializer() {
     ResourceDatabasePopulator databasePopulator =
            new ResourceDatabasePopulator();

     databasePopulator.addScript(dropBlobTables);
     databasePopulator.addScript(createBlobTables);
     databasePopulator.setIgnoreFailedDrops(true);

     DataSourceInitializer initializer = new DataSourceInitializer();
     initializer.setDataSource(dataSource());
     initializer.setDatabasePopulator(databasePopulator);

     return initializer;
   }
}

NB: this configuration is not needed if you use the Spring Boot starters.

To associate content, add Spring Content annotations to your account entity.

Example.java

@Entity
public class Report {

   // replace @Lob field with:

   @ContentId
   private String contentId;

   @ContentLength
   private long contentLength = 0L;

   // if you have rest endpoints
   @MimeType
   private String mimeType = "text/plain";

Create a "store":

ExampleStore.java

@StoreRestResource(path="reportContent")
public interface ReportContentStore extends ContentStore<Report, String> {
}

This is all you need to create REST endpoints @ /reportContent. When your application starts, Spring Content will look at your dependencies (seeing Spring Content JPA/REST), look at your ReportContentStore interface and inject an implementation of that interface for JPA. It will also inject a @Controller that forwards http requests to that implementation. This saves you having to implement any of this yourself.

So...

curl -X POST /reportsContent/{reportId} -F ‘data=@path/to/local/file’

will store the content of path/to/local/file in the database and associate it with the report entity whose id is reportId.

curl /reportContent/{reportId}

will fetch it again and so on...supports full CRUD.

There are a couple of getting started guides and videos here. The reference guide is here.

HTH

Answer 5

I had a similar problem like you where I needed to store a JSON in a field so when I used BLOB I was causing myself a lot of unnecessary headaches. You are using blob for a content kind of data, I respectfully advise you to use CLOB for data if it's in Character format.

to wrap my answer up, if you are using the ORACLE database(which is a database that always causes problems of speaking its language) use bellow format as a guide or best practice, which is based on oracle documentation itself, to solve your problem:

@Lob @Basic(fetch=LAZY)
@Column(name="REPORT")
protected String report;

Good luck!

Answer 6

Maybe you can compress your file with compression algorithms(like lossy and lossless compressions, Huffman, facebook's Zstandard) then store it on your database, and retrieve back by decompressing them.