Some time ago, THE ormLite framework was used to implement a requirement, which involved upgrading the database version. Adding/deleting fields, or adding/deleting tables, requires an update to the database version. The simplest and most violent way to do this is to delete the tables that need to be updated in OrmLiteSqliteOpenHelper#onUpgrade() and then create new tables from scratch. Although this method is simple and effective, data in the data table will be lost, which will seriously affect the user experience. Therefore, you should upgrade the version while preserving the existing data. The following are some changes made after referring to other people’s implementation, if there are unreasonable places or better scheme welcome to point out. Download the source code
- New data table
In the constructor of OrmLiteHlper, the list of Class object instances of the bean Class annotated with the @databasetable annotation is retrieved by reflection through the classes under the package. OnCreate () then creates the nonexistent table by iterating through the list of object instances; However, after the new bean class is added, the database version number needs to be changed before the DatebaseHelper class calls onUpgrade() and calls onCreate() in that method to create the new data table.
public class DatabaseHelper extends OrmLiteSqliteOpenHelper {
private static final String TAG = "DatabaseHelper";
private static DatabaseHelper instance;
// Database name
private static String DATABASE_NAME = "mydb.db";
// Database version number
private static int DATABASE_VERSION = 20;
private Map<String, Dao> daoMap;
privateArrayList<Class<? >> clazzList;static DatabaseHelper getInstance(Context context) {
// The code is omitted here... , complete source: https://github.com/zjjjia/DbVersionManager.return instance;
}
public synchronized Dao getDao(Class clazz) throws SQLException {
/ / omit....return dao;
}
private DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
daoMap = new HashMap<>();
clazzList = new ArrayList<>();
if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.N) {
// Get the list of Class object instances of all table bean classes under the bean package
clazzList.addAll(DatabaseUtil.getClasses(context, "com.example.dbversionmanager.bean"));
}
Log.d(TAG, "DatabaseHelper");
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource) {
Log.d(TAG, "onCreate");
if(clazzList ! =null) {
for(Class<? > clazz : clazzList) {try {
// Traverses the list of bean class object instances, creating the corresponding table if it does not exist
if (!DatabaseUtil.tableIsExist(sqLiteDatabase, DatabaseUtil.extractTableName(clazz))) {
TableUtils.createTable(connectionSource, clazz);
}
} catch(SQLException e) { e.printStackTrace(); }}}}@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource, int oldVersion, int newVersion) {
for (Class clazz : clazzList) {
// Iterate through the list of bean class object instances, updating the table if it exists
if (DatabaseUtil.tableIsExist(sqLiteDatabase, DatabaseUtil.extractTableName(clazz))) {
DatabaseUtil.updateTable(sqLiteDatabase, connectionSource, clazz);
} else { // If not, call onCreate() to create the tableonCreate(sqLiteDatabase, connectionSource); }}}@Override
public void close(a) {
super.close();
// Some code omitted....... }}Copy the code//DatabaseUtil.java
// Get the list of Class objects for the bean Class annotated with the @databasetable annotation below the package
@RequiresApi(api = Build.VERSION_CODES.N)
public staticArrayList<Class<? >> getClasses(Context mContext, String packageName) { ArrayList<Class<? >> classes =new ArrayList<>();
try {
String packageCodePath = mContext.getPackageCodePath();
DexFile df = new DexFile(packageCodePath);
String regExp = "^" + packageName + ".\\w+$";
for (Enumeration iter = df.entries(); iter.hasMoreElements(); ) {
String className = (String) iter.nextElement();
if (className.matches(regExp)) {
Class clazz = Class.forName(className);
if(clazz.getDeclaredAnnotation(DatabaseTable.class) ! =null) { classes.add(clazz); }}}}catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
return classes;
}
Copy the code- Add/delete columns from a table
Rename the original table to a temporary table, then create a new table and copy the original data from the temporary table to the new table. The following is a local method:
//DatabaseUtil.java
// Update the data table
public static <T> void updateTable(SQLiteDatabase db, ConnectionSource connectionSource, Class<T> clazz) {
String tableName = extractTableName(clazz);
db.beginTransaction();
// Rename the original table to a temporary table
try {
String tempTableName = tableName + "_temp";
String sql = "ALTER TABLE " + tableName + " RENAME TO " + tempTableName;
db.execSQL(sql);
Log.d(TAG, "updateTable: " + sql);
sql = TableUtils.getCreateTableStatements(connectionSource, clazz).get(0);
db.execSQL(sql);
// Get the string form of the fields in the table to be copied
String columns = getCopyColumnsStr(db, tableName, tempTableName);
// When there are multiple tables, not all tables will be changed, so there is no need to copy data from unchanged tables
If columns are empty, the table does not need to be created. Delete the new table and change the name of the temporary table back
if (columns == null) {
sql = "DROP TABLE IF EXISTS " + tableName;
Log.d(TAG, "updateTable: " + sql);
db.execSQL(sql);
sql = "ALTER TABLE " + tempTableName + " RENAME TO " + tableName;
Log.d(TAG, "updateTable: " + sql);
db.execSQL(sql);
} else { // The table column is changed, the data from the original table is copied to the new table, and the temporary table is deleted
sql = "INSERT INTO " + tableName + "(" + columns + ") SELECT " + columns + " FROM " + tempTableName;
Log.d(TAG, "updateTable: " + sql);
db.execSQL(sql);
// Drop temporary tables
sql = "DROP TABLE IF EXISTS " + tempTableName;
Log.d(TAG, "updateTable: " + sql);
db.execSQL(sql);
}
db.setTransactionSuccessful();
} catch (SQLException e) {
e.printStackTrace();
} finally{ db.endTransaction(); }}Copy the code