CODEWITHSUNDEEP
mysqljsondatabase
user1823924
user1823924

Reputation:

JSON MySQL database using Java

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.

Upvotes: 1

Views: 604

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

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.

Upvotes: 1

Related Questions