JDBI resultset mapping with joined list of results?

Question

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)

    @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

score 3 · Accepted Answer · answered Mar 08 '17 at 15:28

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.

score 0 · Answer 2 · answered May 13 '22 at 09:10

Jdbi provides the "reduce row" functionnality to handle "joined results": https://jdbi.org/#_joins

You will need to implement a RowReducer, with these responsabilities:

to build the country from the result set row the first time it encounter it (you may use a simplified RawMapper implementation). Detection may be based on country primary key
store the build country in the container, mapped by its primary key, to be able to retrieve it when encountered again in following result set rows
retrieve the contry from the container if current row is about an already encountered country, add the region specified by the current row to the retrieved contry

Jdbi use the row reducer to parse each result set rows, and provide as a result the content of the container.

you can follow the example in the jdbi documentation: https://jdbi.org/#_resultbearing_reducerows

JDBI resultset mapping with joined list of results?

2 Answers2