Search code examples
javajdbcjdbi

JDBI resultset mapping with joined list of results?

Trying to build a Country object with JDBIs ResultSetMapper API, however I have one issue which I am not sure how to solve.

For a resultset like the following which joins the Regions (states/ territories) table to country (1 - 0..n)

enter image description here

    @Override
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(....?)
            .build();

}

How can I get the ResultSetMapper to initialise one Country object with the appropriate list of related Regions in JDBI

e.g.

United States - (USA) - (US) - (PR, RI, WA)

Currently the list of Countries returned is like the followng

United Kingdom - GBR - GB - <>

United States - USA - US - PR

United States - USA - US - RI

United States - USA - US - WA

Puerto Rico - PRI - PR - <>

Canada - CAN - CA - AB

Canada - CAN - CA - BC

Solution

  • You can use the StatementContext argument for that.

    When the map method sees a new country, it creates a new Country instance and calls ctx.setAttribute to save the new instance. Later on, if there's a non-null region, it adds the region to the Country instance taken from the statement context.

    Here's an example:

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

        final String countryIso3Code = resultRow.getString("iso3Code");
        if (countryIso3Code == null) {
            throw new SQLDataException("Iso3Code is required");
        }
        Country country = (Country)ctx.getAttribute(countryIso3Code);
        if (country == null) {
            country = new Country();
            country.setName(resultRow.getString("name"));
            country.setIso3Code(countryIso3Code);
            country.setIso2Code(resultRow.getString("iso2Code"));
            ctx.setAttribute(countryIso3Code, country);
        }

        String region = resultRow.getString("region");
        if (region != null) {
            country.addRegion(region);
        }
        return country;
    }

    It's a bit inconvenient to use a builder like you do in the code you posted, but it's possible to put a builder on the statement context rather than a country.

    Also, this mapper returns a country per DB row, so seven results, but since the same instance is repeated, using a Set<Country> gets you the expected four results.