Switch from JsonStringType to JsonBinaryType when the project uses both MySQL and PostgreSQL

Question

I have a problem with column json when it's necessary to switching from PostgreSQL to MariaDB/MySql.
I use Spring Boot + JPA + Hibernate + hibernate-types-52.
The table i want to map is like this:

CREATE TABLE atable(
 ...
 acolumn JSON,
 ... 
);

Ok it works for PostgreSQL and MariaDB/MySql.
The problem is when i want to deploy an application that switch easly from one to another because the correct hibernate-types-52 implementation for PostgreSQL and MySQL/MariaDB are different

This works on MySQL/MariaDB

@Entity
@Table(name = "atable")
@TypeDef(name = "json", typeClass = JsonStringType.class)
  public class Atable {
  ...
  @Type(type = "json")
  @Column(name = "acolumn", columnDefinition = "json")
  private JsonNode acolumn;
  ...
}

This works on PosgreSQL

@Entity
@Table(name = "atable")
@TypeDef(name = "json", typeClass = JsonBinaryType.class)
public class Atable {
  ...
  @Type(type = "json")
  @Column(name = "acolumn", columnDefinition = "json")
  private JsonNode acolumn;
  ...
}

Any kind of solutions to switch from JsonBinaryType to JsonStringType (or any other solution to solve this) is appreciated.

Answer 1

The Hypersistence Utils project, you can just use the JsonType, which works with PostgreSQL, MySQL, Oracle, SQL Server, or H2.

So, use JsonType instead of JsonBinaryType or JsonStringType

@Entity
@Table(name = "atable")
@TypeDef(name = "json", typeClass = JsonType.class)
public class Atable {

  @Type(type = "json")
  @Column(name = "acolumn", columnDefinition = "json")
  private JsonNode acolumn;

}

That's it!

Answer 2

There are some crazy things you can do - with the limitation that this only works for specific types and columns:

First, to replace the static @TypeDef with a dynamic mapping:

You can use a HibernatePropertiesCustomizer to add a TypeContributorList:

@Configuration
public class HibernateConfig implements HibernatePropertiesCustomizer {

  @Value("${spring.jpa.database-platform:}")
  private Class<? extends Driver> driverClass;

  @Override
  public void customize(Map<String, Object> hibernateProperties) {

    AbstractHibernateType<Object> jsonType;
    if (driverClass != null && PostgreSQL92Dialect.class.isAssignableFrom(driverClass)) {
      jsonType = new JsonBinaryType(Atable.class);
    } else {
      jsonType = new JsonStringType(Atable.class);
    }

    hibernateProperties.put(EntityManagerFactoryBuilderImpl.TYPE_CONTRIBUTORS,
        (TypeContributorList) () -> List.of(
            (TypeContributor) (TypeContributions typeContributions, ServiceRegistry serviceRegistry) ->
                typeContributions.contributeType(jsonType, "myType")));
  }
}

So this is limited to the Atable.class now and I have named this custom Json-Type 'myType'. I.e., you annotate your property with @Type(type = 'myType').

I'm using the configured Dialect here, but in my application I'm checking the active profiles for DB-specific profiles.

Also note that TypeContributions .contributeType(BasicType, String...) is deprecated since Hibernate 5.3. I haven't looked into the new mechanism yet.

So that covers the @Type part, but if you want to use Hibernate Schema generation, you'll still need the @Column(columnDefinition = "... bit, so Hibernate knows which column type to use.

This is where it start's feeling a bit yucky. We can register an Integrator to manipulate the Mapping Metadata:

hibernateProperties.put(EntityManagerFactoryBuilderImpl.INTEGRATOR_PROVIDER,
            (IntegratorProvider) () -> Collections.singletonList(JsonColumnMappingIntegrator.INSTANCE));

As a demo I'm only checking for PostgreSQL and I'm applying the dynamic columnDefinition only to a specific column in a specific entity:

public class JsonColumnMappingIntegrator implements Integrator {

  public static final JsonColumnMappingIntegrator INSTANCE =
      new JsonColumnMappingIntegrator();

  @Override
  public void integrate(
      Metadata metadata,
      SessionFactoryImplementor sessionFactory,
      SessionFactoryServiceRegistry serviceRegistry) {

    Database database = metadata.getDatabase();

    if (PostgreSQL92Dialect.class.isAssignableFrom(database.getDialect().getClass())) {
      Column acolumn=
        ((Column) metadata.getEntityBinding(Atable.class.getName()).getProperty("acolumn").getColumnIterator().next());
      settingsCol.setSqlType("json");
    }
  }

  @Override
  public void disintegrate(SessionFactoryImplementor sessionFactory, SessionFactoryServiceRegistry serviceRegistry) {
  }
}

metadata.getEntityBindings() would give you all Entity Bindings, over which you can iterate and then iterate over the properties. This seems quite inefficient though. I'm also not sure whether you can set things like 'IS JSON' constraints etc., so a custom create script would be better.