CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Farbod Salamat-Zadeh
Farbod Salamat-Zadeh

Reputation: 20080

How to use _COUNT in BaseColumns

I've been reading up on BaseColumns](https://developer.android.com/reference/android/provider/BaseColumns.html) in Android to help structure my database schema.

I know that _ID is a unique identifier for the row that you have to create yourself:

protected static final String SQL_CREATE = "CREATE TABLE " + TABLE_NAME + "( " + 
        _ID + " INTEGER PRIMARY KEY AUTOINCREMENT" +  ...;

I also read that _COUNT is used to refer to the number of rows in a table.

However, when I tried using _COUNT, I got an error. Here is what I tried:

SQLiteDatabase db = TimetableDbHelper.getInstance(context).getReadableDatabase();
Cursor cursor = db.query(
        SubjectsSchema.TABLE_NAME,
        new String[] {SubjectsSchema._COUNT},
        null, null, null, null, null);
cursor.moveToFirst();
int count = cursor.getInt(cursor.getColumnIndex(SubjectsSchema._COUNT));
cursor.close();
return count;

I'm not sure whether or not this is the correct way to use it, but I got this error:

android.database.sqlite.SQLiteException: no such column: _count (code 1): , while compiling: SELECT _count FROM subjects

How should I be using _COUNT?

Upvotes: 2

Views: 760

Answers (1)

CL.
CL.

Reputation: 180070

In the database, there is nothing special about either _id or _count.

Your queries return an _id or _count column when the table is defined to have such a column, or when the query explicitly computes it.

Many objects of the Android framework expect a cursor to have a unique _id column, so many tables define it.

In most places, the _count is not expected to be present, so it is usually not implemented. And if it is actually needed, it can simply be computed with a subquery, like this:

SELECT _id,
       [other fields],
       (SELECT COUNT(*) FROM MyTable) AS _count
FROM MyTable
WHERE ...

If you want to find out the size of your own table, you are not required to use the _count name; you can execute a query like SELECT COUNT(*) FROM subjects, or, even simpler, use a helper function that does this for you.

Upvotes: 1

Related Questions