Convert an SQL command to JSON parameter format

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

1. Remove all single quotes from the query.
2. Create a class with a fields field.
3. 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.
4. For each match: split the groups on a comma, and put the <column, column value> into a map. Add this map to a list.
5. Create a new instance of the object created in step #2, and set the list of maps as the fields value.
6. Use GSON or Jackson libraries to convert the instance from step #5 into JSON.

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"
    }]

}