SQLite Query in Android to count rows
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 });
...
}
Solution 5
Assuming you already have a Database (db
) connection established, I think the most elegant way is to stick to the Cursor
class, and do something like:
String selection = "uname = ? AND pwd = ?";
String[] selectionArgs = {loginname, loginpass};
String tableName = "YourTable";
Cursor c = db.query(tableName, null, selection, selectionArgs, null, null, null);
int result = c.getCount();
c.close();
return result;
arun
Updated on July 08, 2022Comments
-
arun almost 2 years
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.
-
Khobaib about 11 yearsfor OS < 11, it gets NoSuchMethodError error, is there any work-around to support for OS < 11?
-
Eduardo Herzer about 11 years@Khobaib Check the answer from ghchinoy
-
Khobaib about 11 years@EduardoHerzer I found a way with cursor.getCount() directly.
-
redochka almost 9 yearsYou should at least specify a column. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used. #perfmatters
-
Eloi Navarro almost 9 yearsIn fact if the necessary info is only a count, retrieving a single column (ID for instance) would be more than enough
-
stuckj over 7 yearsOne thing I like about this answer vs @scottyab's answer (which IS also really good) is that you can specify a LIMIT clause in this case. This is useful when you just want to know if there are ANY rows in a table (e.g., select (count(*) > 0) from TABLE LIMIT 1) vs no rows. I'm guessing that'll be faster when the table could have no rows or a ton of rows. This is the specific case I had when searching for this...
-
DearVolt almost 7 years@Khobaib That's not a very efficient way of doing it. You should always prefer using
SELECT COUNT(*)
andqueryNumEntries()
over retrieving all the records and seeing how many there are, ie.cursor.getCount()
-
DearVolt almost 7 years@Teknogrebo: This is a nice answer, though I would use the parameterized version. You can use
?
s in the query and then usebindString(int, String)
andbindLong(int, long)
to insert the values. See here. -
slott over 6 yearsAmazing - been working with Android since forever and never knew about this DatabaseUtils - Google should be better at promoting there utils...
-
T.Woody almost 6 yearsFor those wondering, the parameter for
mCount.getInt()
iscolumnindex
. Thank you for the help!