This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Using Hibernate to insert into Oracle Blob, then read back out Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Using Hibernate to insert into Oracle Blob, then read back out" Watch "Using Hibernate to insert into Oracle Blob, then read back out" New topic
Author

Using Hibernate to insert into Oracle Blob, then read back out

Kerry Baer
Ranch Hand

Joined: Jun 23, 2010
Posts: 39
I have been trying to figure this out for over a week now and it's getting on my nerves that I can't figure it out. So I'm reaching out to the Java community to try to get some answers...

I am using Hibernate 3.2.5 for ORM and Spring 2.5.6 for MVC. My database is Oracle 11g. I am using ojdbc6.jar from Oracle 11g.

So far, I have been able to successfully store files <4K into the database. I followed instructions from several web sources attempting to allow larger files to be inserted and have failed to find a working solution.

Here is my code as it is now...

The following is the entity class with annotations:


This is my Hibernate configuration:


Here is the DAO code where I insert into the database:


I have adjusted this code several times. I have reverted back to my original attempt to try to simplify the process of trying other methods. Please help me by posting your solutions for inserting large files into BLOB objects in Oracle using Hibernate.

Here is a list of resources I have looked at (although I have seen many more):
  • http://web.archive.org/web/20090224215158/http://hibernate.org/56.html
  • http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/
  • http://download.oracle.com/javase/6/docs/api/javax/sql/rowset/serial/SerialBlob.html
  • http://blog.ovo.cz/2009/05/oracle-blob-in-java.html
  • http://www.dil.univ-mrs.fr/~massat/docs/hibernate-3.1/api/org/hibernate/lob/SerializableBlob.html
  • http://java-x.blogspot.com/2007/01/handling-oracle-large-objects-with-jdbc.html
  • Waswani Naresh
    Ranch Hand

    Joined: May 01, 2008
    Posts: 66
    Hi Kerry,

    Are you getting some sort of error when you try to insert large file ? If yes, please provide the stack trace and what's the average file size you want to insert ?

    Regards,
    Naresh Waswani


    Naresh Waswani
    Kerry Baer
    Ranch Hand

    Joined: Jun 23, 2010
    Posts: 39
    Waswani Naresh wrote:Hi Kerry,

    Are you getting some sort of error when you try to insert large file ? If yes, please provide the stack trace and what's the average file size you want to insert ?

    Regards,
    Naresh Waswani


    Yes I am getting an Oracle error: ORA-01460: unimplemented or unreasonable conversion requested
    This error occurs at line 11 of the DAO object I included above "session.flush();"

    The average file size will probably be around 20-30 MB, but I want to allow for files up to 4GB for certain "specialty" cases.

    Thank you
    Stas Sokolov
    Ranch Hand

    Joined: Apr 13, 2004
    Posts: 117

    Use java.sql.Clob. Conversion from string/to string using Dozer is provided bellow. Let me know if you needs similar class for Blob




    Good luck for yourself.
    Kerry Baer
    Ranch Hand

    Joined: Jun 23, 2010
    Posts: 39
    @Stas Sokolov Please post a blob example.

    Also; I'd prefer not to use the Dozer library. I'd prefer to be able to use native code and/or the libraries I am already using => specifically Hibernate. Hibernate should be able to handle blobs and I just must not be doing it right. There has to be a simpler solution and if there isn't then I hope the Hibernate team is hard at work on making this easier because it appears as though this is a common issue.
    Stas Sokolov
    Ranch Hand

    Joined: Apr 13, 2004
    Posts: 117

    Kerry Bear wrote:@Stas Sokolov Please post a blob example.

    Also; I'd prefer not to use the Dozer library. I'd prefer to be able to use native code and/or the libraries I am already using => specifically Hibernate. Hibernate should be able to handle blobs and I just must not be doing it right. There has to be a simpler solution and if there isn't then I hope the Hibernate team is hard at work on making this easier because it appears as though this is a common issue.


    >>Hibernate should be able to handle blobs
    It does handle them right. Map a blob field in database to the sql.blob type and you will notice that Hibernate has no problem dealing with it.

    And there is no need to use Dozer if you map a Blob database field to a sql.Blob type and use it like this. But if your domain code operates with a String then you will need somehow do a conversion from a Blob to a String. I use dozer, you can use something else.

    I am not sure if database Blob field can be mapped to a string bypassing sql.Blob (otherwise why sql.Blob would even exist?). Never seen that someone did it not like this: DATABASE BLOB -> Java sql.Blob - String

    I have no problem with this approach. In my code once data is retrieved from a database it is converted in a suitable domain object. It is not a problem if hibernate DTO has a blob field and domain object has a string. Conversion procedure (that in my case involves dozer) takes care about it.
    Kerry Baer
    Ranch Hand

    Joined: Jun 23, 2010
    Posts: 39
    Stas Sokolov wrote:
    >>Hibernate should be able to handle blobs
    It does handle them right. Map a blob field in database to the sql.blob type and you will notice that Hibernate has no problem dealing with it.


    When I attempt to use a java.sql.Blob object in my entity class I get the following error message


    http://docs.jboss.org/hibernate/annotations/3.5/reference/en/html_single/#entity-mapping-property
    Kerry Baer
    Ranch Hand

    Joined: Jun 23, 2010
    Posts: 39
    I tried using a java.sql.Blob, ignored the error and ran the project anyway. The record was inserted, but the Blob was null. I was debugging and saw that the object was not null when the object was sent to Hibernate.

    I am dealing with various document types (pdf, doc, xls, ppt, jpg, png, mp3, wmv, wma, mp4, ... )

    The investigation continues...
    Stas Sokolov
    Ranch Hand

    Joined: Apr 13, 2004
    Posts: 117

    Kerry Bear wrote:I tried using a java.sql.Blob, ignored the error and ran the project anyway. The record was inserted, but the Blob was null. I was debugging and saw that the object was not null when the object was sent to Hibernate.

    I am dealing with various document types (pdf, doc, xls, ppt, jpg, png, mp3, wmv, wma, mp4, ... )

    The investigation continues...

    I would suggest to create a simple example outside of context of your application and experiment with an XML based definition. Annotations based hibernate mapping is new to me and I am not sure how sophisticated it is. It would be easier I guess if you create a single mapping file that connect your database table and your java class.





     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Using Hibernate to insert into Oracle Blob, then read back out
     
    Similar Threads
    com.mysql.jdbc.CommunicationsException MESSAGE: Communications link failure due to underlying except
    Hibernate saves nvarchar values with a trailing space
    Spring,JUnit, Hibernate and persistence db but not insert
    Connection pooling using hibernate and struts1.3
    org.hibernate.MappingNotFoundException