How To Test If Cursor Is Empty in a SQLiteDatabase Query

Question

I have an SQL table which is created by the following code:

public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + _ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + SUBJECT
        + " TEXT NOT NULL," + TOPIC + " TEXT NOT NULL, "
        + LECTURENUMBER + " TEXT NOT NULL, " + PAGENUMBER
        + " TEXT NOT NULL, " + DATE + " TEXT NOT NULL, " + _DATA
        + " TEXT NOT NULL);");
}

I query the table as follows:

String sql = "SELECT " + _ID + "," + SUBJECT + " FROM " + TABLE_NAME
    + " GROUP BY " + SUBJECT + ";";

Cursor cursor = subjects.getReadableDatabase().rawQuery(sql, null);

The problem is I have to start an Activity A if the cursor is empty(i.e. the table is storing no values) and Activity B if the cursor is not empty(i.e. table is filled).

I am unable to find a method which can tell me if the table is empty or not. I Have tried to used Log as follows:

private void showSubjectsOnList() {
    String sql = "SELECT " + _ID + "," + SUBJECT + " FROM " + TABLE_NAME
        + " GROUP BY " + SUBJECT + ";";

    Cursor cursor = subjects.getReadableDatabase().rawQuery(sql, null);

    Log.d("Events",Integer.toString(cursor.getCount()));
    if(cursor.isNull(0)!=false){
        cursor.close();
        subjects.close();
        startActivity(new Intent(this,OpenScreen.class));
    }
}

But the LOG shows 1, if the table is empty...and again 1, if table has 1 entry....it shows 2, if table has two entries and so on.

Can you suggest some method of solving my problem of starting different activities based on if cursor is empty or not.

Solution 1

What about testing the cursor like this, and then doing what you've said:

if(cursor!=null && cursor.getCount()>0)

getCount ()

Returns the numbers of rows in the cursor

http://developer.android.com/reference/android/database/Cursor.html#getCount()

Solution 2

The easiest and cleanest way to test for an empty cursor is the following code:

if ( cursor.moveToFirst() ) {
    // start activity a
} else {
    // start activity b
}

Per the docs, the method returns false if the cursor is empty:

http://developer.android.com/reference/android/database/Cursor.html#moveToFirst%28%29

public abstract boolean moveToFirst ()

Added in API level 1 Move the cursor to the first row.

This method will return false if the cursor is empty.

Returns whether the move succeeded.

Solution 3

Deleted records remain in SQLite as null records, but getCount() counts only not null records. If your table has some records that are null, some of not null records will have _Id numbers bigger than result of getCount(). To reach them, you can iterate cursor ( using for() loop ) double the number of times than result of getCount() and use the cursor to fill record_Id numbers into an array. Lets say resulting array is { 1, 2, 5, 6, 7, 8, 9, 11, 12, 14 }.

That means records 3, 4, 10, 13, are null records and your table has 14 record all together, not 10 that you got from getCount().

Remember:

1. getCount() returns number of not null records ,
2. cursor returns _Id numbers of not null records,
3. _Id numbers "missed" by cursor are _Id numbers of null records,
4. must reach sufficiently further than getCount() to get them all.

Solution 4

You just need to use getCount(). If your sql is correct but doesn't return any row you will have a NOT null cursor object but without a rows and getCount() will return 0.

View more solutions