JDBI mapping a list of objects that is joined to a list of strings?

[Bruce Taylor]

Hi all,

Managed to get my custom resultsetmapper to work as follows

public Country map(final int index, final ResultSet resultRow, final StatementContext ctx) throws SQLException {

    final String countryIso3Code = resultRow.getString("iso3Code");


    return Country.builder().name(resultRow.getString("name"))
            .iso2Code(resultRow.getString("iso2Code"))
            .iso3Code(resultRow.getString("iso3Code"))
            .regions(new ArrayList<>())
            .build();

}

As you will notice the builder method is currently passing an empty array list for the regions property.

This is because I am struggling to figure out how I build a list of regions for each country record through a Left Join

Here is the query (note the Left outer join)

SELECT
  cn.name       AS name,
  c.iso3_code   AS iso3Code,
  c.iso2_code   AS iso2Code,
  r.region_code AS region
FROM website_locale wl
  INNER JOIN country_name cn
    ON cn.language_code = wl.language_code
  INNER JOIN country c
    ON cn.country_iso3_code = c.iso3_code
  LEFT OUTER JOIN region r
    ON r.country_iso3_code = c.iso3_code
WHERE wl.website_locale = "us";

Results

How do I go about building a Country object with a list of all Regions associated with it as type List<String> 

The problem I think is that I will create one Country object which is immutable for example with United States, it will add only "PR" as a region.
Then I will have another United States object with Rhode Island etc etc

Any help would be appreciated!

Thanks

[Bruce Taylor]

For more info:

This is one of my tests that expects 3 country objects to be returned with the appropriate properties which is failing as there are 7 records being returned instead

@Test
public void getCountriesForUSEnglishLocale() throws Exception {
    final WebsiteLocale websiteLocale = WebsiteLocale.US;

    final List<Country> expectedCountries = ImmutableList.of(
            new Country("Canada", "CA", "CAN", ImmutableList.of("AB", "BC")),
            new Country("United Kingdom", "GB", "GBR", ImmutableList.of()),
            new Country("United States", "US", "USA", ImmutableList.of("PR", "RI", "WA")));

    final List<Country> countries = countryRepository.getCountriesForWebsiteLocale(websiteLocale);

    assertEquals(countries.size(), expectedCountries.size());
    assertTrue(countries.containsAll(expectedCountries));
}

[Matthew Hall]

I'm on my phone so I can't write out a complete example, but I'd recommend using Query.fold (the variant the gives you the ResultSet--Folder2 I think), and use a LinkedHashMap<String,Country.Builder> (preserves order of insertion) as your accumulator, where the map key is the country ISO code.

Row by row, check whether the country builder for a country code is already in the map, and produce it if absent. Then add the region, if any, to the builder.

Assuming Java 8, you can chain calls after the fold: values().stream().map(Country.Builder::build).collect(toList());

If not using Java 8, you'll have to extract and transform by hand. Maybe use Guava Lists.transform()

Hope this helps.

-Matthew

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Bruce Taylor]

Thanks, I tried using Fold2 but not really sure how to implement, if you get a chance could you give me a coded example please.

I am using Java 8

This is what I am doing at the moment to get it to work

- I get the List of countries

-Then loop through them and build a LinkedHashMap

-Then stream the LinkedHashMap as a list

@Override
public List<Country> getCountriesForWebsiteLocale(final WebsiteLocale websiteLocale) {
    checkNotNull(websiteLocale, "websiteLocale required");

    try (Handle queryHandler = connection.open()) {

        List<Country> countryResultList = queryHandler.createQuery(CountryQueries.GET_COUNTRIES_FOR_WEBSITE_LOCALE)
                .bind("website_locale", websiteLocale.getId())
                .map(new CountryMapper())
                .list();

        return getStringCountryMap(countryResultList);
    }
}

/**
 * Builds a unique list of Countries and merges properties
 * @param countryResultList unique list of countries
 * @return list
 */
List<Country> getStringCountryMap(final List<Country> countryResultList) {
    final Map<String, Country> countryMap = new LinkedHashMap<>();
    //build unique lists of country records, folding joined regions table
    for (Country country : countryResultList) {
        if (countryMap.containsKey(country.getIso3Code())) {

            final Country oldCountryRecord = countryMap.get(country.getIso3Code());
            final List<String> mergedRegions = new ArrayList<>(oldCountryRecord.getRegions());
            mergedRegions.addAll(country.getRegions());

            countryMap.replace(country.getIso3Code(), Country
                    .builder()
                    .name(country.getName())
                    .iso2Code(country.getIso2Code())
                    .iso3Code(country.getIso3Code())
                    .regions(mergedRegions)
                    .build());
        } else {
            countryMap.put(country.getIso3Code(), country);
        }
    } return countryMap.values().stream().collect(Collectors.toList());
}

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.

[Bruce Taylor]

I figured it out thanks

@Override
public List<Country> getCountriesForWebsiteLocale(final WebsiteLocale websiteLocale) {
    checkNotNull(websiteLocale, "websiteLocale required");

    try (Handle queryHandler = connection.open()) {

        LinkedHashMap<String, Country.Builder> countryBuilderList = queryHandler.createQuery(CountryQueries.GET_COUNTRIES_FOR_WEBSITE_LOCALE)
                .bind("website_locale", websiteLocale.getId())

                //fold allows us to merge rows of regions which has a cardinality of 1 -> n with one country record
                .fold(new LinkedHashMap<String, Country.Builder>(), (map, resultRow, ctx) -> {

                    final String countryIso3 = resultRow.getString("iso3Code");

                    if (map.get(countryIso3) == null) {
                        final Country.Builder countryBuilder = Country.builder().name(resultRow.getString("name"))
                                .iso2Code(resultRow.getString("iso2Code"))
                                .iso3Code(resultRow.getString("iso3Code"));

                        if (!StringUtils.isBlank(resultRow.getString("region"))) {
                            countryBuilder.regions(ImmutableList.of(resultRow.getString("region")));
                        }
                        map.put(countryIso3, countryBuilder);
                    } else {
                        map.put(countryIso3, map.get(countryIso3).addRegion(resultRow.getString("region")));
                    }
                    return map;
                });

        return countryBuilderList.values().stream().map(Country.Builder::build).collect(toList());
    }
}

[Matthew Hall]

Glad you got it working. Cheers

--

You received this message because you are subscribed to the Google Groups "jDBI" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.