JSON MySQL database using Java

Question

I am currently writing a Java program and decided to use JSON MySQL as my database. What is the correct syntax for me to insert a variable to JSON MySQL Database.

Table:

mysql> desc sales;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| sale  | json    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

Java Code:

String name = "Izzat";
String place = "Cheras";

String sql = "INSERT INTO sales(id, sale) VALUES ('9','{\"Name\":"+name+",\"Place\":"+place+"}')";

I got an error:

INSERT INTO sales(id, sale) VALUES ('9','{"Nama":Izzat,"Place":"Cheras"}') Data truncation: Invalid JSON text: "Invalid value." at position 8 in value for column 'sales.sale'.

May I know what is the correct syntax for this?

Thank you in advance.

Tim Biegeleisen · Accepted Answer · 2018-08-16 02:18:03Z

1

The immediate cause of the error seems to be that the data is missing double quotes. So this should work:

String sql = "INSERT INTO sales(id, sale) ";
sql += "VALUES (9, '{\"Name\": \"" + name + ", \"Place\": \"" + place + "\"}')";

Demo

However, given that the JSON data is coming from your Java code, you should consider using one of the JSON APIs available to generate the JSON string. Building a JSON string like this manually, as you have just seen, is error prone.

answered Aug 16, 2018 at 2:18

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1823924 Over a year ago

I'm new with JSON. Will try to look into your suggestion. Thank you very much.

Collectives™ on Stack Overflow

JSON MySQL database using Java

1 Answer 1

Demo

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related