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.

Dapsang

Answer 1

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

Mummer

It is inefficient to load all rows; better use queryNumEntries(). – Keffiyeh

If you are wanting to count rows with a certain condition, the queryNumEntries() method can also take selection and selectionArgs parameters. See developer.android.com/reference/android/database/… – Christiniachristis

@Keffiyeh queryNumEntries() is very useful, thanks for pointing out, saved many lines of code :) – Expansible

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. – Mauriciomaurie

Answer 2

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.

Seemaseeming

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. – Shirley

Best answer !!! This is the most simple answer and utilize the existing feature. Up to the 2nd top. – Cobaltic

Answer 3

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

Undercast

It means my database method is fine? – Dapsang

I just edited my answer: now the method should return directly the rows count. – Undercast

there is one typo.. it should be cursor.getInt(0); and not c.getInt(0); – Ogilvie

Answer 4

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 :)

Sander

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

Jaimeejaimes

what is not working? try logging the tasklist_id to logcat to make sure that you are using the expected id. – Jaimeejaimes

Your method is fine but I think I am getting wrong id from OnItemClick(); – Dapsang

Instead of passing "id" to getTaskCount pass taskList_id from task.getTaskListId() - as you already do later in the if statement. – Jaimeejaimes

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? – Dapsang

i have updated my answer with the code for the listener, does this work? – Jaimeejaimes

I have used cursor.getCount(); it is returning value – Dapsang

Answer 6

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

Representative

Answer 7

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

Foah

Answer 8

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

Bombard

Can you please provide more information on what queryNumEntries does exactly and why it is aweful? Thank you! – Photolithography

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 – Bombard

I agree, it's awful. Using count(*) is a correct answer. – Thirty

Answer 9

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

Hurried

Answer 10

Once you get the cursor you can do

Cursor.getCount()

Dichasium