Multiple one-to-many relations in Spring JDBC
java, jdbc, spring, spring-jdbc
Solution
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
Problem
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?