为什么要进行数据库升级呢？
在项目建立之初，数据库的表结构基本上以满足现阶段的业务而建立，随着后面业务的增加需要更多的column，或者废弃掉表中的某些column,此时如果直接去修改表结构，就会与之前已有的表冲突，导致Crash，此时我们就需要升级数据库中的表。
数据库升级的思路
删除重建
这种方法是最简单直接的。直接将之前的数据库删除后，再重新建立数据库。这样会使得之前保存的数据丢失。不需要持久化的保存数据，可以采用这种方式。
逐级版本迭代升级
比如当前版本为1，最新版本为3，此方案就是先从1–>2–>3. 这种方法实际应用中用起来相当的繁琐，要维护每个版本，所以不做过多介绍。
备份数据库，建立新数据库，然后将备份导入
如题，，奖原来的表删除，之后再将临时表插入到新建的表之中，然后再将临时表给删除了。以此完成数据迁移。
在原表基础上直接添加新的column
对比原表，增加或者删除column
代码实现
首先创建一个数据库帮助类
/**
* description: GreenDao帮助类
* author: bear .
* Created date: 2017/5/17.
*/public class MyOpenHelper extends DaoMaster.DevOpenHelper { public MyOpenHelper(Context context, String name) { super(context, name);
} public MyOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory) { super(context, name, factory);
} @Override
public void onUpgrade(Database db, int oldVersion, int newVersion) { /*此处不用super，因为父类中包含了
dropAllTables(db, true);
onCreate(db);
需要自己定制升级
*/
}
}修改greenDao的版本号，在内层的gradle中的buildTypes节点下添加
greendao{
schemaVersion 1
// 这个地方是自动生成的配置文件存放在哪个位置的
targetGenDir 'src/main/java'
}以下是更新方式：
删除再新建
/**
* 删除原表重新再建立一个表
* @param db
*/
public void dropAndCreate(Database db){
DaoMaster.dropAllTables(db, true);
DaoMaster.createAllTables(db, false);
}备份数据库，建立新数据库，然后将备份导入
/**
* 备份还原
* @param db
* @param daoClasses
*/
public void migrate(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
generateTempTables(db, daoClasses);
DaoMaster.dropAllTables(db, true);
DaoMaster.createAllTables(db, false);
restoreData(db, daoClasses);
} /**
* 数据库备份
* @param db
* @param daoClasses
*/
private void generateTempTables(Database 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) {
}
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());
}
} /**
* 数据库恢复
* @param db
* @param daoClasses
*/
private void restoreData(Database 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())); throw exception;
} private static List<String> getColumns(Database 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;
}对比表差异，向原表中直接插入
column
/**
* 对比差异，在原表中直接添加column,赞不做删除操作
* @param db
* @param daoClasses
*/
public void contrastDiff(Database db,ArrayList<String>properties, Class<? extends AbstractDao<?, ?>>... daoClasses){ for(int i=0;i<daoClasses.length;i++){
DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
String tableName=daoConfig.tablename; if(properties!=null&&properties.size()>0){
ArrayList<String>tem=new ArrayList<>();
StringBuilder sqlBuilder=new StringBuilder(); for(int j=0;j<properties.size();j++){ if(getColumns(db,tableName).contains(properties.get(j))){ continue;
}
tem.add(properties.get(j));
}
sqlBuilder.append("INSERT INTO ").append(tableName).append(" (");
sqlBuilder.append(TextUtils.join(",", tem));
sqlBuilder.append(") SELECT ");
sqlBuilder.append(TextUtils.join(",", tem));
sqlBuilder.append(" FROM ").append(tableName).append(";");
db.execSQL(sqlBuilder.toString());
}
}
}下面贴出，完整的数据库升级帮助类
/**
* description: greenDao升级帮助
* author: bear .
* Created date: 2017/5/17.
*/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;
} /**
* 备份还原
* @param db
* @param daoClasses
*/
public void migrate(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
generateTempTables(db, daoClasses);
DaoMaster.dropAllTables(db, true);
DaoMaster.createAllTables(db, false);
restoreData(db, daoClasses);
} /**
* 对比差异，在原表中直接添加column,赞不做删除操作
* @param db
* @param daoClasses
*/
public void contrastDiff(Database db,ArrayList<String>properties, Class<? extends AbstractDao<?, ?>>... daoClasses){ for(int i=0;i<daoClasses.length;i++){
DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
String tableName=daoConfig.tablename; if(properties!=null&&properties.size()>0){
ArrayList<String>tem=new ArrayList<>();
StringBuilder sqlBuilder=new StringBuilder(); for(int j=0;j<properties.size();j++){ if(getColumns(db,tableName).contains(properties.get(j))){ continue;
}
tem.add(properties.get(j));
}
sqlBuilder.append("INSERT INTO ").append(tableName).append(" (");
sqlBuilder.append(TextUtils.join(",", tem));
sqlBuilder.append(") SELECT ");
sqlBuilder.append(TextUtils.join(",", tem));
sqlBuilder.append(" FROM ").append(tableName).append(";");
db.execSQL(sqlBuilder.toString());
}
}
} /**
* 删除原表重新再建立一个表
* @param db
*/
public void dropAndCreate(Database db){
DaoMaster.dropAllTables(db, true);
DaoMaster.createAllTables(db, false);
} /**
* 数据库备份
* @param db
* @param daoClasses
*/
private void generateTempTables(Database 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) {
}
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());
}
} /**
* 数据库恢复
* @param db
* @param daoClasses
*/
private void restoreData(Database 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())); throw exception;
} private static List<String> getColumns(Database 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;
}
}以上是本人自己整理的一些升级方法，请大家批评指正。
随时随地看视频
