Save blob using a stream from EJB to database (in a memory efficient way)

Question

I want to save large attachments (500Mb, maybe, if possible, even > 2Gb) in a database. I know the pros and cons of doing so are discussed very frequently, but that's not the focus of my question.

The conventional way of storing Blob fields in EJB3 with JPA is to use a code as following:

@Lob
private byte[] data;

This becomes a problem when handling huge data sources, because the whole byte array is saved in memory.

I tried to change it to Blob:

@Lob
private Blob data;

But this results in the same problem. When calling

// session: Hibernate session.
// Actually, I'd like to stay with JPA's abstraction and not
// reference Hibernate directly, if possible
Blob data = session.createBlob(inputStream, lengthOfStream);

the createBlob method creates a byteArray from the inputStream.

Because of the ORM mapping, I also wonder how to handle the insertion of the data. One idea was to make an entity variable

@Lob
private byte[] data;

That I will never use. This way, the database schema get's build. But because the @Lob annotation is lazy, it will not bloat my memory.

And then write

entityManager.persist(dataObject);
// The following lines are _completely_ imaginatory!!
query.prepare("update DataObject d set d.data = :dataInputStream where d = :dataObject");
query.setProperty("dataObject", dataObject);
query.setProperty("dataInputStream", someDataInputStream);

One solution I stumbled across looks nice, but doesn't use JPA: Grooviest way to store a BLOB in a database?

Does anybody have an advice on how to do this?

Answer 1

@Lob annotation can be applied to a Serializable object. You could then declare :

@Lob
private MySmartLOB data;

and

public class MySmartLOB implements Serializable {
    private void writeObject(java.io.ObjectOutputStream out)
            throws IOException {
        // tranfer data from local storage to 'out'
    }
    private void readObject(java.io.ObjectInputStream in)
            throws IOException, ClassNotFoundException {
        // tranfer data from 'in' to local storage
    }
}

This could work, hoping that the underlying JPA implementation is smart enough to supply an ObjectOutputStream directly piped to the JDBC stream and not some sort of ugly ByteArrayOutputStream.