4 Replies Latest reply on Dec 10, 2008 12:50 PM by getaceres

    Forcing bytea for Blobs in Postresql

    getaceres

      Hello, I'm writing a JEE application and I'm planning to support Glassfish and JBoss in their default configurations, so I need to support Toplink and Hibernate. I know that I can use Hibernate with Glassfish but I'd like my code to work in more than one JPA implementation, so using Hibernate in Glassfish would be a Plan B.

      I have a table in a PostgreSQL database with a field that will store binary data. Using Toplink to generate the table, I get a field of type bytea and so, I read the data directly. This is the behavior I want since my binary field will not be very large and having it in the same table that I insert, will simplify the management for me.
      When I try to run this application in Hibernate, I get an error saying that it expected the column to be of type oid while it is of type bytea. Is there a way to force Hibernate to recognize the column with its type?

      I mean, there are two ways to store binary blobs in PostgreSQL, one with bytea columns and another with oid columns as specified in http://jdbc.postgresql.org/documentation/80/binary-data.html. Can I force Hibernate to use the first approach instead of the second one?

        • 1. Re: Forcing bytea for Blobs in Postresql
          rhodan76

          Hello,

          we annotate all our lob-columns in the entity with the @javax.persistence.Lob annotation and let the persistence provider decide, what he will create in the specific database. So you are not tied a specific database handling of lobs.

          Example:

           public String description;
          
           @Column
           @Lob
           public String getDescription() {
           return this.description;
           }
          


          • 2. Re: Forcing bytea for Blobs in Postresql
            getaceres

            I've done that, this is my code:

            @Lob
            protected byte[] content;
            


            However, for that field Toplink assumes that the underlying type is bytea, while Hibernate assumes that it is oid. So basically, every one of them is defaulting to a different way to store a Blob of the two allowed by PostgreSQL.

            • 3. Re: Forcing bytea for Blobs in Postresql
              rhodan76

               

              "getaceres" wrote:
              ... Hibernate assumes that it is oid...


              No Problem, you have to redefine the default Postgres Dialect, that hibernate uses. For a better understanding: when you look in org.hibernate.dialect.PostgresDialect you will see:
              registerColumnType( Types.BLOB, "oid" );


              You must extend these dialect with your own Implementation, and simple re-register the Types.BLOB to whatever you want (and postgres accepts). Then you must tell hibernate, to use your own dialect for postgres (either in hibernate.cfg or persistence.xml):
              <property name="hibernate.dialect" value="my.com.xyz.OwnPostgresDialect" />
              


              • 4. Re: Forcing bytea for Blobs in Postresql
                getaceres

                It's not so easy, I think. That would make the validation to succeed but reading a bytea or reading an oid is completely different.
                When using oid, the blob is saved in another table, so reading it will involve reading in streaming from a table. It's a completely different system. As I'm using byte[] for my JPA type, I expect Hibernate to read the blob when I need it, so it must go to the blob table and read the content marked by the oid field. It's only a foreing key.
                On the other hand, using bytea means saving the blob in the same table as it is defined, so reading it will mean reading from the table that the Entity represents as a field and not using streaming.
                As the reading method is completely different, I don't think that changing the registerColumnType call will be enough, but I can be mistaken. The type registration changes also the way that the column is read? I mean, treating a column as content data or as a foreing key, depending on the type.