Java JDBC报错Before start of result set及参数绑定问题求助

核心问题1：ResultSet指针未移动导致的异常 #

你遇到的Before start of result set异常，是因为刚执行完ps.executeQuery()后，ResultSet rs的指针停在第一条记录的前面，此时直接调用rs.getInt("personId")读取数据必然报错。必须先调用rs.next()将指针移动到有效记录上，才能访问数据。

核心问题2：邮箱查询逻辑错位 #

你的代码只执行了一次邮箱查询，且在遍历Person列表之前，完全无法关联到每个Person对应的邮箱。正确逻辑是：遍历每一条Person记录时，用当前的personId作为参数，查询该用户对应的邮箱。

修正后的代码 #

String query = "Select * from Person;";
String emailQ = "Select Email.email from Email where personId = ?;";
PreparedStatement ps = conn.prepareStatement(query);
ResultSet rs = ps.executeQuery();

int i = 1;
while (rs.next()) {
    // 获取当前Person的基础信息
    int personId = rs.getInt("personId");
    System.out.println(personId);
    String firstname = rs.getString("firstname");
    String lastname = rs.getString("lastname");
    String type = rs.getString("type");
    String userName = rs.getString("username");
    String password = rs.getString("password");

    // 为当前Person查询对应的邮箱
    List<String> emails = new ArrayList<>();
    try(PreparedStatement pstmt = conn.prepareStatement(emailQ)) {
        pstmt.setInt(1, personId); // 用当前personId替换SQL占位符
        ResultSet rs2 = pstmt.executeQuery();
        while (rs2.next()) {
            String email = rs2.getString("email");
            emails.add(email);
        }
    }

    // 创建Person对象并存入map
    Person a = new Person(personId, firstname, lastname, type, userName, password, emails);
    persons.put(i, a);
    i++;
}

// 按需关闭资源（若未使用try-with-resources包裹）
rs.close();
ps.close();

优化建议：用JOIN减少数据库交互 #

上面的代码属于N+1查询（1次Person查询+N次Email查询），数据量大时效率偏低。可以用SQL JOIN一次性拉取所有数据，再在代码中分组处理：

// 用LEFT JOIN关联Person和Email，确保无邮箱的用户也能被查询到
String joinQuery = "SELECT p.personId, p.firstname, p.lastname, p.type, p.username, p.password, e.email " +
                   "FROM Person p LEFT JOIN Email e ON p.personId = e.personId;";
PreparedStatement joinPs = conn.prepareStatement(joinQuery);
ResultSet joinRs = joinPs.executeQuery();

Map<Integer, Person> persons = new HashMap<>();
while (joinRs.next()) {
    int personId = joinRs.getInt("personId");
    // 检查当前用户是否已在map中，避免重复创建对象
    Person person = persons.get(personId);
    if (person == null) {
        String firstname = joinRs.getString("firstname");
        String lastname = joinRs.getString("lastname");
        String type = joinRs.getString("type");
        String userName = joinRs.getString("username");
        String password = joinRs.getString("password");
        List<String> emails = new ArrayList<>();
        person = new Person(personId, firstname, lastname, type, userName, password, emails);
        persons.put(personId, person);
    }
    // 处理邮箱：LEFT JOIN可能返回null，需判断后添加
    String email = joinRs.getString("email");
    if (email != null) {
        person.getEmails().add(email);
    }
}

这种方式仅需1次数据库查询，效率更高，适合数据量较大的场景。

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