android - 无法从 SQLite 数据库中删除一些记录

Question

我的数据库中有 4 个元素（应该是空的），它们是之前添加的，但应用程序已从设备上卸载并重新安装多次。最后我在应用程序中通过 ID 手动删除了它们，它解决了我的问题，但只是暂时的。现在同样的记录又回来了。重新安装后新添加的记录消失了，但那4条仍然存在。

数据库：

public class DBAcceptedTasks {

    private static final String TAG = "ACCEPTED DATABASE";
    private SQLiteDatabase db;
    private Context context;
    private DatabaseHelper dbHelper;

    public static final String DB_TASKS_TABLE = "tasks";
    public static final String DB_NAME = "mytasks.db";
    public static final int DB_VERSION = 1;

    //DATABASE
    public static final String KEY_ID = "_id";
    public static final String ID_OPTIONS = "TEXT NOT NULL PRIMARY KEY";
    public static final int ID_COLUMN = 0;

    public static final String KEY_PATH = "issuePath";
    public static final String ISSUE_OPTIONS = "TEXT NOT NULL";
    public static final int ISSUE_COLUMN = 1;

    public static final String KEY_STREET = "street";
    public static final String STREET_OPTIONS = "TEXT NOT NULL";
    public static final int STREET_COLUMN = 2;

    public static final String KEY_CITY = "city";
    public static final String CITY_OPTIONS = "TEXT NOT NULL";
    public static final int CITY_COLUMN = 3;

    public static final String KEY_DEADLINE_YEAR = "deadlineYear";
    public static final String DEADLINE_YEAR_OPTIONS = "INTEGER NOT NULL";
    public static final int DEADLINE_YEAR_COLUMN = 4;

    public static final String KEY_DEADLINE_MONTH = "deadlineMonth";
    public static final String DEADLINE_MONTH_OPTIONS = "INTEGER NOT NULL";
    public static final int DEADLINE_MONTH_COLUMN = 5;

    public static final String KEY_DEADLINE_DAY = "deadlineDay";
    public static final String DEADLINE_DAY_OPTIONS = "INTEGER NOT NULL";
    public static final int DEADLINE_DAY_COLUMN = 6;

    public static final String KEY_ACCEPT_YEAR = "acceptYear";
    public static final String ACCEPT_YEAR_OPTIONS = "INTEGER NOT NULL";
    public static final int ACCEPT_YEAR_COLUMN = 7;

    public static final String KEY_ACCEPT_MONTH = "acceptMonth";
    public static final String ACCEPT_MONTH_OPTIONS = "INTEGER NOT NULL";
    public static final int ACCEPT_MONTH_COLUMN = 8;

    public static final String KEY_ACCEPT_DAY = "acceptDay";
    public static final String ACCEPT_DAY_OPTIONS = "INTEGER NOT NULL";
    public static final int ACCEPT_DAY_COLUMN = 9;

    public static final String KEY_START_TIME = "startTime";
    public static final String START_TIME_OPTIONS = "TEXT";
    public static final int START_TIME_COLUMN = 10;

    public static final String KEY_END_TIME = "endTime";
    public static final String END_TIME_OPTIONS = "TEXT";
    public static final int END_TIME_COLUMN = 11;

    public static final String KEY_PHONE_NUMBER = "phoneNumber";
    public static final String PHONE_NUMBER_OPTIONS = "TEXT";
    public static final int PHONE_NUMBER_COLUMN = 12;

    public static final String KEY_COMPANY_NAME = "companyName";
    public static final String COMPANY_NAME_OPTIONS = "TEXT";
    public static final int COMPANY_NAME_COLUMN = 13;

    public static final String KEY_START_TIME_MAX = "startTimeMax";
    public static final String START_TIME_MAX_OPTIONS = "TEXT";
    public static final int START_TIME_MAX_COLUMN = 14;



