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

Answer 2

I think that your code will work, but the concern here is about usage of ResultSetExtractor which is mainly for JDBC framework itself, and for most cases documentation recommends to use RowMapper.

So alternative approach would be to have method in your DAO that selects and maps parent object. Then for each object to invoke other Repository or private method that selects and maps child objects, and then to link child objects with parents based on your relationship type (one-directional or bidirectional). This approach may also allow you to control whether you want to load child objects or not.

For example, you may check Spring PetClinic application which has SimpleJdbcClinic class

If you can use other frameworks, you may consider mybatis, it is more about mapping and allows you to control your SQL code.