How To Test If Cursor Is Empty in a SQLiteDatabase

问题:

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.

回答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()

回答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.

回答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.

回答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.

回答5:

My suggestion would be using a ListActivity.

Those are Activity's which are meant to display items in a ListView. You can simply use a SimpleCursorAdapter to populate them (also illustrated in the ListActivitys JavaDoc page).

They also offer a setEmptyView()-method, which can be used to display a View (might be a TextView) which informs the user that there are no records yet and how he can create one.

An example on how to do that can be found here.

回答6:

I believe your problem is you're not creating a proper query.

You should use the SQLiteDatabase query.

Cursor c = db.query(TABLE_NAME, null,
            null, null, null, null, null);

You then can use c.getCount() to determine if the table has anything.