sqlite 数据库更新

来源：互联网发布：java中double类型运算编辑：程序博客网时间：2024/05/10 23:42

本次更新测试基于反射和注解：暂时不允许主键的更改

 @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        try {            List<Class<?>> list = new ArrayList<>();            TableScaner.scan(mContext,IConfig.tablePackageName,list);//扫描beans包下的类            for(Class clazz : list){                if(clazz.isAnnotationPresent(Table.class) && clazz.isAnnotationPresent(TableVersion.class)){//是表才操作                    TableVersion tableVersion = (TableVersion) clazz.getAnnotation(TableVersion.class);                    boolean newTable = false;                    if(tableVersion.newTableCode() > oldVersion){//新增版本大于旧版本,保证先有表                        db.execSQL(DBTools.getCreateSql(clazz));                        newTable = true;                    }                    if(tableVersion.modifyTableCode() > tableVersion.newTableCode()){//修改版本大于新增版本                        Field[] fields = clazz.getDeclaredFields();                        for(Field field : fields){                            FieldVersion fieldVersion = field.getAnnotation(FieldVersion.class);                            if(fieldVersion.newColumnCode() > tableVersion.newTableCode()){//新增列                                if(!newTable){//如果刚执行了新建表true,表明用户跨版本升级,则这里不再操作新增                                    try {                                        String sqlString = "ALTER TABLE " + DBTools.getTableName(clazz)+" ADD COLUMN "+ field.getName()+ getColumnType(field);                                        db.execSQL(sqlString);                                    }catch (Exception e){//已经存在该列                                        Log.d("hjh",e.getMessage());                                    }                                }                            }                            if(fieldVersion.deleteColumnCode() > fieldVersion.newColumnCode()){//删除版本必然大于新增版本//                                db.execSQL("insert into "+DBTools.getTableName(clazz)+" (test,userid) values("+System.currentTimeMillis()+",1)");//                                db.execSQL("insert into "+DBTools.getTableName(clazz)+" (test,userid) values("+System.currentTimeMillis()+",2)");//测试                                db.execSQL("ALTER TABLE " + DBTools.getTableName(clazz)+" RENAME TO "+ "app_temp_table");//将原表重命名                                db.execSQL(createSqlExceptDelete(clazz));//重新创建                                db.execSQL("INSERT INTO " + DBTools.getTableName(clazz) + " SELECT "+getAllColumnsExceptDelete(clazz.getDeclaredFields())+" FROM app_temp_table");//插入数据                                db.execSQL("DROP TABLE app_temp_table");//删除临时表                            }                        }                    }                }            }        } catch (Exception e) {            e.printStackTrace();            Log.d("hjh",e.getMessage());        }    }       //减少字段查找时使用    private String getAllColumnsExceptDelete(Field[] fields){        String content = "";        List<Field> list = new ArrayList<>();        for(Field field : fields){            field.setAccessible(true);            if(field.isAnnotationPresent(ColumnInt.class) || field.isAnnotationPresent(ColumnText.class) ||                    field.isAnnotationPresent(ColumnFloat.class) || field.isAnnotationPresent(ColumnDouble.class)||                    field.isAnnotationPresent(ColumnString.class)|| field.isAnnotationPresent(ColumnDate.class) ||                    field.isAnnotationPresent(ColumnBinary.class) || field.isAnnotationPresent(ColumnLong.class)||                    field.isAnnotationPresent(ColumnBoolean.class)){                if(field.isAnnotationPresent(FieldVersion.class)){                    FieldVersion fieldVersion = field.getAnnotation(FieldVersion.class);                    if(fieldVersion.deleteColumnCode() > fieldVersion.newColumnCode()) {                        continue;//排除删除字段                    }else {                        list.add(field);                    }                }            }        }        for(int index = 0; index < list.size();index ++){            if(index != list.size() -1){//非最后一个                content += list.get(index).getName()+",";            }else {                content += list.get(index).getName();            }        }        return content;    }    //增加字段时使用    private String getColumnType(Field field){        field.setAccessible(true);        String content = "";        if(field.isAnnotationPresent(ColumnInt.class) && !field.isAnnotationPresent(ColumnPrimaryKey.class)) {            content = " integer";        } else if(field.isAnnotationPresent(ColumnText.class)) {            content = " text";        } else if(field.isAnnotationPresent(ColumnFloat.class)) {            content = " float";        } else if(field.isAnnotationPresent(ColumnDouble.class)) {            content = " double";        } else if(field.isAnnotationPresent(ColumnString.class)) {            content = " varchar(" + ((ColumnString)ColumnString.class.cast(field.getAnnotation(ColumnString.class))).length() + ")";        } else if(field.isAnnotationPresent(ColumnDate.class)) {            content = " date";        } else if(field.isAnnotationPresent(ColumnBinary.class)) {            content = " blob";        } else if(field.isAnnotationPresent(ColumnBoolean.class)) {            content = " boolean";        } else if(field.isAnnotationPresent(ColumnLong.class)) {            content = " NUMBER";        }        return content;    }    private String createSqlExceptDelete(Class clazz){        String sql = "";        if(clazz.isAnnotationPresent(Table.class)){            Table  annotationType =(Table) clazz.getAnnotation(Table.class);            String tableName =  annotationType.TableName();            Field[]  fields =clazz.getDeclaredFields();            String content =getContentExceptDelete(fields);            content = content.substring(0, content.lastIndexOf(","));            sql = "create table "+tableName+" ("+content+" )";        }        return sql;    }    private  String getContentExceptDelete(Field[] fields){        String content="";        //先设置主键        for(Field field : fields){            field.setAccessible(true);            if(field.isAnnotationPresent(FieldVersion.class)){                FieldVersion fieldVersion = field.getAnnotation(FieldVersion.class);                if(fieldVersion.deleteColumnCode() > fieldVersion.newColumnCode())continue;            }            if(field.isAnnotationPresent(ColumnPrimaryKey.class) && field.isAnnotationPresent(ColumnInt.class)){                ColumnPrimaryKey primaryKey = field.getAnnotation(ColumnPrimaryKey.class);                content +=field.getName()+primaryKey.TYPE().getContent()+",";                break;            }        }        for(Field field : fields){            field.setAccessible(true);            if(field.isAnnotationPresent(FieldVersion.class)){                FieldVersion fieldVersion = field.getAnnotation(FieldVersion.class);                if(fieldVersion.deleteColumnCode() > fieldVersion.newColumnCode())continue;            }            if(field.isAnnotationPresent(ColumnInt.class) && !field.isAnnotationPresent(ColumnPrimaryKey.class)){                content +=field.getName()+" integer,";                continue;            }            if(field.isAnnotationPresent(ColumnText.class)){                content +=field.getName()+" text,";                continue;            }            if(field.isAnnotationPresent(ColumnFloat.class)){                content +=field.getName()+" float,";                continue;            }            if(field.isAnnotationPresent(ColumnDouble.class)){                content +=field.getName()+" double,";                continue;            }            if(field.isAnnotationPresent(ColumnString.class)){                content +=field.getName()+" varchar("+ColumnString.class.cast(field.getAnnotation(ColumnString.class)).length()+"),";                continue;            }            if(field.isAnnotationPresent(ColumnDate.class)){                content +=field.getName()+" date,";                continue;            }            if(field.isAnnotationPresent(ColumnBinary.class)){                content +=field.getName()+" blob,";                continue;            }            if(field.isAnnotationPresent(ColumnBoolean.class)){                content += field.getName()+" boolean,";                continue;            }            if(field.isAnnotationPresent(ColumnLong.class)){                content += field.getName()+" NUMBER,";            }        }        return content;    }

0 0