How to store Java class as JSON in MySQL8?

Question

In our project we have a need to store some Java objects (with nesting level > 3) as JSONs because structure of this objects are often changed and this is really no convenient to use relational approach to store them.

We were considering different DBs to store objects as JSONs (we are using AWS):

• DynamoDB;
• DocumentDB;
• MySQL 8;

Currently we are considering MySQL 8 as basic option because we are more familiar with this db and it will take less effort to setup it. I was trying to find how to properly store Java objects as JSONs in MySQL 8 and checked this articles: https://prateek-ashtikar512.medium.com/how-to-handle-json-in-mysql-4adaeeb1d42f https://vladmihalcea.com/how-to-map-json-objects-using-generic-hibernate-types/

But I did not find a way how to store whole class as a JSON, not only one field from some class or String field that will have JSON representation of the class.

So my question is - is that possible to store class like:

public class SomeClass {

  private Long id;

  private Instant date;

  private String someInfo;

  private Integer version;

}

as JSON without having this class as field in some other class ?

P.S. I don't want to have using of Jackson libraries in my code because developers can add bugs in this part of the code and destroy structure of object to save.

P.S.S.

I understand that I can do something like this:

@Data
@Entity
@Table(name = "my_table")
@TypeDef(name = "json", typeClass = JsonStringType.class)
public class MyClassWrapper {

    @Id
    private Long id;

    @Type(type = "json")
    @Column(name = "json_data",columnDefinition = "json")
    private MyClass myClass;
}

@Data
public class MyClass {

    private Instant date;

    private String someInfo;

    private Integer version;

}

My question is is - can I avoid having of MyClassWrapper and save MyClass as JSON directly (without using Jackson library by myself) ?

Answer 1

The most straight forward solution would be to use Jackson or Gson to convert any object into a json String or Byte[]. These can then be saved to your DB. restoring them is as easy as reversing that process. Here are some simple examples, saveDataToDB(String, Sring):void and loadDataFromDB(String):String are placeholders for whatever you need to do to save or load to your database.

//can be created globally and reused every time
ObjectMapper mapper = new ObjectMapper();

public void saveObjToDB(String id, Object obj)
{
    String jsonStr = mapper.writeValueAsString(myResultObject);
    saveDataToDB(id, jsonStr);
}

public <T> T loadObjFromDB(String id, Class<T> clazz)
{
    String jsonStr = loadDataFromDB(id);
    return mapper.readValue(jsonStr, clazz);
}

Or with Gson:

//can be created globally and reused every time
Gson gson = new Gson();

public void saveObjToDB(String id, Object obj)
{
    String jsonStr = gson.toJson(myResultObject);
    saveDataToDB(id, jsonStr);
}

public <T> T loadObjFromDB(String id, Class<T> clazz)
{
    String jsonStr = loadDataFromDB(id);
    return gson.fromJson(jsonStr, clazz);
}

The default configuration should already work for most classes, and in most cases only complex nesting or generics need more configuration. But even then, the mapper/gson class can be supplied as Singleton globally so that the configuration is done only once and therefore developers don't have to touch this all the time.