开发的项目采用了greendao 3.2.2，节省了一部分的写代码时间。
此次是写一个版本管理类，用于统计用户的安装版本。所以就需要将数据写入数据库。
所以，第一坑来了。增删改查那些不说了。
发现greendao升级数据库的时候居然会删除原有的数据，这不是坑吗？于是乎看一下源码。惊奇的发现，只在开发中使用，这不是搞笑吗。
/** WARNING: Drops all table on Upgrade! Use only during development. */
public static class DevOpenHelper extends OpenHelper {
public DevOpenHelper(Context context, String name) {
super(context, name);
}
public DevOpenHelper(Context context, String name, CursorFactory factory) {
super(context, name, factory);
}
@Override
public void onUpgrade(Database db, int oldVersion, int newVersion) {
Log.i("greenDAO", "Upgrading schema from version " + oldVersion + " to " + newVersion + " by dropping all tables");
dropAllTables(db, true);
onCreate(db);
}
}跳过这里，于是乎找找看有没有什么好的方法用于数据库升级，找着找着到这里
一行代码解决数据库升级，牛！该作者的思路来自
先附上代码吧，核心思路都一样
1.建立一个临时表（由原表copy一份）
2.删除旧表
3.建立新表
4.将临时表的数据迁移到新表
public final class MigrationHelper {
public static boolean DEBUG = false;
private static String TAG = "MigrationHelper";
private static final String SQLITE_MASTER = "sqlite_master";
private static final String SQLITE_TEMP_MASTER = "sqlite_temp_master";
public static void migrate(SQLiteDatabase db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
printLog("【The Old Database Version】" + db.getVersion());
Database database = new StandardDatabase(db);
migrate(database, daoClasses);
}
public static void migrate(Database database, Class<? extends AbstractDao<?, ?>>... daoClasses) {
printLog("【Generate temp table】start");
generateTempTables(database, daoClasses);
printLog("【Generate temp table】complete");
dropAllTables(database, true, daoClasses);
createAllTables(database, false, daoClasses);
printLog("【Restore data】start");
restoreData(database, daoClasses);
printLog("【Restore data】complete");
}
private static void generateTempTables(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
for (int i = 0; i < daoClasses.length; i++) {
String tempTableName = null;
DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
String tableName = daoConfig.tablename;
if (!isTableExists(db, false, tableName)) {
printLog("【New Table】" + tableName);
continue;
}
try {
tempTableName = daoConfig.tablename.concat("_TEMP");
StringBuilder dropTableStringBuilder = new StringBuilder();
dropTableStringBuilder.append("DROP TABLE IF EXISTS ").append(tempTableName).append(";");
db.execSQL(dropTableStringBuilder.toString());
StringBuilder insertTableStringBuilder = new StringBuilder();
insertTableStringBuilder.append("CREATE TEMPORARY TABLE ").append(tempTableName);
insertTableStringBuilder.append(" AS SELECT * FROM ").append(tableName).append(";");
db.execSQL(insertTableStringBuilder.toString());
printLog("【Table】" + tableName + "\n ---Columns-->" + getColumnsStr(daoConfig));
printLog("【Generate temp table】" + tempTableName);
} catch (SQLException e) {
Log.e(TAG, "【Failed to generate temp table】" + tempTableName, e);
}
}
}
private static boolean isTableExists(Database db, boolean isTemp, String tableName) {
if (db == null || TextUtils.isEmpty(tableName)) {
return false;
}
String dbName = isTemp ? SQLITE_TEMP_MASTER : SQLITE_MASTER;
String sql = "SELECT COUNT(*) FROM " + dbName + " WHERE type = ? AND name = ?";
Cursor cursor = null;
int count = 0;
try {
cursor = db.rawQuery(sql, new String[]{"table", tableName});
if (cursor == null || !cursor.moveToFirst()) {
return false;
}
count = cursor.getInt(0);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cursor != null)
cursor.close();
}
return count > 0;
}
private static String getColumnsStr(DaoConfig daoConfig) {
if (daoConfig == null) {
return "no columns";
}
StringBuilder builder = new StringBuilder();
for (int i = 0; i < daoConfig.allColumns.length; i++) {
builder.append(daoConfig.allColumns[i]);
builder.append(",");
}
if (builder.length() > 0) {
builder.deleteCharAt(builder.length() - 1);
}
return builder.toString();
}
private static void dropAllTables(Database db, boolean ifExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) {
reflectMethod(db, "dropTable", ifExists, daoClasses);
printLog("【Drop all table】");
}
private static void createAllTables(Database db, boolean ifNotExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) {
reflectMethod(db, "createTable", ifNotExists, daoClasses);
printLog("【Create all table】");
}
/**
* dao class already define the sql exec method, so just invoke it
*/
private static void reflectMethod(Database db, String methodName, boolean isExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) {
if (daoClasses.length < 1) {
return;
}
try {
for (Class cls : daoClasses) {
Method method = cls.getDeclaredMethod(methodName, Database.class, boolean.class);
method.invoke(null, db, isExists);
}
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
private static 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");
if (!isTableExists(db, true, tempTableName)) {
continue;
}
try {
// get all columns from tempTable, take careful to use the columns list
List<String> columns = getColumns(db, tempTableName);
ArrayList<String> properties = new ArrayList<>(columns.size());
for (int j = 0; j < daoConfig.properties.length; j++) {
String columnName = daoConfig.properties[j].columnName;
if (columns.contains(columnName)) {
properties.add(columnName);
}
}
if (properties.size() > 0) {
final String columnSQL = TextUtils.join(",", properties);
StringBuilder insertTableStringBuilder = new StringBuilder();
insertTableStringBuilder.append("INSERT INTO ").append(tableName).append(" (");
insertTableStringBuilder.append(columnSQL);
insertTableStringBuilder.append(") SELECT ");
insertTableStringBuilder.append(columnSQL);
insertTableStringBuilder.append(" FROM ").append(tempTableName).append(";");
db.execSQL(insertTableStringBuilder.toString());
printLog("【Restore data】 to " + tableName);
}
StringBuilder dropTableStringBuilder = new StringBuilder();
dropTableStringBuilder.append("DROP TABLE ").append(tempTableName);
db.execSQL(dropTableStringBuilder.toString());
printLog("【Drop temp table】" + tempTableName);
} catch (SQLException e) {
Log.e(TAG, "【Failed to restore data from temp table 】" + tempTableName, e);
}
}
}
private static List<String> getColumns(Database db, String tableName) {
List<String> columns = null;
Cursor cursor = null;
try {
cursor = db.rawQuery("SELECT * FROM " + tableName + " limit 0", null);
if (null != cursor && cursor.getColumnCount() > 0) {
columns = Arrays.asList(cursor.getColumnNames());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cursor != null) {
cursor.close();
}
if (null == columns) {
columns = new ArrayList<>();
}
}
return columns;
}
private static void printLog(String info) {
if (DEBUG) {
Log.d(TAG, info);
}
}于是乎开开心心试用了。先看一下我用于管理的Entity类
@Entity
public class Version {
@Id(autoincrement = true)
private Long id;
private int versionCode;
private String versionName;
public Version(int versionCode, String versionName) {
this.versionCode = versionCode;
this.versionName = versionName;
}
@Generated(hash = 1957153556)
public Version(Long id, int versionCode, String versionName) {
this.id = id;
this.versionCode = versionCode;
this.versionName = versionName;
}
@Generated(hash = 1433053919)
public Version() {
}
public Long getId() {
return this.id;
}
public void setId(Long id) {
this.id = id;
}
public int getVersionCode() {
return this.versionCode;
}
public void setVersionCode(int versionCode) {
this.versionCode = versionCode;
}
public String getVersionName() {
return this.versionName;
}
public void setVersionName(String versionName) {
this.versionName = versionName;
}
}做了一下版本升级测试，修改一下数据库版本号。发现数据被完整保存下来了。于是乎，进行第二次测试，测试一下增加字段或者删除字段，看看效果如何。
于是，第二坑来了，删除字段之后，数据可以完整保存。
可是当我随机增加了一个int的字段testCode之后，发现数据库升级失败，定位到log，发现抛出这样一个异常
Android:android.database.sqlite.SQLiteConstraintException:UNIQUE constraint failed查找相关资料之后，发现可能是以下两种原因：
可能发生这种BUG的两种情况
1：定义的字段为NOT NULL，而插入时对应的字段为NULL
2：你定义的自动为PRIMARY，而插入时想插入的值已经在表中存在。
首先排除第二种情况，那么只能是第一种情况了。新增的testCode不能非空导致数据库迁移的时候失败。首先怀疑是MigrationHelper的sql语句由问题，定位到将临时表数据转移到新表的那行sql语句（insert into table (?,?,?) select (?,?,?) from tempTable),发现sql语句没有问题。
于是我去查找看看greendao有没有数据非空的注解，发现并没有。
查看greendao的相关issus，发现以下两个有用信息
https://github.com/yuweiguocn/GreenDaoUpgradeHelper/issues/23
https://github.com/greenrobot/greenDAO/issues/17
结论在于：由于greenDAO 3.0 生成的字段添加了非空约束。字段的类型设置为基本类型（如：int）默认会添加非空约束，字段类型设置为对象类型（如：Integer）默认不会添加非空约束，而且最终生成的sql会使用对象类型。
从源码角度看，我们可以查看生成的VersionDao类，发现以下代码，当我们使用int类型的时候，默认创建的字段是非空(NOT NULL)，而使用Integer的时候，创建的字段没有添加限制。自己可以试一下，看看区别。
/** Creates the underlying database table. */
public static void createTable(Database db, boolean ifNotExists) {
String constraint = ifNotExists? "IF NOT EXISTS ": "";
db.execSQL("CREATE TABLE " + constraint + "\"VERSION\" (" + //
"\"_id\" INTEGER PRIMARY KEY AUTOINCREMENT ," + // 0: id
"\"VERSION_CODE\" INTEGER," + // 1: versionCode
"\"VERSION_NAME\" TEXT);"); // 2: versionName
}弄清了原因之后，把int的变量都改为Integer。数据库终于升级成功了。。。。
1404
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
