在基类的table中加入upgrade操作:
public abstract class DbBaseTable {
private static final String TAG = "DbBaseTable";
/**
* @return the DB table name
*/
abstract String getName();
/**
* Creates the DB table according to the DB scheme
*
* @param db
*/
abstract void onCreate(SQLiteDatabase db);
void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion, String tempName) {
//Rename old table to temporary name
<strong>DbUtils.renameTable(db, getName(), tempName);</strong>
//Create clear table according to the new scheme
<strong> onCreate(db);</strong>
//Copy content of the matching columns from the old table to the new one
<strong> joinColumns(db, tempName, getName());</strong>
//Delete old table
<strong> DbUtils.dropTable(db, tempName);</strong>
//这个是更新一些表的内容
initTableContent(db);
}
void initTableContent(SQLiteDatabase db) {
}
void joinColumns(SQLiteDatabase db, String tempName, String tableName) {
<strong>DbUtils.joinColumns(db, tempName, tableName);</strong>
}
}
final class DbUtils {
private static final String TAG = "DbUtils";
private static final boolean DEBUG = false;
private static final String SQLITE_STMT_LIST_TABLES =
"SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name NOT LIKE 'android%'";
private static final String SQLITE_TABLE_NAME_COLUMN = "name";
private static final String SQLITE_STMT_TEMPLATE_LIST_COLUMNS = "SELECT * FROM %s LIMIT 1";
private static final String SQLITE_STMT_TEMPLATE_DROP_TABLE = "DROP TABLE IF EXISTS %s";
private static final String SQLITE_STMT_TEMPLATE_RENAME_TABLE = "ALTER TABLE %s RENAME TO %s";
private static final String SQLITE_STMT_TEMPLATE_COPY_COLUMNS = "INSERT INTO %s (%s) SELECT %s FROM %s";
/**
* @param db
* @return Collection object containing table names in the database
*/
static Collection<String> listTables(SQLiteDatabase db) {
Cursor cursor = db.rawQuery(SQLITE_STMT_LIST_TABLES, null);
if (cursor == null || !cursor.moveToFirst()) {
if (cursor != null) {
cursor.close();
}
return null;
}
int table_name_column = cursor.getColumnIndex(SQLITE_TABLE_NAME_COLUMN);
HashSet<String> tables = new HashSet<String>(cursor.getCount());
do {
tables.add(cursor.getString(table_name_column));
} while (cursor.moveToNext());
cursor.close();
return tables;
}
/**
* @param db
* @param table
* @return List of column names in the DB table
*/
public static List<String> listColumns(SQLiteDatabase db, String table) {
Cursor cursor = db.rawQuery(String.format(SQLITE_STMT_TEMPLATE_LIST_COLUMNS, table), null);
if (cursor == null) {
return null;
}
List<String> columns = Arrays.asList(cursor.getColumnNames());
cursor.close();
return columns;
}
/**
* @param db
* @param table
*/
static void dropTable(SQLiteDatabase db, String table) {
db.execSQL(String.format(SQLITE_STMT_TEMPLATE_DROP_TABLE, table));
}
static void renameTable(SQLiteDatabase db, String oldName, String newName) {
db.execSQL(String.format(SQLITE_STMT_TEMPLATE_RENAME_TABLE, oldName, newName));
}
static void joinColumns(SQLiteDatabase db, String oldTable, String newTable) {
//Delete all records in the new table before copying from the old table
db.delete(newTable, null, null);
//Find columns which exist in both tables
ArrayList<String> old_columns = new ArrayList<String>(listColumns(db, oldTable));
List<String> new_columns = listColumns(db, newTable);
old_columns.retainAll(new_columns);
String common_columns = TextUtils.join(",", old_columns);
//Copy records from old table to new table example:<span style="font-family: 微软雅黑;"><strong><span style="font-size:10px;">INSERT INTO Mytest1 (_id,account_id,test1,test3) SELECT _id,account_id,test1,test3 FROM Mytest1_temp_</span></strong></span>
db.execSQL(String.format(SQLITE_STMT_TEMPLATE_COPY_COLUMNS, newTable, common_columns, common_columns, oldTable));
}
}
然后在DBHelper中重载onUpgrade方法:
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//Get table names in the old DB
Collection<String> old_tables = DbUtils.listTables(db);
if (old_tables == null || old_tables.size() == 0) {
onCreate(db);
return;
}
//Get table names in the new DB
Set<String> new_tables = DataBaseClass.sRCMDbTables.keySet();
try {
db.beginTransaction();
//Remove old tables which are not in the new DB scheme
HashSet<String> obsolete_tables = new HashSet<String>();
for (String table : old_tables) {
if (!new_tables.contains(table)) {
System.out.println("====DBHelp onUpgrade droptable table="+table);
DbUtils.dropTable(db, table);
obsolete_tables.add(table);
}
}
old_tables.removeAll(obsolete_tables);
//Create and upgrade new tables
DbBaseTable table_descriptor;
for (String table : new_tables) {
table_descriptor = DataBaseClass.sRCMDbTables.get(table);
//Check if the new table exists in the old DB
if (old_tables.contains(table)) {
String temp_name = getTempTableName(table, old_tables, new_tables);
System.out.println("====DBHelp onUpgrade temp_name ="+temp_name);
table_descriptor.onUpgrade(db, oldVersion, newVersion, temp_name);
} else {
table_descriptor.onCreate(db);
}
}
db.setTransactionSuccessful();
} catch (Throwable e) {
throw new RuntimeException("DB upgrade failed: " + e.getMessage());
} finally {
db.endTransaction();
}
}
中心思想是:
对比新旧的database结构，如果旧表中有table新的database里没有,则删除旧表里的
如果新database中有一个table更新，则需要更新这个表的结构，然后把旧表中数据拷贝过来
(把旧表rename一个名字，把命名后的表中的数据拷贝到新表中)
代码可以在http://download.csdn.net/detail/baidu_nod/7684479下载