Sqlite Check if Table is Empty

Question

Well, I have a databse and it has lots of table. but generally tables are empty. I want check if a database table is empty. IF table is empty, program will fill it.

public static long queryNumEntries (SQLiteDatabase db, String table)

I will use it but it requre API 11.

Note that the `queryNumEntries(db:table:)` method was added in API level 1; it's the `queryNumEntries(db:table:selection)` and `queryNumEntries(db:table:selection:selectionArgs)` methods which were added in API level 11. See the [android.database.DatabaseUtils](https://developer.android.com/reference/android/database/DatabaseUtils) Android Developers page for reference. — Adil Hussain, Nov 27 '20 at 10:01

score 48 · Accepted Answer · answered Mar 25 '14 at 09:32

48

you can execute select count(*) from table and check if count> 0 then leave else populate it.

like

 SQLiteDatabase db = table.getWritableDatabase();
String count = "SELECT count(*) FROM table";
Cursor mcursor = db.rawQuery(count, null);
mcursor.moveToFirst();
int icount = mcursor.getInt(0);
if(icount>0)
//leave 
else
//populate table

answered Mar 25 '14 at 09:32

Waqar Ahmed

5,005
2
23
45

well it is working. I have modified a little bit but I dont know It will accour problem in the future. I have deleted first line `SQLiteDatabase db = table.getWritableDatabase();` and at the beginning of class I have defined my database as **static** like this: `private static SQLiteDatabase mDb;` – mehmet Mar 25 '14 at 14:57
you can declare that way too but why do u define static.? – Waqar Ahmed Mar 25 '14 at 17:53
because of eclipse suggestion – mehmet Mar 26 '14 at 07:18
1

this is not a optimal solutions, As all knows count method take more process to execute a query, Please use select exists query instead of count() – Ashish Dwivedi Aug 28 '14 at 11:07
1

This works good. I put mine in a method that returns an int and assigned the cursor value to the returning value. Then I used this method in a if statement based on its returning value and if it equaled 0 do stuff else do nothing so to speak. I had to add some logging to the if sections just to track its steps in the DDMS and filtered the tags for clear reading tag:Database Operations. i.e. Log.v(TAG, "Some table is not empty"); thanks @Waqar Ahamed – natur3 Dec 30 '14 at 17:47
Why `getWritableDatabase()`? You are just reading the data. – Apurva Jul 24 '15 at 04:27
@Apurva..because if table will empty then we need to write data in table, so for that i use getWritableDatabase. – Waqar Ahmed Jul 24 '15 at 07:46

score 11 · Answer 2 · answered Mar 25 '14 at 09:33

11

Do a SELECT COUNT:

boolean empty = true
Cursor cur = db.rawQuery("SELECT COUNT(*) FROM YOURTABLE", null);
if (cur != null && cur.moveToFirst()) {
    empty = (cur.getInt (0) == 0);
}
cur.close();

return empty;

answered Mar 25 '14 at 09:33

Luca Sepe

2,435
1
20
26

score 10 · Answer 3 · edited Nov 27 '20 at 10:04

10

 public boolean isEmpty(String TableName){

    SQLiteDatabase database = this.getReadableDatabase();
    long NoOfRows = DatabaseUtils.queryNumEntries(database,TableName);

    if (NoOfRows == 0){
        return true;
    } else {
        return false;
    }
}

edited Nov 27 '20 at 10:04

Adil Hussain

30,049
21
112
147

answered Nov 28 '17 at 16:37

Mahesh Uligade

597
8
17

2

Instead of the `if` clause you can simply do `return NoOfRows == 0;` – LoMaPh Jun 29 '18 at 21:12
For the curious: the `DatabaseUtils` class referenced in this answer is the [android.database.DatabaseUtils](https://developer.android.com/reference/android/database/DatabaseUtils) class. – Adil Hussain Nov 27 '20 at 10:03

score 6 · Answer 4 · answered Aug 28 '14 at 11:08

Optimal Solutions

public boolean  isMasterEmpty() {

    boolean flag;
    String quString = "select exists(select 1 from " + TABLE_MASTERS  + ");";

    SQLiteDatabase db = getReadableDatabase();
    Cursor cursor = db.rawQuery(quString, null);
    cursor.moveToFirst();
    int count= cursor.getInt(0);
    if (count ==1) {
        flag =  false;
    } else {
        flag = true;
    } 
    cursor.close();
    db.close();

    return flag;
    }

score 3 · Answer 5 · answered Nov 27 '17 at 02:00

3

Here is a better option:

public boolean validateIfTableHasData(SQLiteDatabase myDatabase,String tableName){
    Cursor c = myDatabase.rawQuery("SELECT * FROM " + tableName,null);
    return c.moveToFirst();
}

answered Nov 27 '17 at 02:00

Juan Pablo Arango

31
1

4

For performance reason I would suggest to add a "LIMIT 1". – Antonio Vlasic May 14 '18 at 05:58

sjain · Answer 6 · 2014-03-25T10:56:36.073

1

This is how you can do it -

if(checkTable("TABLE"))
{
  //table exists fill data.
}

Method to check table -

public static boolean checkTable(String table) {
Cursor cur2 = dbAdapter.rawQuery("select name from sqlite_master where name='"
        + table + "'", null);

if (cur2.getCount() != 0) {
    if (!cur2.isClosed())
        cur2.close();
    return true;
} else {
    if (!cur2.isClosed())
        cur2.close();
    return false;
}
}

edited Mar 25 '14 at 10:56

answered Mar 25 '14 at 09:33

sjain

23,126
28
107
185

// db.close(); whts that? – mehmet Mar 25 '14 at 10:48
that's the commented line. You can skip that for your purpose. Deleted for your purpose. – sjain Mar 25 '14 at 10:55
This will check whether the table exists, not whether it has data in it. – laalto Mar 25 '14 at 10:57
yeah laalto is right it is everytime return else statement if using existing asset database. – mehmet Mar 25 '14 at 14:41

Денис Климков · Answer 7 · 2016-11-10T14:29:09.917

0

I think, this solution is better:

    boolean flag;

    DatabaseHelper databaseHelper = new DatabaseHelper(getApplicationContext(), DatabaseHelper.DATABASE_NAME, null, DatabaseHelper.DATABASE_VERSION);
    try {
        sqLiteDatabase = databaseHelper.getWritableDatabase();
    } catch (SQLException ex) {
        sqLiteDatabase = databaseHelper.getReadableDatabase();
    }
    String count = "SELECT * FROM table";
    Cursor cursor = sqLiteDatabase.rawQuery(count, null);
    if (cursor.moveToFirst()){
        flag = false;
    } else {
        flag = true;
    }
    cursor.close();
    sqLiteDatabase.close();

    return flag;

moveToFirst() check table and return true, if table is empty. Answer that is marked correct - uses extra check.

edited Nov 10 '16 at 14:29

answered Nov 10 '16 at 12:04

Денис Климков

185
2
14

1

Please include an explanation of your answer. – xpereta Nov 10 '16 at 12:27
@xpereta moveToFirst() check table and return true, if table is empty. – Денис Климков Nov 10 '16 at 13:50
@xpereta `moveToFirst()` check table and return `true`, if table is empty. – Денис Климков Nov 10 '16 at 13:50
Please, include the explanation in the actual answer, not in the comments. Also explain why you say it's better that the other answers. – xpereta Nov 10 '16 at 14:07

Sqlite Check if Table is Empty

7 Answers7

Linked