How to read JSON data type in mysql using JDBC

Question

Mysql 5.7 introduced the JSON data type which offers tonnes of query functions.

Since there isn't a compatible resultset function, how and what to i do use retrieve the data stored in this datatype.

Answer 1

In case JDBC isn't a hard requirement, recent jOOQ versions implemented an out-of-the-box way to map SQL JSON values to arbitrary Java types using the popular JSON mapping libraries Jackson or gson (whichever can be found on your classpath). You can then write:

List<MyClass> list =
ctx.select(TABLE.ID, TABLE.JSON_COLUMN)
   .from(TABLE)
   .where(...)
   .fetchInto(MyClass.class);

With, for example:

public class MyClass {
    public int id;
    public MyOtherClass jsonColumn;
}

public class MyOtherClass {
    public String something;
    public List<String> list;
}

Assuming your JSON_COLUMN contains data of the form:

{
  "something": "x",
  "list": [ "a", "b", "c" ]
}

This also works with all the natively supported SQL/JSON functions, to create such JSON documents on the fly.

(Disclaimer: I work for the company behind jOOQ)