How to delete items from sqlite database with SQLiteOpenHelper class

Question

I cant delete items from sqlite with SQLiteOpenHelper class but I can insert data in that

here is my helper class

public class SQLiteHandler extends SQLiteOpenHelper {

    private static SQLiteHandler sInstance;
    private static final String DATABASE_NAME = "telestaDB";
    private static final int DATABASE_VERSION = 2;
    private static final String TAG = "SqliteHelper";

    private SQLiteHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        getWritableDatabase();
        getReadableDatabase();
        Log.i(TAG, "Constractor create!!");
    }

    public static SQLiteHandler getInstance(Context context) {

        if (sInstance == null) {
            Log.i(TAG, "getInstance: new instance created!!");
            sInstance = new SQLiteHandler(context.getApplicationContext());
        }
        return sInstance;
    }

    public void addException(ExceptionsModel model) {
        List<ExceptionsModel> exceptions = getAllExceptions();
        List<String> exception = new ArrayList<>();
        for (int i = 0; i < exceptions.size(); i++) {
            exception.add(exceptions.get(i).getUserName());
        }
        if (!exception.contains(model.getUserName())) {
            SQLiteDatabase db = getWritableDatabase();
            db.beginTransaction();
            try {
                ContentValues values = new ContentValues();
//            values.put(ExceptionsModel.COLUMN_ID, model.getId());
                values.put(ExceptionsModel.USER_NAME, model.getUserName());
                db.insert(ExceptionsModel.TABLE_NAME, null, values);
                db.setTransactionSuccessful();
                db.endTransaction();
            } catch (Exception e) {
                Log.d(TAG, "Error while trying to add user to database" + e.toString());
            }
        } else {
            Log.i(TAG, "this user is exist!!");
        }
    }

    public void clearExceptionRecords() {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        try {
            db.execSQL("DELETE FROM user_exceptions");
        } catch (Exception e) {
            Log.d(TAG, "Error while trying to delete user from database" + e.toString());
        } finally {
            Log.i(TAG, "clearExceptionTable: db");
            db.endTransaction();
        }
    }

    public void deleteException(String model) {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        try {
            Log.d(TAG, "We Are Trying to Delete Item From DataBase!!");
            Log.d(TAG, "this is an item: " + model);
            Log.d(TAG, "this is an item: " + "delete from user_exceptions where username = '" + model + "'");
//            db.execSQL("delete from user_exceptions where id=1");
            db.delete(ExceptionsModel.TABLE_NAME, ExceptionsModel.USER_NAME + "=?", new String[]{model});
        } catch (Exception e) {
            Log.d(TAG, "Error while trying to delete user from database" + e.toString());
        } finally {
            db.endTransaction();
        }
        List<ExceptionsModel> exceptionsModels = getAllExceptions();
        Log.d(TAG, "Exceptions Size Is Like Below: " + exceptionsModels.size());
    }

    public List<ExceptionsModel> getAllExceptions() {

        List<ExceptionsModel> exceptions = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(ExceptionsModel.SELECT, null);
        try {
            while (cursor.moveToNext()) {
                ExceptionsModel model = new ExceptionsModel();
                 model.setId(cursor.getInt(cursor.getColumnIndex(ExceptionsModel.COLUMN_ID)));
                model.setUserName(cursor.getString(cursor.getColumnIndex(ExceptionsModel.USER_NAME)));
                exceptions.add(model);
            }
        } catch (Exception e) {
            Log.d(TAG, "Error while trying to get exceptions from database" + e.toString());
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                cursor.close();
            }
        }
        return exceptions;
    }

}

I have no error when I'm trying to delete. addException() method works fine but deleteException() and clearException() is not working. I mean no deleting and no error.

Here is my query result I have tried without single quote before, same result:

this is an item: delete from user_exceptions where username = 'some user name'

Answer 1

ok i found the problem problem is that i put db.endTransaction in

finally {}

after i move it in try block and add this line

db.setTransactionSuccessful();

its works fine my code is like below

public void deleteException(String model) {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        try {
            Log.d(TAG, "We Are Trying to Delete Item From DataBase!!");
            Log.d(TAG, "this is an item: " + model);
            Log.d(TAG, "this is an item: " + "delete from user_exceptions where username = '" + model + "'");
            db.execSQL("DELETE FROM " + ExceptionsModel.TABLE_NAME + " WHERE " + ExceptionsModel.USER_NAME + "='" + model + "'");

//            db.delete(ExceptionsModel.TABLE_NAME, ExceptionsModel.USER_NAME + "=?", new String[]{model});
            db.setTransactionSuccessful();
            db.endTransaction();
        } catch (Exception e) {
            Log.d(TAG, "Error while trying to delete user from database" + e.toString());
        }
    }

Answer 2

Try this query this one is simple and works on me well.

public void clearExceptionRecords(String value)
{
SQLiteDatabase db = this.getWritableDatabase();       
db.execSQL("DELETE FROM " + user_exceptions+ " WHERE "+username+"='"+value+"'");
db.close();
}