在基类的table中加入upgrade操作:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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 
    DbUtils.renameTable(db, getName(), tempName); 
           
        //Create clear table according to the new scheme 
       onCreate(db);
        //Copy content of the matching columns from the old table to the new one 
      joinColumns(db, tempName, getName());
           
        //Delete old table 
      DbUtils.dropTable(db, tempName);
           
        //这个是更新一些表的内容 
        initTableContent(db); 
    
       
    void initTableContent(SQLiteDatabase db) { 
    
      
    void joinColumns(SQLiteDatabase db, String tempName, String tableName) { 
      DbUtils.joinColumns(db, tempName, tableName); 
    
   
}

     

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    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, nullnull); 
               
            //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:         INSERT INTO Mytest1 (_id,account_id,test1,test3) SELECT _id,account_id,test1,test3 FROM Mytest1_temp_
            db.execSQL(String.format(SQLITE_STMT_TEMPLATE_COPY_COLUMNS, newTable, common_columns, common_columns, oldTable)); 
        
       
    }


    然后在DBHelper中重载onUpgrade方法:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    @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一个名字,把命名后的表中的数据拷贝到新表中)

    創作者介紹
    創作者 shadow 的頭像
    shadow

    資訊園

    shadow 發表在 痞客邦 留言(0) 人氣()