    private static final String DB_CREATE_TASKS_TABLE =
            "CREATE TABLE " + DB_TASKS_TABLE + "( " +
                    KEY_ID + " " + ID_OPTIONS + ", " +
                    KEY_PATH + " " + ISSUE_OPTIONS + ", " +
                    KEY_STREET + " " + STREET_OPTIONS + ", " +
                    KEY_CITY + " " + CITY_OPTIONS + ", " +
                    KEY_DEADLINE_YEAR + " " + DEADLINE_YEAR_OPTIONS + ", " +
                    KEY_DEADLINE_MONTH + " " + DEADLINE_MONTH_OPTIONS + "," +
                    KEY_DEADLINE_DAY + " " + DEADLINE_DAY_OPTIONS + ", " +
                    KEY_ACCEPT_YEAR + " " + ACCEPT_YEAR_OPTIONS + ", " +
                    KEY_ACCEPT_MONTH + " " + ACCEPT_MONTH_OPTIONS + ", " +
                    KEY_ACCEPT_DAY + " " + ACCEPT_DAY_OPTIONS + ", " +
                    KEY_START_TIME + " " + START_TIME_OPTIONS + ", " +
                    KEY_END_TIME + " " + END_TIME_OPTIONS + ", " +
                    KEY_PHONE_NUMBER + " " + PHONE_NUMBER_OPTIONS + ", " +
                    KEY_COMPANY_NAME + " " + COMPANY_NAME_OPTIONS + ", " +
                    KEY_START_TIME_MAX + " " + START_TIME_MAX_OPTIONS + ");";


    private static final String DROP_TASKS_TABLE =
            "DROP TABLE IF EXISTS " + DB_TASKS_TABLE;



    private static class DatabaseHelper extends SQLiteOpenHelper {

        public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            Log.e(TAG, "Tworzenie bazy DBAcceptedTasks... ... ... ... ... ...");
            db.execSQL(DB_CREATE_TASKS_TABLE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {


            Log.e(TAG, "Upgrade bazy DBAcceptedTasks.... .... .... .... .... ....");
            db.execSQL(DROP_TASKS_TABLE);   // TODO zrobić przepisanie (może do tablicy i z powrotem

            onCreate(db);

        }
    }

    public void deleteTasks(){
        Log.e(TAG, "Usuwanie bazy DBAcceptedTasks.... .... .... .... .... ....");
        db.execSQL(DROP_TASKS_TABLE);
    }

    public DBAcceptedTasks(Context context) {
        this.context = context;
    }

    public DBAcceptedTasks open() {
        dbHelper = new DatabaseHelper(context, DB_NAME, null, DB_VERSION);
        try {
            db = dbHelper.getWritableDatabase();
        } catch (SQLiteException e) {
            db = dbHelper.getReadableDatabase();
            Log.e(DB_NAME, e.getMessage());
        }
        return this;
    }

    public void close() {
        dbHelper.close();
    }

    public void insertIntoDB(String id, String path, String street, String city, String phoneNumber, String companyName, int dDay, int dMonth,
                             int dYear, int aDay, int aMonth, int aYear, String startTime, String endTime, String startTimeMax) {// zrobię własną klasę obsługującą datę z intów, dodatkowo metody equals/before/after i liczące odlagłość między datami

        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_ID, id);
        if (path != null) contentValues.put(KEY_PATH, path);
        if (street != null) contentValues.put(KEY_STREET, street);
        if (city != null) contentValues.put(KEY_CITY, city);
        contentValues.put(KEY_DEADLINE_YEAR, dYear);
        contentValues.put(KEY_DEADLINE_MONTH, dMonth);
        contentValues.put(KEY_DEADLINE_DAY, dDay);
        contentValues.put(KEY_ACCEPT_YEAR, aYear);
        contentValues.put(KEY_ACCEPT_MONTH, aMonth);
        contentValues.put(KEY_ACCEPT_DAY, aDay);
        if (startTime != null) contentValues.put(KEY_START_TIME, startTime);
        if (endTime != null) contentValues.put(KEY_END_TIME, endTime);
        contentValues.put(KEY_PHONE_NUMBER, phoneNumber);
        contentValues.put(KEY_COMPANY_NAME, companyName);
        if (startTimeMax != null) contentValues.put(KEY_START_TIME_MAX, startTimeMax);


