如何通过JDBC结合SQL正确计算GPA并筛选符合阈值的学生

原代码的核心问题 #

• 笛卡尔积错误：SQL中直接FROM Students, Classes会生成学生表与课程表的笛卡尔积，导致每个学生和所有课程强制匹配，输出大量重复无效结果。
• GPA计算逻辑缺失：子查询中直接引用credits字段，但HasTaken表并不包含该字段，必须关联Classes表才能获取对应课程的学分，否则无法计算正确的加权GPA。
• 数据类型不匹配：数据库中Students.id是VARCHAR(9)类型，Java代码里用rs.getInt("id")读取会触发类型转换错误。
• 冗余字段查询：查询Classes.credits属于多余操作，我们只需要学生的基础信息。

修正后的实现方案 #

第一步：修正SQL查询语句 #

采用子查询预计算每个学生的GPA，再关联学生表进行筛选，逻辑更清晰且避免笛卡尔积：

SELECT s.first_name, s.last_name, s.id
FROM Students s
JOIN (
    SELECT ht.sid,
           SUM(
               CASE ht.grade
                   WHEN 'A' THEN 4 * c.credits
                   WHEN 'B' THEN 3 * c.credits
                   WHEN 'C' THEN 2 * c.credits
                   WHEN 'D' THEN 1 * c.credits
                   ELSE 0
               END
           ) / SUM(c.credits) AS gpa
    FROM HasTaken ht
    JOIN Classes c ON ht.name = c.name
    GROUP BY ht.sid
) student_gpa ON s.id = student_gpa.sid
WHERE student_gpa.gpa >= ?

第二步：修正Java代码 #

public static void searchByGPAGreaterThan(Connection conn, Scanner input) throws SQLException {
    System.out.print("Enter GPA threshold: ");
    double threshold = input.nextDouble();
    
    String query = "SELECT s.first_name, s.last_name, s.id " +
                   "FROM Students s " +
                   "JOIN ( " +
                   "    SELECT ht.sid, " +
                   "           SUM( " +
                   "               CASE ht.grade " +
                   "                   WHEN 'A' THEN 4 * c.credits " +
                   "                   WHEN 'B' THEN 3 * c.credits " +
                   "                   WHEN 'C' THEN 2 * c.credits " +
                   "                   WHEN 'D' THEN 1 * c.credits " +
                   "                   ELSE 0 " +
                   "               END " +
                   "           ) / SUM(c.credits) AS gpa " +
                   "    FROM HasTaken ht " +
                   "    JOIN Classes c ON ht.name = c.name " +
                   "    GROUP BY ht.sid " +
                   ") student_gpa ON s.id = student_gpa.sid " +
                   "WHERE student_gpa.gpa >= ?";

    try (PreparedStatement ps = conn.prepareStatement(query)) {
        ps.setDouble(1, threshold);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                String id = rs.getString("id");
                String firstName = rs.getString("first_name");
                String lastName = rs.getString("last_name");
                System.out.printf("%s: %s %s%n", id, firstName, lastName);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

示例数据验证 #

针对提供的示例数据，学生10000的GPA计算如下：

• 生物（3学分，A）：4*3=12
• 微积分（4学分，B）：3*4=12
• 世界历史（3学分，C）：2*3=6
• 总加权分：30，总学分：10，GPA=3.0

当输入阈值为3.0时，会输出该学生；输入阈值3.1时，则无结果，完全符合预期。

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