java/sql comparing two ints

Question

I have the following function and I am trying to compare the number of students enrolled in a class with the class max. If the number enrolled is greater than the class max, I want to return a message that says, "The Class if Full".

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();
    }
}

I am inserting the classFullcheck() function into the addClass() function like this:

 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!");
        }
    }

I am currently just getting both messages printed out, even if a class is not full. Any suggestions would help a lot.

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();
        }

You'd better print the stack trace of the `SQLException` in `addClass` . — Arnaud, Mar 23 '16 at 13:13
good idea, it says "class_name" not found on the String innerJoin line — J. Doe, Mar 23 '16 at 13:15

brso05 · Accepted Answer · 2016-03-23T13:47:34.663

0

I think you want this:

currentNumberInClassAsInt = rs.getInt(2);

instead of:

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

I don't think the ResultSet is 0 based...

Also is rs a global variable because it looks like you are changing your ResultSet rs when you call classFullCheck(). You may not have what you think you do in the 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"));

You may think you have this: rs = myStmt.executeQuery("SELECT * FROM ClassSelector.classes WHERE class_id = " + selectedClass); in your result set but you change rs in classFullCheck(). You may want to store the data in a different object that way when you run another query you can still access the data.

edited Mar 23 '16 at 13:47

answered Mar 23 '16 at 13:18

brso05

13,142
2
21
40

thanks this is helpful. What do you mean about changing the resultSet though? – J. Doe Mar 23 '16 at 13:30
You execute another query within `classFullCheck()` so your result set will contain `"SELECT class_size FROM ClassSelector.classes WHERE class_id = " + selectedClass);` instead of `SELECT * FROM ClassSelector.classes WHERE class_id = " + selectedClass);`. Notice there is no `class_name` in `SELECT class_size FROM ClassSelector.classes` only `class_size`...`SELECT * FROM ClassSelector.classes` would have `class_name` (I am assuming `SELECT *`) but when you get to the line `has been added to " + rs.getString("class_name")` there is no `class_name` field in `rs`. – brso05 Mar 23 '16 at 13:33
@J.Doe I hope that helps? – brso05 Mar 23 '16 at 13:33
Hi, this does help a lot! Thank you! so what would be the best way to prevent this rs change? – J. Doe Mar 23 '16 at 13:36
@J.Doe you can store the data in a `Bean` (`Java Object`) or in this case you are selecting the same data in both queries just different fields so you could change `String classSizeAsString = ("SELECT class_size FROM ClassSelector.classes WHERE class_id = " + selectedClass);` to `String classSizeAsString = ("SELECT * FROM ClassSelector.classes WHERE class_id = " + selectedClass);` in your `classFullCheck()` method. I don't like that approach as much but hopefully it should make your code work... – brso05 Mar 23 '16 at 13:41
@J.Doe another option is to create multiple `Statement` and `ResultSet` Objects. 1 `ResultSet` per `Statement`...In my opinion the best approach is to store the data in `Objects` and just use 1 `Statement` and 1 `ResultSet`. – brso05 Mar 23 '16 at 13:43
changing the class_size to * worked! I know its the easy solution, but I'm very new to java, so this makes the most sense to me. I really appreciate the help, I've been struggling with this forever! – J. Doe Mar 23 '16 at 13:55
@J.Doe glad I could help! – brso05 Mar 23 '16 at 13:57
@J.Doe you might want to change `currentNumberInClassAsInt = rs.getInt(1);` to `currentNumberInClassAsInt = rs.getInt(2);` also... – brso05 Mar 23 '16 at 13:58
I added a little chunk of code to the bottom of my OP. I have a column in my classes table called "status". When a class is full, I want to have the status column say "Closed". I'm not getting any errors, but the column in SQL is not updating. Any ideas? – J. Doe Mar 23 '16 at 14:29
@J.Doe Looks like you want to do an `Update` not an `Insert`...Maybe try this:`String updateStatus = "UPDATE ClassSelector.classes SET status = ? WHERE class_id = " + selectedClass;` Also I am not sure why you are doing the `SELECT` right before that but it doesn't look like it is needed... – brso05 Mar 23 '16 at 14:35
@J.Doe also you want to actually execute the update after you set the parameter: `pStmt.executeUpdate();Connection.commit();`... – brso05 Mar 23 '16 at 14:42
I updated the code above, but am getting an MysqlDataTruncation: Data truncation: Data too long for column 'Status' at row 1 error.. – J. Doe Mar 23 '16 at 15:00
@J.Doe at the database level how big is your field? Example: `nvarchar(5)` etc... – brso05 Mar 23 '16 at 15:02
ahh, its set as binary(1). I wanted this column to be true/false. – J. Doe Mar 23 '16 at 15:07
This is off-topic but never never use string concatenation with parameters when building a query because it is not safe. Always use parameters `?` or `NamedParameterStatement` – Alexius DIAKOGIANNIS Mar 23 '16 at 15:16
@AlexiusDiakogiannis why is it not safe? – brso05 Mar 23 '16 at 15:17
@brso05 please refer to stackoverflow question [Does the preparedStatement avoid SQL injection?](http://stackoverflow.com/questions/4333015/does-the-preparedstatement-avoid-sql-injection) – Alexius DIAKOGIANNIS Mar 23 '16 at 15:19
@ brso05, last question. I have class_id and student_id as primary keys to avoid students being enrolled in the same class twice. I used INSERT IGNORE on the String student_x_classJoin, but I want to print a message to the user that they are already enrolled, instead of just saying they were added, but not actually adding them. I tried adding the MySQLIntegrityConstraintViolationException catch at the end, but this didnt seem to work – J. Doe Mar 23 '16 at 15:27
@J.Doe You have the right idea. Just try to insert a duplicate record and see what exception is thrown then handle that exception. Or you can check before doing the insert to see if the user exists. Just select * from your table with the user key, if a record is returned then the user exists if there isn't a record then the user doesn't exist... – brso05 Mar 23 '16 at 15:31
When I try to insert a duplicate I get the MySQLIntegrityConstraintViolationException, it just doesn't seem to be able to be put in a catch statement at the end, – J. Doe Mar 23 '16 at 15:34
I thought something like this would work catch(MySQLIntegrityConstraintViolationException MCVE){ System.out.println("This user is already enrolled in this class!"); } – J. Doe Mar 23 '16 at 15:39
@J.Doe are you sure your key is right for that table? Check the table you may be surprised to find you actually have duplicate records... – brso05 Mar 23 '16 at 15:42
what do you mean key? My table doesn't have duplicate data – J. Doe Mar 23 '16 at 17:10

java/sql comparing two ints

1 Answers1