mysqlpostgresqlhibernatemariadbhibernate-types

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


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.

Solution

  • 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!