ANdroid 数据库升级备份

在网上查了好久发现Sqlite只能添加和修改表字段，不能进行删除。所有想处理的完善，就只能重新创建表，将数据移到新的表。操作如下：

//1.将表改名成为临时文件
String TEMP_SQL_CREATE_TABLE_SUBSCRIBE = "alter table "+dbName+ " rename to temp_"+dbName;

//创建新的表：
String SQL_CREATE_TABLE_SUBSCRIBE =PersistenceHelper.getDDL(User.class);

//将旧表的（改名为临时的）数据移到新创建的数据表里
String[] columnNames=PersistenceHelper.getColumnNames(User.class);
StringBuffer olderBuffer=new StringBuffer();//旧表的字段
StringBuffer newBuffer=new StringBuffer();//新表的字段
for (String columnName:columnNames){
    olderBuffer.append(columnName+",");
    boolean include=checkColumnExist(Deeper.database,"temp_"+dbName,columnName);
    MyLog.e(TAG,"include="+include);
    if(include)
        newBuffer.append(columnName+",");
    else
        newBuffer.append("'',"); //旧的数据库不包含的字段 先用空的字符串代替

}
String tabsOlder=olderBuffer.substring(0, olderBuffer.length() - 1); 
String tabsNew=newBuffer.substring(0, newBuffer.length() - 1);
String sql="INSERT INTO "+dbName+"("+tabsOlder+") SELECT "+tabsNew+" FROM temp_"+dbName+";";


Deeper.database.execSQL(TEMP_SQL_CREATE_TABLE_SUBSCRIBE);

Deeper.database.execSQL(SQL_CREATE_TABLE_SUBSCRIBE);

//删除改名为临时文件的数据表
String DELETE_TEMP_SUBSCRIBE = "drop table if exists temp_"+dbName;
Deeper.database.execSQL(DELETE_TEMP_SUBSCRIBE);

需要用到方法

   /**
     * 方法1：检查某表列是否存在
     * @param db
     * @param tableName 表名
     * @param columnName 列名
     * @return
     */
    private boolean checkColumnExist1(SQLiteDatabase db, String tableName
            , String columnName) {
        boolean result = false ;
        Cursor cursor = null ;
        try{
            //查询一行
            cursor = db.rawQuery( "SELECT * FROM " + tableName + " LIMIT 0"
                    , null );
            result = cursor != null && cursor.getColumnIndex(columnName) != -1 ;
        }catch (Exception e){
            Log.e(TAG, "checkColumnExists1..." + e.getMessage()) ;
        }finally{
            if(null != cursor && !cursor.isClosed()){
                cursor.close() ;
            }
        }

        return result ;
    }

PersistenceHelper类里的方法

  /**
     * @param clazz
     * @return
     */
    public static String getTableName(Class<?> clazz) {
        Table table = clazz.getAnnotation(Table.class);

        if (table == null) {
            throw new IllegalStateException("Need Table Annotation!");
        }

        return table.value();
    }
/**
     * @param clazz
     * @return
     */
    public static String[] getColumnNames(Class<?> clazz) {
        List<String> columns = new ArrayList<String>();
        
        for (Field field : clazz.getFields()) {
            Column column = field.getAnnotation(Column.class);
            
            if (column != null) {
                columns.add(getColumnName(field.getName()));
            }
        }
        
        return columns.toArray(new String[columns.size()]);
    }
 /**
     * @param clazz
     * @return
     */
    public static String getDDL(Class<?> clazz){
        StringBuffer buffer = new StringBuffer();
        
        buffer.append(String.format("CREATE TABLE IF NOT EXISTS  %s (", getTableName(clazz)));
        
        boolean first = true;
        
        for (Field field : clazz.getFields()) {
            Column column = field.getAnnotation(Column.class);
            
            if (column != null) {
                if (first) {
                    first = false;
                } else {
                    buffer.append(",");
                }
                if (field.getName().equals("identity")) {
                    buffer.append("identity INTEGER PRIMARY KEY AUTOINCREMENT");
                } else {
                    Class<?> type = field.getType();
                    
                    String tableColumnName = getColumnName(field.getName());
                    
                    buffer.append(tableColumnName);
                    
                    if (type.equals(int.class) || type.equals(long.class)) {
                        buffer.append(" INTEGER");
                    } else if (type.equals(boolean.class)) {
                        buffer.append(" INTEGER");
                    } else if (type.equals(Calendar.class)) {
                        buffer.append(" INTEGER");
                    } else if (type.equals(Date.class)) {
                        buffer.append(" INTEGER");
                    } else if (type.equals(String.class)) {
                        buffer.append(" TEXT");
                    } else if (type.equals(float.class)) {
                        buffer.append(" REAL");
                    } else {
                        assert (false);
                    }
                }
            }
        }
        
        buffer.append(");");
        
        return buffer.toString();
    }