        db.insertWithOnConflict(DB_TASKS_TABLE, null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);  //można wrócić do zwykłego inserta w finalnej wersji apki, bo nie będzie zdublowanych rekordów...   aaalbo jebac
    }

    public ArrayList<IssuePOJO> getMyTasks(){
        ArrayList<IssuePOJO> myList = new ArrayList<>();
        String query = "SELECT * FROM " + DB_TASKS_TABLE;
        Cursor c;
        c = db.rawQuery(query, null);
        if(c.moveToFirst()){
            do{
                IssuePOJO task = new IssuePOJO();

                task.setId(c.getString(ID_COLUMN));
                task.setPath(c.getString(ISSUE_COLUMN));
                task.setStreet(c.getString(STREET_COLUMN));
                task.setCity(c.getString(CITY_COLUMN));
                task.setDeadlineYear(c.getInt(DEADLINE_YEAR_COLUMN));
                task.setDeadlineMonth(c.getInt(DEADLINE_MONTH_COLUMN));
                task.setDeadlineDay(c.getInt(DEADLINE_DAY_COLUMN));
                task.setAcceptYear(c.getInt(ACCEPT_YEAR_COLUMN));
                task.setAcceptMonth(c.getInt(ACCEPT_MONTH_COLUMN));
                task.setAcceptDay(c.getInt(ACCEPT_DAY_COLUMN));
                task.setStartTime(c.getString(START_TIME_COLUMN));
                task.setEndTime(c.getString(END_TIME_COLUMN));
                task.setPhoneNumber(c.getString(PHONE_NUMBER_COLUMN));
                task.setCompanyName(c.getString(COMPANY_NAME_COLUMN));
                task.setStartTimeMax(c.getString(START_TIME_MAX_COLUMN));

                task.setAcceptDate(DateParser.parse(context, task.getAcceptDay(), task.getAcceptMonth(), task.getAcceptYear(), "-", true, DateParser.DMY));
                task.setEndDate(DateParser.parse(context, task.getDeadlineDay(), task.getDeadlineMonth(), task.getDeadlineYear(), "-", true, DateParser.DMY));


                myList.add(task);

            }while(c.moveToNext());
        }
        return myList;
    }

