luongkhanh luongkhanh - 2 months ago 8
Java Question

JSONObject and JDBC return format json incorrect

I have facing a problem in my small project. I have spend time about 4 days and not yet have best solution.
I want to have correct format following as:

{
"id": 1
"manufacturer": "Honda"
"cars": [4]
0: {
"id": 1
"name": "Honda1"
"price": "20000"
}
1: {
"id": 2
"name": "Honda2"
"price": "10000"
}
2: {
"id": 3
"name": "Honda3"
"price": "30000"
}
3: {
"id": 4
"name": "Honda4"
"price": "66000"
}

"id": 2
"manufacturer": "Huyndai"
"cars": [2]
0: {
"id": 1
"name": "Huyndai1"
"price": "20000"
}
1: {
"id": 2
"name": "Huyndai2"
"price": "10000"
}
}


This is my source code:

public static JSONObject fetchPersonCarInfo() throws Exception {

// get data from 2 tables: Cars and Manufacturer
String query = "SELECT "
+ " CAR.ID AS ID,"
+ " CAR.NAME AS NAME, "
+ " CAR.PRICE AS PRICE, "
+ " MAN.ID AS MANUFACTURER_ID, "
+ " MAN.NAME AS MANUFACTURER_NAME "
+ " FROM CARS CAR, "
+ " MANUFACTURER MAN "
+ " WHERE"
+ " CAR.MANUFACTURER_ID = MAN.ID "
+ " ORDER BY "
+ " CAR.NAME ";
statement = connection.prepareStatement(query);
rs = statement.executeQuery();
JSONArray carsArray = null;
while(rs.next()) {
carsArray = new JSONArray();
JSONObject personCarObj = new JSONObject();
personCarObj.put("manufacturer_id", (new String(rs.getString("manufacturer_id"))));
personCarObj.put("manufacturer_name", (new String(rs.getString("manufacturer_name"))));

carsArray.put(personCarObj);
JSONObject carObj = new JSONObject();
carObj.put("id", (new string(rs.getString("id"))));
carObj.put("name", (new String(rs.getString("name"))));
carObj.put("price", (new String(rs.getString("price"))));

carsArray.put(carObj);

for(int i=0 ; i< carsArray.length() ; i++) {

manufacturerObj.put(new String(rs.getString("manufacturer_id")), carsArray);
manufacturerObj.put(new String(rs.getString("manufacturer_name")), carsArray);
}
System.out.println(manufacturerObj.toString());
}
System.out.println("Ok");
}
catch (Exception e) {
// TODO Auto-generated catch block
if (connection != null) {
connection.close();
}
throw e;
}
finally {
if (connection != null) {
connection.close();
}
}

return manufacturerObj;
}


And then I build my code and deploy by url, it seem incorrect and there are what's wrong as I expected, and this is my result json format:

{
"1":
[
{
"manufacturer_id": "1",
"manufacturer_name": "Honda"
},
{
"id": "4",
"name": "Honda1",
"price": "66000"
}
],
"2":
[
{
"manufacturer_id": "2",
"manufacturer_name": "Toyota"
},
{
"id": "551",
"name": "Toyota1",
"price": "3233333"
}
],
"3":
[
{
"manufacturer_id": "3",
"manufacturer_name": "Huyndai"
},
{
"id": "641",
"name": "Huyndai1",
"price": "66000"
}
],
"5":
[
{
"manufacturer_id": "5",
"manufacturer_name": "Mercedes"
},
{
"id": "581",
"name": "Mescedes1",
"price": "200000"
}
],
"Honda":
[
{
"manufacturer_id": "1",
"manufacturer_name": "Honda"
},
{
"id": "4",
"name": "Honda1",
"price": "66000"
}
],
"Huyndai":
[
{
"manufacturer_id": "3",
"manufacturer_name": "Huyndai"
},
{
"id": "641",
"name": "Huyndai1",
"price": "66000"
}
],
"Mercedes":
[
{
"manufacturer_id": "5",
"manufacturer_name": "Mercedes"
},
{
"id": "581",
"name": "Mescedes1",
"price": "200000"
}
],
"Toyota":
[
{
"manufacturer_id": "2",
"manufacturer_name": "Toyota"
},
{
"id": "551",
"name": "Toyota51",
"price": "3233333"
}
]
}


How I have to change my code to build correct as above json format. thank so much...

Answer

The carObj section is good, so I would recommend to make it a method.

public static JSONObject getCarJSON(String id, String name, String price) {
    JSONObject carObj = new JSONObject();                   
    carObj.put("id", id);                    
    carObj.put("name", name);
    carObj.put("price", price);
    return carObj;
}

Your problem seems to be here. You are adding a personCarObj to the carsArray. That should probably just contain cars.

JSONObject personCarObj = new JSONObject();                         
personCarObj.put("manufacturer_id", (new String(rs.getString("manufacturer_id"))));
personCarObj.put("manufacturer_name", (new String(rs.getString("manufacturer_name"))));
carsArray.put(personCarObj); 

And here. For every "car" in the carsArray, you are putting a key-value pair into manufacturerObj where the value is always the same carsArray. manufacturerObj should probably just contain manufacturer information.

for(int i=0 ; i< carsArray.length() ; i++) {

    manufacturerObj.put(new String(rs.getString("manufacturer_id")), carsArray);
    manufacturerObj.put(new String(rs.getString("manufacturer_name")), carsArray);
}

So, with those recommendations, you want to try to get something like your initially displayed non-JSON.

{
    "manufacturers": [{
        "id": "1",
        "name": "Honda",
        "cars": [{
            "id": "4",
            "name": "Honda1",
            "price": "66000"
        }]
    }, {
        "id": "2",
        "name": "Toyota",
        "cars": [{
            "id": "551",
            "name": "Toyota51",
            "price": "3233333"
        }]
    }]
}

And the code below might get close (warning: not tested). The reason for the map is to capture when you've seen a manufacturer already, then append the data rather than duplicate.

JSONArray manufacturerArray = new JSONArray();
HashMap<String, JSONArray> manufacturerMap = new HashMap<String, JSONArray>();

while(rs.next()) {
    String mId = rs.getString("manufacturer_id");
    JSONArray carsArray = manufacturerMap.get(mId);
    if (carsArray == null) {
        carsArray = new JSONArray();
        manufacturerMap.put(mId, carsArray);
    }
    JSONObject manufacturer = new JSONObject();                         
    manufacturer.put("id", mId);
    manufacturer.put("name", rs.getString("manufacturer_name"));                  

    String carId = rs.getString("id");                    
    String carName = rs.getString("name");
    String carPrice = rs.getString("price");
    carsArray.put(getCarJSON(carId, carName, carPrice));

    manufacturer.put("cars", carsArray);
    manufacturerArray.put(manufacturer);
}
JSONObject result = new JSONObject();
result.put("manufacturers", manufacturerArray);

You could use Jackson/Gson to make some POJO's like this

class Manufacturer {
    String id, name;
    List<Car> cars;
} 

class Car {
    String id, name, price;
}

Then you would serialize a List<Manufacturer>