How to map a map JSON column to Java Object with JPA

Question

We have a big table with a lot of columns. After we moved to MySQL Cluster, the table cannot be created because of:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 14000. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

As an example:

@Entity @Table (name = "appconfigs", schema = "myproject")
public class AppConfig implements Serializable
{
    @Id @Column (name = "id", nullable = false)
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    private int id;

    @OneToOne @JoinColumn (name = "app_id")
    private App app;

    @Column(name = "param_a")
    private ParamA parama;

    @Column(name = "param_b")
    private ParamB paramb;
}

It's a table for storing configuration parameters. I was thinking that we can combine some columns into one and store it as JSON object and convert it to some Java object.

For example:

@Entity @Table (name = "appconfigs", schema = "myproject")
public class AppConfig implements Serializable
{
    @Id @Column (name = "id", nullable = false)
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    private int id;

    @OneToOne @JoinColumn (name = "app_id")
    private App app;

    @Column(name = "params")
    //How to specify that this should be mapped to JSON object?
    private Params params;
}

Where we have defined:

public class Params implements Serializable
{
    private ParamA parama;
    private ParamB paramb;
}

By using this we can combine all columns into one and create our table. Or we can split the whole table into several tables. Personally I prefer the first solution.

Anyway my question is how to map the Params column which is text and contains JSON string of a Java object?

Answer 1

You can use a JPA converter to map your Entity to the database. Just add an annotation similar to this one to your params field:

@Convert(converter = JpaConverterJson.class)

and then create the class in a similar way (this converts a generic Object, you may want to specialize it):

@Converter(autoApply = true)
public class JpaConverterJson implements AttributeConverter<Object, String> {

  private final static ObjectMapper objectMapper = new ObjectMapper();

  @Override
  public String convertToDatabaseColumn(Object meta) {
    try {
      return objectMapper.writeValueAsString(meta);
    } catch (JsonProcessingException ex) {
      return null;
      // or throw an error
    }
  }

  @Override
  public Object convertToEntityAttribute(String dbData) {
    try {
      return objectMapper.readValue(dbData, Object.class);
    } catch (IOException ex) {
      // logger.error("Unexpected IOEx decoding json from database: " + dbData);
      return null;
    }
  }

}

That's it: you can use this class to serialize any object to json in the table.