How can i check to see if my sqlite table has data in it?

Question

EDIT, Changed the code slightly based on answers below, but still haven't got it working. I also added a log message to tell me if getCount was returning > 0, and it was, so i supect somthing might be wrong with my query? or my use of the cursor..

I've created a table, and i want to check if its empty or not, if it's empty i want to run some insert statements (that are stored in an array).

Below is my code, while i have no errors, when i pull the .db file out i can see that it doesn't work. How would you approach this problem?

public void onCreate(SQLiteDatabase db) {
        Log.i("DB onCreate", "Creating the database...");//log message
        db.execSQL(createCATBUDTAB);
        db.execSQL(createTWOWEETAB);
        try{
            Cursor cur = db.rawQuery("SELECT COUNT(*) FROM CAT_BUD_TAB", null);
        if (cur.getCount() > 0){
            Log.i("DB getCount", " getcount greater than 0");//log message
            //do nothing everything's as it should be
        }
        else{//put in these insert statements contained in the array
            Log.i("DB getCount", " getcount less than 0, should read array");//log message
            for(int i=0; i<13; i++){
                db.execSQL(catInsertArray[i]);
            }
        }
        }catch(SQLiteException e){System.err.println("Exception @ rawQuery: " + e.getMessage());}
    }

Sorry if this is a pretty stupid question or approach, i'm new to all this. Any answers much appreciated!

Answer 1

The query SELECT COUNT(*) on an existing table should never return null. If there are no rows in the table, it should return one row containing the value zero.

Conversely, a row with a non-zero value indicates that it's not empty.

In both cases, one row should be returned, meaning that it will always go through the

//do nothing everything's as it should be

section.

To fix it, leave your query as-is (you don't want to do select column_name simply because that would be unnecessary and possibly a little inefficient). Leave it as select count(*), which will always return one row, and use the following code (tested only in my head so be careful):

Cursor cur = db.rawQuery("SELECT COUNT(*) FROM CAT_BUD_TAB", null);
if (cur != null) {
    cur.moveToFirst();                       // Always one row returned.
    if (cur.getInt (0) == 0) {               // Zero count means empty table.
        for (int i = 0; i < 13; i++) {
            db.execSQL (catInsertArray[i]);
        }
    }
}

Answer 2

for java programs this worked for me:

    ResultSet rst = stm.executeQuery("SELECT * from Login");
           int i = 0;
           while(rst.next()){
               ++i;
               System.out.println(i);
           }
           System.out.println(i);
           if(i<1){
               //No data in the table
           }
           else{
               //data exists
           }