Android数据库升级解决方案
一、SQLiteOpenHelper介绍
SQLiteOpenHelper有两个方法, public void onCreate(SQLiteDatabase db)以及public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)。第一次安装App时onCreate(SQLiteDatabase db)会被系统回调,通常在onCreate(SQLiteDatabase db)中创建数据表;当数据库版本升级时,系统会回调onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion),通常在onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)中做数据库升级方案。数据库的版本号在SQLiteOpenHelper的构造方法中传入。
二、数据库升级方案
1、onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)详细介绍
在创建SQLiteOpenHelper时传入的数据库版本号如果大于当前版本App数据库文件的版本号onUpgrade会被系统回调。oldVersion为当前版本App数据库文件的版本号, newVersion为SQLiteOpenHelper传入的数据库版本号,newVersion肯定大于oldVersion。
2、举例说明
假设App已经发版过V1、V2,即将发版V3。V1创建了数据表feedback,数据库版本为1;V2在V1的feedback基础上增加了一个字段username,数据库版本为2;V3在V2的基础上新增了一个表crash,数据库版本为3。V1发版时SQLiteOpenHelper如下:
public class DBOpenHelper extends SQLiteOpenHelper { private static final String TAG = DBOpenHelper.class.getSimpleName(); public DBOpenHelper(Context context) { super(context, "feedback.db", null, 1); } @Override public void onCreate(SQLiteDatabase db) { LogUtil.log(TAG, "onCreate"); String createSql = "create table feedback (" + " online integer," + " speechtype integer," + " priority integer ," + " domain char(100)," + " keyparams char(200)," + " success integer," + " errorcode integer," + " errormsg char(100)," + " network integer," + " networktype integer," + " ip char(32)," + " userid char(100)," + " userphone char(20)," + " useraddress char(200)," + " starttime bigint," + " finishtime bigint" + ")"; db.execSQL(createSql); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { LogUtil.log(TAG, "onUpgrade"); }}
V2需要将feedback增加一个字段username,用户从V1升级到V2时,需要改变库表结构并且将原来的feedback进行迁移,V2发版时SQLiteOpenHelper如下:
public class DBOpenHelper extends SQLiteOpenHelper { private static final String TAG = DBOpenHelper.class.getSimpleName(); public DBOpenHelper(Context context) { super(context, "feedback.db", null, 2); } /** * 新增字段username * @param db */ @Override public void onCreate(SQLiteDatabase db) { LogUtil.log(TAG, "onCreate"); String createSql = "create table feedback (" + " online integer," + " speechtype integer," + " priority integer ," + " domain char(100)," + " keyparams char(200)," + " success integer," + " errorcode integer," + " errormsg char(100)," + " network integer," + " networktype integer," + " ip char(32)," + " userid char(100)," + " username char(100),"+ " userphone char(20)," + " useraddress char(200)," + " starttime bigint," + " finishtime bigint" + ")"; db.execSQL(createSql); } /** * 用户从V1升级到V2时改变库表结构并将V1数据库中的数据进行迁移 * @param db * @param oldVersion * @param newVersion */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { LogUtil.log(TAG, "onUpgrade"); if (oldVersion==1) {//用户当前是V1版本 //重命名 String rename = "alter TABLE feedback RENAME TO feedback_temp"; db.execSQL(rename); //建立新表 onCreate(db); //数据拷贝 String copy = "insert into feedback(online,speechtype,priority,domain,keyparams,success,errorcode,errormsg,network,networktype,ip,userid,userphone,useraddress,starttime,finishtime) " + "select (online,speechtype,priority,domain,keyparams,success,errorcode,errormsg,network,networktype,ip,userid,userphone,useraddress,starttime,finishtime) from feedback_temp"; db.execSQL(copy); //删除原表 String delete = "drop table feedback_temp"; db.execSQL(delete); } }}
V3在V2的基础上增加了表crash,用户升级方案可能是:V1-V2,V1-V3,V2-V3,V3发版时SQLiteOpenHelper如下:
public class DBOpenHelper extends SQLiteOpenHelper { private static final String TAG = DBOpenHelper.class.getSimpleName(); public DBOpenHelper(Context context) { super(context, "feedback.db", null, 3); } /** * 建表feedback、crash * * @param db */ @Override public void onCreate(SQLiteDatabase db) { LogUtil.log(TAG, "onCreate"); String createFeedbackSql = "create table feedback (" + " online integer," + " speechtype integer," + " priority integer ," + " domain char(100)," + " keyparams char(200)," + " success integer," + " errorcode integer," + " errormsg char(100)," + " network integer," + " networktype integer," + " ip char(32)," + " userid char(100)," + " username char(100)," + " userphone char(20)," + " useraddress char(200)," + " starttime bigint," + " finishtime bigint" + ")"; String createCrashSql = "create table crash (starttime bigint,finishtime bigint)"; db.execSQL(createFeedbackSql); db.execSQL(createCrashSql); } /** * 升级方案可能为V1-V2;V1-V3;V2-V3 * * @param db * @param oldVersion * @param newVersion */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { LogUtil.log(TAG, "onUpgrade"); switch (oldVersion) { case 1://用户当前是V1版本 if (newVersion == 2) {//升级到V2 upgradeToV2(db); } else if (newVersion == 3) {//升级到V3 upgradeToV2(db); upgradeToV3(db); } break; case 2://用户当前是V2版本 if (newVersion == 3) {//升级到V3 upgradeToV3(db); } break; } } private void upgradeToV2(SQLiteDatabase db) { //重命名 String rename = "alter TABLE feedback RENAME TO feedback_temp"; db.execSQL(rename); //建立新表 String createFeedbackSql = "create table feedback (" + " online integer," + " speechtype integer," + " priority integer ," + " domain char(100)," + " keyparams char(200)," + " success integer," + " errorcode integer," + " errormsg char(100)," + " network integer," + " networktype integer," + " ip char(32)," + " userid char(100)," + " username char(100)," + " userphone char(20)," + " useraddress char(200)," + " starttime bigint," + " finishtime bigint" + ")"; db.execSQL(createFeedbackSql); //数据拷贝 String copy = "insert into feedback(online,speechtype,priority,domain,keyparams,success,errorcode,errormsg,network,networktype,ip,userid,userphone,useraddress,starttime,finishtime) " + "select * from feedback_temp"; db.execSQL(copy); //删除原表 String delete = "drop table feedback_temp"; db.execSQL(delete); } private void upgradeToV3(SQLiteDatabase db) { String createCrashSql = "create table crash (starttime bigint,finishtime bigint)"; db.execSQL(createCrashSql); }}
更多相关文章
- android简单登陆和注册功能实现+SQLite数据库学习
- Android中SQLite数据库中query或者rawquery查询失败闪退问题
- Android中SQLite数据库操作(2)——使用SQLiteDatabase提供的方法操
- 【Android】数据库处理
- Android文件管理器与media数据库的同步问题
- Android里监视数据库的变化 registerContentObserver 接口
- android SQLiteDatabase中版本控制作用详解
- Dev Guide/Framework Topics/Search-版本为Android 4.0 r1