How to delete SQLite database table in android java

Question

I am using the following code for deleting SQLite database table. Apparently, there is a message that the table has been deleted, but when the app is run again, the table is shown again.

ContactsDetailsDbAdapter.java

    private String r(String s, String str, String s1) {
this.s = s;
this.str = str;
this.s1 = s1;
return str;
}

 public void deleteGroupData(String str) {
this.mDb.delete(DATABASE_TABLE, r("contact_group_id='", str, "'"), (String[]) null);
 }

GroupDetailsDbAdapter.java

        public boolean deleteData(String str) {
    SQLiteDatabase sQLiteDatabase = this.mDb;
    StringBuilder sb = new StringBuilder();
    sb.append("group_id=");
    sb.append(str);
    return sQLiteDatabase.delete(DATABASE_TABLE, sb.toString(), 
(String[]) null) > 0;
}

ContactsList.java

   button.setOnLongClickListener(new View.OnLongClickListener() {
public boolean onLongClick(View view) {
    GroupDetailsDbAdapter groupDetailsDbAdapter = new 
   GroupDetailsDbAdapter(ContactsList.this);
    groupDetailsDbAdapter.open();
    groupDetailsDbAdapter.deleteData(str);
    groupDetailsDbAdapter.close();
    ContactsDetailsDbAdapter contactsDetailsDbAdapter = new 
    ContactsDetailsDbAdapter(ContactsList.this);
    contactsDetailsDbAdapter.open();
    contactsDetailsDbAdapter.deleteGroupData(str);
    contactsDetailsDbAdapter.close();
    dialog.dismiss();
    Toast.makeText(ContactsList.this.getApplicationContext(), "Deleted Successfully", 
   Toast.LENGTH_SHORT).show();
    ContactsList.this.finish();
    return true;
   }
  });

While following error is being shown in Logcat. Can someone please check this code and tell me what is the error?

Answer 1

The delete convenience method says :-

Convenience method for deleting rows in the database.

A table is a container of rows (0 to n rows). Deleting all rows will result in the table still existing but empty.

You need to DROP the table. There is no convenience method for doing so. You need to use the execSQL method to invoke the drop SQL.

e.g. sQLiteDatabase.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE + ";"); instead of return sQLiteDatabase.delete(DATABASE_TABLE, sb.toString(), (String[]) null) > 0;. But see the note below about execSQL not returning any values.

as per https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#execSQL(java.lang.String)

• IF EXISTS means that if the table does not exist there will be no failure.

• execSQL does not return any values, so you would need to either not return anything or ascertain the suitable value to return.

So perhaps use:-

public deleteData(String str) {
    SQLiteDatabase sQLiteDatabase = this.mDb;
    sQLiteDatabase.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE + ";");
}

• note no value returned

or:-

public boolean deleteData(String str) {
    SQLiteDatabase sQLiteDatabase = this.mDb;
    sQLiteDatabase.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE + ";");
    return true;
}

• unlike the convenience delete which includes extraction of the number of rows deleted. Deleting (DROPing) a table has no such results. Hence there is no value to return. If you wanted an indication as to whether or not the table was dropped then you would have to check to see if it existed prior to the delete or use drop without IF EXISTS and handle the exception if the table does not exist.