One-to-Many JDBC Template

Question

I have a class that looks like this. I need to populate it from two database tables, which are also shown below. Is there any preferred way to do this?

My thought is to have a service class to select List<> via ResultSetExtractor from the DAO. Then do foreach on this list and select List<> emails for an individual through another ResultSetExtractor and attach it using the foreach .

Is there a better way, or is it as good as it gets?

 public class Person { private String personId; private String Name; private ArrayList<String> emails; } create table Person ( person_id varchar2(10), name varchar2(30) ); create table email ( person_id varchar2(10), email varchar2(30) ); 

Answer 1

This is best solved by ORM. With JDBC, you must manually do what ORM does for you. Performing N + 1 queries is very inefficient. You have to execute one request and create your objects manually. Cumbersome but not difficult:

 select person.id, person.name, email.email from person person left join email on person.id = email.person_id ... Map<Long, Person> personsById = new HashMap<>(); while (rs.next()) { Long id = rs.getLong("id"); String name = rs.getString("name"); String email = rs.getString("email"); Person person = personsById.get(id); if (person == null) { person = new Person(id, name); personsById.put(person.getId(), person); } person.addEmail(email); } Collection<Person> persons = personsById.values();