1. Support classes that extend Spring features

The Spring Framework projects JDBC support is excellent but every now and then there are some features that seem useful, but might not warrant inclusion in the framework project itself. The Spring Data JDBC Extensions project provides a home for these type of extensions.

1.1 Mapping a one-to-many relationship

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(
  name VARCHAR(255));
CREATE TABLE address (
  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) {

// 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",

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());

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

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

  protected void addChild(Customer root, Address 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();
    return c;
private static class AddressMapper implements RowMapper<Address> {

  public Address mapRow(ResultSet rs, int rowNum) throws SQLException {
    Address a = new Address();
    return a;

We now have a complete solution for this common problem.