将每个版本的升级语句按版本顺序放在一个LIST中,这样在数据库版本升级的时候就不用做版本号判断了。List中的INDEX就是每个版本的SQL语句。这里只是做了一个简单的DEMO,没有考虑数据迁移。
如果是第一次安装,会调用onCreate,在onCreate里直接调用execUpgradSql(db, 0, 3)执行所有的SQL语句。
如果是从其它版本升级来的,比如从V2升到V3,则会调用onUpgrade,oldVersion是2,newVersion是3,这里调用execUpgradeSql(db, 2, 3),它只会执行V3新增的SQL语句。V1升到V3也是如此,它执行的是V2,V3的SQL。
以后每升级数据库,只要把这个版本对应的新增SQL放在LIST中就可以了。
package com.gouhao.databaseupdate;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import java.util.ArrayList;
import java.util.List;
/**
* Created by GouHao on 2017/9/16.
*/
public class DatabaseHelper extends SQLiteOpenHelper {
private static final String TAG = DatabaseHelper.class.getSimpleName();
public static final String DB_NAME = "test.db";
public static final String TABLE_USER_OLD = "user";
public static final String TABLE_USER = "table_user";
public static final String TABLE_STUDENT = "table_student";
public static final int VERSION_CODE = 3;
private List<List<String>> upgradeSqlList;
public DatabaseHelper(Context context) {
super(context, DB_NAME, null, VERSION_CODE);
upgradeSqlList = new ArrayList<>();
addUpgradeSqlVersion1();
addUpgradeSqlVersion2();
addUpgradeSqlVersion3();
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.d(TAG, "onCreate");
execUpgradeSql(db, 0, VERSION_CODE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.d(TAG, "onUpgrade: oldVersion=" + oldVersion + ", newVersion=" + newVersion);
execUpgradeSql(db, oldVersion, newVersion);
}
private void addUpgradeSqlVersion1() {
List<String> sqlListVersion1 = new ArrayList<>();
String userSql = "CREATE TABLE IF NOT EXISTS " + TABLE_USER_OLD + "(id INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR);";
sqlListVersion1.add(userSql);
upgradeSqlList.add(sqlListVersion1);
}
private void addUpgradeSqlVersion2() {
List<String> sqlListVersion2 = new ArrayList<>();
String userAlter = "ALTER TABLE " + TABLE_USER_OLD + " ADD COLUMN age INTEGER;";
sqlListVersion2.add(userAlter);
upgradeSqlList.add(sqlListVersion2);
}
private void addUpgradeSqlVersion3() {
List<String> sqlListVersion3 = new ArrayList<>();
String alterUserTableName = "ALTER TABLE user RENAME TO " + TABLE_USER + ";";
String tableStudent = "CREATE TABLE IF NOT EXISTS " + TABLE_STUDENT + "(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR);";
sqlListVersion3.add(alterUserTableName);
sqlListVersion3.add(tableStudent);
upgradeSqlList.add(sqlListVersion3);
}
private void execUpgradeSql(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.d(TAG, "execUpgradeSql: oldVersion=" + oldVersion + ", newVersion=" + newVersion);
for (int i = oldVersion; i < newVersion; i++) {
List<String> sqlListVersion = upgradeSqlList.get(i);
int size = sqlListVersion.size();
for (int j = 0; j < size; j++) {
try {
db.execSQL(sqlListVersion.get(j));
Log.d(TAG, "execUpgradeSql: version" + (i + 1) + " exec success:" + sqlListVersion.get(j));
} catch (Exception e) {
e.printStackTrace();
Log.e(TAG, "execUpgradeSql: version" + (i + 1) + " exec failed: " + sqlListVersion.get(j));
}
}
Log.d(TAG, "execUpgradeSql: version" + (i + 1) + " exec success");
}
}
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.d(TAG, "onDowngrade: oldVersion: " + oldVersion + ", newVersion=" + newVersion);
}
}