proper hibernate annotation for byte[]

I'm using the Hibernate 4.2.7.SP1 with Postgres 9.3 and following works for me:

@Entity
public class ConfigAttribute {
  @Lob
  public byte[] getValueBuffer() {
    return m_valueBuffer;
  }
}

as Oracle has no trouble with that, and for Postgres I'm using custom dialect:

public class PostgreSQLDialectCustom extends PostgreSQL82Dialect {

    @Override
    public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {
    if (sqlTypeDescriptor.getSqlType() == java.sql.Types.BLOB) {
      return BinaryTypeDescriptor.INSTANCE;
    }
    return super.remapSqlTypeDescriptor(sqlTypeDescriptor);
  }
}

the advantage of this solution I consider, that I can keep hibernate jars untouched.

For more Postgres/Oracle compatibility issues with Hibernate, see my blog post.


What is the portable way to annotate a byte[] property?

It depends on what you want. JPA can persist a non annotated byte[]. From the JPA 2.0 spec:

11.1.6 Basic Annotation

The Basic annotation is the simplest type of mapping to a database column. The Basic annotation can be applied to a persistent property or instance variable of any of the following types: Java primitive, types, wrappers of the primitive types, java.lang.String, java.math.BigInteger, java.math.BigDecimal, java.util.Date, java.util.Calendar, java.sql.Date, java.sql.Time, java.sql.Timestamp, byte[], Byte[], char[], Character[], enums, and any other type that implements Serializable. As described in Section 2.8, the use of the Basic annotation is optional for persistent fields and properties of these types. If the Basic annotation is not specified for such a field or property, the default values of the Basic annotation will apply.

And Hibernate will map a it "by default" to a SQL VARBINARY (or a SQL LONGVARBINARY depending on the Column size?) that PostgreSQL handles with a bytea.

But if you want the byte[] to be stored in a Large Object, you should use a @Lob. From the spec:

11.1.24 Lob Annotation

A Lob annotation specifies that a persistent property or field should be persisted as a large object to a database-supported large object type. Portable applications should use the Lob annotation when mapping to a database Lob type. The Lob annotation may be used in conjunction with the Basic annotation or with the ElementCollection annotation when the element collection value is of basic type. A Lob may be either a binary or character type. The Lob type is inferred from the type of the persistent field or property and, except for string and character types, defaults to Blob.

And Hibernate will map it to a SQL BLOB that PostgreSQL handles with a oid .

Is this fixed in some recent version of hibernate?

Well, the problem is that I don't know what the problem is exactly. But I can at least say that nothing has changed since 3.5.0-Beta-2 (which is where a changed has been introduced)in the 3.5.x branch.

But my understanding of issues like HHH-4876, HHH-4617 and of PostgreSQL and BLOBs (mentioned in the javadoc of the PostgreSQLDialect) is that you are supposed to set the following property

hibernate.jdbc.use_streams_for_binary=false

if you want to use oid i.e. byte[] with @Lob (which is my understanding since VARBINARY is not what you want with Oracle). Did you try this?

As an alternative, HHH-4876 suggests using the deprecated PrimitiveByteArrayBlobType to get the old behavior (pre Hibernate 3.5).

References

  • JPA 2.0 Specification
    • Section 2.8 "Mapping Defaults for Non-Relationship Fields or Properties"
    • Section 11.1.6 "Basic Annotation"
    • Section 11.1.24 "Lob Annotation"

Resources

  • http://opensource.atlassian.com/projects/hibernate/browse/HHH-4876
  • http://opensource.atlassian.com/projects/hibernate/browse/HHH-4617
  • http://relation.to/Bloggers/PostgreSQLAndBLOBs

Here goes what O'reilly Enterprise JavaBeans, 3.0 says

JDBC has special types for these very large objects. The java.sql.Blob type represents binary data, and java.sql.Clob represents character data.

Here goes PostgreSQLDialect source code

public PostgreSQLDialect() {
    super();
    ...
    registerColumnType(Types.VARBINARY, "bytea");
    /**
      * Notice it maps java.sql.Types.BLOB as oid
      */
    registerColumnType(Types.BLOB, "oid");
}

So what you can do

Override PostgreSQLDialect as follows

public class CustomPostgreSQLDialect extends PostgreSQLDialect {

    public CustomPostgreSQLDialect() {
        super();

        registerColumnType(Types.BLOB, "bytea");
    }
}

Now just define your custom dialect

<property name="hibernate.dialect" value="br.com.ar.dialect.CustomPostgreSQLDialect"/>

And use your portable JPA @Lob annotation

@Lob
public byte[] getValueBuffer() {

UPDATE

Here has been extracted here

I have an application running in hibernate 3.3.2 and the applications works fine, with all blob fields using oid (byte[] in java)

...

Migrating to hibernate 3.5 all blob fields not work anymore, and the server log shows: ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: column is of type oid but expression is of type bytea

which can be explained here

This generaly is not bug in PG JDBC, but change of default implementation of Hibernate in 3.5 version. In my situation setting compatible property on connection did not helped.

...

Much more this what I saw in 3.5 - beta 2, and i do not know if this was fixed is Hibernate - without @Type annotation - will auto-create column of type oid, but will try to read this as bytea

Interesting is because when he maps Types.BOLB as bytea (See CustomPostgreSQLDialect) He get

Could not execute JDBC batch update

when inserting or updating

Tags:

Database

Java

Postgresql

Hibernate

Blob

Recent Posts