Java Database Design – Best Way to Lazy Load Blob from Database

databasedesignjava

I have a database table that stores uploaded files (excel files) in a blob field. Each record contains the uploader, upload-time, the upload ID (primary key) and of-course, the blob itself.

At some point, I need to get that data from the database do something with it. Currently, I have a POJO which just mirrors the table structure and stores the file in a byte array, ie;

class Upload {
    private String ID;
    private ByteArray[] data;
    //Other fields and methods ommited for brevity
}

As I am sure you will agree, this leaves the application open to memory issues if the file is particularly large. In reality, this file is unlikely to get so large as to cause a problem, but this definitely can't be considered a good design.

I have been trying to find what the best practices are for lazy-loading blob data but I am having trouble finding material that isn't about using particular application frameworks.

I am thinking I need to lose the 'data' field and just have a method, ie. getData(), that starts a new DB transaction to retrieve the blob returns an InputStream to it? See below for example, please let me know if there is a better way to do this..

public InputStream getData(){
    //Construct and execute SQL query.
    Blob blob = rs.getBlob(1);
    return blob.getBinaryStream();
}

Best Answer

the other thing you can do is caching the value after the first load so you don't need to keep going back to the data base

public InputStream getData(){
    if(data==null){
        //Construct and execute SQL query.
        Blob blob = rs.getBlob(1);
        data = blob.getBinaryStream();
    }
    return data;
}

if you expect these blobs to be very large and want to be able to reclaim space while still keeping the record itself in memory you can use a WeakReference for the blob

private WeakReference<ByteArray[]> data;

public InputStream getData(){
    ByteArray[] local = data!=null?data.get():null;
    if(local==null){
        //Construct and execute SQL query.
        Blob blob = rs.getBlob(1);
        local = blob.getBinaryStream();
        data = new WeakReference<ByteArray[]>(local);
    }
    return local;
}
Related Topic