How to get the row count of a query in Android using SQLite?

Question

How do I get the row count of a query in Android using SQLite? It seems my following method does not work.

public int getFragmentCountByMixId(int mixId) {
    int count = 0;
    SQLiteDatabase db = dbOpenHelper.getWritableDatabase();

    Cursor cursor = db.rawQuery(
        "select count(*) from downloadedFragement where mixId=?",
        new String[]{String.valueOf(mixId)});
    while(cursor.moveToFirst()){
        count = cursor.getInt(0);
    }
    return count;
}

Unrelated to question: You can't iterate a cursor by calling "moveToFirst" indefinitely. You should call it once and then use while(cursor.moveToNext()) { ... — Ankhwatcher
– Ankhwatcher, Commented Oct 26, 2017 at 16:02

michaelg · Accepted Answer · 2012-04-15 14:52:13Z

107

Cursor.getCount()

edited Apr 15, 2012 at 14:52

user142162

answered Jun 15, 2011 at 0:07

michaelg

2,6921 gold badge17 silver badges16 bronze badges

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

3 Comments

Noel Over a year ago

I don't know how this would work with your current implementation but if you simply change count(*) to *, this should work.

Pratik Butani Over a year ago

I think best solution return (int) DatabaseUtils.longForQuery(mDB, "SELECT COUNT(*) FROM table_name", null);

IgorGanapolsky Over a year ago

This would return 0 for a cursor that has 1 row.

Pramod Setlur · Accepted Answer · 2012-12-19 10:40:24Z

8

cursor.moveToNext();
cursor.getCount();

If the moveToNext() is not called, the cursorIndexOutOfBoundException may arise.

answered Dec 19, 2012 at 10:40

Pramod Setlur

8112 gold badges15 silver badges27 bronze badges

Comments

Pratik Butani · Accepted Answer · 2015-09-11 07:47:08Z

6

This would be more efficient because work for all versions:

int numRows = DatabaseUtils.longForQuery(db, "SELECT COUNT(*) FROM table_name", null);

or

int numRows = DatabaseUtils.queryNumEntries(db, "table_name");

or if you want to get number of rows which specific selection then you should go with (added in API 11)

public static long queryNumEntries (SQLiteDatabase db, String table, String selection)

Thanks :)

answered Sep 11, 2015 at 7:47

Pratik Butani

62.6k63 gold badges289 silver badges453 bronze badges

1 Comment

AJW Over a year ago

In you first example you use longForQuery(). I thought that method only returns the value in the first column of the first row. So how would that show the total number of rows in the database? Is it because the query SELECT COUNT(*) is run on the full database?

LK Yeung · Accepted Answer · 2012-07-26 18:17:44Z

1

use String instead of int

String strCount = "";
int count = 0;
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();

Cursor cursor = db.rawQuery(
    "select count(*) from downloadedFragement where mixId=?",
    new String[]{String.valueOf(mixId)});

while(cursor.moveToFirst()){
    strCount = cursor.getString(cursor.getColumnIndex("COUNT(*)"));
}
count = Integer.valueOf(strCount).intValue();

answered Jul 26, 2012 at 18:17

LK Yeung

3,4965 gold badges29 silver badges42 bronze badges

Comments

Gopal · Accepted Answer · 2011-06-15 00:18:23Z

0

Query for _ID column in table and then call getCount on cursor. Here is a link I am doing in one of my project. Look at line number 110.

answered Jun 15, 2011 at 0:18

Gopal

1,71912 silver badges13 bronze badges

Comments

James Wierzba · Accepted Answer · 2015-10-16 19:14:41Z

0

In DatabaseUtils

public static long queryNumEntries(SQLiteDatabase db, String table)

answered Oct 16, 2015 at 19:14

James Wierzba

17.8k16 gold badges92 silver badges136 bronze badges

2 Comments

IgorGanapolsky Over a year ago

What if it's an in-memory database, with no database name?

AJW Over a year ago

Is "DatabaseUtils.queryNumEntries" still a good choice or has it been deprecated?

Rohit · Accepted Answer · 2018-11-28 20:24:25Z

0

 public long getRecords() {
    return DatabaseUtils.longForQuery(db, "SELECT COUNT(*) FROM contacts", null);
}

You can use this as a method or use this if your database uses auto increment

 public long getRecords() {
    return DatabaseUtils.longForQuery(db, "SELECT seq FROM sqlite_sequence", null);
}

answered Nov 28, 2018 at 20:24

Rohit

131 silver badge3 bronze badges

Comments

shim · Accepted Answer · 2019-01-08 20:05:16Z

0

val query = "SELECT * FROM $TABLE_NAME ;"
val result = db.rawQuery(query,null)
Toast.makeText(ctx,result.count,Toast.LENGTH_SHORT).show()

edited Jan 8, 2019 at 20:05

shim

10.3k13 gold badges79 silver badges121 bronze badges

answered Jan 8, 2019 at 19:33

Stan

1

Collectives™ on Stack Overflow

How to get the row count of a query in Android using SQLite?

8 Answers 8

3 Comments

Comments

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

3 Comments

Comments

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related