Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Kerry Baer
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
    Posts: 66
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
     
    Kerry Baer
    Ranch Hand
    Posts: 39
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
     
    Stan Sokolov
    Ranch Hand
    Posts: 120
    Hibernate IntelliJ IDE Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Use java.sql.Clob. Conversion from string/to string using Dozer is provided bellow. Let me know if you needs similar class for Blob


     
    Kerry Baer
    Ranch Hand
    Posts: 39
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    @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.
     
    Stan Sokolov
    Ranch Hand
    Posts: 120
    Hibernate IntelliJ IDE Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 39
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 39
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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...
     
    Stan Sokolov
    Ranch Hand
    Posts: 120
    Hibernate IntelliJ IDE Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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.





     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic