greenDao 3.x遇到数据库升级，且表结构中添加int类型字段问题。

最新推荐文章于 2025-07-11 10:53:52 发布

转载最新推荐文章于 2025-07-11 10:53:52 发布 · 2.4k 阅读

文章标签：

#数据库

greenDao 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用GreenDao进行数据库升级的方法，解决了升级时因添加Int类型字段导致的NOT NULL约束问题。通过创建临时表并调整数据迁移逻辑，确保升级过程顺利。

【转载自http://blog.youkuaiyun.com/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 该列。

下面对之前网上的代码做一些调整：

[java] view plain copy

print ?

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

该类全部代码如下：

[java] view plain copy

print ?

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&lt;String&gt; getColumns(Database db, String tableName) {
    List&lt;String&gt; columns = new ArrayList&lt;&gt;();
    Cursor cursor = null;
    try {
        cursor = db.rawQuery("SELECT * FROM " + tableName + " limit 1", null);
        if (cursor != null) {
            columns = new ArrayList&lt;&gt;(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&lt;? extends AbstractDao&lt;?, ?&gt;&gt;... daoClasses) {
    generateTempTables(db, daoClasses);
    DaoMaster.dropAllTables(db, true);
    DaoMaster.createAllTables(db, false);
    restoreData(db, daoClasses);
}

private void generateTempTables(Database db, Class&lt;? extends AbstractDao&lt;?, ?&gt;&gt;... daoClasses) {
    for (int i = 0; i &lt; daoClasses.length; i++) {
        DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);

        String divider = "";
        String tableName = daoConfig.tablename;
        String tempTableName = daoConfig.tablename.concat("_TEMP");
        ArrayList&lt;String&gt; properties = new ArrayList&lt;&gt;();

        StringBuilder createTableStringBuilder = new StringBuilder();

        createTableStringBuilder.append("CREATE TABLE ").append(tempTableName).append(" (");

        for (int j = 0; j &lt; 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&lt;? extends AbstractDao&lt;?, ?&gt;&gt;... daoClasses) {
    for (int i = 0; i &lt; daoClasses.length; i++) {
        DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);

        String tableName = daoConfig.tablename;
        String tempTableName = daoConfig.tablename.concat("_TEMP");
        ArrayList&lt;String&gt; properties = new ArrayList();
        ArrayList&lt;String&gt; propertiesQuery = new ArrayList();
        for (int j = 0; j &lt; 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&lt;?&gt; 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;
}

}