java - JSONObject 和 JDBC 返回格式 json 不正确

Question

我在我的小项目中遇到了问题。我花了大约 4 天的时间，但还没有最好的解决方案。

这是我的源代码:

public static JSONObject fetchPersonCarInfo() throws Exception {

    // get data from 2 tables: Cars and Manufacturer            
    String query = "SELECT * from mytable";       
        statement = connection.prepareStatement(query);         
        rs = statement.executeQuery();
        JSONArray carsArray = null;                         
        while(rs.next()) {

            carObj.put("id", (new  string(rs.getString("id"))));                    


            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; 
}        

然后我构建我的代码并通过 url 进行部署，它似乎不正确并且有我预期的错误，这是我的结果 json 格式:

{
    "1":
    [
        {
            "manufacturer_id": "1",
            "manufacturer_name": "Honda"
        },
        {
            "id": "4",
            "name": "Honda1",
            "price": "66000"
        }
    ],        
}

我如何更改我的代码以构建正确的上述 json 格式。非常感谢...

Answer 1

carObj部分很好，所以我建议将其作为一种方法。

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;
}

您的问题似乎出在这里。您正在添加 personCarObj到 carsArray .那应该只是包含汽车。

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); 

还有这里。对于 carsArray 中的每辆“汽车” ，您将键值对放入 manufacturerObj其中值始终相同 carsArray . manufacturerObj应该只是包含制造商信息。

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);
}

---

因此，根据这些建议，您想要尝试获得类似于最初显示的非 JSON 格式的内容。

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

下面的代码可能会很接近(警告:未测试)。该 map 的原因是在您已经看到制造商时进行捕获，然后附加数据而不是复制。

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);

---

您可以使用 Jackson/Gson 来制作一些像这样的 POJO

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

class Car {
    String id, name, price;
}

然后你会序列化一个List<Manufacturer>