How to get row count in sqlite using Android?

Question

I am creating task manager. I have tasklist and I want when I click on particular tasklist name if it empty then it goes on Add Task activity but if it has 2 or 3 tasks then it shows me those tasks into it in list form.

I am trying to get count in list. my database query is like:

public Cursor getTaskCount(long tasklist_Id) {

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor= db.rawQuery("SELECT COUNT (*) FROM " + TABLE_TODOTASK + " WHERE " + KEY_TASK_TASKLISTID + "=?",
             new String[] { String.valueOf(tasklist_Id) });
    if(cursor!=null && cursor.getCount()!=0)
          cursor.moveToNext();
    return cursor;
}

In My activity:

list_tasklistname.setOnItemClickListener(new OnItemClickListener() {
    @Override
    public void onItemClick(AdapterView<?> arg0,
            android.view.View v, int position, long id) {
                db = new TodoTask_Database(getApplicationContext());
                Cursor c = db.getTaskCount(id);
                System.out.println(c.getCount());
                if(c.getCount()>0) {    
                System.out.println(c);
                Intent taskListID = new Intent(getApplicationContext(), AddTask_List.class);
                task = adapter.getItem(position);
                int taskList_id = task.getTaskListId();
                taskListID.putExtra("TaskList_ID", taskList_id);
                startActivity(taskListID);
            }
            else {
                Intent addTask = new Intent(getApplicationContext(), Add_Task.class);
                startActivity(addTask);
            }
        }
    });
    db.close();
}

but when I am clicking on tasklist name it is returning 1, bot number of tasks into it.

score 165 · Accepted Answer · edited Jan 24 '18 at 09:22

165

Using DatabaseUtils.queryNumEntries():

public long getProfilesCount() {
    SQLiteDatabase db = this.getReadableDatabase();
    long count = DatabaseUtils.queryNumEntries(db, TABLE_NAME);
    db.close();
    return count;
}

or (more inefficiently)

public int getProfilesCount() {
    String countQuery = "SELECT  * FROM " + TABLE_NAME;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    int count = cursor.getCount();
    cursor.close();
    return count;
}

In Activity:

int profile_counts = db.getProfilesCount();
    db.close();

edited Jan 24 '18 at 09:22

Komal12

3,340
4
16
25

answered Aug 07 '13 at 08:38

Exceptional

2,994
1
18
25

40

It is inefficient to load all rows; better use [queryNumEntries()](http://developer.android.com/reference/android/database/DatabaseUtils.html#queryNumEntries%28android.database.sqlite.SQLiteDatabase,%20java.lang.String%29). – CL. Dec 07 '14 at 20:45
1

If you are wanting to count rows with a certain condition, the `queryNumEntries()` method can also take `selection` and `selectionArgs` parameters. See https://developer.android.com/reference/android/database/DatabaseUtils.html#queryNumEntries(android.database.sqlite.SQLiteDatabase,%20java.lang.String,%20java.lang.String,%20java.lang.String[]) – ban-geoengineering Feb 02 '18 at 15:56
@CL. queryNumEntries() is very useful, thanks for pointing out, saved many lines of code :) – TapanHP Jul 19 '18 at 11:06
2

To get a count you should never do getCount on a cursor. This will bring in all the results from the database into memory thus use a lot more memory. Instead you could use "SELECT COUNT(*) FROM " + TABLE_NAME. Then the result will be stored in column 0 of the cursor. – Jona Feb 28 '19 at 22:46

score 50 · Answer 2 · edited Nov 01 '18 at 15:20

50

Use android.database.DatabaseUtils to get number of count.

public long getTaskCount(long tasklist_Id) {
        return DatabaseUtils.queryNumEntries(readableDatabase, TABLE_NAME);
}

It is easy utility that has multiple wrapper methods to achieve database operations.

edited Nov 01 '18 at 15:20

John Cummings

1,949
3
22
38

answered Feb 05 '15 at 15:43

Mayur Raiyani

765
7
15

4

This should be marked as the answer to OP's question. No need to write any SQL queries or load all the entries from the table. +1. – Martin Jul 07 '15 at 09:04
2

Best answer !!! This is the most simple answer and utilize the existing feature. Up to the 2nd top. – poring91 Dec 13 '15 at 12:22

score 39 · Answer 3 · edited Jan 22 '18 at 15:25

39

c.getCount() returns 1 because the cursor contains a single row (the one with the real COUNT(*)). The count you need is the int value of first row in cursor.

public int getTaskCount(long tasklist_Id) {

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor= db.rawQuery(
        "SELECT COUNT (*) FROM " + TABLE_TODOTASK + " WHERE " + KEY_TASK_TASKLISTID + "=?",
         new String[] { String.valueOf(tasklist_Id) }
    );
    int count = 0;
    if(null != cursor)
        if(cursor.getCount() > 0){
          cursor.moveToFirst();    
          count = cursor.getInt(0);
        }
        cursor.close();
    }

    db.close();
    return count;
}

edited Jan 22 '18 at 15:25

alexandre-rousseau

2,321
26
33

answered Aug 07 '13 at 08:02

Ungureanu Liviu

4,034
4
37
42

It means my database method is fine? – Shweta Aug 07 '13 at 08:31
I just edited my answer: now the method should return directly the rows count. – Ungureanu Liviu Aug 07 '13 at 08:52
2

there is one typo.. it should be cursor.getInt(0); and not c.getInt(0); – Zain Ullah Muhammad Nov 30 '16 at 15:04

score 7 · Answer 4 · answered Oct 28 '16 at 06:56

I know it is been answered long time ago, but i would like to share this also:

