Search code examples
javaservletsjdbcsqlexception

java.sql.SQLException: Invalid operation: wasNull() called with no data retrieved . But I 'am' checking if it was null

The follwoing servlet snippet :

ResultSet set = statement.executeQuery();
       // userName = set.getString(1);
        if(set.next()) {
            userName = set.getString("FirstName");
            Email = set.getString("Email");
        }
        if(set.wasNull()) {  //<<------------- line 33
            // turn to the error page
            response.sendRedirect("LoginFailure.jsp");
        } else {
            // start the session and take to his homepage
            HttpSession session = request.getSession();
            session.setAttribute("UserName", userName);
            session.setMaxInactiveInterval(900); // If the request doesn't come withing 900 seconds the server will invalidate the session
            RequestDispatcher rd = request.getRequestDispatcher("portfolio_one.jsp");
            rd.forward(request, response); // forward to the user home-page
        }

creates the following exceptions :

INFO: java.sql.SQLException: Invalid operation: wasNull() called with no data retrieved.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.ResultSet.wasNull(Unknown Source)
at com.sun.gjc.spi.base.ResultSetWrapper.wasNull(ResultSetWrapper.java:141)
--------->> at projectcodes.ValidateDataForSignIn.doPost(ValidateDataForSignIn.java:33)
    ..........

Why does this exception occur ? The exception occurs due to the highlighted line : 33

Solution

  • This exception can occur when ResultSet#next() has returned false. I.e. there is no row at all and thus no column has been retrieved at all. The ResultSet#wasNull() only applies on the last retrieved column, not on the last retrieved row.

    You need to rearrange your code logic.

        if(set.next()) {
        userName = set.getString("FirstName");
        Email = set.getString("Email");

        // start the session and take to his homepage
        HttpSession session = request.getSession();
        session.setAttribute("UserName", userName);
        session.setMaxInactiveInterval(900); // If the request doesn't come withing 900 seconds the server will invalidate the session
        RequestDispatcher rd = request.getRequestDispatcher("portfolio_one.jsp");
        rd.forward(request, response); // forward to the user home-page
    } else {
        // turn to the error page
        response.sendRedirect("LoginFailure.jsp");
    }

    More clear would be to refactor all that JDBC mess into a standalone UserDAO DAO class with a User model class which you then use as follows:

    User user = userDAO.find(username, password);

if (user != null) {
    request.getSession().setAttribute("user", user);
    request.getRequestDispatcher("portfolio_one.jsp").forward(request, response);
} else {
    response.sendRedirect("LoginFailure.jsp");
}

    where the find() method look something like this:

    public User find(String username, String password) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    User user = null;

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement("SELECT id, username, email, firstname, lastname, FROM user WHERE username = ? AND password = MD5(?)");
        statement.setString(1, username);
        statement.setString(2, password);
        resultSet = statement.executeQuery();

        if (resultSet.next()) {
            user = new User();
            user.setId(resultSet.getLong("id"));
            user.setUsername(resultSet.getString("username"));
            user.setEmail(resultSet.getString("email"));
            user.setFirstname(resultSet.getString("firstname"));
            user.setLastname(resultSet.getString("lastname"));
        }
    } finally {
        close(resultSet, statement, connection);
    }

    return user;
}

    This way you end up with more self-documenting and better reuseable/testable code.

    See also: