Обновление SQLite таблицы без потери предыдущих данных в андроиде

Обновить

January 2019

Просмотры

611 раз

1

Я пытаюсь добавить новый столбец в старой SQLite таблиц базы данных. Таблицы являются динамическими. Я имею в виду, они создаются, когда пользователи приложения добавить конкретные данные в приложении.

Имена таблиц имеют сходный суффикс. Префикс зависит от пользовательского ввода. Теперь, в андроиде, как я могу добавить новый столбец в этих таблицах без потери предыдущих данных?

2 ответы

0

Try This Logic

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

        int upgradeTo = oldVersion + 1;
        while (upgradeTo <= newVersion)
        {
            switch (upgradeTo)
            {
                case 2:
                    db.execSQL("ALTER TABLE " + TABLE_REGISTER +"  ADD COLUMN user_mobile TEXT");
                    break;
                case 3:
                    db.execSQL("ALTER TABLE " + TABLE_REGISTER +"  ADD COLUMN user_mobile_new TEXT");
                    break;

            }
            upgradeTo++;
        }
    }

Every time you add new column or new table just add another case and increase db version. Your last case would be your database version. In my case dbVersion = 3

3

I hope you already know how the onUpgrade method works. The implementation of onUpgrade is pretty simple, which detects if the database version is changed and take the changes in effect for the newer version of database. Here's a sample implementation.

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

    // The last case will contain the break statement only. As the migration will take place one by one.
    // Here's a nice explanation - http://stackoverflow.com/a/26916986/3145960
    switch (oldVersion) {
        case 1:
            doSomeChangesInDBForVersion1();
        case 2:
            doSomeChangesInDBForVersion2();
            break;
    }
}

Now let us come to the specific problem that you asked. As far as I could understand, the tables in your database are dynamic which are based on user input. You want to add columns in your database table which are created based on user inputs.

In that case, you need to find out first the existing tables and then run the alter command on each of your tables. To find the existing tables in your database you might do something like the following.

Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
List<String> tableList = new ArrayList<>();

if (c.moveToFirst()) {
    while (!c.isAfterLast()) {
        tableList.add(c.getString(0));
        c.moveToNext();
    }
}

Now store the table names in an array and then run alter command on each of your table to add a new column in each of your tables.

for (String tableName : tableList) {
    db.execSQL("alter table " + tableName +"  add column new_col TEXT default null");
}

Run these executions in your onUpgrade function. I have not tested this code. Please modify as per your requirement.