What abstraction to use for hiearchical sum from ordered list

Question

I am looking for an abstraction from which to solve a class of problems similar to the following:

I have this ordered ResultSet (in this case sorted for NATION,REGION,DISTRICT,COUNTRY):

CITY' Table

| NATION | REGION | DISTRICT | COUNTRY |    POPULATION |
--------------------------------------------------------
ITALIA     CAMPANIA CE          AVERSA          70.000
ITALIA     CAMPANIA CE          CASERTA        500.000
ITALIA     CAMPANIA CE          MARCIANISE   5.000.000
ITALIA     CAMPANIA SA          BATTIPAGLIA     50.000
ITALIA     CAMPANIA SA          EBOLI           60.000
ITALIA     CAMPANIA SA          SARNO           70.000
ITALIA     LAZIO    RM          CAMPAGNANO      50.000
ITALIA     LAZIO    RM          FORMELLO       500.000
ITALIA     LAZIO    RM          ROMA         5.000.000
ITALIA     LAZIO    VT          BOLSENA         50.000
ITALIA     LAZIO    VT          FALERIA         60.000
ITALIA     LAZIO    VT          NEPI            80.000
....
ALBANIA ...
...
RUSSIA....
...

I want to get this output as a list of City objects in which the different totals (groupings) will be included of the population.

| NATION | REGION | DISTRICT |  COUNTRY   |  POPULATION |
--------------------------------------------------------
ITALIA     CAMPANIA CE          AVERSA          70.000
ITALIA     CAMPANIA CE          CASERTA        500.000
ITALIA     CAMPANIA CE          MARCIANISE   5.000.000
ITALIA     CAMPANIA SA          BATTIPAGLIA     50.000
ITALIA     CAMPANIA SA          EBOLI           60.000
ITALIA     CAMPANIA SA          SARNO           70.000
ITALIA     LAZIO    RM          CAMPAGNANO      50.000
ITALIA     LAZIO    RM          FORMELLO       500.000
ITALIA     LAZIO    RM          ROMA         5.000.000
ITALIA     LAZIO    VT          BOLSENA         50.000
ITALIA     LAZIO    VT          FALERIA         60.000
ITALIA     LAZIO    VT          NEPI            80.000
...         ...     ...         ...             ...
TOT. CE                                      5.570.000
TOT. SA                                        230.000
TOT. RM                                      5.550.000
TOT. VT                                        240.000
TOT. CAMPANIA                                5.800.000
TOT. LAZIO                                   5.790.000
TOT. ITALIA                                 11.590.000
ALBANIA     ...     ...     ...                 ...
...         ...     ...     ...                 ...
TOT. XX                                         XXX
TOT. YY                                         YYY
...                                             ...
TOT. ALBANIA                                    ZZZ
RUSSIA      ...     ...     ...                 ...
...         ...     ...     ...                 ...
TOT. XX                                         XXX
TOT. YY                                         YYY
...                                             ...
TOT. RUSSIA                                     ZZZ
...

I thought of an algorithm in which I use a map to scan the changes that occur for each column.

Map map = new LinkedHashMap<>(); //preserve insertion order List cityList;

class City {
    private BigDecimal population = 0;  
    ...
    //getter/setter...

    public static final City createBean(ResultSet rs){
        City city = new City();
        population  = rs.getBigDecimal("POPULATION");
        ...
        return city;
    }

    public add(City bean) {
        this.population.add(bean.getPopulation());
        ...
    }
}

Algorithm:

boolean isFirst = true;
ResultSet rs = .... from persistence
while(rs.next()) {
    City current = City.createBean(rs);
    cityList.add(current);  
    if(isFirst) {
        isFirst = false;
        map.put(current.getNation(),new City()); //tot.nation   
        map.put(current.getRegion(),new City()); //tot.region
        map.put(current.getDistrict(),new City()); //tot.district
    }

    if(!map.containsKey(current.getNation())) { //main breaking for nation
        //there was a change of nation then load the map on the cityList
        forach key in map 
            cityList.add(map.getKey(key));
        map.clear();
        map.put(current.getNation(),new City()); //tot.nation   
        map.put(current.getRegion(),new City()); //tot.region
        map.put(current.getDistrict(),new City()); //tot.district
    }

    map.get(current.getNation()).add(current);
    map.get(current.getRegion()).add(current);
    map.get(current.getDistrict()).add(current);
}

Can you direct me to a design with which you can abstract this logic?

I would like to produce a component with these characteristics: - unbound from the persistence system (ResultSet) - unbound from City

Thank you all for every feedback you will give me.

Your code has error. What happens if you have two cities in different regios or nations? Or city and nation that happen to have same name? — Euphoric, Apr 09 '18 at 13:01
Possible duplicate of [Choosing the right Design Pattern](https://softwareengineering.stackexchange.com/questions/227868/choosing-the-right-design-pattern) — gnat, Apr 09 '18 at 13:13
I removed mentions of "design pattern" as this is good problem, that is dragged down by mentioning "design pattern". I wish people would overlook novices mentioning "design patterns" and focused on real question being asked. — Euphoric, Apr 09 '18 at 13:18

score 1 · Answer 1 · answered Apr 09 '18 at 14:40

Based on what you've written above, I would be inclined to approach the problem in the following way:

As you are retrieving the cities from the ResultSet, allocate each CityRecord a unique identifier (let's assume the type is CityId).
Put these records into some sort of Map that allows you to lookup a CityRecord by its CityId.
Have three separate Maps that allow you to lookup a Collection<CityId> by nation, region and district respectively.
You would then calculate the population total for a region/nation/district by looking up the Collection<CityId> using the maps in step 3, then converting this into Collection<CityRecord> using the map in step 2, and finally reducing this by summing the populations of the associated data records.

What abstraction to use for hiearchical sum from ordered list

1 Answers1