Announcement Announcement Module
Collapse
No announcement yet.
Saving large files to a db using hibernate? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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...

    Comment


    • #3
      Looks good.

      Comment


      • #4
        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

        Comment


        • #5
          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.*;
          
          
          /**
           * <p>Hibernate UserType implementation for files that get mapped to BLOBs. Retrieves the LobHandler to use from
           * LocalSessionFactoryBean at config time.</p>
           * <p>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&#58; For databases like MySQL and MS SQL
           * Server, any large enough binary type will work.</p>
           * <p>This UserType creates a temporary file using &#123;@link #createTemporaryFile&#40;java.io.File, java.sql.ResultSet, String&#91;&#93;, Object&#41;&#125;
           * method. By default the file name is generated using the value returned from &#123;@link Object#hashCode&#40;&#41;&#125; on the owner.
           * This can be modified by extending this class and re-implementing the
           * &#123;@link #createTemporaryFile&#40;java.io.File, java.sql.ResultSet, String&#91;&#93;, Object&#41;&#125; method.</p>
           *
           * <p>Based on org.springframework.orm.hibernate3.support.BlobByteArrayType by Juergen Hoeller.</p>
           *
           * @author Dan Washusen
           * @version $Id&#58; FileType.java,v 1.1 2005/03/24 23&#58;37&#58;55 dan Exp $
           */
          public class FileType
                  extends AbstractLobType &#123;
              private static final Log log = LogFactory.getLog&#40;FileType.class&#41;;
          
              /**
               * Constructor used by Hibernate&#58; 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&#40;&#41; &#123;
                  super&#40;&#41;;
              &#125;
          
              /**
               * Constructor used for testing&#58; takes an explicit LobHandler
               * and an explicit JTA TransactionManager &#40;can be null&#41;.
               */
              protected FileType&#40;LobHandler lobHandler, TransactionManager jtaTransactionManager&#41; &#123;
                  super&#40;lobHandler, jtaTransactionManager&#41;;
              &#125;
          
              /**
               * Creates a temporary file using the &#123;@link #createTemporaryFile&#40;java.io.File, java.sql.ResultSet, String&#91;&#93;, Object&#41;&#125;
               * 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&#40;ResultSet resultSet, String&#91;&#93; columns, Object owner, LobHandler lobHandler&#41;
                      throws SQLException, IOException, HibernateException &#123;
                  // we only handle one column, so panic if it isn't so
                  if &#40;columns == null || columns.length != 1&#41;
                      throw new HibernateException&#40;"Only one column name can be used for the " + getClass&#40;&#41; + " user type"&#41;;
          
                  // check the temporary directory
                  File tempDirectory = checkTempDirectory&#40;resultSet, columns, owner&#41;;
          
                  // delegate to the createTemporaryFile method to create file
                  File file = createTemporaryFile&#40;tempDirectory, resultSet, columns, owner&#41;;
          
                  // write the contents of the file to disk
                  BufferedInputStream inputStream = new BufferedInputStream&#40;lobHandler.getBlobAsBinaryStream&#40;resultSet, columns&#91;0&#93;&#41;&#41;;
                  OutputStream outputStream = new BufferedOutputStream&#40;new FileOutputStream&#40;file&#41;&#41;;
          
                  // we really should read and write more than one byte at a time...
                  int foo = -1;
                  while &#40;&#40;foo = inputStream.read&#40;&#41;&#41; != -1&#41; &#123;
                      outputStream.write&#40;foo&#41;;
                  &#125;
                  outputStream.flush&#40;&#41;;
                  outputStream.close&#40;&#41;;
          
                  inputStream.close&#40;&#41;;
          
                  return file;
              &#125;
          
              /**
               * 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&#40;ResultSet resultSet, String&#91;&#93; columns, Object owner&#41;
                      throws IOException &#123;
                  File tempDirectory = new File&#40;System.getProperty&#40;"java.io.tmpdir"&#41;&#41;;
                  log.debug&#40;"Using temporary directory " + tempDirectory.getAbsolutePath&#40;&#41;&#41;;
          
                  if &#40;!tempDirectory.exists&#40;&#41;&#41; &#123;
                      log.info&#40;"Creating temporary directory " + tempDirectory.getAbsolutePath&#40;&#41;&#41;;
                      tempDirectory.createNewFile&#40;&#41;;
                  &#125;
          
                  return tempDirectory;
              &#125;
          
              /**
               * 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.
               * <p>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&#40;File tempDirectory, ResultSet resultSet, String&#91;&#93; columns, Object owner&#41; &#123;
                  String filename = String.valueOf&#40;owner.hashCode&#40;&#41;&#41;;
                  File file = new File&#40;tempDirectory, filename&#41;;
                  log.debug&#40;"Creating temporary file at " + file.getAbsolutePath&#40;&#41;&#41;;
          
                  // delete the file if it already exists
                  if &#40;file.exists&#40;&#41;&#41; &#123;
                      log.debug&#40;file.getAbsolutePath&#40;&#41; + " is in the way, removing..."&#41;;
                      if &#40;!file.delete&#40;&#41;&#41; &#123;
                          throw new HibernateException&#40;"File " + file.getAbsolutePath&#40;&#41; + " was in the way and couldn't be deleted"&#41;;
                      &#125;
                  &#125;
          
                  // 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&#40;&#41;;
          
                  return file;
              &#125;
          
              /**
               * Creates a buffered &#123;@link FileInputStream&#125; 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 &#123;@link Integer.MAX_VALUE&#125;
               */
              protected void nullSafeSetInternal&#40;PreparedStatement statement, int index, Object value, LobCreator lobCreator&#41;
                      throws SQLException, HibernateException, FileNotFoundException &#123;
                  // 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 &#40;&#40;&#40;File&#41; value&#41;.length&#40;&#41; >= Integer.MAX_VALUE&#41;
                      throw new HibernateException&#40;"File size exceeds " + Integer.MAX_VALUE + " in length."&#41;;
          
                  BufferedInputStream inputStream = new BufferedInputStream&#40;new FileInputStream&#40;&#40;File&#41; value&#41;&#41;;
                  lobCreator.setBlobAsBinaryStream&#40;statement, index, inputStream, &#40;int&#41; &#40;&#40;File&#41; value&#41;.length&#40;&#41;&#41;;
              &#125;
          
              public int&#91;&#93; sqlTypes&#40;&#41; &#123;
                  return new int&#91;&#93;&#123;Types.BLOB&#125;;
              &#125;
          
              public Class returnedClass&#40;&#41; &#123;
                  return File.class;
              &#125;
          
              public boolean isMutable&#40;&#41; &#123;
                  return true;
              &#125;
          &#125;

          Comment


          • #6
            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..

            Comment


            • #7
              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:
              &#91;reports&#93; DEBUG &#91;http-8080-Processor23&#93; ReportFormController.onSubmit&#40;49&#41; | entering 'onSubmit' method...
              &#91;reports&#93; DEBUG &#91;http-8080-Processor23&#93; FileBlobType.nullSafeSet&#40;194&#41; | Registering Spring transaction synchronization for Hibernate LOB type
              &#91;reports&#93; WARN &#91;http-8080-Processor23&#93; JDBCExceptionReporter.logExceptions&#40;71&#41; | SQL Error&#58; 0, SQLState&#58; 42804
              &#91;reports&#93; ERROR &#91;http-8080-Processor23&#93; JDBCExceptionReporter.logExceptions&#40;72&#41; | ERROR&#58; column "binary_file" is of type oid but expression is of type bytea
              &#91;reports&#93; ERROR &#91;http-8080-Processor23&#93; AbstractFlushingEventListener.performExecutions&#40;277&#41; | Could not synchronize database state with session
              org.hibernate.exception.SQLGrammarException&#58; could not insert&#58; &#91;com.reports.model.Report&#93;
              	at org.hibernate.exception.SQLStateConverter.convert&#40;SQLStateConverter.java&#58;59&#41;
              	at org.hibernate.exception.JDBCExceptionHelper.convert&#40;JDBCExceptionHelper.java&#58;43&#41;
              	at org.hibernate.persister.entity.BasicEntityPersister.insert&#40;BasicEntityPersister.java&#58;1869&#41;
              	at org.hibernate.persister.entity.BasicEntityPersister.insert&#40;BasicEntityPersister.java&#58;2200&#41;
              	at org.hibernate.action.EntityInsertAction.execute&#40;EntityInsertAction.java&#58;46&#41;
              	at org.hibernate.engine.ActionQueue.execute&#40;ActionQueue.java&#58;239&#41;
              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&#40;&#41; &#123;
                      return binaryFile;
                  &#125;
              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

              Comment


              • #8
                Fixed

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

                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

                Comment


                • #9
                  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

                  Comment


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

                    Thanks!

                    Comment


                    • #11
                      OK chaps, since no one else is posting any helpful code for people who don't want to deal with BLOBs, here's what I did to deal with regular binary column types (like LONGRAW in Oracle, etc) - as inspired by Brendan's earlier response. I hope this will be helpful to some of you, feel free to use as is or modify, no strings attached:


                      package greenears;

                      import java.io.BufferedInputStream;
                      import java.io.BufferedOutputStream;
                      import java.io.File;
                      import java.io.FileInputStream;
                      import java.io.FileNotFoundException;
                      import java.io.FileOutputStream;
                      import java.io.IOException;
                      import java.sql.PreparedStatement;
                      import java.sql.ResultSet;
                      import java.sql.SQLException;
                      import java.sql.Types;
                      import java.util.Date;

                      import net.sf.hibernate.HibernateException;
                      import net.sf.hibernate.type.ImmutableType;

                      /**
                      * This class is used to stream large binary files through a buffer between the app's file cache directory
                      * (see System.getProperty("java.io.tmpdir") + "/" + appCacheDirName) and the JDBC database.
                      *
                      * NOTE: The app's cache directory and its contents will be deleted on JVM exit or whenever the custom-made pruning process is run to prune old cache files (I use a Spring Scheduler task).
                      *
                      * @author green_ears
                      *
                      */
                      public class StreamingBinaryArrayFileType extends ImmutableType {
                      private static final int BUFFER_SIZE_IN_BYTES = 4 * 1024;

                      /* (non-Javadoc)
                      * @see net.sf.hibernate.type.NullableType#fromStringValue (java.lang.String)
                      */
                      @Override
                      public Object fromStringValue(String arg0) throws HibernateException {
                      return new File(arg0);
                      }

                      /* (non-Javadoc)
                      * @see net.sf.hibernate.type.NullableType#get(java.sql.Re sultSet, java.lang.String)
                      */
                      @Override
                      public Object get(ResultSet rs, String name) throws HibernateException,
                      SQLException {
                      File tempFile = null;
                      BufferedInputStream bis = null;
                      BufferedOutputStream bos = null;

                      try {
                      bis = new BufferedInputStream(rs.getBinaryStream(name));
                      File tempDirectory = new File(System.getProperty("java.io.tmpdir"));
                      if(!tempDirectory.exists()) {
                      tempDirectory.mkdir();
                      }
                      File appTempDirectory = new File(tempDirectory, "greenCache");
                      if(!appTempDirectory.exists()) {
                      appTempDirectory.mkdir();
                      appTempDirectory.deleteOnExit();
                      }
                      tempFile = new File(appTempDirectory, new Date().getTime() + "" + Math.random());
                      tempFile.deleteOnExit();
                      bos = new BufferedOutputStream(new FileOutputStream(tempFile));
                      int bytesRead = -1;
                      byte[] buffer = new byte[BUFFER_SIZE_IN_BYTES];
                      while((bytesRead = bis.read(buffer)) != -1) {
                      bos.write(buffer, 0, bytesRead);
                      }
                      bos.flush();
                      } catch (IOException ioe) {
                      ioe.printStackTrace();
                      throw new HibernateException("SELECT from LONGRAW to File failed.");
                      }
                      finally {
                      if(bos != null) {
                      try {
                      bos.close();
                      } catch (IOException e) {
                      // TODO Auto-generated catch block
                      e.printStackTrace();
                      }
                      }
                      if(bis != null) {
                      try {
                      bis.close();
                      } catch (IOException e) {
                      // TODO Auto-generated catch block
                      e.printStackTrace();
                      }
                      }
                      }
                      return tempFile;
                      }

                      /* (non-Javadoc)
                      * @see net.sf.hibernate.type.NullableType#set(java.sql.Pr eparedStatement, java.lang.Object, int)
                      */
                      @Override
                      public void set(PreparedStatement pstmt, Object value, int index)
                      throws HibernateException, SQLException {
                      // 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 after
                      if (((File) value).length() >= Integer.MAX_VALUE)
                      throw new HibernateException("File size exceeds " + Integer.MAX_VALUE + " in length.");

                      BufferedInputStream bis = null;
                      try {
                      bis = new BufferedInputStream(new FileInputStream((File) value), BUFFER_SIZE_IN_BYTES);
                      pstmt.setBinaryStream(index, bis, (int) ((File) value).length());
                      } catch (FileNotFoundException e) {
                      e.printStackTrace();
                      throw new HibernateException("INSERT/UPDATE from File to LONGRAW failed.");
                      }
                      /**
                      * Apparently Hibernate will close this stream when it is done... It must remain open for this to work!
                      finally {
                      if(bis != null) {
                      try {
                      bis.close();
                      } catch (IOException e) {
                      // TODO Auto-generated catch block
                      e.printStackTrace();
                      }
                      }
                      }*/
                      }

                      /* (non-Javadoc)
                      * @see net.sf.hibernate.type.NullableType#sqlType()
                      */
                      @Override
                      public int sqlType() {
                      return Types.LONGVARBINARY;
                      }

                      /* (non-Javadoc)
                      * @see net.sf.hibernate.type.NullableType#toString(java.l ang.Object)
                      */
                      @Override
                      public String toString(Object arg0) throws HibernateException {
                      return ((File)arg0).toString();
                      }

                      /* (non-Javadoc)
                      * @see net.sf.hibernate.type.Type#equals(java.lang.Object , java.lang.Object)
                      */
                      public boolean equals(Object arg0, Object arg1) throws HibernateException {
                      if(arg0.getClass() == File.class && arg1.getClass() == File.class) {
                      if(arg0 == null && arg1 == null) {
                      return true;
                      }
                      else if(arg0 != null && arg1 != null) {
                      File arg0File = (File)arg0, arg1File = (File)arg1;
                      if(arg0File.getAbsolutePath().compareTo(arg1File.g etAbsolutePath()) == 0 && arg0File.length() == arg1File.length()) {
                      return true;
                      }
                      }
                      }
                      return false;
                      }

                      /* (non-Javadoc)
                      * @see net.sf.hibernate.type.Type#getName()
                      */
                      public String getName() {
                      return "streaming-binary-array-file";
                      }

                      /* (non-Javadoc)
                      * @see net.sf.hibernate.type.Type#getReturnedClass()
                      */
                      public Class getReturnedClass() {
                      return File.class;
                      }
                      }

                      Comment

                      Working...
                      X