java - 搜索多个表并返回/映射动态结果集

Question

我有一个名为“联系人”的 mySql 架构，其中包含 4 个表:“联系人”、“电话”、“电子邮件”和“地址”。联系人表包含有关人员的基本信息，例如 ID 号、名字和姓氏。其他表都包含一个将其链接到“联系人”表的外键，例如，“联系人”表中的 John Doe 可以在“电话”表中拥有多个电话号码，这些电话号码都可以使用 John Doe 的 ID 号码进行搜索。

我的问题是如何查询此架构并返回单个(或多个)用户的所有数据。是否可以使用一条 SQL 语句来完成，或者我是否需要为每个单独的表联系数据库，因为返回的结果量与从联系人表返回的每一行不匹配。例如，我有一些基本的搜索功能，可以根据搜索条件在“联系人”表中搜索一行或多行:

public class ContactsListDAO {
//Constants
private static final String SQL_FIND_BY_SEARCH_CRITERIA = "SELECT * FROM Contacts.Contacts WHERE Id LIKE :searchString OR FirstName LIKE :searchString OR LastName LIKE :searchString";

//Variables
private DAOFactory daoFactory;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

//Constructors
public ContactsListDAO(DAOFactory daoFactory) {
    this.daoFactory = daoFactory;
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(daoFactory.getDataSource());
}

public List<Contact> findSearchResults(String searchCriteria) {
    Map<String, String> namedParameters = Collections.singletonMap("searchString", searchCriteria);

    RowMapper<Contact> mapper = new RowMapper<Contact>() {
        @Override
        public Contact mapRow(ResultSet resultSet, int row) throws SQLException {
            Contact contact = new Contact(
                    resultSet.getInt("Id"),
                    resultSet.getString("FirstName"),
                    resultSet.getString("LastName")
                );
            return contact;
        }           
    };

    return namedParameterJdbcTemplate.query(SQL_FIND_BY_SEARCH_CRITERIA, namedParameters, mapper);
}
}

我正在使用 spring 来查询并将结果映射回 Contact bean。我将如何修改这个 SQL 语句和映射功能来搜索联系人表，获取每行的数据，然后根据每个返回行的 id，查询电话、电子邮件和地址表，然后将它们映射到存储在 bean 中的 List 对象？问题是第 1 行的 id 可能会找到 8 个与该 id 匹配的电话号码行，但第 2 行的 id 可能只能找到 3 个电话号码。这将如何存储在 ResultSet 中？或者我是否必须先查询 Contacts 表，然后对每个其他表(对于从第一个表返回的每一行)执行单独的查询，并将该数据逐个添加到 bean 中？如果第一个查询返回 100 个结果，并且我必须对 3 个表中的每个结果执行查询，那么我将查看 301 次数据库往返次数。

是否可以使用一个查询并仅从电话、电子邮件、地址表中为“联系人”表中找到的每个结果返回 1 个结果？也许我可以添加一个主列或其他列，这样它只返回 1 个结果，然后如果用户单击某些内容来请求有关结果的更多信息，它可以执行其他查询并收集有关该用户的所有信息。

Answer 1

我提出的查询使用 LEFT JOIN 来搜索表:

SELECT * FROM Contacts.Contacts LEFT JOIN Contacts.Phone ON Contacts.Id = Phone.ContactId AND Phone.Primary = 1 LEFT JOIN Contacts.Email ON Contacts.Id = Email.ContactId AND Email.Primary = 1 WHERE Contacts.Id LIKE :searchString OR Contacts.FirstName LIKE :searchString OR Contacts.LastName LIKE :searchString AND Contacts.OrganizationId = :organizationId

我在电话、电子邮件和地址数据库中创建了一个名为 Primary 的列，其中包含一个 boolean 值，以便在初始查询中我只会为数据库中的每个联系人返回 1 个结果。到目前为止，这正在做我需要的事情。不确定这是否是处理此类事情的正确方法？