背景
最近在bugly上看到少量的duplicate column 類似報(bào)錯(cuò)。準(zhǔn)備看下數(shù)據(jù)庫相關(guān)的代碼埋哟。咋一看沒什么問題郎汪，但仔細(xì)看數(shù)據(jù)庫升級(jí)的代碼和提交日志發(fā)現(xiàn)了一個(gè)非常容易犯的錯(cuò)誤闯狱。什么錯(cuò)誤？報(bào)錯(cuò)很明了照筑，就是某個(gè)數(shù)據(jù)庫中的列重復(fù)了瘦陈。
場(chǎng)景復(fù)現(xiàn)
1，假設(shè)數(shù)據(jù)在version1的時(shí)候創(chuàng)建了一個(gè)表person：
public class DataBaseOpenHelper extends SQLiteOpenHelper {
private final static String DATABASE_NAME = "test.db";
private static DataBaseOpenHelper mDataBaseOpenHelper;
//version1
public static final String CREATE_PERSON =
"create table person(personid integer primary key autoincrement,name " +
"varchar" +
"(20)," +
"phone VARCHAR(12))";
public DataBaseOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
int version) {
super(context, name, factory, version);
}
static synchronized DataBaseOpenHelper getDBInstance(Context context) {
if (mDataBaseOpenHelper == null) {
mDataBaseOpenHelper = new DataBaseOpenHelper(context, DATABASE_NAME, null, 1);
}
return mDataBaseOpenHelper;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_PERSON);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
2蛾默，升級(jí)到version2的時(shí)候又創(chuàng)建一個(gè)表student：
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DataBaseOpenHelper extends SQLiteOpenHelper {
private final static String DATABASE_NAME = "test.db";
private static DataBaseOpenHelper mDataBaseOpenHelper;
//version1
public static final String CREATE_PERSON =
"create table person(personid integer primary key autoincrement,name " +
"varchar" +
"(20)," +
"phone VARCHAR(12))";
public static final String CREATE_STUDENT =
"create table student(studentid integer primary key autoincrement,name varchar(20)," +
"phone VARCHAR(12))";
public DataBaseOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
int version) {
super(context, name, factory, version);
}
static synchronized DataBaseOpenHelper getDBInstance(Context context) {
if (mDataBaseOpenHelper == null) {
mDataBaseOpenHelper = new DataBaseOpenHelper(context, DATABASE_NAME, null, 2);
}
return mDataBaseOpenHelper;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_PERSON);
//version2
db.execSQL(CREATE_STUDENT);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//數(shù)據(jù)庫升級(jí)邏輯捉貌，記得要break
for (int i = oldVersion; i < newVersion; i++) {
switch (i) {
case 1:
db.execSQL(CREATE_STUDENT);
break;
default:
break;
}
}
}
}
3，升級(jí)到version3的時(shí)候苍匆，我們需要在student表中加一個(gè)新字段genderid integer棚菊。注意，version3如何做统求？
3.1 錯(cuò)誤做法： 直接在CREATE_STUDENT 語句中添加genderid integer，然后在數(shù)據(jù)庫升級(jí)中處理升級(jí)邏輯码邻，代碼：
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DataBaseOpenHelper extends SQLiteOpenHelper {
private final static String DATABASE_NAME = "test.db";
private static DataBaseOpenHelper mDataBaseOpenHelper;
//version1
public static final String CREATE_PERSON =
"create table person(personid integer primary key autoincrement,name " +
"varchar" +
"(20)," +
"phone VARCHAR(12))";
public static final String CREATE_STUDENT =
"create table student(studentid integer primary key autoincrement,name varchar(20)," +
"phone VARCHAR(12),genderid integer)";
//version3
public static final String ALTER_STUDENT = "alter table student add column genderid integer";
public DataBaseOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
int version) {
super(context, name, factory, version);
}
static synchronized DataBaseOpenHelper getDBInstance(Context context) {
if (mDataBaseOpenHelper == null) {
mDataBaseOpenHelper = new DataBaseOpenHelper(context, DATABASE_NAME, null, 3);
}
return mDataBaseOpenHelper;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_PERSON);
db.execSQL(CREATE_STUDENT);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
for (int i = oldVersion; i < newVersion; i++) {
switch (i) {
case 1:
db.execSQL(CREATE_STUDENT);
break;
case 2:
//version3
db.execSQL(ALTER_STUDENT);
break;
default:
break;
}
}
}
}
至此像屋，一切都非常完美。version2升級(jí)到version3奏甫，走的是onUpgrade邏輯凌受，執(zhí)行alert語句直接插入genderid列阵子；直接安裝version3走的是onCreate邏輯胜蛉，直接創(chuàng)建新表student肯定包含genderid色乾。
但當(dāng)從version1直接升級(jí)到version3的時(shí)候就會(huì)報(bào)錯(cuò)Ａ焱弧！攘须！ 根據(jù)逐級(jí)升級(jí)邏輯殴泰，首先走的是case1，執(zhí)行語句CREATE_STUDENT（注意捞魁，此時(shí)的語句已經(jīng)不是version2時(shí)候的原始語句离咐，而是version3改動(dòng)后加了genderid列的語句）谱俭，因此宵蛀，當(dāng)逐級(jí)執(zhí)行到case2時(shí)，再新增genderid列時(shí)候就報(bào)錯(cuò)凑懂。
正確的做法：當(dāng)數(shù)據(jù)庫升級(jí)過程中向原有數(shù)據(jù)庫新增字段時(shí)候梧宫，不要直接改動(dòng)原來創(chuàng)建表的sql語句，而是另外寫包含新字段的語句
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DataBaseOpenHelper extends SQLiteOpenHelper {
private final static String DATABASE_NAME = "test.db";
private static DataBaseOpenHelper mDataBaseOpenHelper;
//version1
public static final String CREATE_PERSON =
"create table person(personid integer primary key autoincrement,name " +
"varchar" +
"(20)," +
"phone VARCHAR(12))";
public static final String CREATE_STUDENT =
"create table student(studentid integer primary key autoincrement,name varchar(20)," +
"phone VARCHAR(12))";
//注意這里
public static final String CREATE_STUDENT_NEW =
"create table student(studentid integer primary key autoincrement,name varchar(20)," +
"phone VARCHAR(12),genderid integer)";
//version3
public static final String ALTER_STUDENT = "alter table student add column genderid integer";
public DataBaseOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
int version) {
super(context, name, factory, version);
}
static synchronized DataBaseOpenHelper getDBInstance(Context context) {
if (mDataBaseOpenHelper == null) {
mDataBaseOpenHelper = new DataBaseOpenHelper(context, DATABASE_NAME, null, 3);
}
return mDataBaseOpenHelper;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_PERSON);
// db.execSQL(CREATE_STUDENT); ///注意這里
db.execSQL(CREATE_STUDENT_NEW); ///注意這里
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
for (int i = oldVersion; i < newVersion; i++) {
switch (i) {
case 1:
db.execSQL(CREATE_STUDENT);
break;
case 2:
//version3
db.execSQL(ALTER_STUDENT);
break;
default:
break;
}
}
}
}
這樣不管是從哪個(gè)舊版本升級(jí)還是直接安裝最新版本都能不會(huì)報(bào)錯(cuò)了。ok扫夜，細(xì)節(jié)很重要驰徊。
