SQLite Query in Android to count rows

I'm trying to create a simple Login form, where I compare the login id and password entered at the login screen with that stored in the database.

I'm using the following query:

final String DATABASE_COMPARE =
"select count(*) from users where uname=" + loginname + "and pwd=" + loginpass + ");" ;

The issue is, I don't know, how can I execute the above query and store the count returned.

Here's how the database table looks like ( I've manged to create the database successfully using the execSQl method)

private static final String
DATABASE_CREATE =
            "create table users (_id integer autoincrement, "
            + "name text not null, uname primary key text not null, " 
            + "pwd text not null);";//+"phoneno text not null);";

Can someone kindly guide me as to how I can achieve this? If possible please provide a sample snippet to do the above task.

Solution 1:

DatabaseUtils.queryNumEntries (since api:11) is useful alternative that negates the need for raw SQL(yay!).

SQLiteDatabase db = getReadableDatabase();
DatabaseUtils.queryNumEntries(db, "users",
                "uname=? AND pwd=?", new String[] {loginname,loginpass});

Solution 2:

@scottyab the parametrized DatabaseUtils.queryNumEntries(db, table, whereparams) exists at API 11 +, the one without the whereparams exists since API 1. The answer would have to be creating a Cursor with a db.rawQuery:

Cursor mCount= db.rawQuery("select count(*) from users where uname='" + loginname + "' and pwd='" + loginpass +"'", null);
mCount.moveToFirst();
int count= mCount.getInt(0);
mCount.close();

I also like @Dre's answer, with the parameterized query.

Solution 3:

Use an SQLiteStatement.

e.g.

 SQLiteStatement s = mDb.compileStatement( "select count(*) from users where uname='" + loginname + "' and pwd='" + loginpass + "'; " );

  long count = s.simpleQueryForLong();

Solution 4:

See rawQuery(String, String[]) and the documentation for Cursor

Your DADABASE_COMPARE SQL statement is currently invalid, loginname and loginpass won't be escaped, there is no space between loginname and the and, and you end the statement with ); instead of ; -- If you were logging in as bob with the password of password, that statement would end up as

select count(*) from users where uname=boband pwd=password);

Also, you should probably use the selectionArgs feature, instead of concatenating loginname and loginpass.

To use selectionArgs you would do something like

final String SQL_STATEMENT = "SELECT COUNT(*) FROM users WHERE uname=? AND pwd=?";

private void someMethod() {
    Cursor c = db.rawQuery(SQL_STATEMENT, new String[] { loginname, loginpass });
    ...
}