Multiple one-to-many relations in Spring JDBC

Question

I am using Spring JDBC and I am a bit unsure on how to work with multiple one-to-many relations (or many-to-many). In this case I am injecting a repository into one of my resultsetextractors so that I can retrieve its associations. Is this the way to do it? Is it bad? Are there other better ways?

Note: I have left out the injection of repository

public class SomeResultSetExtractor implements ResultSetExtractor {

  public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
    List result = new LinkedList();

    while (rs.next()) {
        SomeObject object = new SomeObject(rs.getString(1), rs.getLong(2));
        result.add(object);

        List<AnotherObject> otherObjects = anotherRepository.findAllById(object.getId);
        object.setOtherObjects(otherObjects);
        // and so on
    }

    return result;

  }
}

Okey so after reading Dmytro Polivenok answer I have changed to RowMapper interface instead and I am currently using the other repositories to populate all associations like I show in my example. Is this a good way of doing it?

Answer 1

I think a good practice for Spring JDBC and SQL queries in general is to use one query for each entity.

E.g. assume this model:

• Customer (customerId, name, age, ...)
• Address (customerId, type, street, city, ...)

• PaymentOption (customerId, cardnumber, cardtype, ...)

• Customer 1---* Address

• Customer 1---* PaymentOption

I would build 3 queries, 3 Daos, 3 ResultSetExtractors/RowcallbackHandlers:

• CustomerDao with readCustomerData(Customer or List)
• AddressDao with readAddressForCustomer(Customer or List)
• PaymentOptionDao with readPaymentOptionsForCustomer(Customer or List)

If you would bake this in 1 query, you would have to build some logic to revert the cartasian product.

• I.e. if the customer has 3 addresses and 2 payment options the query would return 6 rows.
• This gets quite hard, if Address or PaymentOption does not have an own primary key.

For many to many:

• Customer * --recommends-- * Product

I would probably build:

• CustomerDao.readRecommendationsAndProductKeys
• getDistinctListOfProductKeysFromRecommendations
• ProductDao.readProducts
• replaceProductKeysByProductsOnRecommendations

Like this you could reuse ProductDao.readProducts for

• Customer * --buys-- * Product or
• ProductGroup 1---* Product