Saving large files to a db using hibernate?

Hey all,
I'm attempting to save large files (about 1Mb) to a database using hibernate3. The files are being placed in the database as a backup and will be cached on the app server so I don't really need to worry about performance to much. I was hoping of avoiding the use of byte arrays due to memory restrcitions, and wanted to use streams similar to the spring imagedb sample app instead.

Currently I'm thinking of making a hibernate custom type that handles a java.io.File similar to the BlobByteArrayType... Can anyone offer any insight? Has this problem been solved for me?

Cheers,
Dan

Easy, here is the code for the File user type:

Code:

import org.springframework.jdbc.support.lob.LobHandler; import org.springframework.jdbc.support.lob.LobCreator; import org.hibernate.HibernateException; import javax.transaction.TransactionManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.PreparedStatement; import java.sql.Types; import java.io.*; public class FileType extends AbstractLobType { public FileType() { super(); } protected FileType(LobHandler lobHandler, TransactionManager jtaTransactionManager) { super(lobHandler, jtaTransactionManager); } /** * This method's signature has been modified to pass in the owner... */ protected Object nullSafeGetInternal(ResultSet resultSet, int index, Object owner, LobHandler lobHandler) throws SQLException, IOException, HibernateException { // todo: come up with a better way to generate a file name File file = new File(String.valueOf(owner.hashCode())); // todo: take advantage of the buffer, read in more than a byte per loop BufferedInputStream inputStream = new BufferedInputStream(lobHandler.getBlobAsBinaryStream(resultSet, index)); OutputStream outputStream = new BufferedOutputStream(new FileOutputStream(file)); int foo = -1; while ((foo = inputStream.read()) != -1) { outputStream.write(foo); } outputStream.flush(); outputStream.close(); inputStream.close(); return file; } protected void nullSafeSetInternal(PreparedStatement statement, int index, Object value, LobCreator lobCreator) throws SQLException, IOException, HibernateException { // check the file length, it can't be greater than Integer.MAX_VALUE because we are going to cast the length // down to an int later if (((File) value).length() >= Integer.MAX_VALUE) throw new HibernateException("File size exceeds " + Integer.MAX_VALUE + " in length."); BufferedInputStream inputStream = new BufferedInputStream(new FileInputStream((File) value)); lobCreator.setBlobAsBinaryStream(statement, index, inputStream, (int) ((File) value).length()); } public int[] sqlTypes() { return new int[]{Types.BLOB}; } public Class returnedClass() { return File.class; } public boolean isMutable() { return true; } }

This will only work with the latest (1.2) code in CVS. I've also modified the AbstractLobType as the nullSafeGet method doesn't pass the owner to nullSafeGetInternal method...

Also need to come up with a way to remove the temp file created in the nullSafeGetInternal method...

Looks good.

I'm going to need to re-implement the logic found in AbstractLobType to get access to the owner parameter. I'd presumed that the missing parameter on the nullSafeGetInternal method was a bug, but it's been around for a while. I'll post a new version when I've fixed it up...

Cheers,
Dan

Here is the updated implementation. The major problem at the moment is thread safety and I'm interested in hearing what people suggest. The problem lies in the nullSafeGetInternal metod, if two threads try and read the same record from the DB all hell will break loose. I'm thinking of either adding some random number to the end of the owner.hasCode() value or creating a cache (although that would cause problems if the record is updated by something other than this class).

Code:

/* * Copyright 2005 the original author or authors. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package ...; import org.springframework.jdbc.support.lob.LobHandler; import org.springframework.jdbc.support.lob.LobCreator; import org.springframework.orm.hibernate3.support.AbstractLobType; import org.hibernate.HibernateException; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import javax.transaction.TransactionManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.PreparedStatement; import java.sql.Types; import java.io.*; /** * Hibernate UserType implementation for files that get mapped to BLOBs. Retrieves the LobHandler to use from * LocalSessionFactoryBean at config time. * Can also be defined in generic Hibernate mappings, as DefaultLobCreator will work with most JDBC-compliant * database drivers. In this case, the field type does not have to be BLOB: For databases like MySQL and MS SQL * Server, any large enough binary type will work. * This UserType creates a temporary file using {@link #createTemporaryFile(java.io.File, java.sql.ResultSet, String[], Object)} * method. By default the file name is generated using the value returned from {@link Object#hashCode()} on the owner. * This can be modified by extending this class and re-implementing the * {@link #createTemporaryFile(java.io.File, java.sql.ResultSet, String[], Object)} method. * * Based on org.springframework.orm.hibernate3.support.BlobByteArrayType by Juergen Hoeller. * * @author Dan Washusen * @version $Id: FileType.java,v 1.1 2005/03/24 23:37:55 dan Exp $ */ public class FileType extends AbstractLobType { private static final Log log = LogFactory.getLog(FileType.class); /** * Constructor used by Hibernate: fetches config-time LobHandler and * config-time JTA TransactionManager from LocalSessionFactoryBean. * * @see org.springframework.orm.hibernate3.LocalSessionFactoryBean#getConfigTimeLobHandler * @see org.springframework.orm.hibernate3.LocalSessionFactoryBean#getConfigTimeTransactionManager */ public FileType() { super(); } /** * Constructor used for testing: takes an explicit LobHandler * and an explicit JTA TransactionManager (can be null). */ protected FileType(LobHandler lobHandler, TransactionManager jtaTransactionManager) { super(lobHandler, jtaTransactionManager); } /** * Creates a temporary file using the {@link #createTemporaryFile(java.io.File, java.sql.ResultSet, String[], Object)} * method and then writes the contents of the blob to the the temporary file. * @param resultSet a JDBC result set * @param columns the column names * @param owner the containing entity * @param lobHandler the LobHandler to use * @todo This class will run into problems in a threaded environment, two threads creating the same file... * @todo This could be a lot more efficent when writing the temporary file */ protected Object nullSafeGetInternal(ResultSet resultSet, String[] columns, Object owner, LobHandler lobHandler) throws SQLException, IOException, HibernateException { // we only handle one column, so panic if it isn't so if (columns == null || columns.length != 1) throw new HibernateException("Only one column name can be used for the " + getClass() + " user type"); // check the temporary directory File tempDirectory = checkTempDirectory(resultSet, columns, owner); // delegate to the createTemporaryFile method to create file File file = createTemporaryFile(tempDirectory, resultSet, columns, owner); // write the contents of the file to disk BufferedInputStream inputStream = new BufferedInputStream(lobHandler.getBlobAsBinaryStream(resultSet, columns[0])); OutputStream outputStream = new BufferedOutputStream(new FileOutputStream(file)); // we really should read and write more than one byte at a time... int foo = -1; while ((foo = inputStream.read()) != -1) { outputStream.write(foo); } outputStream.flush(); outputStream.close(); inputStream.close(); return file; } /** * Checks for the existance of the temporary directory specifed by the java.io.tmpdir system property, attempting * to create the directory if it doesn't exist. * @param resultSet a JDBC result set * @param columns the column names * @param owner the containing entity * @return the temporary directory * @throws IOException */ protected File checkTempDirectory(ResultSet resultSet, String[] columns, Object owner) throws IOException { File tempDirectory = new File(System.getProperty("java.io.tmpdir")); log.debug("Using temporary directory " + tempDirectory.getAbsolutePath()); if (!tempDirectory.exists()) { log.info("Creating temporary directory " + tempDirectory.getAbsolutePath()); tempDirectory.createNewFile(); } return tempDirectory; } /** * Creates a temporary file to write the data to. By default the method generates a file name based on * the hash code of the owner using the tempDirectory parameter as the parent. * If the file exists it will be deleted. The file is then marked for deletion when the JVM exits. * If you wish to change the default behavior, this is the method to override.</p. * @param tempDirectory The directory to create the temporary files * @param resultSet a JDBC result set * @param columns the column names * @param owner the containing entity * @return The tempoary file * @throws HibernateException if the temporary file exists and can't be deleted */ protected File createTemporaryFile(File tempDirectory, ResultSet resultSet, String[] columns, Object owner) { String filename = String.valueOf(owner.hashCode()); File file = new File(tempDirectory, filename); log.debug("Creating temporary file at " + file.getAbsolutePath()); // delete the file if it already exists if (file.exists()) { log.debug(file.getAbsolutePath() + " is in the way, removing..."); if (!file.delete()) { throw new HibernateException("File " + file.getAbsolutePath() + " was in the way and couldn't be deleted"); } } // as the file is only a temporary representation of a database column // we are going to mark the file for deleting when the JVM exists file.deleteOnExit(); return file; } /** * Creates a buffered {@link FileInputStream} from the file provided as the value parameter. * @param statement the PreparedStatement to set on * @param index the statement parameter index * @param value the file * @param lobCreator the LobCreator to use * @throws SQLException if thrown by JDBC methods * @throws FileNotFoundException If the file specified by the value parameter could not be found * @throws HibernateException if the file is bigger than {@link Integer.MAX_VALUE} */ protected void nullSafeSetInternal(PreparedStatement statement, int index, Object value, LobCreator lobCreator) throws SQLException, HibernateException, FileNotFoundException { // check the file length, it can't be greater than Integer.MAX_VALUE because we are going to cast the length // down to an int alter if (((File) value).length() >= Integer.MAX_VALUE) throw new HibernateException("File size exceeds " + Integer.MAX_VALUE + " in length."); BufferedInputStream inputStream = new BufferedInputStream(new FileInputStream((File) value)); lobCreator.setBlobAsBinaryStream(statement, index, inputStream, (int) ((File) value).length()); } public int[] sqlTypes() { return new int[]{Types.BLOB}; } public Class returnedClass() { return File.class; } public boolean isMutable() { return true; } }

I'm leaning toward making the temporary file name more unique by adding a random number to the end of the owner.hashCode() value then letting the user manage the caching of files..

To re-visit the issue...

I have implemented your class above, mostly as-is with the minor adjustment of buffering the disk write 8k at a time. Everything appears great except that I get the following error when I attempt to submit a file:

Code:

[reports] DEBUG [http-8080-Processor23] ReportFormController.onSubmit(49) | entering 'onSubmit' method... [reports] DEBUG [http-8080-Processor23] FileBlobType.nullSafeSet(194) | Registering Spring transaction synchronization for Hibernate LOB type [reports] WARN [http-8080-Processor23] JDBCExceptionReporter.logExceptions(71) | SQL Error: 0, SQLState: 42804 [reports] ERROR [http-8080-Processor23] JDBCExceptionReporter.logExceptions(72) | ERROR: column "binary_file" is of type oid but expression is of type bytea [reports] ERROR [http-8080-Processor23] AbstractFlushingEventListener.performExecutions(277) | Could not synchronize database state with session org.hibernate.exception.SQLGrammarException: could not insert: [com.reports.model.Report] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1869) at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2200) at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:46) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)

