Hibernate, Postgresql: Column "x" is of type oid but expression is of type byte

I have a strange problem regarding the hibernate mapping containing large objects (BLOB), when switching between different databases.

@Lob
private byte[] binaryData;

The field above creates a byte array field in MySQL and in Oracle, however in PostreSQL it creates a field of type oid.

Now when I try to access this field it works fine in the other databases, but in PostgreSQL it fails with the following error

Column "binaryData" is of type oid but expression is of type bytea.

So I tried to simply remove the "@Lob" annotation, which will solve the problem for PostgreSQL, however in MySQL without this annotation, hibernate creates a field of type "tinyblob", which is to small in most of our cases. And, as we want to use this project in more than one environment it is annoying to have two di开发者_开发百科fferent mappings to switch.

Is there any annotation that forces postgreSQL to use bytea instead of oid for fields annotated with @Lob? Or is it somehow possible to omit the @Lob and put something else in order to force MySQL to allocate it with a larger datatype as it would using @Lob?

I could even imagine to have a solution like this

if (field is of type oid)
  store it as oid
else if (field is of type bytea)
  store it as bytea
else
  // not storable

and the same as a getter, if there exists a way to do kind of this

EDIT:

The following declaration is working. It allocates the column as oid, however hibernate using this knows how to store and retrieve data from such a field

@Lob
@Type(type="org.hibernate.type.PrimitiveByteArrayBlobType")
private byte[] binaryFile;

---

This field mapping is defined in org.hibernate.dialect.PostgreSQLDialect and can be changed by subclassing this and configuring your app to use the modified dialect when running with postgres.

The relevant incantation in the subclass is probably to put

    registerColumnType( Types.BLOB, "bytea" );

in your constructor after a call to super().

---

For me this may have meant once "revert your postgres jdbc version back down to 9.3-1101.jdbc4"

  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.4-1200-jdbc41</version>
  </dependency>

worked as well. Newer than that failed...

---

The class is deprecated since Hibernate 3.6. Now with latest Hibernate we need to use @Type(type="org.hibernate.type.MaterializedBlobType") instead. Please check https://docs.jboss.org/hibernate/core/3.6/javadocs/deprecated-list.html and https://docs.jboss.org/hibernate/core/3.6/javadocs/org/hibernate/type/MaterializedBlobType.html for more information.

---

Hibernate 6+, javaee 9+

  @Lob
  @JdbcTypeCode(Types.BINARY)
  private byte[] binaryData;

---

PostgreSQL large objects are of type oid, as they are actually references to objects in a separate table, pg_largeobject_metadata.

The JDBC interface maps oid to java.sql.Types.DISTINCT.

None of the Hibernate dialects know that this is a Blob type, but you can provide your own that treats them as equivalent:

public static class PostgreSQLDialect extends PostgreSQL10Dialect {
    @Override
    public boolean equivalentTypes(int type1, int type2) {
        return super.equivalentTypes(type1, type2) ||
                (type1 == Types.BLOB && type2 == Types.DISTINCT) ||
                (type1 == Types.DISTINCT && type2 == Types.BLOB);
    }
}