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.
3 Answers
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"}
4 Comments
Kushan
i am not saving JSON as a string inside varchar or text. I was talking about the new JSON data type dev.mysql.com/doc/connector-j/5.1/en/… according to mysql docs, the connector does not support this type yet
Youcef LAIDANI
i know @Kushan for example you are using
CREATE TABLE t1 (jdoc JSON); JSON in your database, so to get this result you have to use getString to get JSonKushan
have you tested if this works? if so i'll accept your answer.
Youcef LAIDANI
@Kushan yes double check with MySQL 5.7 and PostgreSQL 9.4 work perfectly
In case anyone is still looking, I was trying it with Mysql 5.7, but the methods suggested above don't handle non-ascii data quite well a lot of asian characters etc). They end up displaying as garbled.
The solution is to read the raw bytes using getBinaryStream. The code is demonstrated below:
public String fromJSON(ResultSet rs, String fieldName){
InputStream is = rs.getBinaryStream(fieldName);
BufferedReader br = new BufferedReader(new InputStreamReader(is));
String content = br.lines().reduce("", String::concat);
br.close();
return content;
}
The above could be encapsulated in a function fromJSON and used widely. e.g
PreparedStatement st = getStatement("select json from x;");
ResultSet rs = st.executeQuery();
while (rs.next()) {
return fromJSON(rs, "json");
}
Comments
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)
String? Then you could use some library like Jackson to parse this string into a POJO.