How to read JSON data type in mysql using JDBC

Question

Mysql 5.7 introduces a JSON data type that offers tons of query functions.

Since there is no compatible result function, how and what can I use to retrieve the data stored in this data type.

Answer 1

He must be rs.getString, because the getStringused to VARCHAR, TEXTwe can consider JSonas a type String, 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();
    }
}

He prints to me:

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