We often have to map one-to-many relationships in our database projects. A customer can have many addresses, an order can contain many line items and so on. We are now providing a ResultSetExtractor implementation to deal with this common task.

Let’s look at the schema definition first:

CREATE TABLE customer(
  id BIGINT IDENTITY PRIMARY KEY, 
  name VARCHAR(255));
CREATE TABLE address (
  id BIGINT IDENTITY PRIMARY KEY, 
  customer_id BIGINT CONSTRAINT address_customer_ref 
    FOREIGN KEY REFERENCES customer (id), 
  street VARCHAR(255), 
  city VARCHAR(255));

Two tables linked by a foreign key constraint. To map this we need two domain classes - Customer and Address where Customer has a Set of Addresses.

public class Customer {

  private Integer id;

  private String name;

  private Set<Address> addresses = new HashSet<Address>();

  public Set<Address> getAddresses() {
    return addresses;
  }

  public void addAddress(Address address) {
    this.addresses.add(address);
  }

// other setters and getters

}

public class Address {

  private Integer id;

  private String street;

  private String city;

// setters and getters

}

Executing the following query we would potentially get multiple rows returned for each customer.

List<Customer> result = template.query(
  "select customer.id, customer.name, address.id, " +
    "address.customer_id, address.street, address.city " +
    "from customer " +
    "left join address on customer.id = address.customer_id " +
    "order by customer.id",
  resultSetExtractor);

To be able to handle the multiple rows we create a new CustomerAddressExtractor that extends the abstract class OneToManyResultSetExtractor. We parameterize the OneToManyResultSetExtractor with the root class (Customer), the child class (Address), and the class for the primary and foreign key (Integer).

public class CustomerAddressExtractor extends
    OneToManyResultSetExtractor<Customer, Address, Integer> {

  public CustomerAddressExtractor() {
    super(new CustomerMapper(), new AddressMapper());
  }

  @Override
  protected Integer mapPrimaryKey(ResultSet rs) throws SQLException {
    return rs.getInt("customer.id");
  }

  @Override
  protected Integer mapForeignKey(ResultSet rs) throws SQLException {
    if (rs.getObject("address.customer_id") == null) {
      return null;
    }
    else {
      return rs.getInt("address.customer_id");
    }
  }

  @Override
  protected void addChild(Customer root, Address child) {
    root.addAddress(child);
  }
}

We need a way to match the primary key of the Customer with the foreign key of the Address so we provide mappings for these via the abstract methods mapPrimaryKey and mapForeignKey. We have to take into account that there might not be an address record for every customer so the foreign key could be null. We also need to add the mapped Address instances to the Customer instance. We do this by implementing the abstract method addChild. We simply call the addAddress on the Customer class here.

Looking at the constructor of the CustomerAddressExtractor we see that we call the super constructor providing RowMapper implementations for the Customer and the Address classes. These are standard RowMappers that we in this example provide as static inner classes.

private static class CustomerMapper implements RowMapper<Customer> {

  public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
    Customer c = new Customer();
    c.setId(rs.getInt("customer.id"));
    c.setName(rs.getString("customer.name"));
    return c;
  }
}

private static class AddressMapper implements RowMapper<Address> {

  public Address mapRow(ResultSet rs, int rowNum) throws SQLException {
    Address a = new Address();
    a.setId(rs.getInt("address.id"));
    a.setStreet(rs.getString("address.street"));
    a.setCity(rs.getString("address.city"));
    return a;
  }
}

We now have a complete solution for this common problem.