版本迭代時(shí)難免需要對(duì)數(shù)據(jù)庫字段進(jìn)行增加副渴，刪除與修改操作奈附。這時(shí)便需要對(duì)原有的數(shù)據(jù)進(jìn)行遷移，以保證不丟失數(shù)據(jù)煮剧。對(duì)數(shù)據(jù)庫的遷移我們需要做以下幾個(gè)步驟：
- ① 創(chuàng)建臨時(shí)表斥滤，將原來的數(shù)據(jù)復(fù)制到臨時(shí)表中。
- ② 刪除原表勉盅，原表數(shù)據(jù)已經(jīng)備份至臨時(shí)表中佑颇，不再需要，進(jìn)行刪除草娜。
- ③ 創(chuàng)建新表挑胸，調(diào)用創(chuàng)建新表的方法，創(chuàng)建所有的新表宰闰。
- ④ 恢復(fù)原表的數(shù)據(jù)茬贵，將臨時(shí)表中的數(shù)據(jù)恢復(fù)至新表中。
- ⑤ 刪除臨時(shí)表议蟆，完成所有的步驟闷沥。
Show Code：
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import java.util.ArrayList;
import java.util.List;
/**
* Created by CY on 2016/10/24.
* Email: tmxdyf@163.com
*/
public class UpgradeHelper {
/**
* 升級(jí)入口
*
* @param db db
* @param openHelper 重新建表時(shí)需要用上
*/
public void upgrade(SQLiteDatabase db, SQLiteOpenHelper openHelper) {
List<String> tables = queryAllTables(db);
for (String tableName : tables) {
if ("android_metadata".equals(tableName)||"sqlite_sequence".equals(tableName)) {//表的元數(shù)據(jù)，過濾
continue;
}
String tempTableName = tableName + "_Temp";
createTempTables(db, tableName, tempTableName);//創(chuàng)建臨時(shí)表
dropTable(db, tableName);//刪除原表
openHelper.onCreate(db);//創(chuàng)建新表
restoreData(db, tableName, tempTableName);//恢復(fù)原表數(shù)據(jù)
dropTable(db, tempTableName);//刪除臨時(shí)表
}
}
/**
* 創(chuàng)建零時(shí)表
*/
private void createTempTables(SQLiteDatabase db, String tableName, String tempTableName) {
copyTable(db, tableName, tempTableName);
}
/**
* 刪除所有表
*
* @param db
*/
private void dropTable(SQLiteDatabase db, String tableName) {
String sql = "DROP TABLE IF EXISTS " + tableName;
db.execSQL(sql);
}
/**
* 查詢數(shù)據(jù)庫中所有表名
*
* @param db
* @return
*/
private List<String> queryAllTables(SQLiteDatabase db) {
List<String> list = new ArrayList<>();
String sql = "SELECT name FROM SQLITE_MASTER WHERE type='table' ORDER BY name";
Cursor cursor = db.rawQuery(sql, null);
while (cursor.moveToNext()) {
list.add(cursor.getString(0));
}
cursor.close();
return list;
}
/**
* 復(fù)制表及內(nèi)容
*
* @param db
* @param oldTableName
* @param newTableName
*/
private void copyTable(SQLiteDatabase db, String oldTableName, String newTableName) {
String sql = "CREATE TABLE IF NOT EXISTS " + newTableName + " AS SELECT * FROM " + oldTableName;
db.execSQL(sql);
}
/**
* 從臨時(shí)表中恢復(fù)數(shù)據(jù)
*
* @param db
* @param tableName 需要恢復(fù)的表
* @param tableNameTemp 臨時(shí)表
*/
private void restoreData(SQLiteDatabase db, String tableName, String tableNameTemp) {
String columns = TextUtils.join(",", queryColumns(db, tableNameTemp));
String sql = "INSERT INTO " + tableName + "(" + columns + ") SELECT " + columns + " FROM " + tableNameTemp;
db.execSQL(sql);
}
/**
* 獲取表中所有字段名
*
* @return
*/
private String[] queryColumns(SQLiteDatabase db, String tableName) {
String sql = "SELECT * FROM " + tableName;
Cursor cursor = db.rawQuery(sql, null);
String[] columnNames = cursor.getColumnNames();
cursor.close();
return columnNames;
}
}
如何使用咐容？如以下示例舆逃。
- E.g： GreenDao
public class MergeDevOpenHelper extends DaoMaster.DevOpenHelper {
public MergeDevOpenHelper(Context context, String name) {
super(context, name);
}
public MergeDevOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory) {
super(context, name, factory);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (newVersion > oldVersion) {
new UpgradeHelper().upgrade(db, this);
}
}
}
}
DaoMaster.DevOpenHelper devOpenHelper = new MergeDevOpenHelper(mContext, "data.db");
SQLiteDatabase db = devOpenHelper.getWritableDatabase();
DaoMaster daoMaster = new DaoMaster(db);
這是我在簡書的第一篇文章，希望能幫助到大家戳粒，同時(shí)也給我自己加深記憶路狮。謝謝。_
