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

Answer 1

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'.

Answer 2

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