JDBC模板 - 一对多 [英] JDBC Template - One-To-Many

问题描述

我有一个看起来像这样的课程。我需要从两个数据库表中填充它，如下所示。有没有首选的方法呢？

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?

我的想法是让服务类选择列表<> 通过来自DAO的 ResultSetExtractor 。然后在该列表上执行 foreach ，并通过另一个人选择列表<> 个人电子邮件 ResultSetExtractor ，并使用 foreach 循环附加它。

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

推荐答案

这最好由ORM解决。使用JDBC，您必须手动执行ORM为您执行的操作。执行N + 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();

这篇关于JDBC模板 - 一对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！