JDBC Template - One-To-Many

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 a List<> via a ResultSetExtractor from a DAO. Then do a foreach on that list, and select a List<> of emails for the individual person via another ResultSetExtractor, and attach it from with the foreach loop.

Is there a better way, or is this 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)
);

Emsmus

Answer 1

This is best solved by an ORM. With JDBC, you have to do by hand what an ORM would do for you. Executing N + 1 queries is very inefficient. You should execute a single query, and build your objects manually. Cumbersome, but not hard:

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();

Conglobate

This is great. Wish I had done this on a previous project. Can you tell me what the last line does? I am not sure I follow that part. – Emsmus

It just gets the values from the map, because what you probably want as a result of the query is a collection of persons, and not a Map<Long, Person>. Read the javadoc of Map.values() to get more details. – Conglobate

Or you can use a java.util.Set<Person> where class Person { public boolean equals(Object o) { return id == (Person) o.id; public int hashCode() { return id.hashCode(); } } that would avoid calling Map.values() which may be more desirable for performance and readability purposes. – Gatefold

Answer 2

I was looking for something similar, and although the answer is perfectly valid I went with this nice library instead https://simpleflatmapper.org/0203-joins.html

It also integrates perfectly with Spring boot.

main advantage is that you have a clean repository layer, it uses your pojo and makes refactoring much easier, and like hibernate you can still map deep nested and complex one to many and still be in control of what is executed.

It also has a nice jdbctemplate CRUD and Java 13 finally brings support for multi-line string literals which is very good for sql statements readability. hope this helps someone :)

Sisterly

Answer 3

In my case, I had to use the LinkedHashMap to keep the query result ordered by the position field.

From JavaDoc:

LinkedHashMap: "This linked list defines the iteration ordering, which is normally the order in which keys were inserted into the map (insertion-order). Note that insertion order is not affected if a key is re-inserted into the map."

HashMap: "This class makes no guarantees as to the order of the map; in particular, it does not guarantee that the order will remain constant over time".

TIP: using the getOrDefault method eliminates the extra check for nullable object.

public List<BucketDto> findAll() {

    var sql = """
        SELECT
            b.uuid bucket_uuid, b.position bucket_position, b.name bucket_name,
            c.uuid card_uuid, c.position card_position, c.name card_name
        FROM bucket AS b
            LEFT JOIN card AS c ON c.bucket_id = b.id
        ORDER BY b.position ASC, c.position ASC
        """;

    return jdbcTemplate.query(sql, rs -> {

        Map<Double, BucketDto> resultMap = new LinkedHashMap<>();

        while (rs.next()) {

            var position = rs.getDouble("bucket_position");

            var bucketDto = resultMap.getOrDefault(position, new BucketDto(
                UUID.fromString(rs.getString("bucket_uuid")),
                position,
                rs.getString("bucket_name")));

            if (Optional.ofNullable(rs.getString("card_uuid")).isPresent()) {
                bucketDto.addCard(new CardDto(
                    UUID.fromString(rs.getString("card_uuid")),
                    rs.getDouble("card_position"),
                    rs.getString("card_name")));
            }

            resultMap.put(position, bucketDto);
        }

        return new ArrayList<>(resultMap.values());
    });
}

Joijoice