JDBC check if entry exists

Question

I am trying to check if an entry exists within my table. I have used the following resources and run the code in mysql workbench to see if i was running something wrong but that wasn't the case.

Check if ResultSet is filled -> Is ResultSet Filled

SQL Syntax -> SQL

This is the current code i'm running

    public static Boolean exists(Table t, int userId){
    boolean e = false;
    if (connection != null){
        try {
            String SQL = "SELECT EXISTS(SELECT 1 FROM " + t.table + " WHERE id = " + String.valueOf(userId) + ")";
            System.out.println(SQL);
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(SQL);
            if (isFilled(rs))
                e = true;
        } catch (SQLException l) {
            l.printStackTrace();
        }
    }
    return e;
}
public static boolean isFilled(ResultSet rs){
    boolean isEmpty = true;
    try {
        while(rs.next()){
            isEmpty = false;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return !isEmpty;
}

The problem is exists always returns true no matter the userID I enter

score 2 · Accepted Answer · answered Sep 05 '17 at 20:09

So as per your question you want to check that a row is available or not for a corresponding id.

so for that, you can easily do this.

String sql_res= "select * from students where sid=2";
rs=st.executeQuery(sql_res);

and then check that the row is exist or not

//it will print true if a row exists for the given id and false if not. 
    System.out.println(rs.next());

score 1 · Answer 2 · answered Sep 05 '17 at 19:24

1

EXISTS already returns a boolean result, so you'll always have 1 row in the ResultSet.

Either check the return value which is guaranteed to exist or remove the EXISTS clause from the query, where it will return either zero rows or one row (with the value 1).

answered Sep 05 '17 at 19:24

Kayaman

72,141
5
83
121

i don't understand when i run this script in mysql workbench it returns one so the isfilled should return true because it is greater than 0 so it should all check out the problem is it if i use an id that doesn't exist it is still true though is filled should return false because it isn't filled the result set is 0 – Archishmaan Okonkwo Sep 05 '17 at 19:30
You're checking if the resultset has any rows. You always have a row. Either `1` or `0`. But `0` is not the same as no rows. I gave you two options what to do. – Kayaman Sep 05 '17 at 19:32

score 1 · Answer 3 · edited Aug 30 '20 at 14:46

1

If you count the result, you will always get a correct answer:

PreparedStatment ps = connection.prepareStatement("SELECT COUNT(*) FROM students WHERE sid = ?");
ps.setInt(1,userId);
ResultSet rs = ps.executeQuery();
int n = 0;
if ( rs.next() ) {
    n = rs.getInt(1);
)
if ( n > 0 ) {
   // do what ever you need, if the row exists  
}

edited Aug 30 '20 at 14:46

atan

58
2
10

answered Sep 06 '17 at 05:52

Fredy Fischer

458
3
12

JDBC check if entry exists

3 Answers3