CODEWITHSUNDEEP
javajdbc
Pranay Pant
Pranay Pant

Reputation: 61

java: resultSet error

I was writing a java code for authenticating and updating login credentials for my revenue reporting tool.

getName() is the function for asking for users name and then cross checking my database for any duplicate entries and accordingly adding.

My problem is that when I provide a different name in the starting it works and adds the name to the database. But when I provide a duplicate name and then provide a different name it gives me this error:

Exception in thread "main" java.sql.SQLException: Operation not allowed after ResultSet closed
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:804)
    at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6986)
    at database_console1.test2.getName(test2.java:67)
    at database_console1.test2.getName(test2.java:74)
    at database_console1.test2.getName(test2.java:74)
    at database_console1.test2.getName(test2.java:74)
    at database_console1.test2.getName(test2.java:74)
    at database_console1.test2.getName(test2.java:74)
    at database_console1.test2.main(test2.java:44)
Java Result: 1
BUILD SUCCESSFUL (total time: 20 seconds)

Here is my function.

public void getName() throws SQLException {         
    String sqla = "SELECT * FROM veuser where UserName = ?" ;
    String sql = "INSERT INTO veuser (UserName) VALUES (?)";
    boolean b = true;
    while (b) {
        System.out.println("Enter your username");
        name = sc.nextLine();
        if (!"".equals(name)) {
            b = false;
        } else {
            System.out.println("Username cant be left blank");
        }
    }


    ps = conn.prepareStatement(sqla);
     ps.setString(1, name);
    rs = ps.executeQuery();
    while (rs.next()) {

            String i = rs.getString(2);
            System.out.println(i);

            if (i.equals(name)) {
                System.out.println("User already exists.Choose another username");
                getName();

            }
            }

                os = conn.prepareStatement(sql);
                os.setString(1, name);
                int row = os.executeUpdate(); 
                if (row > 0){
                    System.out.println("Successfully added...");
          ps.close();
          os.close();              
    }

Upvotes: 0

Views: 170

Answers (2)

qmateub
qmateub

Reputation: 512

The problem is that you can't execute updates over a Statement if you are already using that same Statement, in your case, iterating on it.

Read this post

Upvotes: 0

Veselin Davidov
Veselin Davidov

Reputation: 7081

Your code is recurrsive. You call the function getName() from itself but after it returns the first invocation continues its execution.

For example let's say you have username John and you reach this line:

if (i.equals(name)) {
            System.out.println("User already exists.Choose another username");
            getName();

        }

The username John already exists and you call getName() again. You set the name in the second invocation to a correct one. Then in the second invocation you insert the row and return to the first invocation where it tries to insert the same name again. If you try with 3-4 different names and all fail it will try to do 3-4 inserts in the end.

You shouldn't call getName() again but construct the if-else logic to return to the beginning of the method.

Upvotes: 3

Related Questions