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

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
       开发者_如何学JAVA     //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!

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

The rawQuery returns a Cursor object which is positioned before the first entry (See more info here) SELECT COUNT(*) will always return a result (considering the table exists)

So I would do:

if (cur != null){
    cur.moveToFirst();
    if (cur.getInt(0) == 0) {
      // Empty 
    }

}

As paxdiablo said, the cursor will not be null. What you can do is try like this:

if (cur != null && cur.getCount() > 0){
     // do nothing, everything's as it should be
}

EDIT

Actually i had used the db.query() and it worked for me. I did this.

cursor = db.query(TABLE_NAME, new String[] { KEY_TYPE }, null, null, null, null, null);
if (cursor != null && cursor.getCount() > 0)
{
   retVal = true;
}

TABLE_NAME is my table and KEY_TYPE was my columnname

you mean if it has record right ? i've tried this and for me it's work

    dbCur = db.rawQuery("select * from player", null);
    Log.e("a", ""+dbCur.getCount());

it will show the sum of the table, if the table doesn't have record, then it will show 0.

My way of checking was something like this:

Cursor cursor = db.query(DbHelper.DB_NAME,DbHelper.DB_C_ID_ONLY,null,null,null,null,null);
if(cursor.isAfterLast())

I get the _id entries which in my case are auto-increment. If the DB is empty, isAfterLast for sure returns true.

What about this query?

SELECT CASE WHEN EXISTS (SELECT * FROM CAT_BUD_TAB) THEN 1 ELSE 0 END

It's slightly faster that SELECT COUNT(*).

P.S. Checked on tables with 9 million rows.

You can check it manually :

1. Go to DDMS in File Explorer. /data/data/(your application package)/databases Here you'll get your database file. And if you want to see inserted values in table.

2. Copy adb, aapt, AdbWinApi.dll and AdbWinUsbApi.dll files from platform_tools to tools folder.

3. Then Open Command Prompt and set your path to "Tools" directory where your android setup exist. It look like as :- (Local Drive):\Android\android-sdk-windows\tools

4. After set path you'll write command "adb shell" without quotes. E:\Developers\Android\android-sdk-windows\tools> adb shell

5. Press Enter and write path of DDMS told above:- # sqlite3 /data/data/(Your Application Package)/databases/name of database

6. Press Enter sqlite>.tables

7. Press Enter And you'll get all table's name existing in that database.

sqlite> Select * from table name

All data exists in that table will show.

For any further query feel free to comment.

Another alternative to the one already mentioned would be to use the function queryNumEntries from de class DatabaseUtils.

An example may be as follows:

public boolean checkEmpty(SQLiteDatabase db, String table){
        return DatabaseUtils.queryNumEntries(db, table) == 0;
}

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
           }

Here's what I did...

Cursor cur = db.rawQuery("SELECT count(*) FROM " + SQLHelper.TABLE_CART, null);
if (cur != null && cur.moveToFirst() && cur.getInt(0) > 0) {
    Log.i(getClass().getName(), "table not empty");
} 
else {
    Log.i(getClass().getName(), "table is empty");
}

My app was crashing trying the above codes, so I did this and now it's working perfectly!

public boolean checkIfEmpty()
{
    Cursor cursor = getDatabase().query(DatabaseHelper.Products.TABLE,
            DatabaseHelper.Products.COLUMNS, null, null, null, null, null);

    if (cursor != null)
    {
        try
        {
            //if it is empty, returns true.
            cursor.moveToFirst();
            if (cursor.getInt(0) == 0)
                return true;
            else
                return false;
        }

        //this error usually occurs when it is empty. So i return true as well. :)
        catch(CursorIndexOutOfBoundsException e)
        {
            return true;
        }

    }
    return false;
}

Use this:

public Boolean doesRecordExist(String TableName, String ColumnName, String ColumnData) {
    String q = "Select * FROM "+TableName+" WHERE "+ColumnName+"='"+ColumnData+"';";
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(q, null);
    if (cursor.moveToFirst()) {
        return true;
    } else {
        return false;
    }
}

if (doesRecordExist("student", "name", "John") == true)
 {
 //Do Something
 } else { //Do something
 }

Modify This according to your usage:

String q = "Select * FROM "+TableName+" WHERE "+ColumnName+"='"+ColumnData+"';";

public boolean isEmpty(String TableName){

SQLiteDatabase database = this.getReadableDatabase();
return (int) DatabaseUtils.queryNumEntries(database,TableName) == 0;
}