JDBC 雙層查詢 ResultSet 為空：連接未關閉、參數傳遞錯誤排查

常見原因

1. 外層 ResultSet 未關閉即開新連接：同一連接下只能有一個開啟的 ResultSet（MySQL 默認），需先 rs1.close() 或用獨立連接。
2. 參數 na 為 null/空字符串：斷點檢查 rs1.getString("beiguanzhu") 返回值。
3. SQL 條件不匹配：數據庫中無 author=na 的記錄。

修復代碼

public List getAll1(String name) {
    List result = new ArrayList<>();
    String sql1 = "SELECT beiguanzhu FROM guanzhu WHERE guanzhu=?";
    String sql2 = "SELECT * FROM message WHERE author=? ORDER BY modified DESC";

    try (Connection con = DatabaseConnection.getConnection();
         PreparedStatement ps1 = con.prepareStatement(sql1)) {

        ps1.setString(1, name);
        try (ResultSet rs1 = ps1.executeQuery()) {
            while (rs1.next()) {
                String author = rs1.getString("beiguanzhu");
                if (author == null) continue;

                try (PreparedStatement ps2 = con.prepareStatement(sql2)) { // 復用同一連接
                    ps2.setString(1, author);
                    try (ResultSet rs2 = ps2.executeQuery()) {
                        while (rs2.next()) {
                            Message m = new Message();
                            m.setId(rs2.getInt("id"));
                            m.setTitle(rs2.getString("title"));
                            m.setContent(rs2.getString("content"));
                            m.setModified(rs2.getTimestamp("modified"));
                            m.setAuthor(rs2.getString("author"));
                            result.add(m);
                        }
                    }
                }
            }
        }
    } catch (SQLException e) { throw new RuntimeException(e); }
    return result;
}

優化建議

• 單條 SQL 用 JOIN 或 IN 合併，減少往返：
  SELECT m.* FROM message m JOIN guanzhu g ON m.author=g.beiguanzhu WHERE g.guanzhu=? ORDER BY m.modified DESC
• 使用連接池 (HikariCP) 而非每次 getConnection()。