Convert an SQL command to JSON parameter format

Question

I've have in the example below a string which holds an sql insert command.

I would like to parse the field name and values and convert them to JSON.

How can I achieve this goal using Java?

My SQL command in a String:

String sqlQuery = "INSERT INTO myTable (ID, CITY, STATE, LAT_N, LONG_W) VALUES " +
                  "(13, 'Phoenix', 'AZ', 33, 112); |\r\n" + 
                  "INSERT INTO myTable (ID, CITY, STATE, LAT_N, LONG_W) VALUES " +
                  "(44, 'Denver', 'CO', 40, 105); |\r\n" + 
                  "INSERT INTO myTable (ID, CITY, STATE, LAT_N, LONG_W) VALUES " +
                  "(66, 'Caribou', 'ME', 47, 68); |";

I'm expecting output like below:

{
    "fields": [{
        "ID": "13",
        "CITY": "Phoenix",
        "STATE": "AZ",
        "LAT_N": "33",
        "LONG_W": "112"
    }, {
        "ID": "44",
        "CITY": "Denver",
        "STATE": "CO",
        "LAT_N": "40",
        "LONG_W": "105"
    }, {
        "ID": "66",
        "CITY": "Caribou",
        "STATE": "ME",
        "LAT_N": "47",
        "LONG_W": "68"
    }]

}

If it always have the format of this, I would first carve out the two '()'s which have all the fields. Since it is a one to one relationship, you can easily split it by a comma and iterate both arrays to store it to a map. GSON or other libraries probably have a method to convert a map to json. — mckuok, Apr 19 '18 at 03:50
@Henry : we can point him and future referrers to this question in the right direction, right ? — Rann Lifshitz, Apr 19 '18 at 03:54
You could do something like this split the string on ( and ) then you will get 4 element in the list or array you can take 1st and 3rd index fields and again split them on , just creat a map and convert it into json. if the structure will be the same for the string which you are going to be split — Raman Mishra, Apr 19 '18 at 03:55
You could use the substring method to cut specific indexes, as example sqlQuery.substring(sqlQuery.indexOf("("), sqlQuery.indexOf(")")) would give you the first set of fields. Then you can rinse and repeat by cutting the string each time starting at the first index of ')' character. — billy.mccarthy, Apr 19 '18 at 03:56
Do you want to parse arbitrary insert statements or inserts into this particular table? Also, you need to provide your own attempt at a solution. There're multiple subtasks here and it's unclear which one is problematic to you. — default locale, Apr 19 '18 at 03:59
@Learn Java : you've got some good answers to this question, mainly from Angel Koh who took the time to write you all the required code, I strongly suggest you pick one of the answers and select it as your answer. The people who wrote the answer took the time to help you out, it's the least you can do in return. — Rann Lifshitz, Apr 19 '18 at 06:11
Using regular expressions you have some problems to solve, e.g. inner string quotation. But if you have only to deal with this simple kind of inserts you could give it a try. For more complex parsing you should use some SQL parser like JSqlParser to extract your data. — wumpz, Apr 20 '18 at 06:08

score 1 · Answer 1 · edited Apr 19 '18 at 04:45

Here's the actual code that will produce the desired JSON format output

