如何使用SQLiteOpenHelper类从sqlite数据库中删除项目 [英] How to delete items from sqlite database with SQLiteOpenHelper class

问题描述

我无法使用SQLiteOpenHelper类从sqlite删除项目，但可以在其中插入数据

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

这是我的助手课

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

}

我尝试删除时没有错误.addException()方法工作正常，但deleteException()和clearException()无法正常工作.我的意思是没有删除，也没有错误.

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:

这是一项:从user_exceptions中删除，其中username ='some user name'

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

推荐答案

好，我发现问题所在是我将db.endTransaction放入

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

finally {}

在try块中将其移动并添加此行之后

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

这篇关于如何使用SQLiteOpenHelper类从sqlite数据库中删除项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！