GreenDAO数据库版本升级

GreenDAO是一款非要流行的android平台上的数据库框架，性能优秀，代码简洁。
初始化数据库模型代码的时候需要使用java项目生成代码，依赖的jar包已经上传到我的资源里了，下载地址如下：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 = ; 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 = ; 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 = ; 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 = ; 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);

    }

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

GreenDAO数据库版本升级

巴特西

GreenDAO数据库版本升级

最新文章

热门文章