Hibernate 6, Postgres, and bytea

Question

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);
  }
}

Answer 1

I had a similar problem, that I need to support H2,MariaDB and PostgreSQL at the same time. In addition to the original problem I also had to map CLOB to text. Based on the Kotlin solution, I have created this java class.

public class CustomPostgreSQLDialect extends PostgreSQLDialect {

    public CustomPostgreSQLDialect() {
        super();
    }

    @Override
    protected String columnType(int sqlTypeCode) {
        if (SqlTypes.BLOB == sqlTypeCode) {
            return "bytea";
        }
        if (SqlTypes.CLOB == sqlTypeCode) {
            return "text";
        }
        return super.columnType(sqlTypeCode);
    }

    @Override
    protected String castType(int sqlTypeCode) {
        if (SqlTypes.BLOB == sqlTypeCode) {
            return "bytea";
        }
        if (SqlTypes.CLOB == sqlTypeCode) {
            return "text";
        }
        return super.castType(sqlTypeCode);
    }

    @Override
    public void contributeTypes(TypeContributions typeContributions, ServiceRegistry serviceRegistry) {
        super.contributeTypes(typeContributions, serviceRegistry);
        JdbcTypeRegistry jdbcTypeRegistry = typeContributions.getTypeConfiguration().getJdbcTypeRegistry();
        jdbcTypeRegistry.addDescriptor(Types.BLOB, BinaryJdbcType.INSTANCE);
        jdbcTypeRegistry.addDescriptor(Types.CLOB, LongVarcharJdbcType.INSTANCE);
    }

}