Insert Json string into MsSQL with JAVA

Question

I got MsSQL server 2012 and Json string with fixed schema.

I'm trying to insert Json into the server, there must be a way to automatically parse the Json and then insert all the values.

This is how the Json look like:

{
    "UUID": "1408611728327",
    "accuracy": 0,
    "timestamp": 1408611668.274444000,
    "x": -2.46,
    "y": 24.779999,
    "z": -17.46
}

MongoDB does this automatically, but with a RDB system like mySQL, the tables have all got to be set up beforehand, and there is no way to automatically parse — ControlAltDel
– ControlAltDel, Commented Sep 11, 2014 at 15:00
Why don't you just write a method that takes in this data, and persists it to the database? Then you have a way to automatically parse it: call the method. — forgivenson
– forgivenson, Commented Sep 11, 2014 at 15:05

Attila Neparáczki · Accepted Answer · 2014-09-11 15:24:00Z

1

Maybe reformat with a regex? So it becomes like:

insert into my_table values ('1408611728327',0,1408611668.274444000,-2.46,24.779999,-17.46);

This of course assuming the table fields are in the correct order. Something like:

myJson.replace("{","("); // you need round brackets instead of the json style ones
myJson.replace("}",")");
myJson.replaceAll("\".*\": ([^,]*),","$1"); // drops the name of the columns, only keeps the values, comma separated
myJson.replaceAll("\"","\'"); // sql strings are between ' not "
myJson= "insert into my_table values " + myJson + ";";

This will fail if the json has a null attribute and therefore not showing (if x is null the json won't contain x at all, maybe can be changed by config).

answered Sep 11, 2014 at 15:24

Attila Neparáczki

4961 gold badge6 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

Insert Json string into MsSQL with JAVA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related