Android Sqlite 数据库升级时数据丢失解决方案

Db Helper 事件

 @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "";
        List<DbTable> dbTableList = new ArrayList<DbTable>();
        //备份当前数据
        try {
            boolean upgradeDone = false;
            for (int j = oldVersion; j <= newVersion; j++) {
                switch (j) { 
                    default: //添加要进行数据移植的表
                        dbTableList.add(new DbTable(db, "BarcodeScanList"));
                        dbTableList.add(new DbTable(db, "PutawayList"));
                        dbTableList.add(new DbTable(db, "PickedList"));
                        dbTableList.add(new DbTable(db, "UserWarehouse"));
                        dbTableList.add(new DbTable(db, "Pod"));
                        dbTableList.add(new DbTable(db, "PodLine"));
                        dbTableList.add(new DbTable(db, "RmaOrder"));
                        dbTableList.add(new DbTable(db, "RmaOrderLine"));
                        upgradeDone = true; //默认只执行一次
                        break;
                }
                if(upgradeDone)
                    break;
            }
        } catch (Exception e) {
            Utils.writeLog("Backup data before upgrade", e);
        }
        //升级数据库
        super.onUpgrade(db, oldVersion, newVersion);
        //恢复数据
        try {
            for (DbTable dbTable : dbTableList) {
                dbTable.restoreOldData();
            }
        } catch (Exception e) {
            Utils.writeLog("Restore data before upgrade", e);
        }
    }

DbTable类

   private class DbTable {
        public DbTable(SQLiteDatabase sqLiteDatabase, final String tableName) {
            this.db = sqLiteDatabase;
            this.TableName = tableName;
            this.Columns = this.getColumnNames(db, tableName);
            backupOldData();
        }
 
        private SQLiteDatabase db;
        private String TableName;
        private String[] Columns;
        private boolean BackupDataSuccess = false;        
        //备份数据
        public boolean backupOldData() {
            try {
                //db.beginTransaction();
                // 1, Rename table.
                String tempTableName = this.TableName + "_temp";
                String sql = "DROP TABLE IF EXISTS " + tempTableName; //删除上次的临时表
                execSQL(db, sql);
                sql = "CREATE TABLE " + tempTableName + "  AS SELECT * FROM " + this.TableName;
                //产生临时表
                execSQL(db, sql);
                //db.setTransactionSuccessful();
                this.BackupDataSuccess = true; //备份成功
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            } finally {
                //db.endTransaction();
            }
            return true;
        }
        //恢复数据
        public boolean restoreOldData() {
            if (this.BackupDataSuccess) {
                try {
                    db.beginTransaction();
                    String columns = "";
                    List<String> newColumns = Arrays.asList(this.getColumnNames(db, this.TableName)); 
                    //对比新旧表之间的字段，把旧表里有而新表没有的字段排除掉
                    for(String column: this.Columns)
                    {
                        if(newColumns.contains(column))
                            columns += column+',';
                    }
                    if(!columns.isEmpty())
                        columns = columns.substring(0, columns.length()-1);
                    String tempTableName = this.TableName + "_temp";   
                    //数据移植
                    String sql = "INSERT INTO " + this.TableName +
                            " (" + columns + ") " +
                            " SELECT " + columns + " FROM " + tempTableName;
                    execSQL(db, sql);
                    //删除临时表
                    execSQL(db, "DROP TABLE IF EXISTS " + tempTableName);
                    db.setTransactionSuccessful();
                } catch (SQLException e) {
                    e.printStackTrace();
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    db.endTransaction();
                }
            }
            return true;
        }
        //获取字段列表
        private String[] getColumnNames(SQLiteDatabase db, String tableName) {
            String[] columnNames = null;
            Cursor c = null;
            try {
                c = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);
                if (null != c) {
                    int columnIndex = c.getColumnIndex("name");
                    if (-1 == columnIndex) {
                        return null;
                    }
                    int index = 0;
                    columnNames = new String[c.getCount()];
                    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
                        columnNames[index] = c.getString(columnIndex);
                        index++;
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (c != null)
                    c.close();
            }
            return columnNames;
        }
 
        private void execSQL(SQLiteDatabase db, final String sql) {
            db.execSQL(sql);
        }
    }