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!

paxdiablo · Accepted Answer · 2013-02-13T22:03:47.723

30

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

edited Feb 13 '13 at 22:03

answered Dec 09 '10 at 11:49

paxdiablo

854,327
234
1,573
1,953

okay that makes sense! But I need a cursor to query the database right? and i cant compare cur to 0, so i guess i should be looking for a way to turn my returned cur to an int? I'm not sure how it can count the rows in my table if a curser can't be an int..? Haha lots of question marks am i on the right track at all? – Holly Dec 09 '10 at 11:53
1

I don't know whether you _need_ one. I usually use either direct SQL or JDBC. But, assuming the code's correct in terms of using cursors, then you should read the first (and only) row and extract the value to see if it's zero (empty table) or non-zero (non-empty table). – paxdiablo Dec 09 '10 at 11:56
So maybe i should try a select statement to read the data in the first row of the first column (_id)? could i then check for nullity.. or maybe it would still need to be changed to an int, thanks for help i'll try and implement those ideas. – Holly Dec 09 '10 at 12:11
1

@Holly, see the update. I'd leave the query as is since it doesn't need to be any more complicated. Simply realise that you _will_ get back exactly one row and it's the _content_ of that row that indicates if the table was empty, not the _existence_ of that row. – paxdiablo Dec 09 '10 at 13:02
4

You **should not** use `SELECT COUNT(*)` on large tables!!!!! Use `SELECT EXISTS (select 1 from CAT_BUD_TAB)` instead. Note that _exists_ is smart enough to stop after the first row. – Cheborra Aug 08 '13 at 14:17

score 7 · Answer 2 · answered Dec 09 '10 at 12:20

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 
    }

}

Varun · Answer 3 · 2010-12-09T13:03:06.323

5

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

edited Dec 09 '10 at 13:03

answered Dec 09 '10 at 11:55

Varun

33,833
4
49
42

Thanks i tried as you suggested, i hadn't seen getCount before so thanks for mentioning it. But still nothing's in there, i'm going to try anding to log messages just to see if it's actually executing. Perhaps the problem is with my query statement? – Holly Dec 09 '10 at 12:06
@Holly I don see any prob in the quey but i was doing this yesterday and had this prob. and solved it the way i said above. – Varun Dec 09 '10 at 12:10
Okay thanks, im sure the code you provided is fine and something else im doing isn't quite right. I put a log message in the if to see if getCount was returning greater than 0 and it was, so something else is wrong. Thanks for your help – Holly Dec 09 '10 at 12:16
That code won't work simply because `getcount` will always return 1 for the query `select count(*)`. – paxdiablo Dec 09 '10 at 12:49
@paxdiablo @Holly. I had used db.query() method :) Edited the answer above – Varun Dec 09 '10 at 13:04

score 2 · Answer 4 · answered Jul 07 '11 at 00:43

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.

score 1 · Answer 5 · edited Jun 22 '12 at 07:59

1

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.

edited Jun 22 '12 at 07:59

Jon Lin

142,182
29
220
220

answered Jun 22 '12 at 07:10

Jimmy

11
1

Manish Dubey · Answer 6 · 2013-02-27T12:43:58.533

You can check it manually :

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.
Copy adb, aapt, AdbWinApi.dll and AdbWinUsbApi.dll files from platform_tools to tools folder.
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
After set path you'll write command "adb shell" without quotes. E:\Developers\Android\android-sdk-windows\tools> adb shell
Press Enter and write path of DDMS told above:- # sqlite3 /data/data/(Your Application Package)/databases/name of database
Press Enter sqlite>.tables
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.

score 1 · Answer 7 · edited Oct 12 '12 at 14:50

1

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.

edited Oct 12 '12 at 14:50

OcuS

5,320
3
36
45

answered Oct 12 '12 at 14:44

bellum

3,642
1
17
22

1

Hm.. Or even simpler: `SELECT 1 FROM CAT_BUD_TAB LIMIT 1`. In this situation you can check that the table is empty if this query returns nothing. – bellum Oct 18 '12 at 10:00
I believe you can omit the `CASE` expression as `EXISTS()` function already returns 0 or 1: `SELECT EXISTS(SELECT * FROM CAT_BUD_TAB)` – Dmitry Pashkevich Jul 18 '13 at 13:19

score 1 · Answer 8 · answered May 03 '17 at 10:25

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

Mohd Zaid · Answer 9 · 2018-03-24T19:01:37.183

1

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
           }

edited Mar 24 '18 at 19:01

answered Mar 24 '18 at 18:50

Mohd Zaid

659
6
14

score 0 · Answer 10 · answered Feb 12 '14 at 21:07

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

score 0 · Answer 11 · answered May 07 '15 at 18:18

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

score 0 · Answer 12 · answered Aug 27 '15 at 10:42

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

Example

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+"';";

score 0 · Answer 13 · edited Nov 28 '17 at 19:31

0

public boolean isEmpty(String TableName){

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

edited Nov 28 '17 at 19:31

DeadlyChambers

5,217
4
44
61

answered Nov 28 '17 at 17:19

Mahesh Uligade

597
8
17

1

A small explanation would be helpful. What's different than OPs code? Why does your work and his version not? – Peanut Nov 28 '17 at 18:04
sorry not seen Joacer's answer before – Mahesh Uligade Nov 28 '17 at 18:50

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

13 Answers13

Linked

Related