Generate SQL insert statements from JSON Object

Question

So I have a sql database which I know the schema to. What I want to do is have a model of that Entity/Table in json format, is there a library that will parse my JSON object and give me sql insert statements for that JSON?

For example, suppose I had

{
  Table:{
    id: 'abcd'
    name: 'Jim'
    apples: '5'
  }
}

Is there a library that would take that in and give me

INSERT INTO Table1 ('id', 'name', 'apples') VALUES ('abcd', 'Jim', '5');

Thank you in advance. IT's also fine if there's library for doing that but with format that can be translated into from JSON such as XML

Javascript, I found this npm module called json-sql but it outputs it in this format insert into test (check1, check2, check3) values ($p1, $p2, $p3); { p1: 'check1', p2: 'check2', p3: 'check3' } Which i recognize is php formatting, but is there a way to quickly make that recognized by sql? — hthomos
– hthomos, Commented Oct 21, 2014 at 20:01
I wanna insert my json object into dashdb or any other SQL database directly. Rather than having to prepare a query. Is there any javascript library that can help do that like spring data jpa in Java? Just wanna do - db.insert('MyTable', {name:'Adam', phone:'0576940', email:'[email protected]'}); — Sacky San
– Sacky San, Commented Sep 22, 2016 at 6:41

Grice · Accepted Answer · 2014-10-21 18:57:22Z

1

It shouldn't be too hard to code yourself in java. Use GSON or some other json parsing library to parse the JSON. Iterate over each pair in the json and use PreparedStatements to build a sql string.

Something like this to build the prepared statement:

    String query = "Insert Into Table1 (";
    for (int i = 0; i < numOfPairs; i++) {
        query += "?, ";
    }
    query += ") Values (";
    for (int j = 0; j < numOfPairs; j++) {
        query += "?, ";
    }
    query += ")";

Then just iterate through each pair and set your values in the statement. Ensure your values are sanitized if from a user.

Note: This is really meant as some proof of concept and strings shouldn't be built this way. Use things like StringBuilder to build your prepared statement.

answered Oct 21, 2014 at 18:57

Grice

1,37511 silver badges24 bronze badges

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

1 Comment

Sacky San Over a year ago

This is too much of a code for different types of SQL statements. Insert may be taken care of what about others? I wanna insert/delete/modify my record from dashdb or any other SQL database directly using simple JavaScript function like JPA provides in Java, rather than having to prepare a query. Is there any javascript library that can help do that Just wanna do - db.save('MyTable', {name:'Adam', phone:'0576940', email:'[email protected]'}); or db.update('MyTable', {name:'Adam', phone:'0576940', email:'[email protected]'}); or db.delete('MyTable', {name:'Adam'});

Collectives™ on Stack Overflow

Generate SQL insert statements from JSON Object

1 Answer 1

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