创建一个数据库帮助类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public class MyOpenHelper extends DaoMaster.OpenHelper{
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) {
L.d("db version update from " + oldVersion + " to " + newVersion);
switch (oldVersion){
case 1:
CollectAdditionDao.createTable(db,true);
break;
}
}

}

修改greenDao的版本号,在内层的gradle中的buildTypes节点下添加

1
2
3
4
5
greendao {
// schemaVersion 1
schemaVersion 2//@add 增值服务表
targetGenDir 'src/main/java'
}

更新方式

1.删除再新建

1
2
3
4
5
6
7
8
/**
* 删除原表重新再建立一个表
* @param db
*/
public void dropAndCreate(Database db){
DaoMaster.dropAllTables(db, true);
DaoMaster.createAllTables(db, false);
}

2.备份数据库,建立新数据库,然后将备份导入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
/**
* 备份还原
* @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;
}

3.对比表差异,向原表中直接插入column

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/**
* 对比差异,在原表中直接添加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());
}
}
}

完整的数据库升级帮助类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
/**
* 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;
}
}