Any ideas about what may be causing the java.io.File object to be interpreted as a bytea? The following is my xdoclet type declaration:

Code:

* @hibernate.property column="binary_file" type="com.reports.dao.hibernate.usertypes.FileBlobType" */ public File getBinaryFile() { return binaryFile; }

I am using a DefaultLobHandler, Spring 1.2.1, Hibernate 3.0.5, and PostgreSQL 8 on windows xp.

Any ideas or further developments with this UserType since you posted it initially?

Thanks in advance,
Brendan

Fixed

Just for closure on this issue - I fixed the problem I was having. (if anyone was curious!) :D

I actually moved away from using BLOB as my type in PostgreSQL, as the later versions of Postgres require use of their specialized BLOB API to work with BLOB objects. Also other databases implement BLOB's differently, but every database should provide a binary type - SO...

Instead I used a postgres bytea (binary value) type for the db column, and used a custom usertype to map that column to a java.io.File object. Since none of the files will be > 1Gig, using the bytea type instead of a BLOB should be just fine. It also provides additional data security as blobs are global in the DB (see postgres docs.) The usertype extends ImmutableType and uses BufferedStream's to map between the bytea from the db and the java.io.File field of the pojo. I chose to generate a cryptographically strong (but not massive) random number to represent the filename when "materializing" the bytea to prevent the race conditions described in the post above. Since I already store the real filename in the db, this works well and should avoid multiple threads attempting to access the same stream. For additional safety I materialize the file in a directory named "$user_name". I use an interceptor to delete the file when finished.

After plenty of load testing it works very well. I can provide the custom usertype upon request - as long as my employer agrees. :)

Additionally, in case anyone is curious, I'm using this in combination with Commons.FileUpload as implemented by Spring to let user's upload and download large files. For mapping on the upload side I use a CustomFileEditor to bind the MultipartFile object to the java.io.File of the my pojo.

Using java.io.File for these use cases seems like such a common need when storing a materialized file completely in memory is not an option. I wonder - is anyone interested in me contributing my CustomFileEditor and CustomFileType implementations to Spring for continued use?

Brendan

Hi everyone,

I would be very interested in receiving your custom usertype class Brendan. I have to upload big files and I'm having some problems. Please let me know.

Thanx in advance

Brendan,
Please post your binary array solution so we may all benefit from this feature which Hibernate currently lacks.

Thanks! :D