Java - Cannot return/access ResultSet from MySQL StoredProcedure

Question

I have users table in MySQL and I created a stored procedure so that when get username and password from swing textfields passed them into stored procedure and learn if is there exist that user to login, but I can not get resultset actually in phpMyAdmin stored procedure work properly but in netbeans can not get resultset and runtime never stop in console , running always.

I do not think there is a problem in my code somewhere else because it is so simple code.

Here is my stored procedure in MySQL

SELECT * FROM `users` WHERE `users`.`E-Mail` = @p0 AND `users`.`Password` = @p1

it takes two parameter varchar and I tried before those as a text

Here is the specific part of my java code

     public void loginPass(String email, String password){

     try {
        DriverManager.registerDriver((Driver) Class.forName("com.mysql.jdbc.Driver").newInstance());
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/askdocdb","root","");
        CallableStatement mystatement = connection.prepareCall("{CALL sp_Login (?, ?)}");
        mystatement.setString(1, email);
        mystatement.setString(2, password);

        // mystatement.setString("@p0", email);
        // mystatement.setString("@p1", password);


         boolean situation = mystatement.execute();
         System.out.println(situation);
        // resultset = mystatement.executeQuery();
         resultset = mystatement.getResultSet();

         String res =    resultset.getString(2);
         System.out.println(res);

         //    resultset = mystatement.executeQuery();
        while(resultset.next()){
            System.out.println("asdsad");
        }
        resultset.close();

    } catch (Exception e) {
    }
}

The reason of comment lines, I tried any possible combination of syntax

situation returns true

res does not return

and can not enter into while statement

Thank you for your support and comments already now.

Answer 1

It's difficult to say what exactly is wrong with your code as there are quite a few possible points for failure if you choose to use a stored procedure for this simple task (incorrect syntax in the procedure, problems with getting the return value over JDBC, etc). I would simply run the SQL query over JDBC for checking the credentials:

public void registerDriver() {
    try {
        DriverManager.registerDriver((Driver) Class.forName(
                "com.mysql.jdbc.Driver").newInstance());
    } catch (InstantiationException | IllegalAccessException
            | ClassNotFoundException | SQLException e) {
        throw new RuntimeException("Could not register MySQL driver!", e);
    }
}

public boolean checkLogin(String email, String password) {      

    try (Connection connection = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/askdocdb", "root", "");
            PreparedStatement ps = connection
                    .prepareStatement("SELECT 1 FROM users WHERE " 
                            + "E-Mail = ? AND Password = ?")) {

        ps.setString(1, email);
        ps.setString(2, password);

        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                return true; // username and password match
            } else {
                return false; // no row returned, i.e. no match
            }
        }
    } catch (SQLException e) {
        throw new RuntimeException(
                "Error while checking user credentials!", e);
    }
}

What was changed:

• JDBC driver registration has been extracted into a separate method (registerDriver()), which you only need to call once (e.g. after the program has started), not each time you check for credentials.
• Resources such as Connection, PreparedStatement and ResultSet are now being closed properly (even if an exception is thrown) because they are declared through the try-with-resources statement.
• The method now returns a boolean that corresponds to whether the credentials were valid or not, making it easier to use from calling code.
• Exceptions that cannot be handled (e.g. SQLException) are rethrown as RuntimeExceptions (instead of just swallowing them in an empty catch block).
  • Basically, when an SQLException is thrown, either there is a programming error in the code (invalid query syntax) or something severely wrong with the database. In either case, the only option is usually to halt your program. You can declare throws SQLException in the method signature if you'd want to handle the situation in the calling method instead.

Finally, it needs to be mentioned that you should never store passwords in the database as plain text, to avoid anyone with read access to the db to login as an arbitrary user. Instead, you should store password hashes, or even better, salted hashes. More on this e.g. in Best way to store password in database.