insert Map into MySQL JSON column

Question

I have the following code:

public void createFilter(String value1, String value2, Map<String, Object> value3) {        
   jdbcTemplate.update("INSERT INTO mytable(string_column1, string_column2, json_column3) VALUES(?, ?, ?)", new PreparedStatementSetter() {              
            public void setValues(PreparedStatement stmt) throws SQLException {
                stmt.setString(1, value1);
                stmt.setString(2, value2);
                stmt.setObject(3, value3);
            }
        }); 
}

The json_column3 is of type JSON. When I execute this method, I have the following error message:

Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'.; nested exception is com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'."

I will be very appreciated for any help.

This might help you https://stackoverflow.com/questions/38078119/mysql-5-7-12-import-cannot-create-a-json-value-from-a-string-with-character-set — Romil Patel, Mar 07 '19 at 16:40
Duplicate of [38078119](https://stackoverflow.com/questions/38078119/mysql-5-7-12-import-cannot-create-a-json-value-from-a-string-with-character-set) — Amit K Bist, Mar 07 '19 at 22:22
Possible duplicate of [MySQL 5.7.12 import cannot create a JSON value from a string with CHARACTER SET 'binary'](https://stackoverflow.com/questions/38078119/mysql-5-7-12-import-cannot-create-a-json-value-from-a-string-with-character-set) — Amit K Bist, Mar 07 '19 at 22:22
This is not a duplicate. It is similar but not the same as the question in the aforementioned comments. I believe the above 3 comments are wrong. — Shadow Man, Mar 07 '19 at 23:14
I tried various of changes on charset (utf8mb4), but it doesn't fixed in this case. — ks099, Mar 08 '19 at 14:52
This works: String jsonStr = new ObjectMapper().writeValueAsString(value3); stmt.setString(3, jsonStr); — ks099, Mar 08 '19 at 15:25

insert Map into MySQL JSON column

0 Answers0