    // PATH
    public String getIssueDB(String id) {   //TODO WSZYSTKIE SETTERY POPSUTE - DODAĆ ID DO PARAMETRU !!! !!! !!!
        String aIssue = null;
        String select = "SELECT " + KEY_PATH + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) aIssue = cursor.getString(0);
        cursor.close();
        return aIssue;
    }

    // ACCEPT DATE

    public int getAcceptYearDB(String id) {
        int aYear = -1;
        String select = "SELECT " + KEY_ACCEPT_YEAR + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) aYear = cursor.getInt(0);
        cursor.close();
        return aYear;
    }


    public int getAcceptMonthDB(String id) {
        int aMonth = -1;
        String select = "SELECT " + KEY_ACCEPT_MONTH + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) aMonth = cursor.getInt(0);
        cursor.close();
        return aMonth;
    }

    public int getAcceptDayDB(String id) {
        int aDay = -1;
        String select = "SELECT " + KEY_ACCEPT_DAY + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + id;
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) aDay = cursor.getInt(0);
        cursor.close();
        return aDay;
    }

    public boolean setAcceptYearDB(String id, int year) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_ACCEPT_YEAR, year);
        String where = KEY_ID + "=" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    public boolean setAcceptMonthDB(String id, int month) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_ACCEPT_MONTH, month);
        String where = KEY_ID + "=" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    public boolean setAcceptDayDB(String id, int day) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_ACCEPT_DAY, day);
        String where = KEY_ID + "=" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    public boolean setAcceptDateDB(String id, int day, int month, int year) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_ACCEPT_YEAR, year);
        contentValues.put(KEY_ACCEPT_MONTH, month);
        contentValues.put(KEY_ACCEPT_DAY, day);
        String where = KEY_ID + "=" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    public String getAcceptDate(String id) {
        int iDay = -1;
        int iMonth = -1;
        int iYear = -1;
        String select = "SELECT " + KEY_ACCEPT_DAY + ",\n" + KEY_ACCEPT_MONTH + ",\n" + KEY_ACCEPT_YEAR + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);

        if(cursor.moveToFirst()) {
            iDay = cursor.getInt(0);
            iMonth = cursor.getInt(1);
            iYear = cursor.getInt(2);
        }

        cursor.close();
        return DateParser.parse(context, iDay, iMonth, iYear, "-", true, DateParser.DMY);

    }

    // DEADLINE DATE

    public int getDeadlineYearDB(String id) {
        int dYear = -1;
        String select = "SELECT " + KEY_DEADLINE_YEAR + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) dYear = cursor.getInt(0);
        cursor.close();
        return dYear;
    }

    public int getDeadlineMonthDB(String id) {
        int dMonth = -1;
        String select = "SELECT " + KEY_DEADLINE_MONTH + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) dMonth = cursor.getInt(0);
        cursor.close();
        return dMonth;
    }

    public int getDeadlineDayDB(String id) {
        int dDay = -1;
        String select = "SELECT " + KEY_DEADLINE_DAY + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) dDay = cursor.getInt(0);
        cursor.close();
        return dDay;
    }

    public boolean setDeadlineYearDB(String id, int year) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_DEADLINE_YEAR, year);
        String where = KEY_ID + "=" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    public boolean setDeadlineMonthDB(String id, int month) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_DEADLINE_MONTH, month);
        String where = KEY_ID + "-" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    public boolean setDeadlineDayDB(String id, int day) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_DEADLINE_DAY, day);
        String where = KEY_ID + "=" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    public boolean setDeadlineDateDB(String id, int day, int month, int year) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_DEADLINE_YEAR, year);
        contentValues.put(KEY_DEADLINE_MONTH, month);
        contentValues.put(KEY_DEADLINE_DAY, day);
        String where = KEY_ID + "=" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    public String getDeadlineDate(String id) {
        int iDay = -1;
        int iMonth = -1;
        int iYear = -1;

        String select = "SELECT " + KEY_DEADLINE_DAY + "," + KEY_DEADLINE_MONTH + "," + KEY_DEADLINE_YEAR + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) {
            iDay = cursor.getInt(0);
            iMonth = cursor.getInt(1);
            iYear = cursor.getInt(2);
        }


        cursor.close();
        return DateParser.parse(context, iDay, iMonth, iYear, "-", true, DateParser.DMY);
    }

    public boolean setIdDB(String prevID, String ID) {  //TODO
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_ID, ID);
        String where = KEY_ID + "=" + '"' + prevID + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    // ADRESS

    public boolean setAdress(String id, String street, String city) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_STREET, street);
        contentValues.put(KEY_CITY, city);
        String where = KEY_ID + "=" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    //STREET
    public String getStreetDB(String id) {
        String street = null;
        String select = "SELECT " + KEY_STREET + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) street = cursor.getString(0);
        cursor.close();
        return street;
    }

    public boolean setStreetDB(String id, String street) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_STREET, street);
        String where = KEY_ID + "=" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    public String getStreetNumber(String street) {
        return null;
    }

