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.

Martin Harris · Accepted Answer · 2012-01-30 16:42:44Z

124

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

edited Jan 30, 2012 at 16:42

Martin Harris

28.7k8 gold badges92 silver badges102 bronze badges

answered Jan 15, 2012 at 17:10

scottyab

24.1k17 gold badges98 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Khobaib Over a year ago

for OS < 11, it gets NoSuchMethodError error, is there any work-around to support for OS < 11?

Eduardo Herzer Over a year ago

@Khobaib Check the answer from ghchinoy

Khobaib Over a year ago

@EduardoHerzer I found a way with cursor.getCount() directly.

DearVolt Over a year ago

@Khobaib That's not a very efficient way of doing it. You should always prefer using SELECT COUNT(*) and queryNumEntries() over retrieving all the records and seeing how many there are, ie. cursor.getCount()

ghchinoy · Accepted Answer · 2012-01-18 14:37:00Z

120

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

answered Jan 18, 2012 at 14:37

ghchinoy

1,5331 gold badge11 silver badges16 bronze badges

2 Comments

T.Woody Over a year ago

For those wondering, the parameter for mCount.getInt() is columnindex. Thank you for the help!

android developer Over a year ago

How would you do this for contentResolver.query ?

Teknogrebo · Accepted Answer · 2014-11-04 23:26:06Z

62

Use an SQLiteStatement.

e.g.

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

  long count = s.simpleQueryForLong();

edited Nov 4, 2014 at 23:26

user4039065

answered Mar 21, 2011 at 17:42

Teknogrebo

1,2471 gold badge12 silver badges22 bronze badges

2 Comments

stuckj Over a year ago

One 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 Over a year ago

@Teknogrebo: This is a nice answer, though I would use the parameterized version. You can use ?s in the query and then use bindString(int, String) and bindLong(int, long) to insert the values. See here.

Dre · Accepted Answer · 2011-03-05 07:50:24Z

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

Alexander Abakumov · Accepted Answer · 2017-07-11 20:33:25Z

22

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;

edited Jul 11, 2017 at 20:33

Alexander Abakumov

14.7k16 gold badges99 silver badges135 bronze badges

answered Jan 14, 2013 at 10:19

Eloi Navarro

1,4451 gold badge14 silver badges26 bronze badges

2 Comments

redochka Over a year ago

You 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 Over a year ago

In fact if the necessary info is only a count, retrieving a single column (ID for instance) would be more than enough

Community · Accepted Answer · 2017-05-23 10:31:26Z

12

how to get count column

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

int sometotal = (int) DatabaseUtils.longForQuery(db, DATABASE_COMPARE, null);

This is the most concise and precise alternative. No need to handle cursors and their closing.

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered May 6, 2012 at 13:22

Arif Amirani

26.7k3 gold badges35 silver badges30 bronze badges

1 Comment

slott Over a year ago

Amazing - been working with Android since forever and never knew about this DatabaseUtils - Google should be better at promoting there utils...

Beshoy Fayez · Accepted Answer · 2014-02-14 01:06:15Z

6

If you are using ContentProvider then you can use:

Cursor cursor = getContentResolver().query(CONTENT_URI, new String[] {"count(*)"},
            uname=" + loginname + " and pwd=" + loginpass, null, null);
    cursor.moveToFirst();
    int count = cursor.getInt(0);

answered Feb 14, 2014 at 1:06

Beshoy Fayez

3005 silver badges10 bronze badges

Comments

Anik Islam Abhi · Accepted Answer · 2016-05-04 09:56:51Z

6

If you want to get the count of records then you have to apply the group by on some field or apply the below query.

Like

db.rawQuery("select count(field) as count_record from tablename where field =" + condition, null);

edited May 4, 2016 at 9:56

Anik Islam Abhi

25.4k8 gold badges61 silver badges82 bronze badges

answered Mar 5, 2011 at 7:03

Chirag

56.9k29 gold badges155 silver badges199 bronze badges

Comments

Jochen Birkle · Accepted Answer · 2013-06-18 07:03:03Z

5

Another way would be using:

myCursor.getCount();

on a Cursor like:

Cursor myCursor = db.query(table_Name, new String[] { row_Username }, 
row_Username + " =? AND " + row_Password + " =?",
new String[] { entered_Password, entered_Password }, 
null, null, null);

If you can think of getting away from the raw query.

answered Jun 18, 2013 at 7:03

Jochen Birkle

3351 gold badge4 silver badges14 bronze badges

Comments

4b0 · Accepted Answer · 2018-09-14 03:22:21Z

1

int nombr = 0;
Cursor cursor = sqlDatabase.rawQuery("SELECT column FROM table WHERE column = Value", null);
nombr = cursor.getCount();

edited Sep 14, 2018 at 3:22

4b0

22.4k30 gold badges97 silver badges143 bronze badges

answered Sep 13, 2018 at 18:10

Antoine Luckenson

52 bronze badges

Collectives™ on Stack Overflow

SQLite Query in Android to count rows

10 Answers 10

4 Comments

2 Comments

2 Comments

Comments

2 Comments

1 Comment

Comments

Like

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

4 Comments

2 Comments

2 Comments

Comments

2 Comments

1 Comment

Comments

Like

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related