使用Java和JDBC查询一对多关系数据时如何避免重复输出？

提供两种纯Java+JDBC场景下的实现方式，可根据业务需求选择：

方案1：SQL层面直接聚合（推荐，性能最优） #

你使用的是Oracle数据库，可以直接用Oracle内置的LISTAGG聚合函数完成邮箱拼接，无需修改过多Java逻辑：

修改后的查询语句 #

static final String QUERY = "select prs.person_id, prs.first_name, prs.last_name, " +
        "LISTAGG(pe.email, ',') WITHIN GROUP (ORDER BY pe.email) as emails " +
        "from persons prs " +
        "left join person_emails pe on prs.person_id = pe.person_fk " +
        "where prs.person_id = 1 " +
        "group by prs.person_id, prs.first_name, prs.last_name";

调用逻辑 #

查询结果仅返回1条记录，直接读取emails字段输出即可，无需调整遍历逻辑。该方案适合仅需要拼接输出邮箱、无后续单独处理邮箱需求的场景。

方案2：Java内存层面聚合 #

如果需要保留邮箱列表结构做后续业务处理，或后续可能切换非Oracle数据库，可以在内存中完成数据聚合：

修改后的Java代码 #

public class OneToManyDB {
   static final String DB_URL = "jdbc:oracle:thin:@1.1.1.1:1521:orcl";
   static final String USER = "user";
   static final String PASS = "pass";
   static final String QUERY = "select prs.person_id, prs.first_name, prs.last_name, pe.email" +
           " from persons prs" +
           " left join person_emails pe on prs.person_id = pe.person_fk" +
           " where prs.person_id = 1";

   public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(QUERY);
            
            Integer personId = null;
            String firstName = null;
            String lastName = null;
            List<String> emails = new ArrayList<>();

            while(rs.next()){
                // 人员基础信息仅首次赋值
                if (personId == null) {
                    personId = rs.getInt("person_id");
                    firstName = rs.getString("first_name");
                    lastName = rs.getString("last_name");
                }
                String email = rs.getString("email");
                if (email != null) {
                    emails.add(email);
                }
            }
            
            // 统一输出
            if (personId != null) {
                System.out.print("ID: " + personId);
                System.out.print(", First: " + firstName);
                System.out.print(", Last: " + lastName);
                System.out.println(", Email: " + String.join(",", emails));
            }
        } catch (SQLException e) {
            e.printStackTrace();       
        } finally {
            // 手动关闭资源避免泄漏
            try { if (rs != null) rs.close(); } catch (SQLException e) {}
            try { if (stmt != null) stmt.close(); } catch (SQLException e) {}
            try { if (conn != null) conn.close(); } catch (SQLException e) {}
        }
    }
}

方案说明 #

该方案无需修改SQL，聚合逻辑完全在Java层实现，可扩展性更高，后续如果需要查询多个人员的邮箱，只需要把存储结构替换为Map<Integer, 人员实体类>即可实现批量聚合。

内容的提问来源于stack exchange，提问作者NewJavaEnthusiast