// CITY

    public String getCityDB(String id) {
        String city = null;
        String select = "SELECT " + KEY_CITY + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) city = cursor.getString(0);
        cursor.close();
        return city;
    }

    public boolean setCityDB(String id, String city) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_CITY, city);
        String where = KEY_ID + "=" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    public String getPostalCode(String city) {
        return null;
    }

    // ELEM DELETE

    public boolean deleteTask(String id){
        String where = KEY_ID + "=" + '"' + id + '"';
        return db.delete(DB_TASKS_TABLE, where, null) > 0;
    }

    public boolean updateTask(String id, String street, String city, String phoneNumber, String companyName, int dDay, int dMonth, int dYear, int aDay, int aMonth,
                              int aYear, String startTime, String endTime, String startTimeMax){

        ContentValues contentValues = new ContentValues();
        String where = KEY_ID + "=" + '"' + id + '"';

        if (street != null) contentValues.put(KEY_STREET, street);
        if (city != null) contentValues.put(KEY_CITY, city);
        if (dDay > 0 && dDay <= 31) contentValues.put(KEY_DEADLINE_DAY, dDay);
        if (dMonth > 0 && dMonth <= 12) contentValues.put(KEY_DEADLINE_MONTH, dMonth);
        if (dYear > 2015 && dYear < 2500) contentValues.put(KEY_DEADLINE_YEAR, dYear);
        if (aDay > 0 && aDay <= 31) contentValues.put(KEY_ACCEPT_DAY, aDay);
        if (aMonth > 0 && aMonth <= 12) contentValues.put(KEY_ACCEPT_MONTH, aMonth);
        if (aYear > 2015 && aYear < 2500) contentValues.put(KEY_ACCEPT_YEAR, aYear);
        if (startTime != null) contentValues.put(KEY_START_TIME, startTime);
        if (endTime != null) contentValues.put(KEY_END_TIME, endTime);
        if (phoneNumber != null) contentValues.put(KEY_PHONE_NUMBER, phoneNumber);
        if (companyName != null) contentValues.put(KEY_COMPANY_NAME, companyName);
        if (startTimeMax != null) contentValues.put(KEY_START_TIME_MAX, startTimeMax);


        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    // TIME

    public String getStartTimeDB(String id) {
        String startT = null;
        String select = "SELECT " + KEY_START_TIME + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) startT = cursor.getString(0);
        cursor.close();
        return startT;
    }

    public String getEndTimeDB(String id) {
        String endT = null;
        String select = "SELECT " + KEY_END_TIME + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"' ;
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) endT = cursor.getString(0);
        cursor.close();
        return endT;
    }

    public String getStartTimeMaxDB(String id) {
        String startTM = null;
        String select = "SELECT " + KEY_START_TIME_MAX + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) startTM = cursor.getString(0);
        cursor.close();
        return startTM;
    }

    // PHONE NUMB

    public boolean setPhoneNumber(String id, String phoneNumber){
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_PHONE_NUMBER, phoneNumber);
        String where = KEY_ID + "=" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    public String getPhoneNumber(String id){
        String phoneNumber = null;
        String select = "SELECT " + KEY_PHONE_NUMBER + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) phoneNumber = cursor.getString(0);
        cursor.close();
        return phoneNumber;
    }

    // COMP NAME

    public boolean setCompanyName(String id, String companyName){
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_COMPANY_NAME, companyName);
        String where = KEY_ID + "=" + '"' + id + '"';

        return db.update(DB_TASKS_TABLE, contentValues, where, null) != -1;
    }

    public String getCompanyName(String id){
        String companyName = null;
        String select = "SELECT " + KEY_COMPANY_NAME + " FROM " + DB_TASKS_TABLE + " WHERE " + KEY_ID + "=" + '"' + id + '"';
        Cursor cursor = db.rawQuery(select, null);
        if(cursor.moveToFirst()) companyName = cursor.getString(0);
        cursor.close();
        return companyName;
    }

}

标签： androidsqlitememory