How to read JSON data type in mysql using JDBC

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)

It should be rs.getString, because getString used with VARCHAR, TEXT, we can consider JSon like a String type, so you can get the result, using getString.

---

Simple Example

Double check with MySQL 5.7 and PostgreSQL 9.4 :

MySQL 5.7 SQL

create database db_test;
create table table_test(json JSON);
INSERT INTO table_test VALUES('{"key1": "value1", "key2": "value2"}');

CODE

public static void checkJSon() {
    try {
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(DB_URL, DB_username, DB_password);
        String q = "SELECT * FROM table_test";
        PreparedStatement preparedStatement = connection.prepareStatement(q);
        preparedStatement.execute();
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString("json"));
        }

    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    }
}

It print to me :

{"key1": "value1", "key2": "value2"}