ANdroid 数据库升级备份

来源：互联网发布：小乔丹体测数据编辑：程序博客网时间：2024/06/15 05:51

在网上查了好久发现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();    }

0 0