This past week I’ve been doing a lot of work using Blobs with Postgresql in Java. There doesn’t seam to be a lot of documentation on using Blobs in Postgresql, luckily I found some good information in the Postgresql JDBC Mailing list regarding using Blobs with Java.

I wrote some test code using plain JDBC that worked very well. Since I’ve always had good experience with Hibernate in the past, I assumed that using Blobs with Hibernate and Postgres would work fine. I was disappointed.

My test JDBC code looks something like:

PreparedStatement stmt = conn.prepareStatement("INSERT INTO foo (data) VALUES (?)");
stmt.setBlob(1, Hibernate.createBlob(myInputStream));
stmt.executeUpdate();

The above example works fine. The Postgresql JDBC driver accepts any object that implements javax.sql.Blob.

I therefore assumed that the following code would work:

HibernateObject o = new HibernateObject();
o.setBlob(Hibernate.createBlob(myInputStream));
hibernate.save(o);

Unfortunately this doesn’t work. The problem lies in net.sf.hibernate.type.BlobType. Hibernate contains the following logic:

public void set(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
   if (value instanceof BlobImpl) {
       BlobImpl blob = (BlobImpl) value;
       st.setBinaryStream( index, blob.getBinaryStream(), (int) blob.length() );
   }
   else {
       st.setBlob(index, (Blob) value);
   }
}

The problem with the above code is that Postgresql uses setBinaryStream for byte array (bytea) fields. Hibernate incorrectly assumes that all databases can use setBinaryStream on blob fields.

Steve Lustbader filed a bug HB-955 regarding this issue that has, unfortunately, been rejected. Fortunately, there is a fairly simple workaround. net.sf.hibernate.lob.BlobImpl is the Hibernate class that implements javax.sql.Blob. I copied the implementation of this class and put it in my own class. I then use this class to the set the blob field in my Hibernate object. Hibernate than passes this object onto the JDBC driver and everything works well.

I understand the logic behind what is happening in Hibernate. They are trying to prevent problems with JDBC drivers that cast the blob field to their own implementation of javax.sql.Blob. I’m of the opinion that any JDBC driver that does this is broken. Any JDBC driver that does not accept any implementation of javax.sql.Blob is in my opinion broken. The assumption that all blobs can be set using setBinaryStream() is also incorrect, in my opinion.