【转载自http://blog.csdn.net/u013183495/article/details/53467746】不过在测试阶段发现问题还是存在,后来逐步调试过程中发现getTypeByClass()方法中,type.getName()的值为int。显然无法return。后来就添加了if(type.getName.equals(“int”)){type = Integer.class}。再次运行,升级成功。后来反过来查看转载的bolg里面的getTypeByClass()方法,其实已经做了相同的操作。不管怎样,记录一下。怕再次疏忽。
首先在试用GreenDao的时候,默认的数据库升级会先Drop表再Create,这样问题显而易见,如果版本已经上线必然不能接受
解决方法:网上有很多转载解决方案。一般是利用临时表转移2次数据
现在要说的问题:
当某张表需要添加一个Int类型的列时,升级数据库则会报NOT NULL约束不通过,原因是 GreenDao建表时Int 类型 加了NOT NULL。而再数据转移的时候并没有Insert 该列。
下面对之前网上的代码做一些调整:
- for (int j = 0; j < daoConfig.properties.length; j++) {
- String columnName = daoConfig.properties[j].columnName;
- if (getColumns(db, tempTableName).contains(columnName)) {
- properties.add(columnName);
- propertiesQuery.add(columnName);
- } else {
- try {
- <span style=”color:#ff0000;”> if (getTypeByClass(daoConfig.properties[j].type).equals(“INTEGER”)) {
- propertiesQuery.add(”0 as ” + columnName);
- properties.add(columnName);
- }</span>
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
for (int j = 0; j < daoConfig.properties.length; j++) {
String columnName = daoConfig.properties[j].columnName;
if (getColumns(db, tempTableName).contains(columnName)) {
properties.add(columnName);
propertiesQuery.add(columnName);
} else {
try {
if (getTypeByClass(daoConfig.properties[j].type).equals("INTEGER")) {
propertiesQuery.add("0 as " + columnName);
properties.add(columnName);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
遍历新的表结构,发现新增的列的时候,从临时表查数据的时候,对应把新增的列默认填值 0 as columnName
该类全部代码如下:
- public class GreenDaoUpgrade {
- private static final String CONVERSION_CLASS_NOT_FOUND_EXCEPTION = “MIGRATION HELPER - CLASS DOESN’T MATCH WITH THE CURRENT PARAMETERS”;
- private static GreenDaoUpgrade instance;
- public static GreenDaoUpgrade getInstance() {
- if (instance == null) {
- instance = new GreenDaoUpgrade();
- }
- return instance;
- }
- 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;
- }
- public void migrate(Database db, Class<? extends AbstractDao<?, ?>>… daoClasses) {
- generateTempTables(db, daoClasses);
- DaoMaster.dropAllTables(db, true);
- DaoMaster.createAllTables(db, false);
- restoreData(db, 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) {
- exception.printStackTrace();
- }
- 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(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();
- ArrayList<String> propertiesQuery = 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);
- propertiesQuery.add(columnName);
- } else {
- try {
- if (getTypeByClass(daoConfig.properties[j].type).equals(“INTEGER”)) {
- propertiesQuery.add(”0 as ” + columnName);
- properties.add(columnName);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
- StringBuilder insertTableStringBuilder = new StringBuilder();
- insertTableStringBuilder.append(”INSERT INTO ”).append(tableName).append(“ (“);
- insertTableStringBuilder.append(TextUtils.join(”,”, properties));
- insertTableStringBuilder.append(”) SELECT ”);
- insertTableStringBuilder.append(TextUtils.join(”,”, propertiesQuery));
- 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) || type.equals(int.class)) {
- return “INTEGER”;
- }
- if (type.equals(Boolean.class) || type.equals(boolean.class)) {
- return “BOOLEAN”;
- }
- Exception exception = new Exception(CONVERSION_CLASS_NOT_FOUND_EXCEPTION.concat(“ - Class: ”).concat(type.toString()));
- exception.printStackTrace();
- throw exception;
- }
- }
public class GreenDaoUpgrade {
private static final String CONVERSION_CLASS_NOT_FOUND_EXCEPTION = "MIGRATION HELPER - CLASS DOESN'T MATCH WITH THE CURRENT PARAMETERS";
private static GreenDaoUpgrade instance;
public static GreenDaoUpgrade getInstance() { if (instance == null) { instance = new GreenDaoUpgrade(); } return instance; } 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; } public void migrate(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) { generateTempTables(db, daoClasses); DaoMaster.dropAllTables(db, true); DaoMaster.createAllTables(db, false); restoreData(db, 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) { exception.printStackTrace(); } 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(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(); ArrayList<String> propertiesQuery = 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); propertiesQuery.add(columnName); } else { try { if (getTypeByClass(daoConfig.properties[j].type).equals("INTEGER")) { propertiesQuery.add("0 as " + columnName); properties.add(columnName); } } catch (Exception e) { e.printStackTrace(); } } } StringBuilder insertTableStringBuilder = new StringBuilder(); insertTableStringBuilder.append("INSERT INTO ").append(tableName).append(" ("); insertTableStringBuilder.append(TextUtils.join(",", properties)); insertTableStringBuilder.append(") SELECT "); insertTableStringBuilder.append(TextUtils.join(",", propertiesQuery)); 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) || type.equals(int.class)) { return "INTEGER"; } if (type.equals(Boolean.class) || type.equals(boolean.class)) { return "BOOLEAN"; } Exception exception = new Exception(CONVERSION_CLASS_NOT_FOUND_EXCEPTION.concat(" - Class: ").concat(type.toString())); exception.printStackTrace(); throw exception; }
}