This code works very well:

SQLiteDatabase db = this.getReadableDatabase();
long taskCount = DatabaseUtils.queryNumEntries(db, TABLE_TODOTASK);

BUT what if i dont want to count all rows and i have a condition to apply?

DatabaseUtils have another function for this: DatabaseUtils.longForQuery

long taskCount = DatabaseUtils.longForQuery(db, "SELECT COUNT (*) FROM " + TABLE_TODOTASK + " WHERE " + KEY_TASK_TASKLISTID + "=?",
         new String[] { String.valueOf(tasklist_Id) });

The longForQuery documentation says:

Utility method to run the query on the db and return the value in the first column of the first row.

public static long longForQuery(SQLiteDatabase db, String query, String[] selectionArgs)

It is performance friendly and save you some time and boilerplate code

Hope this will help somebody someday :)

Markus Penguin · Answer 5 · 2013-08-07T09:03:17.850

5

Change your getTaskCount Method to this:

public int getTaskCount(long tasklist_id){
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor= db.rawQuery("SELECT COUNT (*) FROM " + TABLE_TODOTASK + " WHERE " + KEY_TASK_TASKLISTID + "=?", new String[] { String.valueOf(tasklist_id) });
    cursor.moveToFirst();
    int count= cursor.getInt(0);
    cursor.close();
    return count;
}

Then, update the click handler accordingly:

public void onItemClick(AdapterView<?> arg0, android.view.View v, int position, long id) {
    db = new TodoTask_Database(getApplicationContext());

    // Get task list id
    int tasklistid = adapter.getItem(position).getTaskListId();

    if(db.getTaskCount(tasklistid) > 0) {    
        System.out.println(c);
        Intent taskListID = new Intent(getApplicationContext(), AddTask_List.class);
        taskListID.putExtra("TaskList_ID", tasklistid);
        startActivity(taskListID);
    } else {
        Intent addTask = new Intent(getApplicationContext(), Add_Task.class);
        startActivity(addTask);
    }
}

edited Aug 07 '13 at 09:03

answered Aug 07 '13 at 08:06

Markus Penguin

1,581
12
19

what is not working? try logging the tasklist_id to logcat to make sure that you are using the expected id. – Markus Penguin Aug 07 '13 at 08:39
Your method is fine but I think I am getting wrong id from OnItemClick(); – Shweta Aug 07 '13 at 08:53
Instead of passing "id" to getTaskCount pass taskList_id from task.getTaskListId() - as you already do later in the if statement. – Markus Penguin Aug 07 '13 at 08:57
I can not do this because task is Task class object but addtask is ADDTask class object, addtask can't use adapter, how can i get position or id? – Shweta Aug 07 '13 at 09:01
i have updated my answer with the code for the listener, does this work? – Markus Penguin Aug 07 '13 at 09:04
I have used cursor.getCount(); it is returning value – Shweta Aug 07 '13 at 09:08

score 4 · Answer 6 · answered Oct 24 '15 at 04:52

In order to query a table for the number of rows in that table, you want your query to be as efficient as possible. Reference.

Use something like this:

/**
 * Query the Number of Entries in a Sqlite Table
 * */
public long QueryNumEntries()
{
    SQLiteDatabase db = this.getReadableDatabase();
    return DatabaseUtils.queryNumEntries(db, "table_name");
}

score 3 · Answer 7 · answered Nov 03 '18 at 16:54

3

Sooo simple to get row count:

cursor = dbObj.rawQuery("select count(*) from TABLE where COLUMN_NAME = '1' ", null);
cursor.moveToFirst();
String count = cursor.getString(cursor.getColumnIndex(cursor.getColumnName(0)));

answered Nov 03 '18 at 16:54

ghislaink

51
1

score 2 · Answer 8 · answered Aug 31 '17 at 14:20

2

Do you see what the DatabaseUtils.queryNumEntries() does? It's awful! I use this.

public int getRowNumberByArgs(Object... args) {
    String where = compileWhere(args);
    String raw = String.format("SELECT count(*) FROM %s WHERE %s;", TABLE_NAME, where);
    Cursor c = getWriteableDatabase().rawQuery(raw, null);
    try {
        return (c.moveToFirst()) ? c.getInt(0) : 0;
    } finally {
        c.close();
    }
}

answered Aug 31 '17 at 14:20

Stanislav Perchenko

370
3
4

1

Can you please provide more information on what queryNumEntries does exactly and why it is aweful? Thank you! – Ray Li Oct 30 '17 at 00:26
1

It goes 4 levels deep and executes lots of collateral code to do the same thing as I wrote in my answer. The code-size economy with using this helper library is doubtful – Stanislav Perchenko Nov 22 '17 at 13:33
I agree, it's awful. Using count(*) is a correct answer. – mes Jun 22 '18 at 06:40

score 1 · Answer 9 · answered Aug 09 '21 at 14:23

looking at the sources of DatabaseUtils we can see that queryNumEntries uses a select count(*)... query.

public static long queryNumEntries(SQLiteDatabase db, String table, String selection,
        String[] selectionArgs) {
    String s = (!TextUtils.isEmpty(selection)) ? " where " + selection : "";
    return longForQuery(db, "select count(*) from " + table + s,
                selectionArgs);
}

score -1 · Answer 10 · edited Apr 26 '19 at 09:44

-1

Once you get the cursor you can do

Cursor.getCount()

edited Apr 26 '19 at 09:44

AS Mackay

2,831
9
19
25

answered Jul 10 '18 at 16:02

Shyam Sunder

523
4
13

How to get row count in sqlite using Android?

10 Answers10

Linked