How to use JSON mapping with text column

Hibernate ORM
1

Hi,

I understood that with Hibernate 6, I can use json serialization with the following:

@JdbcTypeCode(SqlTypes.JSON)
private Map<String, Object> myField;

And it works well, but hbm2ddl tooling expects the column datatype to be jsonb while I would like to be able to add, in some specific cases, an annotation to the field to express that I just want to use SqlTypes.LONGVARCHAR as the concrete db type.

So basically, I want:

  • json parsing of the data on the Java side
  • text based columns in the db (but of course, I also have sometimes jsonb columns, so I can’t just override the DdlType I think)
  • still continue to work with mariadb and postgresql, so I think I can’t just set @JdbcType() because I think each Dialect implementation provide a different implementation for dealing with json parsing?

Is it something that is possible or am I asking for too much? :slight_smile:

I know it’s seems a bit strange but I’m in the process of migrating a huge codebase to Hibernate 6 and for now I would prefer to avoid touching my db schema.

2

You can specify the DDL type with @Column(columnDefinition = "json"), but I think you might have trouble with PostgreSQL then, because it sets parameters with the type jsonb.

All of the DdlType and JdbcType can be customized based on Dialect though, so you can check in a TypeContributor if the dialect is an instance of PostgreSQLDialect, and if so, override the DdlType and JdbcType.

3

Thank you for your answer, unfortunately in my particular case I cannot use the column definition because I want to support both MariaDB and PostgreSQL ^^

I instead found an alternative solution: because my problem was with the schema validator/migrator tools, I defined in my PostgreSQL dialect the following until I can fix the schema:

    @Override
    public boolean equivalentTypes(int typeCode1, int typeCode2) {
        return super.equivalentTypes(typeCode1, typeCode2)
            // we have a few entities that incorrectly used TEXT instead of JSONB in the db schema
            || isJsonOrText(typeCode1) && isJsonOrText(typeCode2);
    }

    private boolean isJsonOrText(int typeCode) {
        return typeCode == SqlTypes.JSON || typeCode == SqlTypes.VARCHAR;
    }
4

In Hibernate 6, use @Column(columnDefinition = “LONGTEXT”) to specify text-based columns for JSON data. Implement custom JSON parsing in your application for Map<String, Object> fields. Ensure Hibernate dialects (e.g., MariaDBDialect, PostgreSQLDialect) are configured correctly for database compatibility. This approach allows flexibility in column type mapping across MariaDB and PostgreSQL.