Question 90235 in PostgreSQL Question posted in
The official documentation can be found here.

Hibernate 6, Postgres, and bytea – Postgresql

With the release of Hibernate 6, once again I am scratching my head trying to figure out how to get Hibernate to convert a @Lob to bytea for a PostGres database. I also need the code to be testable with H2 (or any in memory database).

I am using Postgres 10+ (v13), so that takes care of that requirement.

This is what I used before, which was tricky, because I also needed it to work with H2. (The PostGres dialect was only loaded in production, not tests.)

Sadly, getting bytea to work is "outside the scope" of the Hibernate 6 user guide. See 2.2.47.
https://docs.jboss.org/hibernate/orm/6.0/userguide/html_single/Hibernate_User_Guide.html

The migration guide also seems silent on the topic.
https://github.com/hibernate/hibernate-orm/blob/6.0/migration-guide.adoc

The entire API for SQLDialect family has been rewritten, so I am back to square 1.

public class CustomPostgresSQL10Dialect extends PostgreSQL10Dialect{

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

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

    return super.remapSqlTypeDescriptor(sqlTypeDescriptor);
  }
}
Tags:

2

Answers


  1. 0

    It’s hard to be sure, since you have not told us what the Java type of the field you’re trying to map with the @Lob annotation is, but hell, just this once I’ll have a go at reading your mind, and speculate that what you’ve written is this:

    @Lob 
byte[] binaryData;

    Now, the Postgres JDBC driver doesn’t support using the LOB APIs to read/write bytea columns, and Hibernate knows about this, so it does the next-most-sensible thing, and assumes that this field maps to an oid column. This turns out to not be what you want.

    Of course, you could try bypassing Hibernate’s knowledge of Postgres by writing:

    @Lob @Column(columnDefinition="bytea") 
byte[] binaryData;

    And then you would get an error from Postgres or its driver (I forget which), because, as I said, the postgres driver doesn’t support this.

    So what to do?

    Well, sure, you could try hacking the Hibernate Dialect as you have done above, to tell Hibernate to:

    1. use bytea for the JDBC type BLOB, and
    2. bind JDBC BLOBs using the usual handling for the JDBC type BINARY or VARBINARY or whatever.

    And yeah, you could still make that work easily enough in H6.

    Or, you know, you could just remove the stupid @Lob annotation which isn’t doing any useful work here, and let Hibernate treat your field as a varbinary, which maps to bytea by default.

    byte[] binaryData;

    Now, look, there might be something more to what you’re doing here, but then you’ll need to help me out here by actually posting the details of the field and how you’ve mapped it.

    Login or Signup to reply.
  2. 0

    We had exactly the same problem and manage to fix it with this dialect class (kotlin):

    class CustomSQLDialect : PostgreSQLDialect(DatabaseVersion.make(9, 5)) {
    override fun columnType(sqlTypeCode: Int): String {
        return when (sqlTypeCode) {
            SqlTypes.BLOB -> "bytea"
            else -> super.columnType(sqlTypeCode)
        }
    }

    override fun castType(sqlTypeCode: Int): String {
        return when (sqlTypeCode) {
            SqlTypes.BLOB -> "bytea"
            else -> super.castType(sqlTypeCode)
        }
    }

    override fun contributeTypes(typeContributions: TypeContributions, serviceRegistry: ServiceRegistry) {
        super.contributeTypes(typeContributions, serviceRegistry)
        val jdbcTypeRegistry = typeContributions.typeConfiguration
            .jdbcTypeRegistry
        jdbcTypeRegistry.addDescriptor(Types.BLOB, BinaryJdbcType.INSTANCE)
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search