GreenDAO数据库版本升级

偏执的太偏执、 2022-08-01 18:03 194阅读 0赞

GreenDAO是一款非要流行的android平台上的数据库框架，性能优秀，代码简洁。  
初始化数据库模型代码的时候需要使用java项目生成代码，依赖的jar包已经上传到我的资源里了，下载地址如下：[http://download.csdn.net/detail/fancylovejava/8859203][http_download.csdn.net_detail_fancylovejava_8859203]

项目开发中用到的就是GreenDAO数据库框架，需要进行数据库版本升级。  
其实数据库版本升级比较麻烦的就是数据的迁移，data migration。

数据库版本升级有很多方法，按不同需求来处理。

本质上是去执行sql语句去创建临时数据表，然后迁移数据，修改临时表名等。

数据版本升级，为了便于维护代码可以先定义一个抽象类

public abstract class AbstractMigratorHelper {
    
    public abstract void onUpgrade(SQLiteDatabase db);
    }

然后让自己更新数据库逻辑的类继承这个类

public class DBMigrationHelper6 extends AbstractMigratorHelper {
    
    /* Upgrade from DB schema 6 to schema 7 , version numbers are just examples*/
    
    public void onUpgrade(SQLiteDatabase db) {
    
        /* Create a temporal table where you will copy all the data from the previous table that you need to modify with a non supported sqlite operation */
        db.execSQL("CREATE TABLE " + "'post2' (" + //
                "'_id' INTEGER PRIMARY KEY ," + // 0: id
                "'POST_ID' INTEGER UNIQUE ," + // 1: postId
                "'USER_ID' INTEGER," + // 2: userId
                "'VERSION' INTEGER," + // 3: version
                "'TYPE' TEXT," + // 4: type
                "'MAGAZINE_ID' TEXT NOT NULL ," + // 5: magazineId
                "'SERVER_TIMESTAMP' INTEGER," + // 6: serverTimestamp
                "'CLIENT_TIMESTAMP' INTEGER," + // 7: clientTimestamp
                "'MAGAZINE_REFERENCE' TEXT NOT NULL ," + // 8: magazineReference
                "'POST_CONTENT' TEXT);"); // 9: postContent
    
        /* Copy the data from one table to the new one */
        db.execSQL("INSERT INTO post2 (_id, POST_ID, USER_ID, VERSION, TYPE,  MAGAZINE_ID, SERVER_TIMESTAMP, CLIENT_TIMESTAMP, MAGAZINE_REFERENCE, POST_CONTENT)" +
                "   SELECT _id, POST_ID, USER_ID, VERSION, TYPE,  MAGAZINE_ID, SERVER_TIMESTAMP, CLIENT_TIMESTAMP, MAGAZINE_REFERENCE, POST_CONTENT FROM post;");
    
        /* Delete the previous table */
        db.execSQL("DROP TABLE post");
        /* Rename the just created table to the one that I have just deleted */
        db.execSQL("ALTER TABLE post2 RENAME TO post");
    
        /* Add Index/es if you want them */
        db.execSQL("CREATE INDEX " + "IDX_post_USER_ID ON post" +
                " (USER_ID);");
        //Example sql statement
        db.execSQL("ALTER TABLE user ADD COLUMN USERNAME TEXT");
       }
    }

最后在OpenHelper方法里的OnUpgrade方法里面处理，这里方法调用的比较巧妙，借用国外大牛的代码复制下。

public static class UpgradeHelper extends OpenHelper {
        
    
        public UpgradeHelper(Context context, String name, CursorFactory factory) {
            super(context, name, factory);
        }
    
        /**
         * Here is where the calls to upgrade are executed
         */
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    
            /* i represent the version where the user is now and the class named with this number implies that is upgrading from i to i++ schema */
            for (int i = oldVersion; i < newVersion; i++) {
                try {
                    /* New instance of the class that migrates from i version to i++ version named DBMigratorHelper{version that the db has on this moment} */
                    AbstractMigratorHelper migratorHelper = (AbstractMigratorHelper) Class.forName("com.nameofyourpackage.persistence.MigrationHelpers.DBMigrationHelper" + i).newInstance();
    
                    if (migratorHelper != null) {
    
                        /* Upgrade de db */
                        migratorHelper.onUpgrade(db);
                    }
    
                } catch (ClassNotFoundException | ClassCastException | IllegalAccessException | InstantiationException e) {
    
                    Log.e(TAG, "Could not migrate from schema from schema: " + i + " to " + i++);
                    /* If something fail prevent the DB to be updated to future version if the previous version has not been upgraded successfully */
                    break;
                }
    
    
            }
        }
    }

上面已经做了数据的迁移，国外有个大牛封装的代码摘抄下来：

