项目使用到Greendao当做数据库,升级的时候遇到个问题,但新添加int类型字段的时候,数据迁移报错了,查看到是因为新增int、long类型字段的时候,给的是NOT NULL,就是说不能为空。
数据库升级方案使用的是外国某个大神写的代码,大概思路是:
1、新建一张跟原先表一样的临时表,同时把数据也复制进去。
2、删除原先表
3、新建最新的表,将临时表数据复制进去
4、删除临时表
问题就出在第三不,如果临时表有1个字段。新表有2个字段,而且新多的字段为int类型,那么复制数据的时候,字段二(新增)是存NULL,但是其属性是不能为NULL,这就异常了,这里有个解决思路:在临时表上也给多加一个一样的字段,然后给默认值,因为有了默认值,所有当复制数据的时候就不会是NULL,亲测,可行。
代码:
openHelper:
public class ReleaseOpenHelper extends DaoMaster.OpenHelper { Class<? extends AbstractDao<?,?>>[] daoClasses; public ReleaseOpenHelper(Context context, String name,Class<? extends AbstractDao<?, ?>>... daoClasses) { super(context, name); this.daoClasses = daoClasses; } public ReleaseOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,Class<? extends AbstractDao<?, ?>>... daoClasses) { super(context, name, factory); this.daoClasses = daoClasses; } @Override public void onCreate(Database db) { super.onCreate(db); } @Override public void onUpgrade(Database db, int oldVersion, int newVersion) { // super.onUpgrade(db, oldVersion, newVersion); if(oldVersion < newVersion){ MigrationHelper.migrate(db,daoClasses); } } }
Manager:
public class DatabaseManager { public static final String mDBName = "xxx.db"; private DaoSession mDaoSession = null; private NavModelDao favModelDao = null; private VideoModelDao videoModelDao = null; private VideoStatusModelDao videoStatusModelDao = null; private PlayListDao playListDao = null; private PlaylistDetailListDao playlistDetailListDao; private DatabaseManager() { } public DatabaseManager init(Context context,Class<? extends AbstractDao<?, ?>>... daoClasses) { initDao(context,daoClasses); return this; } private static final class Holder { private static final DatabaseManager INSTANCE = new DatabaseManager(); } public static DatabaseManager getInstance() { return Holder.INSTANCE; } private void initDao(Context context,Class<? extends AbstractDao<?, ?>>... daoClasses) { final ReleaseOpenHelper helper = new ReleaseOpenHelper(context, mDBName,daoClasses); final Database db = helper.getWritableDb(); mDaoSession = new DaoMaster(db).newSession(); favModelDao = mDaoSession.getNavModelDao(); videoModelDao = mDaoSession.getVideoModelDao(); videoStatusModelDao = mDaoSession.getVideoStatusModelDao(); playListDao = mDaoSession.getPlayListDao(); playlistDetailListDao = mDaoSession.getPlaylistDetailListDao(); } public final NavModelDao getNavDao() { return favModelDao; } public final VideoModelDao getVideoModelDao() { return videoModelDao; } public final VideoStatusModelDao getVideoStatusModelDao() { return videoStatusModelDao; } public final PlayListDao getVideoPlayListDao() { return playListDao; } public final PlaylistDetailListDao getVideoPlayListDetailDao() { return playlistDetailListDao; } }
import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.support.annotation.NonNull; import android.text.TextUtils; import org.greenrobot.greendao.AbstractDao; import org.greenrobot.greendao.database.Database; import org.greenrobot.greendao.database.StandardDatabase; import org.greenrobot.greendao.internal.DaoConfig; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.Arrays; import java.util.List; public class MigrationHelper { public static boolean DEBUG = true; 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) { Database database = new StandardDatabase(db); migrate(database, daoClasses); } public static void migrate(Database database, Class<? extends AbstractDao<?, ?>>... daoClasses) { generateTempTables(database, daoClasses); dropAllTables(database, true, daoClasses); createAllTables(database, false, daoClasses); restoreData(database, daoClasses); } 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)) { 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()); } catch (SQLException 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); } private static void createAllTables(Database db, boolean ifNotExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) { reflectMethod(db, "createTable", ifNotExists, daoClasses); } /** * 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)) { StringBuilder insertTableStringBuilder = new StringBuilder(); insertTableStringBuilder.append("ALTER TABLE "+tempTableName +" ADD COLUMN "+columnName+ getTableType(daoConfig.properties[j].type)); db.execSQL(insertTableStringBuilder.toString()); } 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); insertTableStringBuilder.append(";"); db.execSQL(insertTableStringBuilder.toString()); } StringBuilder dropTableStringBuilder = new StringBuilder(); dropTableStringBuilder.append("DROP TABLE ").append(tempTableName); db.execSQL(dropTableStringBuilder.toString()); } catch (SQLException 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 Object getTableType(Class<?> type){ if(type.equals(int.class)){ return " INTEGER DEFAULT 0"; } if(type.equals(long.class)){ return " Long DEFAULT 0"; } if(type.equals(String.class)){ return " TEXT "; } if(type.equals(boolean.class)){ return " NUMERIC DEFAULT 0"; } return " TEXT"; } }
Application :
public class MyApplication extends Application { @Override public void onCreate() { super.onCreate(); DatabaseManager.getInstance().init( this, NavModelDao.class, PlayListDao.class, PlaylistDetailListDao.class, VideoModelDao.class, VideoStatusModelDao.class); }