Sqlite verify if value of column exists

问题:

i'm wondering if this method is right to verify if the value of _username already exists in the column "username"

public boolean verification(String _username) throws SQLException{
    Cursor c = dataBase.rawQuery("SELECT * FROM "+TABLE_NAME+" WHERE "+KEY_USERNAME+"="+_username, null);
    if (c!=null)
        return true; // return true if the value of _username already exists
    return false; // Return false if _username doesn't match with any value of the columns "Username"
}

Is there a better way to do the same thing, i'm really not sure about this, it seemed right for me. Thanks.

回答1:

Is there a better way to do the same thing, i'm really not sure about this, it seemed right for me. Thanks.

In the terms of security and purity yes, for sure.

public boolean verification(String _username) throws SQLException {
    int count = -1;
    Cursor c = null; 
    try {
       String query = "SELECT COUNT(*) FROM " 
                   + TABLE_NAME + " WHERE " + KEY_USERNAME + " = ?"
       c = dataBase.rawQuery(query, new String[] {_username});
       if (c.moveToFirst()) {
          count = c.getInt(0);
       }
       return count > 0;
    }
    finally {
       if (c != null) {
          c.close();
       }
    }
}

I recommend you to an usage of ? that is called placeholder. Each placeholder will be replaced with value from string array in the same order. This is called also parametrized statement as a defence agains SQL injection. When your work with Cursor is finished, release it.

回答2:

Beware of SQL injection attacks! You should always use a parameterized query:

Cursor c = dataBase.rawQuery("SELECT 1 FROM "+TABLE_NAME+" WHERE "+KEY_USERNAME+"=?", new String[] {_username});

(Honestly I'm not sure how your first query didn't throw an exception since you forgot to wrap the string in quotes...)

Also rawQuery() will always return a Cursor, you must check if the Cursor is empty, not null.

---

As for "the best" approach, this works fine, but I recommend closing the Cursor to free up resources. All together:

public boolean verification(String _username) {
    Cursor c = dataBase.rawQuery("SELECT 1 FROM "+TABLE_NAME+" WHERE "+KEY_USERNAME+"=?", new String[] {_username});
    boolean exists = c.moveToFirst();
    c.close();
    return exists;
}