How to get array of row values from JDBC result se

问题:

I have a sql statement that brings back the following rows:

I would like to obtain these values from a JDBC result set as two objects. One for the customer with customerNo 1 and the other for customer 2. I would like the two objects to have another array value with the related Titles with the object values.

The structure would ultimately look like this (in JSON):

{customerNo : 1, ["Object 1", "Object 2", "Object 3"]}, {customerNo : 2, ["Object 4", "Object 5"]}

How can I accomplish this with JDBC?

回答1:

You can use a Map to initially collect the results in the format you want.

Map<Integer, Set<String>> customerTitles = new HashMap<Integer, Set<String>>();
while(resultSet.next()) {
    Integer custId = resultSet.getInt(1);
    Set<String> titles = customerTitles.containsKey(custId) ? 
            customerTitles.get(custId) : new HashSet<String>();
    titles.add(resultSet.getString(2));
    customerTitles.put(custId, titles);
}

Once you have it collected this way, you can iterate over the Map and in turn the Sets within and convert them to JSON

// Convert to Json array here using your JSON library
for(Integer custId :  customerTitles.keySet()) {
    for(String title : customerTitles.get(custId)) {

    }
}

回答2:

Two ways that I can think of.

1. Get the CustomerNo first with a SELECT DISTINCT query and keep it in a COLLECTION. Then for every (using a loop) CustomerNo in the COLLECTION perform a SELECT Title from table WHERE CustomerNo = <collectionValue> and create a new JSON Object for each CustomerNo.

2. Fetch the RESULTSET with a SELECT CustomerNo, Title FROM tablename ORDER BY CustomerNo. In the fetching code (inside the loop) of the RESULTSET assign a variable to the CustomerNo you are getting from the RESULTSET and check for the next row. If you encounter a new CustomerNo then create a new JSON Object.