Save file in mysql database using Hibernate

Question

I'm trying to figure out the 'correct', failsafe way to save/retrieve files in a mysql database using Hibernate. The solution has to work with files that are quite large, so minimizing the memory footprint would be important.

My solution, so far:

A model class containing a java.sql.Blob field, the getter annotated with "@Lob".

public static void main(String[] args) {
    DAOFactory factory = DAOFactory.getFactory();
    IResultExtraDAO resultExtraDAO = factory.getResultExtraDAO();
    factory.getResultExtraDAO().beginTransaction();

    ResultExtra resultExtra = new ResultExtra();
    LobHelper lh = HibernateUtil.getSession().getLobHelper();
    try {
        File file = new File("/3030.jpg");
        FileInputStream fis = new FileInputStream(file);
        Blob b = lh.createBlob(fis, fis.available());
        resultExtra.setFile(b);
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    resultExtraDAO.save(resultExtra);

    factory.getResultExtraDAO().commitTransaction();
}

Is this the correct way to do it? Whould there be a risk of running out of memmory if there are manny simultanious uploads and/or large files? Is there another solution that would be better?

Also, I'm using the 'Generic Data Access Object' pattern encapsule hibernate, so I don't like accessing the HibernateUtil directly as I do here, but so far I haven't figured out a good generic way to access it. Suggestions are welcome.

Answer 1

Storing the files as a Blob in your DB will work. However, this can become a bit of an issue as you start to get more and more such files in your DB. Depending on the volume of files that you expect, I would recommend that you consider not storing said files in your DB at all.

Perhaps consider storing them in a content repository, like Jackrabbit. These files are then accessible via a URL that tells you where they are in the repository.

So to summarise, if you really want the files in your DB then what you have sounds fine you may just want to ensure that you have Hibernate lazy-load these blobs especially if they can get quite big. However, I would recommend that you at least consider a content repository.