Jared Hart Jared Hart - 23 days ago 5
Java Question

Java JSON Array of String Arrays formatting issue

As the title suggests, I'm having an issue with my JSON formatting for an Array of String Arrays (List)

Basically, this is the format that I would like to see:

{"Item":
["Id: 1","Title: Surface Pro 4","Phone: 4077603835","Email:
XXXXXXXX@gmail.com","Description: Surface Pro 4 for sale, in
excellent condition, basically brand new!"],

"Item":
"Id: 2","Title: Macbook Air","Phone: null","Email:
null","Description: null"],
"Item":
"Id: 3","Title: Lenovo Laptop","Phone: 3433215565","Email:
XXXXXXXX.com","Description: Free Macbook Air, I promise"]
}


However, this is the below format that my code is producing:

{"Item":["Id: 1","Title: Surface Pro 4","Phone: 4077603835","Email:
XXXXXXXX@gmail.com","Description: Surface Pro 4 for sale, in
excellent condition, basically brand new!","Id: 2","Title: Macbook
Air","Phone: null","Email: null","Description: null","Id: 3","Title:
Lenovo Laptop","Phone: 3433215565","Email:
XXXXXXXX.com","Description: Free Macbook Air, I promise"]}


I just want to see separate arrays for each item! I think the issue lies with this part of code: "obj.put("Item", items);" as it's putting all the items in the same array, but how can I dynamically create different arrays? My list of items will keep expanding and will never be constant.

Code:

public List<String[]> SelectAllSQL() {
List<String[]> result = new ArrayList<String[]>();

sql = "select * from item";

try {
ResultSet rs = stmt.executeQuery(sql);

rs.last();
int lastRow = rs.getRow();

for(int row = 1; row <= lastRow; row++) {
rs.absolute(row);

result.add(new String[] {rs.getString("title"),
rs.getString("phone"), rs.getString("email"),
rs.getString("description"),
rs.getString("id")});
}
return result;
}
catch (SQLException e) {
e.printStackTrace();
}
return null;
}

public String getIt() {
ConnectionDB db = new ConnectionDB();
QueryDB query = new QueryDB();

List<String[]> sqlResult = new ArrayList<String[]>();

db.Connect();

sqlResult = query.SelectAllSQL();

JSONObject obj = new JSONObject();
JSONArray items = new JSONArray();

for(String[] arr : sqlResult) {
items.add("Id: " + arr[4]);
items.add("Title: " + arr[0]);
items.add("Phone: " + arr[1]);
items.add("Email: " + arr[2]);
items.add("Description: " + arr[3]);

obj.put("Item", items);
}
return obj.toJSONString();

Answer

Two things:

  1. Re-initialize the JSONArray for each item inside the loop, so that each row of your sqlResult will have its own array of attributes. The existing code initializes it once, which is how the array in the current output ended up with data from all three in it.

    for(String[] arr : sqlResult) {
        JSONArray items = new JSONArray();
    
  2. JSONObject does not allow multiple entries with the same key. JSONObject will not allow you have three entries, all with the same value of "Item". When obj.put("Item", items); gets called the second and third time, it replaces the previous value of "Item" instead of making new entries. Two of many possible solutions, both require changing your desired output

    • Choose a key that's unique for each record, such as maybe the id
    • Create another JSONArray containing the 3+ items instead of having it be an associative JSONObject