To create a custom mapper which will consume rows from DB until the id changes

Question

I work on my JDBC project and I need to get the object from ResultSet.

This my getAll() method

public List<Person> getAll() throws DaoException {
 List<Person> persons = new ArrayList<>();
 try (Connection connection = connectionProvider.getConnection();
 PreparedStatement statement = connection.prepareStatement(getQueryToGetAll(), Statement.RETURN_GENERATED_KEYS)) {
 ResultSet resultSet = statement.executeQuery();
 while (resultSet.next()) {               
 students.add(mapper.mapRow(resultSet));  // here I get person from the mapper
}
 return persons;
 } catch (
 SQLException e) {
 throw new DaoException("Couldn't get all persons" + persons, e);
 }
}

---

My model Person has field Set :

class Person {
private int id;
private String name;
private Set<Activity> activities;
}

---

And I need to get this set from ResiltSet also using a single join query.

My query to DB get me the next data: link

So my problem is that my mapper doesn't work correctly. It should consume rows until person_id changes to produce a single Person instance.

Mapper method :

---

public Person mapRow(ResultSet resultSet) throws SQLException {
 Integer id = resultSet.getInt("person_id");
 String name= resultSet.getString("person_name");
 Set<Activity> activities = new HashSet<>();
 do {
 Integer activityId = resultSet.getInt("activity_id");
 String activityName = resultSet.getString("activity_name");
 activities.add(new Activity(activityId, activityName));
 } while (resultSet.next() && id.equals(resultSet.getInt("person_id")));
 return new Person(id, name, activities);
 }

---

Answer 1

Try something like this:

public List<Person> getAll() throws DaoException {
    Map<Integer, Person> personMap = new HashMap<>();
    try (Connection connection = connectionProvider.getConnection();
         PreparedStatement statement = connection.prepareStatement(getQueryToGetAll(), Statement.RETURN_GENERATED_KEYS)) {
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
            mapper.appOrUpdatePersonFromRow(resultSet, personMap);  // here I get person from the mapper
        }
        return new ArrayList<>(personMap.values());
    } catch (
            SQLException e) {
        throw new DaoException("Couldn't get all persons" + new ArrayList<>(personMap.values()), e);
    }
}

public void appOrUpdatePersonFromRow(ResultSet resultSet, Map<Integer, Person> personMap) throws SQLException {
    Person person = getOrCreatePerson(resultSet, personMap);
    Integer activityId = resultSet.getInt("activity_id");
    String activityName = resultSet.getString("activity_name");
    person.getActivities().add(new Activity(activityId, activityName));
}

public Person getOrCreatePerson(ResultSet resultSet, Map<Integer, Person> personMap) {
    Integer id = resultSet.getInt("person_id");
    if(!personMap.containsKey(id)) {
        String name= resultSet.getString("person_name");
        Person person = new Person(id, name, new HashSet<>());
        personMap.put(id, person);
    }
    return personMap.get(id);
}