/**
     * Created by pokawa on 18/05/15.
     */
    public class MigrationHelper {
        
    
        private static final String CONVERSION_CLASS_NOT_FOUND_EXCEPTION = "MIGRATION HELPER - CLASS DOESN'T MATCH WITH THE CURRENT PARAMETERS";
        private static MigrationHelper instance;
    
        public static MigrationHelper getInstance() {
            if(instance == null) {
                instance = new MigrationHelper();
            }
            return instance;
        }
    
        public void migrate(SQLiteDatabase db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
        
            generateTempTables(db, daoClasses);
            DaoMaster.dropAllTables(db, true);
            DaoMaster.createAllTables(db, false);
            restoreData(db, daoClasses);
        }
    
        private void generateTempTables(SQLiteDatabase db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
        
            for(int i = 0; i < daoClasses.length; i++) {
                DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
    
                String divider = "";
                String tableName = daoConfig.tablename;
                String tempTableName = daoConfig.tablename.concat("_TEMP");
                ArrayList<String> properties = new ArrayList<>();
    
                StringBuilder createTableStringBuilder = new StringBuilder();
    
                createTableStringBuilder.append("CREATE TABLE ").append(tempTableName).append(" (");
    
                for(int j = 0; j < daoConfig.properties.length; j++) {
                    String columnName = daoConfig.properties[j].columnName;
    
                    if(getColumns(db, tableName).contains(columnName)) {
                        properties.add(columnName);
    
                        String type = null;
    
                        try {
                            type = getTypeByClass(daoConfig.properties[j].type);
                        } catch (Exception exception) {
                            Crashlytics.logException(exception);
                        }
    
                        createTableStringBuilder.append(divider).append(columnName).append(" ").append(type);
    
                        if(daoConfig.properties[j].primaryKey) {
                            createTableStringBuilder.append(" PRIMARY KEY");
                        }
    
                        divider = ",";
                    }
                }
                createTableStringBuilder.append(");");
    
                db.execSQL(createTableStringBuilder.toString());
    
                StringBuilder insertTableStringBuilder = new StringBuilder();
    
                insertTableStringBuilder.append("INSERT INTO ").append(tempTableName).append(" (");
                insertTableStringBuilder.append(TextUtils.join(",", properties));
                insertTableStringBuilder.append(") SELECT ");
                insertTableStringBuilder.append(TextUtils.join(",", properties));
                insertTableStringBuilder.append(" FROM ").append(tableName).append(";");
    
                db.execSQL(insertTableStringBuilder.toString());
            }
        }
    
        private void restoreData(SQLiteDatabase db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
        
            for(int i = 0; i < daoClasses.length; i++) {
                DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
    
                String tableName = daoConfig.tablename;
                String tempTableName = daoConfig.tablename.concat("_TEMP");
                ArrayList<String> properties = new ArrayList();
    
                for (int j = 0; j < daoConfig.properties.length; j++) {
                    String columnName = daoConfig.properties[j].columnName;
    
                    if(getColumns(db, tempTableName).contains(columnName)) {
                        properties.add(columnName);
                    }
                }
    
                StringBuilder insertTableStringBuilder = new StringBuilder();
    
                insertTableStringBuilder.append("INSERT INTO ").append(tableName).append(" (");
                insertTableStringBuilder.append(TextUtils.join(",", properties));
                insertTableStringBuilder.append(") SELECT ");
                insertTableStringBuilder.append(TextUtils.join(",", properties));
                insertTableStringBuilder.append(" FROM ").append(tempTableName).append(";");
    
                StringBuilder dropTableStringBuilder = new StringBuilder();
    
                dropTableStringBuilder.append("DROP TABLE ").append(tempTableName);
    
                db.execSQL(insertTableStringBuilder.toString());
                db.execSQL(dropTableStringBuilder.toString());
            }
        }
    
        private String getTypeByClass(Class<?> type) throws Exception {
        
            if(type.equals(String.class)) {
                return "TEXT";
            }
            if(type.equals(Long.class) || type.equals(Integer.class) || type.equals(long.class)) {
                return "INTEGER";
            }
            if(type.equals(Boolean.class)) {
                return "BOOLEAN";
            }
    
            Exception exception = new Exception(CONVERSION_CLASS_NOT_FOUND_EXCEPTION.concat(" - Class: ").concat(type.toString()));
            Crashlytics.logException(exception);
            throw exception;
        }
    
        private static List<String> getColumns(SQLiteDatabase db, String tableName) {
            List<String> columns = new ArrayList<>();
            Cursor cursor = null;
            try {
                cursor = db.rawQuery("SELECT * FROM " + tableName + " limit 1", null);
                if (cursor != null) {
                    columns = new ArrayList<>(Arrays.asList(cursor.getColumnNames()));
                }
            } catch (Exception e) {
                Log.v(tableName, e.getMessage(), e);
                e.printStackTrace();
            } finally {
                if (cursor != null)
                    cursor.close();
            }
            return columns;
        }
    }

然后在OnUpgrade方法里面去调用

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.i("greenDAO", "Upgrading schema from version " + oldVersion + " to " + newVersion + " by migrating all tables data");
    
            MigrationHelper.getInstance().migrate(db,
                    UserDao.class,
                    ItemDao.class);
        }

上面代码全是干货，记录下来以备不时之需！

[http_download.csdn.net_detail_fancylovejava_8859203]: http://download.csdn.net/detail/fancylovejava/8859203