SQLite Query in Android to count rows

Question

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.

Answer 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}); 

Answer 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.