Storing JSON In MySQL

Question

I am trying to store JSON as string in mySql. I am getting below error :

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key, value) values ('SYSTEM_USER', '2017-11-30 13:28:38.512', 'SYSTEM_US' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)

Code Snippet :

    final Information informationObj = Information.newBuilder().withCreateBy("").withCreatedDate(null)
    .withModifiedBy("").withModifiedDate(null).withValue(jsonString).withKey(id).build();
    dataRepository.save(informationObj);

Value is type of json in db. Fields in table :

`_id`, `key`, `value`, `created_date`, `created_by`, `modified_by`, `modified_date`

What i think, its probably because of json format because it contains commas etc characters. What is the solution to store this json in MYSQL DB. Thanks,

hope it will help you https://dev.mysql.com/doc/refman/5.7/en/json.html there is a json datatype — SpringLearner, Nov 30 '17 at 08:07
The bug is in your code. But we have no way of what your code looks like, so... — JB Nizet, Nov 30 '17 at 08:07

score 0 · Answer 1 · answered Nov 30 '17 at 08:15

0

This exception happens where your trying to use a restricted keyword which breaks the sql insert.

System_user may be a key word in MySQL

Are you using jpa insert ?

answered Nov 30 '17 at 08:15

adia

404
2
8

yes, JPA insert – Vivek Dhiman Nov 30 '17 at 10:24
Then the issue is related to musql reserved keyword. – adia Nov 30 '17 at 10:54
yes it was issue of reserved keyword "key" – Vivek Dhiman Dec 01 '17 at 06:38

Storing JSON In MySQL

1 Answers1