java/sql 比较两个整数

Question

我有以下函数，我试图将类(class)注册的学生人数与类(class)最大人数进行比较。如果注册人数大于类(class)上限，我想返回一条消息“类(class)已满”。

public static void classFullCheck() {
    try {
        String currentNumberInClassAsString = ("SELECT class_id, COUNT(*) FROM ClassSelector.student_x_class WHERE class_id = " + selectedClass);
        rs = myStmt.executeQuery(currentNumberInClassAsString);
        int currentNumberInClassAsInt = 0;
        if(rs.next()){
            currentNumberInClassAsInt = rs.getInt(1);
        }
        String classSizeAsString = ("SELECT class_size FROM ClassSelector.classes WHERE class_id = " + selectedClass);
        rs = myStmt.executeQuery(classSizeAsString);
        int classSizeAsInt = 0;
        if(rs.next()){
            classSizeAsInt = rs.getInt("class_size");
        }
        if (currentNumberInClassAsInt > classSizeAsInt){
                System.out.println("Sorry, this class is Full!");
        }
    } catch (java.sql.SQLException SQL) {
        SQL.printStackTrace();
    }
}

我将 classFullcheck() 函数插入到 addClass() 函数中，如下所示:

 public static void addClass() {

        try {
            rs = myStmt.executeQuery("SELECT * FROM ClassSelector.classes");
            while (rs.next()) {
                String availableClasses = rs.getString("class_id") + "\t" + rs.getString("class_name") + "\t" + rs.getString("description");
                System.out.println(availableClasses);
            }
            System.out.println("Enter Class ID from Classes Listed Above to Join: ");
            selectedClass = sc.nextLine();
            rs = myStmt.executeQuery("SELECT * FROM ClassSelector.classes WHERE class_id = " + selectedClass);
            while (rs.next()) {
                classFullCheck();
                String innerJoin = (userEnterIdAsName + " has been added to " + rs.getString("class_name") + " " + rs.getString("class_id"));
                System.out.println(innerJoin);
                String student_x_classJoin = "INSERT INTO student_x_class" + "(student_id, student_name, class_id, class_name)" + "VALUES (?, ?, ?, ?)";
                PreparedStatement pStmt = con.prepareStatement(student_x_classJoin);
                    pStmt.setString(1, user_entered_student_id);
                    pStmt.setString(2, userEnterIdAsName);
                    pStmt.setString(3, rs.getString("class_id"));
                    pStmt.setString(4, rs.getString("class_name"));
                    pStmt.executeUpdate();
                    System.out.println("Would you like to enroll " + userEnterIdAsName + " into another class? (Y/N)");
                    String addAdditionalClass = sc.nextLine();
                    if (addAdditionalClass.equalsIgnoreCase("Y")) {
                        addClass();
                    } else if (addAdditionalClass.equalsIgnoreCase("N")) {
                        return;
                    }
                }
        }
        catch (java.sql.SQLException SQL) {
            System.out.println("Wait, This Student is already enrolled in this class!");
        }
    }

即使类(class)未满，我目前也只是打印两条消息。任何建议都会有很大帮助。

if (currentNumberInClassAsInt >= classSizeAsInt) {
            String updateStatus = "Update ClassSelector.classes SET status = ? WHERE class_id = " + selectedClass;
            PreparedStatement pStmt = con.prepareStatement(updateStatus);
            pStmt.setString(1, "Closed");
            pStmt.executeUpdate();

            System.out.println("Sorry, this class is Full! Select a different Class:");
            System.out.println("\nSign Up For a Class\n");
            addClass();
        }

Answer 1

我想你想要这个:

currentNumberInClassAsInt = rs.getInt(2);

而不是:

currentNumberInClassAsInt = rs.getInt(**1**);

我不认为ResultSet是基于 0...

也是 rs全局变量，因为看起来您正在更改 ResultSet rs当您调用classFullCheck()时。您可能没有您认为在 ResultSet 中所做的事情...

rs = myStmt.executeQuery("SELECT * FROM ClassSelector.classes WHERE class_id = " + selectedClass);
while (rs.next()) {
    classFullCheck();//****************result set changed here******************
    String innerJoin = (userEnterIdAsName + " has been added to " + rs.getString("class_name") + " " + rs.getString("class_id"));

您可能认为您有这个:rs = myStmt.executeQuery("SELECT * FROM ClassSelector.classes WHERE class_id = " + selectedClass);在你的结果集中，但你改变了 rs在classFullCheck() 。您可能希望将数据存储在不同的对象中，这样当您运行另一个查询时，您仍然可以访问数据。