主要思路：
- 删除表不改动
- 新增表：创建新表
- 增加新字段：在原有表上直接新增字段
- 修改或删除原有字段：数据迁移
package com.xh.acldlib.db;
import android.database.Cursor;
import android.support.annotation.NonNull;
import android.support.annotation.Nullable;
import android.text.TextUtils;
import android.util.Log;
import com.xh.acldlib.BuildConfig;
import org.greenrobot.greendao.AbstractDao;
import org.greenrobot.greendao.Property;
import org.greenrobot.greendao.database.Database;
import org.greenrobot.greendao.internal.DaoConfig;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 数据库升级类
* please call {@link #upgrade(Database, List)} (Database, List)}
* <p>
* Created by LYF on 2018/1/19.
*/
public class UpgradeHelper {
private static boolean DEBUG = BuildConfig.DEBUG;
private static final String SQLITE_MASTER = "sqlite_master";
private static final String SQLITE_TEMP_MASTER = "sqlite_temp_master";
private static final String NULL = "NULL";//空类型
private static final String TEXT = "TEXT";//文本类型
private static final String INTEGER = "INTEGER";//整形
private static final String BOOLEAN = "BOOLEAN";//boolean类型
private static final String REAL = "REAL";//浮点型
private static final String BLOB = "BLOB";//Byte[]类型
/**
* 升级
*
* @param database Database
* @param daoClasses 有变更表的表
*/
public static void upgrade(@Nullable Database database, @Nullable List<Class<? extends AbstractDao<?, ?>>> daoClasses) {
if (database == null || daoClasses == null) {
return;
}
log("", "********************** 开始数据库升级 **********************");
for (Class<? extends AbstractDao<?, ?>> daoClass : daoClasses) {
DaoConfig daoConfig = new DaoConfig(database, daoClass);
String tableName = daoConfig.tablename;
if (!isTableExists(database, false, tableName)) {
//发现新表
createTable(database, daoClass);
log(tableName, "新增表");
continue;
}
// 获取本地数据库中表的所有列
Map<String, String> oldColumns = getColumns2(database, tableName);
Map<String, String> newColumns = covertMap(daoConfig.properties);
if (isEquals(oldColumns, newColumns)) {
//相同不做处理
continue;
}
Map<String, String> diffMap = getDiff(oldColumns, newColumns);
if (diffMap == null || diffMap.size() <= 0) {
//旧表中的字段新表中都包含，直接走新增字段的操作
//新增的字段
diffMap = getDiff(newColumns, oldColumns);
if (diffMap != null && diffMap.size() > 0) {
//表中新增字段操作
addColumn(database, tableName, diffMap);
log(tableName, "新增字段:" + getStringByMap(diffMap));
}
continue;
}
log("", " ");
log(tableName, "表中修改了字段或者删除了字段:" + getStringByMap(diffMap));
log("", "《《《《《数据迁移开始》》》》》");
//表中修改了字段或者删除了字段，要走导表的流程（SQLite）暂不支持直接删字段或这修改字段
//如果已经存在临时表，则先删除
String tempTableName = tableName.concat("_TEMP");
database.execSQL("DROP TABLE IF EXISTS " + tempTableName + ";");
log("", "删除临时表 --->>> " + tempTableName);
//创建临时表，并将原有表的值复制过来
database.execSQL("CREATE TEMPORARY TABLE " + tempTableName + " AS SELECT * FROM " + tableName + ";");
log("", "创建临时表：" + tempTableName + " --->>> 并将原有表的值复制过来：" + tableName);
//删除原表
deleteTable(database, daoClass);
log("", "删除表 --->>> " + tableName);
//创建新表
createTable(database, daoClass);
log("", "创建表 --->>> " + tableName);
if (newColumns != null) {
List<String> newList = new ArrayList<>();
List<String> oldList = new ArrayList<>();
for (Map.Entry<String, String> entry : newColumns.entrySet()) {
String columnName = entry.getKey();
if (oldColumns.containsKey(columnName)) {
newList.add(columnName);
oldList.add(columnName);
} else {
switch (entry.getValue()) {
case INTEGER:
case BOOLEAN:
case REAL:
newList.add(columnName);
oldList.add("0");
break;
default:
newList.add(columnName);
oldList.add(null);
break;
}
}
}
if (newList.size() > 0) {
//将临时表中的数据插入新表中
String insertSql = "INSERT INTO " + tableName + " (" +
TextUtils.join(",", newList) +
") SELECT " +
TextUtils.join(",", oldList) +
" FROM " + tempTableName + ";";
database.execSQL(insertSql);
log("", "导入数据 " + tempTableName + " --->>> " + tableName);
}
}
//删除临时表
database.execSQL("DROP TABLE " + tempTableName);
log("", "删除临时表 --->>> " + tempTableName);
log(tableName, "《《《《《数据迁移结束》》》》》\r\n");
log("", " ");
}
log("", "********************** 数据库升级结束 **********************");
}
/**
* 转换Map
*
* @param properties Property数组
* @return 转换后的 Map<String, String>
*/
@Nullable
private static Map<String, String> covertMap(@Nullable Property[] properties) {
if (properties == null || properties.length <= 0) {
return null;
}
Map<String, String> map = new HashMap<>();
for (Property property : properties) {
map.put(property.columnName, getTypeByClass(property.type));
}
return map;
}
/**
* 对面两个Map是否相同
*
* @param map1 Map1 Map<String, String>
* @param map2 Map2 Map<String, String>
* @return 内容完全一样也算相同
*/
private static boolean isEquals(@Nullable Map<String, String> map1, @Nullable Map<String, String> map2) {
if (map1 == map2) {
return true;
}
if (map1 != null && map2 != null && map1.size() == map2.size()) {
for (Map.Entry<String, String> entry : map1.entrySet()) {
String columnName = entry.getKey();
if (!map2.containsKey(columnName)) {
return false;
}
if (!TextUtils.equals(map2.get(columnName), entry.getValue())) {
return false;
}
}
return true;
}
return false;
}
/**
* 获取map1中有，map2中没有的数据
*
* @param map1 Map1 Map<String, String>
* @param map2 Map2 Map<String, String>
* @return 列名与类型 Map<columnName, Type>
*/
@Nullable
private static Map<String, String> getDiff(@Nullable Map<String, String> map1, @Nullable Map<String, String> map2) {
if (map1 == null || map1.size() <= 0) {
return null;
}
if (map2 == null || map2.size() <= 0) {
return map1;
}
Map<String, String> diffMap = new HashMap<>();
for (Map.Entry<String, String> entry : map1.entrySet()) {
String columnName = entry.getKey();
String type = entry.getValue();
if (map2.containsKey(columnName) && TextUtils.equals(map2.get(columnName), type)) {
continue;
}
diffMap.put(columnName, type);
}
return diffMap;
}
/**
* 判断表是否存在
*
* @param db Database
* @param isTemp 是否为临时表，true临时表
* @param tableName 表明
* @return true存在，false不存在
*/
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;
}
/**
* 创建表
*
* @param database Database
* @param daoClasses AbstractDao
*/
private static void createTable(Database database, @NonNull Class<? extends AbstractDao<?, ?>> daoClasses) {
reflectMethod(database, "createTable", true, daoClasses);
}
/**
* 删除表
*
* @param database Database
* @param daoClasses AbstractDao
*/
private static void deleteTable(Database database, @NonNull Class<? extends AbstractDao<?, ?>> daoClasses) {
reflectMethod(database, "dropTable", true, daoClasses);
}
/**
* 通过反射，调用dao中对应的创建或删除表的方法
*
* @param database Database
* @param methodName 方法名 createTable创建表，dropTable删除表
* @param isExists 是否存在表，true存在
* @param daoClass 表对应的dao
*/
private static void reflectMethod(Database database, String methodName, boolean isExists,
@NonNull Class<? extends AbstractDao<?, ?>> daoClass) {
if (database == null) {
return;
}
try {
Method method = daoClass.getDeclaredMethod(methodName, Database.class, boolean.class);
method.invoke(null, database, isExists);
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
/**
* 在现有表中添加列
*
* @param database Database
* @param tableName 表名
* @param map 包含columnName,columnType
*/
private static void addColumn(@Nullable Database database, @Nullable String tableName, @Nullable Map<String, String> map) {
if (database == null || TextUtils.isEmpty(tableName)) {
return;
}
if (map == null || map.size() <= 0) {
return;
}
for (Map.Entry<String, String> entry : map.entrySet()) {
String defaultValue = null;
switch (entry.getValue()) {
case NULL:
break;
case TEXT:
defaultValue = "\"\"";
break;
case INTEGER:
case BOOLEAN:
case REAL:
defaultValue = "0";
break;
default:
break;
}
if (defaultValue == null) {
continue;
}
String addSql = "ALTER TABLE %s ADD %s %s DEFAULT %s";
addSql = String.format(addSql, tableName, entry.getKey(), entry.getValue(), defaultValue);
database.execSQL(addSql);
}
}
/**
* 根据class返回对应数据库中保存的数据类型
*
* @param type class
* @return sqlite数据库中存储的数据类型
*/
private static String getTypeByClass(Class<?> type) {
if (type.equals(String.class)) {
return TEXT;
}
if (type.equals(Long.class)
|| type.equals(Integer.class)
|| type.equals(Short.class)
|| type.equals(long.class)
|| type.equals(int.class)
|| type.equals(short.class)) {
return INTEGER;
}
if (type.equals(Boolean.class) || type.equals(boolean.class)) {
//boolean类型在数据库中保存的也是Integer类型
return INTEGER;
}
if (type.equals(Float.class) || type.equals(Double.class) || type.equals(float.class) || type.equals(double.class)) {
return REAL;
}
if (type.equals(Byte.class) || type.equals(byte.class)) {
//byte类型在数据库中保存的也是Integer类型
return INTEGER;
}
final String strTagB = "[B";
if (strTagB.equals(type.getName())) {
//byte[]类型在数据库中保存的也是Integer类型
return BLOB;
}
log("", "未知类型 --> " + type.toString());
return NULL;
}
/**
* 获取表中的所有列
*
* @param db Database
* @param tableName 表名
* @return 列名集合
*/
@Nullable
private static Map<String, String> getColumns2(Database db, String tableName) {
Map<String, String> map = null;
Cursor cursor = null;
try {
cursor = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);
if (cursor == null) {
return null;
}
map = new HashMap<>();
while (cursor.moveToNext()) {
String columnName = cursor.getString(1);
String columnType = cursor.getString(2);
if (TextUtils.isEmpty(columnName)) {
continue;
}
map.put(columnName, columnType);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cursor != null) {
cursor.close();
}
}
return map;
}
/**
* 打印日志
*
* @param label 标签
* @param info 日志内容
*/
private static void log(@Nullable String label, @Nullable String info) {
if (!DEBUG) {
return;
}
if (TextUtils.isEmpty(label)) {
label = "";
} else {
label += " ———>>> ";
}
if (info == null) {
info = "";
}
Log.d("数据库升级", label + info);
}
/**
* 从Map获取内容
*
* @param map 数据 Map<K, V>
* @param <K> Key
* @param <V> Value
* @return String
*/
private static <K, V> String getStringByMap(@Nullable Map<K, V> map) {
if (map == null || map.size() <= 0) {
return "空";
}
StringBuilder result = new StringBuilder();
for (Map.Entry<K, V> entry : map.entrySet()) {
result.append(entry.getKey().toString()).append(",").append(entry.getValue().toString()).append("; ");
}
return "【" + result.toString() + "】";
}
}
网友评论