ResultSet.next() row counter fails to count

Question

I'm trying to figure out why this won't count and show Rows: 2 when I enter "ashton" for username and "ashton" for password. In my database I inserted 2 entries of username and password.

Here's the screenshot of table:

Here's the GRAB file:

Here's my code:

private void loginButtonActionPerformed(java.awt.event.ActionEvent evt) {                                            
    String userNameEntered = userNameTxtField.getText().trim();
    String passwordEntered = passwordTxtField.getText().trim();

    if(userNameEntered.isEmpty() || passwordEntered.isEmpty()){
        JOptionPane.showMessageDialog(this, "Please fill out all fields");
    }

    else{
    String username = "jordan";
    String password = "jordan";
    String dbURL = "jdbc:derby://localhost:1527/JDBCSTUDY";
    Connection myConnection = null;
    ResultSet myRs = null;
    String SQL = "SELECT * FROM USERS WHERE USERNAME = ? AND PASSWORD = ?";


    try {
      myConnection = DriverManager.getConnection(dbURL,username,password);
        JOptionPane.showMessageDialog(null, "Successfully Connected To Database");

        PreparedStatement myPrepStmt = myConnection.prepareStatement(SQL,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            myPrepStmt.setString(1,userNameEntered); //assigns a string value to the first ?
            myPrepStmt.setString(2,passwordEntered); //assigns a string value to the second ?
        myRs = myPrepStmt.executeQuery(); // executes the select query and stores it to myRs


        if(myRs.next() == false){//next() method returns true if the select statement is satisfied or if query is valid
            JOptionPane.showMessageDialog(this, "Not found");
        }

        int countRows = 0;
        while(myRs.next()){
            countRows++;
            if((myRs.getString(2).equals(userNameEntered))
                && (myRs.getString(3).equals(passwordEntered))){
                JOptionPane.showMessageDialog(this,"found" +"\nRows: " + countRows );
            }
        }


    } //end of try
    catch (SQLException e) {
        //if an exception or an error even occured while executing the try{} block, the 3 lines will be printed
        System.err.println("Error message: " + e.getMessage());
        System.err.println("Error Code: " + e.getErrorCode());
        System.err.println("SQL State: " + e.getSQLState());
    }

    finally{
        if(myConnection!=null){
            try {

                myConnection.close();
            } catch (SQLException ex) {
                JOptionPane.showMessageDialog(null,"Error encountered: " + ex.toString());
            }
        }//end of if   
    }//end of finally
}
}

In my understanding, next() returns true if the SELECT query is successful or if there are rows when cursor is moved by next(). I need to be able to count the rows to show that there are more than 1 row holding the same username and password. I can't proceed on making another ifelse for counting duplication of username and password because in my code, it doesn't seem to count 2 rows.

I'd appreciate any help.

Thanks.

this is the output i get,

This is what I did, and it worked. Thanks for the suggestions guys! It's helping me learn more.

int countRows = 0;
        while(myRs.next()){
            countRows++;
        }

        if(countRows == 0)
        {
            JOptionPane.showMessageDialog(this, "User details doesn't exist. \n Please register first");
        }
        else if(countRows > 1) //if there are duplications 
        {
            JOptionPane.showMessageDialog(null, "User details found but has more 1 one entry" +
                    "\nFound: " + countRows + " users" );
        }
        else if(countRows == 1){
            JOptionPane.showMessageDialog(null, "User Found");
        }

Sidenote: don't store plain text passwords in the database. Always at least employ some hashing and better add some salt (static and dynamic) as well. — Thomas, Feb 09 '16 at 16:45
Count rows inside the loop and show the dialog after you finished counting. Currently you're showing the dialog for _every_ line in the resultset. — Thomas, Feb 09 '16 at 16:47
The check for an user name and password is not need in the loop. And you can do simply SQL count to get count of users. — v.ladynev, Feb 09 '16 at 16:49

score 3 · Accepted Answer · answered Feb 09 '16 at 16:46

3

Your error is to call rs.next twice: Every time you call next you are implicitly discarding the last state of the cursor. It's a good (and clearer) practice to read the resultset's columns after every call to next.

In your case, it's enough to move if after the while loop, changing the condition:

    int countRows = 0;
    while(myRs.next()){
        countRows++;
        ...
    }

    if (countRows==0)
    {
        JOptionPane.showMessageDialog(this, "Not found");
    }

answered Feb 09 '16 at 16:46

Little Santi

8,563
2
18
46

thanks for your help. I put all my ifelse outside and it populated the number of rows within the while loop. So, it solved my problem. Thank you. Im still learning JDBC so this is a great help. – heisenberg Feb 09 '16 at 18:02

score 2 · Answer 2 · edited May 23 '17 at 10:32

2

The main problem is you call myRs.next() two times before getting data. You can use

myRs.isBeforeFirst()

as described here

or use this template as described here

if (!myRs.next() ) {
    System.out.println("no data");
} else {

    do {
     //statement(s)
    } while (myRs.next());
}

And you don't need loop at all — just use a SQL request with count

SELECT COUNT(*) FROM USERS WHERE USERNAME = ? AND PASSWORD = ?

edited May 23 '17 at 10:32

Community

1
1

answered Feb 09 '16 at 16:46

v.ladynev

19,275
8
46
67

thanks for the help. The SELECT(*) was a very good suggestion. I'm still learning JDBC so sometimes, I tend to forget to make use of the SQL functions when I overthink. negating the myRs.next() is also great. Thanks for the idea. It's now working. – heisenberg Feb 09 '16 at 18:04
@p3ace You are welcome. And it was pleasure to help such polite person as you :) – v.ladynev Feb 09 '16 at 18:41

ResultSet.next() row counter fails to count

2 Answers2