Search code examples
androidandroid-sqlitedelete-rowmobile-application

My mobile application crashes when deleting item

I am trying to develop a mobile application to get familiar with SQLite database. In here create, add methods are working. But when I am going to delete in list view the mobile app crashes. Here is the code that I have written.

In MainActivity.java

        lv_customerList.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                CustomerModel clickedCustomer = (CustomerModel) parent.getItemAtPosition(position);
                dataBaseHelper.deleteOne(clickedCustomer);
                extracted(dataBaseHelper);
                Toast.makeText(MainActivity.this, "Deleted" ,Toast.LENGTH_SHORT).show();
            }
        });



    }

    private void extracted(DataBaseHelper dataBaseHelper) {
        customerArrayAdapter = new ArrayAdapter<CustomerModel>(MainActivity.this, android.R.layout.simple_list_item_1, dataBaseHelper.getEveryone());
        lv_customerList.setAdapter(customerArrayAdapter);
    }


}

In databaseHelper.java class

`

public boolean deleteOne(CustomerModel customerModel){

        SQLiteDatabase db = this.getWritableDatabase();
        String queryString = "DELETE FROM" + CUSTOMER_TABLE + " WHERE " + COLUMN_ID + " = " + customerModel.getId();

        Cursor cursor = db.rawQuery(queryString, null);

        if(cursor.moveToFirst()){
            return true;
        }else {
            return false;
        }
    }
Solution

  • You have omitted a space between the keyword FROM and the table name and thus will encounter an exception along the lines of:-

     android.database.sqlite.SQLiteException: near "FROMcustomer": syntax error (code 1 SQLITE_ERROR): , while compiling: DELETE FROMcustomer WHERE _id = 9999
    • note the 9999 would be whatever value was extracted from the Customer.

    HOWEVER, you would then ALWAYS encounter a returned result of false from the DeleteOne method (see Demo below).

    This is due to a two-fold issue a) that the rawQuery method should ONLY be used for returning data that is extracted as an output SQL statement i.e. a SELECT statement or some PRAGMA statements and b) that other statements such as DELETE do not return anything and actually result in a rollback undoing what they have done.

    Instead you should either use:-

    1. an execSQL method, which again does not return a result,

      1. exeSQL does what is to be done, which may be nothing, or it fails, or

    2. the respective convenience method, e.g. the delete method for a deletion.

      1. the convenience methods, in addition to undertaking the action, do return an appropriate result:-

        1. the insert method returns the rowid or alias thereof for ROWID tables
        2. the delete and update methods return the number of affected rows (number of rows delete or updated).

      2. i.e. the convenience methods invoke the appropriate SQLite API function AFTER the execution to return the value

    Demo

    The following is a demonstration of:-

    1. a working (at least not failing) version of your DeleteOne method, and
    2. an example of using the execSQL method and then returning a useful result, and
    3. the suggested use of the delete convenience method

    The following is the DatabaseHelper class used for the Demo that is based upon what can be ascertained from your code:-

    class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "the_database";
    public static final int DATABASE_VERSION = 1;

    public static final String CUSTOMER_TABLE = "customer";
    public static final String COLUMN_ID = BaseColumns._ID;
    public static final String COLUMN_WHATEVER = "_whatever";

    private DatabaseHelper(Context context) {
        super(context,DATABASE_NAME,null,DATABASE_VERSION);
    }

    private static volatile  DatabaseHelper instance;
    public static DatabaseHelper getInstance(Context context) {
        if (instance==null) {
            instance = new DatabaseHelper(context);
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(
                "CREATE TABLE IF NOT EXISTS " + CUSTOMER_TABLE +"(" +
                COLUMN_ID + " INTEGER PRIMARY KEY" +
                "," + COLUMN_WHATEVER + " TEXT" +
                ");"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {

    }

    public boolean deleteOne(/*CustomerModel customerModel*/ long id){
        SQLiteDatabase db = this.getWritableDatabase();
        String queryString = "DELETE FROM " + CUSTOMER_TABLE + " WHERE " + COLUMN_ID + " = " + /*customerModel.getId()*/ + id;
        Cursor cursor = db.rawQuery(queryString, null);
        if(cursor.moveToFirst()){
            return true;
        }else {
            return false;
        }
    }

    /* Using EXECSQL to return result */
    public long DeleteByExecSQL(long id) {
        SQLiteDatabase db = this.getWritableDatabase();
        long beforeDeletionCount, afterDeletionCount;

        db.beginTransaction();
        beforeDeletionCount = getTableRowCount(CUSTOMER_TABLE,db);
        db.execSQL("DELETE FROM " + CUSTOMER_TABLE + " WHERE " + COLUMN_ID + "=?",new String[]{String.valueOf(id)});
        afterDeletionCount = getTableRowCount(CUSTOMER_TABLE,db);
        db.setTransactionSuccessful();
        db.endTransaction();
        return beforeDeletionCount - afterDeletionCount;
    }

    /* Using Convenience DELETE */
    public long DeleteViaConvenienceMethod(long id) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(CUSTOMER_TABLE,COLUMN_ID+"=?",new String[]{String.valueOf(id)});
    }

    /* method to get the number of rows in the table */
    public long getTableRowCount(String table, SQLiteDatabase db) {
        long rv=0;
        if (db == null) {
            db = this.getWritableDatabase();
        }
        Cursor csr = db.query(table,new String[]{"count(*)"},null,null,null,null,null);
        if (csr.moveToFirst()) rv = csr.getLong(0);
        csr.close();
        return rv;
    }

    /* Insert using the convenience INSERT method (returns rowid or alias thereof of inserted row) */
    public long insertCustomer(Long id, String whatever) {
        ContentValues cv = new ContentValues();
        if (id!=null) cv.put(COLUMN_ID,id);
        cv.put(COLUMN_WHATEVER,whatever);
        return this.getWritableDatabase().insert(CUSTOMER_TABLE,null,cv);
    }
   /* for generated id */
    public long insertCustomer(String whatever) {
        return insertCustomer(null,whatever);
    }
}
    • A corrected version of your deleteOne method
    • The deleteByExecSQL method, which uses the execSQL method to undertake the deletion but also returns the number of rows that have been deleted by interrogating the number of rows in the table before and after the actual deletion and calculating the difference.
    • The suggested deleteViaConvenienceMethod
    • other methods to support the above.
    • Please refer to the comments.

    To actually demonstrate the 3 approaches, the following Activity code:-

    public class MainActivity extends AppCompatActivity {

    DatabaseHelper dbh;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        dbh = DatabaseHelper.getInstance(this);


        dbh.insertCustomer("C1");
        dbh.insertCustomer("C2");
        dbh.insertCustomer(100L,"C3");
        dbh.insertCustomer("C4");
        dbh.insertCustomer("C5");

        try {
            Log.d("DBINFO_DLT1_1","DELETED=" + dbh.deleteOne(9999));
            Log.d("DBINFO_DLTE_1","DELETED=" + dbh.deleteByExecSQL(9999));
            Log.d("DBINFO_DLTC_1","DELETED=" + dbh.deleteViaConvenienceMethod(9999));

            Log.d("DBINFO_DLT1_2","DELETED="+dbh.deleteOne(100));
            dbh.insertCustomer(100L,"C3");
            Log.d("DBINFO_DLTE_2","DELETED=" + dbh.deleteByExecSQL(100));
            dbh.insertCustomer(100L,"C3");
            Log.d("DBINFO_DLTC_2","DELETED=" + dbh.deleteViaConvenienceMethod(100));
        } catch (SQLiteException e) {
            e.printStackTrace();
        }
    }
}
    • a number of rows are inserted, one being provided a specific id of 100 (the row to be deleted)
    • two sets using each method of deletion (or attempted deletion):
      • The first set tries to delete a non-existent row with an id of 9999
      • The second set tries to delete the existent row with an id of 100
        • after deletion the row is inserted again
    • Logging is wrapped around the deletions so that the results can be extracted from the log
    • the try/catch caters for trapping any errors encountered

    When run then the results are:-

    2023-11-19 12:20:27.995 D/DBINFO_DLT1_1: DELETED=false
2023-11-19 12:20:27.997 D/DBINFO_DLTE_1: DELETED=0
2023-11-19 12:20:27.997 D/DBINFO_DLTC_1: DELETED=0


2023-11-19 12:20:27.998 D/DBINFO_DLT1_2: DELETED=false
2023-11-19 12:20:28.000 D/DBINFO_DLTE_2: DELETED=1
2023-11-19 12:20:28.001 D/DBINFO_DLTC_2: DELETED=1
    • as explained/anticipated the deleteOne method returns false even if the row is deleted (no output so the moveToFirst cannot be made i.e. ALWAYS FALSE)
    • the other two methods return the actual number of rows deleted
    • obviously the code using the convenience delete method is simpler and more efficient (it uses the the appropriate SQLite interface) and does not rescan the table to get the count
      • as per to get the row change counts is to use the sqlite3_changes() or sqlite3_total_changes() interfaces.

    It is suggested that you refer to https://developer.android.com/studio/debug, this would have enabled you to ascertain the initial issue.