CODEWITHSUNDEEP
javamysqljdbc
Sajad
Sajad

Reputation: 2363

How to return false where the result of JDBC is empty

In my code, i need when the result of query is empty, it should return false.

My code:

public boolean user_bookValidationQuery(String userID, String bookID) {
    Connection con;    
    PreparedStatement ps;    
    String query = " select * from borrowed where userID=? and bookID=? ";    
    try {    
        con = DriverManager.getConnection(...);    
        ps = con.prepareStatement(query);    
        ps.setString(1, userID);    
        ps.setString(2, bookID);
        if (ps.execute()) {
            System.out.println("You can do it! , id DB");
            return true;
        } else return false;

    } catch (SQLException sqle) {
        sqle.printStackTrace();
        return false;
    }
}

Now for a give value for userID and bookID , i try in mysql console and see that it's result was empty, But in my code it return true still!

Why not false here?

Upvotes: 1

Views: 3007

Answers (8)

Stephen C
Stephen C

Reputation: 718796

Why not false here?

According to the javadoc for execute():

Returns: true if the first result is a ResultSet object; false if the first result is an update count or there is no result.

In your case, the first result will be a ResultSet object. An empty ResultSet is still a ResultSet.

In this case (e.g. when the SQL is a "select") the solution is to use executeQuery and test if the ResultSet is empty.

Alternatively (e.g. when the SQL may or may not return a ResultSet), you could test the result of query(), and then call getResultSet() and test if it is empty.

The most general way to test if a ResultSet is empty is to call next() on it1. Apparently ResultSet.isBeforeFirst() is an optional operation for TYPE_FORWARD_ONLY resultsets.

1 - Of course that next() call will advance to the first row in the resultset as per the javadoc.

Upvotes: 2

Engine Bai
Engine Bai

Reputation: 626

The simplest way to check ResultSet returned from execute() is empty

if ( resultSet.next() == false )
    return false;

Upvotes: 0

George Smith
George Smith

Reputation: 584

Here is just a sample, similar to what Mark Rotteveel above mentioned. I using rs.isBeforeFirst() instead of rs.next() because this way I don't need to reset the current position back to the begining:

    ResultSet rs = stmt.executeQuery();
    isLoggedIn = rs.isBeforeFirst();

Upvotes: 0

Mark Rotteveel
Mark Rotteveel

Reputation: 108988

The return value of execute() doesn't mean what you think it means:

Returns:
true if the first result is a ResultSet object; false if the first result is an update count or there is no result

A SELECT statement will always produce a ResultSet (even if there is no row in the result). Instead you should

  1. Use the ResultSet obtained from executeQuery()
  2. Check the value of ResultSet.next() to see if there was at least one result

Upvotes: 3

surya
surya

Reputation: 2749

javadoc for execute : true if the first result is a ResultSet object; false if the first result is an update count or there is no result

Thus whether or not you expect result,for a select statement, execute() would always return true ;

Upvotes: 0

batman
batman

Reputation: 269

You are using the wrong conditional statement even if the result is empty the query will execute and ps.execute() will return true.

Upvotes: 1

Nimrod007
Nimrod007

Reputation: 9913

you need to check the result of the statement ...

ResultSet rs= ps.execute();
rs.isBeforeFirst()

will returns false if there are no rows in the ResultSet

so your code should look like this :

try {
    con = DriverManager.getConnection(...);
    ps = con.prepareStatement(query);
    ps.setString(1, userID);
    ps.setString(2, bookID);
    ResultSet rs= ps.execute();

    if (!rs.isBeforeFirst() ) {    
          System.out.println("empty"); 
          return false;
    }else {
          System.out.println("with data"); 
          return true;
    }

} catch (SQLException sqle) {
    sqle.printStackTrace();
    return false;
}

Upvotes: 4

Lucas
Lucas

Reputation: 3281

Empty result doesn't mean that the SQL exception has occured. You need to check if the result is empty and THEN return false.

Upvotes: 1

Related Questions