I have commented the logic of each part:

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class ToJson {

    public static void main(String[] args) {
        String sqlQuery = "INSERT INTO myTable (ID, CITY, STATE, LAT_N, LONG_W) VALUES (13, 'Phoenix', 'AZ', 33, 112); |\r\n" +
                "INSERT INTO myTable (ID, CITY, STATE, LAT_N, LONG_W) VALUES (44, 'Denver', 'CO', 40, 105); |\r\n" +
                "INSERT INTO myTable (ID, CITY, STATE, LAT_N, LONG_W) VALUES (66, 'Caribou', 'ME', 47, 68); |";

        //REMOVE UNNEEDED STUFF
        String trimUnwanted = sqlQuery.replaceAll("INSERT INTO myTable \\(ID, CITY, STATE, LAT_N, LONG_W\\) VALUES \\(", "")
                .replaceAll("'", "")
                .replaceAll("\\);", ""); 
        System.out.println("afterTrim: " + trimUnwanted);
        /*
        afterTrim: 13, Phoenix, AZ, 33, 112 |
        44, Denver, CO, 40, 105 |
        66, Caribou, ME, 47, 68 |
         */

        //SEPARATE INTO SEPARATE QUERIES
        String[] choppedUpText = trimUnwanted.split("\\|");
        System.out.println("Chopped up text >>");
        Arrays.stream(choppedUpText).forEach(System.out::println);

        //SEPARATE EACH QUERY INTO AN OBJECT, AND ADD TO LIST
        List<Address> list = new ArrayList<>();
        Arrays.stream(choppedUpText).forEach(s -> {
            System.out.println("adding -> "+ s);
            list.add(new Address(s.split(",")) ); //remember to do your error checking!
        }) ;

        String jsonStart = "{\n     \"fields\": " ;
        String jsonEnd = "\n\n}";

        //THE ACTUAL RESULT
        String json = jsonStart + list.toString() + jsonEnd;

        System.out.println(json);
    }

}

/* THE MAGIC CLASS  HANDLES THE PARSING AND DISPLAY */
class Address {

    String ID;
    String CITY;
    String STATE;
    String LAT_N;
    String LONG_W;

    public Address(String[] input) {
        this.ID = input[0].trim();
        this.CITY = input[1].trim();
        this.STATE = input[2].trim();
        this.LAT_N = input[3].trim();
        this.LONG_W = input[4].trim();
    }

    @Override
    public String toString() {
        //prints out in correct format
        return " {\n" +
                "        \"ID\": \""+ ID+ "\",\n" +
                "        \"CITY\": \""+ CITY+ "\",\n" +
                "        \"STATE\": \""+ STATE+ "\",\n" +
                "        \"LAT_N\": \""+ LAT_N+ "\",\n" +
                "        \"LONG_W\": \""+ LONG_W+ "\"\n" +
                "    }";
    }
}

And the output of the code above is:

afterTrim: 13, Phoenix, AZ, 33, 112 |
44, Denver, CO, 40, 105 |
66, Caribou, ME, 47, 68 |
Chopped up text >>
13, Phoenix, AZ, 33, 112 

44, Denver, CO, 40, 105 

66, Caribou, ME, 47, 68 
adding -> 13, Phoenix, AZ, 33, 112 
adding -> 
44, Denver, CO, 40, 105 
adding -> 
66, Caribou, ME, 47, 68 
{
     "fields": [ {
        "ID": "13",
        "CITY": "Phoenix",
        "STATE": "AZ",
        "LAT_N": "33",
        "LONG_W": "112"
    },  {
        "ID": "44",
        "CITY": "Denver",
        "STATE": "CO",
        "LAT_N": "40",
        "LONG_W": "105"
    },  {
        "ID": "66",
        "CITY": "Caribou",
        "STATE": "ME",
        "LAT_N": "47",
        "LONG_W": "68"
    }]

}

I assume OP wants to take the column names out of the first set of parentheses in each SQL statement, rather than having them hard-coded. — Dawood ibn Kareem, Apr 19 '18 at 04:23
Ahhh, that makes sense. just change the Address class to handle key-value pairs, split with " VALUES " as the delimiter and use the first portion as the key and the second portion as the value. — Angel Koh, Apr 19 '18 at 04:49

score 1 · Answer 2 · answered Apr 19 '18 at 04:41

Here's one approach on how you can do it. This assumes that the column names are dynamic.

Remove all single quotes from the query.
Create a class with a fields field.
Use a regex + java.util.Pattern + jave.util.Matcher to extract the items between (). You will get 3 matches, each one will have 2 groups, one with the column names and one with te values.
For each match: split the groups on a comma, and put the <column, column value> into a map. Add this map to a list.
Create a new instance of the object created in step #2, and set the list of maps as the fields value.
Use GSON or Jackson libraries to convert the instance from step #5 into JSON.

Convert an SQL command to JSON parameter format

2 Answers2