MySQL JSON type insert and select using JDBC drivers

user_1577652 0
Viewed 1382

I am reading and writing mysql JSON type using JDBC driver and ResultSet. How do I fetch the JSON type from ResultSet as string JSON to be used in java program ?

mysql> set names UTF8MB4; // so that json values can be inserted using jdbc driver otherwise it was complaining of binary type

Java code (pseudo code):

String myjsonString = "{x: y}"; // assume this is proper
conn.getPreparedStatement("insert into col_json values (myjsonString)");


rs = conn.getStatement("select col_json from table_json_test");

String myorig_json = rs.getString("col_json");

myorig_json is coming as a string like this:

"base64:type15:rO0ABXNyABdqYXZhLnV0aWwuTGlua2VkSGFzaE1hcDTATlwQbMD7AgABWgALYWNjZXNzT3JkZXJ4
cgARamF2YS51dGlsLkhhc2hNYXAFB9rBwxZg0QMAAkYACmxvYWRGYWN0b3JJAAl0aHJlc2hvbGR4
cD9AAAAAAAAMdwgAAAAQAAAAAXQABXJlZmlkdAAgZWI3ODkzZWVjMDU5NGNhMjlhYjY1OTJjMjFj
MjIwNWZ4AA=="

expected is "{x: y}"

mysql json
1 Answers

The JSON data type in MySQL is a special type that's stored internally as a binary object. This is why you can't get the value from the database as a string - the JDBC driver will grab the contents of the binary value and try to read it as a String, giving you the garbage you see.

JDBC drivers don't support the JSON object type at all. Instead, you have two options:

  1. Store your JSON in a TEXT field - then you can use getString to read it, but you'll have to parse it yourself.

  2. Use MySQL's special JSON query language to build your queries, which will mean that your ResultSet contains data read from the JSON and converted for you. For example, having the query:

SELECT col_json->>'$.x' AS x FROM <table>

will give you a ResultSet containing:

+----+
|  x |
+----+
|  y |
+----+

and you can use resultSet.getString(1) to extract the value.

There are plenty of tutorials around for how to query JSON fields in MySQL. Here's a nice simple one to start you off: http://www.mysqltutorial.org/mysql-json/

user_34168970
Related