JPA map MySQL json type, got garbled string

Question

I'm using AttributeConverter to convert MySQL JSON column to Object as mentioned in https://stackoverflow.com/a/31547965/1633272

It works fine when storing data into MySQL, I can see the correct values in WorkBench.

But, when getting the column back to java application, one unicode char is mapped into 3 chars.

Here's an example, the JSON string "中" just has 1 unicode char wrapped by quotes. In Evaluation Expression window I can see it is mapped as

0 = '"' 34
1 = 'ä' 228
2 = '¸' 184
3 = '\u00AD' 173
4 = '"' 34

Libs I'm using:

'mysql:mysql-connector-java:5.1.39',
'org.springframework.data:spring-data-jpa:1.9.0.RELEASE',
'org.hibernate:hibernate-entitymanager:5.0.6.Final',

Seems it is a known issue for MySQL: https://bugs.mysql.com/bug.php?id=80631

So you're getting UTF-8 out of the database? Where's the problem? — Kayaman, Jun 23 '16 at 07:04
The problem is that one unicode char is mapped into 3 garbled chars. In my example the correct JSON string has 3 chars (including 2 quotes), but now it has 5 chars when getting from MySQL to Java. — user1633272, Jun 23 '16 at 07:10
OK, so how can I get the JSON string back correctly like the other columns? — user1633272, Jun 23 '16 at 07:15
At this point you should clarify to yourself what character encodings are. It'll be very useful the next time you get "garbled" characters. — Kayaman, Jun 23 '16 at 07:23
Sorry I didn't mention, it is UTF-8. Don't know why it isn't mapped correct back to Java. I have a workaround now, still don't know it is a environment issue or library issue. — user1633272, Jun 23 '16 at 07:28
Usually (well, always) the case is that one "side" is using a different encoding than the other. If regular text columns are returned correctly, then the problem could be with the object mapper. — Kayaman, Jun 23 '16 at 07:36
It's not in ObjectMapper, the ObjectMapper is converting String to Object, but the String itself is already wrong. — user1633272, Jun 23 '16 at 07:38
Yes of course. But can you read unicode data out of a VARCHAR field properly? If not, then it's an easy issue. If yes, then it'll be harder to debug. — Kayaman, Jun 23 '16 at 07:41
Yes, it shows correctly in WorkBench just like the other columns. It is mostly like JPA doesn't interpret the column as UTF-8. So now I have to re-interpret it myself. — user1633272, Jun 23 '16 at 07:45
And you have the settings correct like [here](http://stackoverflow.com/questions/18163328/jpa-utf-8-characters-not-persisted)? — Kayaman, Jun 23 '16 at 07:53
I assume if settings not correct, other utf-8 columns will be wrong. The situation here is all persistence & reading (except reading on JSON column) are correct. However it is worth to double check. — user1633272, Jun 23 '16 at 08:01
Dup of http://stackoverflow.com/questions/279170/utf-8-all-the-way-through . — Rick James, Jun 26 '16 at 05:47

score 2 · Accepted Answer · answered Oct 15 '16 at 04:33

2

This bug has been fixed in mysql-connector-java:5.1.40

answered Oct 15 '16 at 04:33

user1633272

2,007
5
25
48

user1633272 · Answer 2 · 2016-06-23T13:34:33.503

Seems that JSON column should not be mapped as String.

It should be mapped as byte[], then converted to Object.

public class JsonAttributeConverter<T extends Object> implements AttributeConverter<T, byte[]> {
    private final static ObjectMapper objectMapper = new ObjectMapper();
    private final Class<T> clazz;

    public JsonAttributeConverter(Class<T> clazz) {
        this.clazz = clazz;
    }

    @Override
    public byte[] convertToDatabaseColumn(Object attribute) {
        try {
            return objectMapper.writeValueAsString(attribute).getBytes();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public T convertToEntityAttribute(byte[] dbData) {
        try {
            return objectMapper.readValue(dbData, clazz);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

Update:

Using code above, reading is OK, but when persisting, there is an exception:

Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'.

JPA map MySQL json type, got garbled string

2 Answers2

Linked

Related