JSON insert get automatically encoded into base64

Question

I have a JSON object that I insert into my MySQL DB.

ps = con.prepareStatement("INSERT INTO tableA(a, b, c, d_json)" +
                    " VALUES(?,?,?,cast(? AS JSON))", Statement.RETURN_GENERATED_KEYS);
// some code
ps.setObject(4, jsonValue);

I insert a hashmap of string as :

    Map<String, String> map = new HashMap<String, String>();
    map.put("key", "value");
    map.put("key1", "value1");
    map.put("key2", "value2");

However the json get encoded as following :

'"base64:type15:rO0ABXNyABFqYXZhLnV0aWwuSGFzaE1hcAUH2sHDFmDRAwACRgAKRvckkACXRocmVz\naG9sZHhwP0AAAAAAAAx3CAAAABAAABnZhbHVlMXQABGtleTJ0AAZ2YWx1ZTJ0\nAANrZXl0AAV2YWx1ZXg="'

I would like to insert a string instead of this. I saw some posts on S.O but I did not find any proper way to fix it. Any help would be appreciated. Thanks !

so `map` is your `json'Value`? The base64 you show in your posts contains a lot of unprintable bytes, it's not a JSON representation of `map` — jps, Feb 26 '20 at 15:06
Yes it is. Indeed my issue is that even when I try to decode it using online tools it just returns something that does not have any sense. — Sox -, Feb 26 '20 at 15:13
does [this](https://stackoverflow.com/questions/12155800/how-to-convert-hashmap-to-json-object-in-java) help? — jps, Feb 26 '20 at 15:15
Not that much since I use the sql method: cast as json. Actually I d like to not transform my data that much that s why I simply use an hashmap instead of a JSON object. — Sox -, Feb 26 '20 at 15:42
But it obviously doesn't work and I wonder how should SQL know how to cast a Java object to JSON. — jps, Feb 26 '20 at 15:47
In the future, don't forget to tag your question, the database you're using is pretty important information for questions like this. — Mark Rotteveel, Feb 27 '20 at 09:12

score 1 · Answer 1 · answered Mar 03 '20 at 11:47

The Java Mysql Driver does not support automatic conversion of Hashmaps. In fact if you use an unkown type in setObject, it will use default Java Serialization to convert it to something it can handle. The database receives a byte array containing the serialized HashMap. The cast(? as JSON) converts that into something that can be stored in the column, which is the BASE64 encoded byte array.

You need to convert your HashMap to a JSON String in your Java code before sending it to the database.

Thanks a lot for answering and explaining why it did not work. I accepted Piotr 's answer but yours is as good as his ! — Sox -, Mar 04 '20 at 09:22

Piotr Niewinski · Accepted Answer · 2020-03-10T10:29:37.793

You can't directly write Java object into MySQL - you need to convert your map to JSON string before writing it to table column. According to official docs: "Attempting to insert a value into a JSON column succeeds if the value is a valid JSON value, but fails if it is not".

Here is an example (Jackson):

import com.fasterxml.jackson.databind.ObjectMapper;

public class Test {

  private static final ObjectMapper objectMapper = new ObjectMapper();

  public static String toJSON(Map<String, String> map) throws JsonProcessingException {
    return objectMapper.writeValueAsString(map);
  }
}

Another option would be to create one additional column, e, and then simply write map to two columns: map(k,v) => d(k), e(v)

JSON insert get automatically encoded into base64

2 Answers2