proper hibernate annotation for byte[]

Question

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

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.

I have finally got this working. It expands on the solution from A. Garcia, however, since the problem lies in the hibernate type MaterializedBlob type just mapping Blob > bytea is not sufficient, we need a replacement for MaterializedBlobType which works with hibernates broken blob support. This implementation only works with bytea, but maybe the guy from the JIRA issue who wanted OID could contribute an OID implementation.

Sadly replacing these types at runtime is a pain, since they should be part of the Dialect. If only this JIRA enhanement gets into 3.6 it would be possible.

public class PostgresqlMateralizedBlobType extends AbstractSingleColumnStandardBasicType<byte[]> {
 public static final PostgresqlMateralizedBlobType INSTANCE = new PostgresqlMateralizedBlobType();

 public PostgresqlMateralizedBlobType() {
  super( PostgresqlBlobTypeDescriptor.INSTANCE, PrimitiveByteArrayTypeDescriptor.INSTANCE );
 }

  public String getName() {
   return "materialized_blob";
  }
}

Much of this could probably be static (does getBinder() really need a new instance?), but I don't really understand the hibernate internal so this is mostly copy + paste + modify.

public class PostgresqlBlobTypeDescriptor extends BlobTypeDescriptor implements SqlTypeDescriptor {
  public static final BlobTypeDescriptor INSTANCE = new PostgresqlBlobTypeDescriptor();

  public <X> ValueBinder<X> getBinder(final JavaTypeDescriptor<X> javaTypeDescriptor) {
   return new PostgresqlBlobBinder<X>(javaTypeDescriptor, this);
  }
  public <X> ValueExtractor<X> getExtractor(final JavaTypeDescriptor<X> javaTypeDescriptor) {
   return new BasicExtractor<X>( javaTypeDescriptor, this ) {
    protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException { 
      return (X)rs.getBytes(name);
    }
   };
  }
}

public class PostgresqlBlobBinder<J> implements ValueBinder<J> {
 private final JavaTypeDescriptor<J> javaDescriptor;
 private final SqlTypeDescriptor sqlDescriptor;

 public PostgresqlBlobBinder(JavaTypeDescriptor<J> javaDescriptor, SqlTypeDescriptor sqlDescriptor) { 
  this.javaDescriptor = javaDescriptor; this.sqlDescriptor = sqlDescriptor;
 }  
 ...
 public final void bind(PreparedStatement st, J value, int index, WrapperOptions options) 
 throws SQLException {
  st.setBytes(index, (byte[])value);
 }
}

i fixed My issue by adding the annotation of @Lob which will create the byte[] in oracle as blob , but this annotation will create the field as oid which not work properly , To make byte[] created as bytea i made customer Dialect for postgres as below

Public class PostgreSQLDialectCustom extends PostgreSQL82Dialect {
    public PostgreSQLDialectCustom() {
        System.out.println("Init PostgreSQLDialectCustom");
        registerColumnType( Types.BLOB, "bytea" );

      }

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

Also need to override parameter for the Dialect

spring.jpa.properties.hibernate.dialect=com.ntg.common.DBCompatibilityHelper.PostgreSQLDialectCustom

more hint can be found her : https://dzone.com/articles/postgres-and-oracle

On Postgres @Lob is breaking for byte[] as it tries to save it as oid, and for String also same problem occurs. Below code is breaking on postgres which is working fine on oracle.

@Lob
private String stringField;

and

@Lob
private byte[]   someByteStream;

In order to fix above on postgres have written below custom hibernate.dialect

public class PostgreSQLDialectCustom extends PostgreSQL82Dialect{

public PostgreSQLDialectCustom()
{
    super();
    registerColumnType(Types.BLOB, "bytea");
}

 @Override
 public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {
    if (Types.CLOB == sqlTypeDescriptor.getSqlType()) {
      return LongVarcharTypeDescriptor.INSTANCE;
    }
    return super.remapSqlTypeDescriptor(sqlTypeDescriptor);
  }
}

Now configure custom dialect in hibernate

hibernate.dialect=X.Y.Z.PostgreSQLDialectCustom   

X.Y.Z is package name.

Now it working fine. NOTE- My Hibernate version - 5.2.8.Final Postgres version- 9.6.3