• Incrementally migrate from SQLite to Room
• Originally written by Florina Muntenescu
• The Nuggets translation Project
• Permanent link to this article: github.com/xitu/gold-m…
• Translator: IllllllIIl
• Proofread by: Tanglie1993, JayMZ1439

Gradually migrate from SQLite to Room

Migrate complex databases to Room with manageable PR

You’ve heard of Room — maybe you’ve seen the documentation, watched a video or two, and decided to start incorporating Room into your project. If you only have a few tables and simple queries in your database, you can easily follow these 7 steps to migrate to Room with a small change of pull Request-like operation.

• 7 Steps To Room: A step by step guide on how to migrate your app to Room medium.com

However, if your database is large or has complex query operations, implementing all entity classes, DAO classes, test classes for DAO, and replacing SQLiteOpenHelper with SQLiteOpenHelper can be time-consuming. You will eventually need a pull request with major changes to implement these and checks. Let’s see how you can gradually migrate from SQLite to Room through manageable PR (pull Request).

If you don’t read the article, you can see the following summary points:

First PR: Create your Entity class, RoomDatabase, and update your custom SQLiteOpenHelper to SupportSQLiteOpenHelper.

The rest of PR: Create the DAO class instead of the code that has Cursor and ContentValue.

Project Settings

We consider the following scenarios:

• Our database has 10 tables, each with a corresponding Model object. For example, if we have the Users table, we have the corresponding User object.
• One inherited fromSQLiteOpenHelper 的 CustomDbHelper.
• LocalDataSourceClass, this is throughCustomDbHelperClasses that access databases.
• We have some pairsLocalDataSourceClass test.

The first PR

Your first PR will contain the minimal changes needed to set up Room.

Create the entity class

If you already have a Model object class for each table’s data, just add annotations for @entity, @primaryKey, and @ColumnInfo.

+ @Entity(tableName = "users")
  public class User {

    + @PrimaryKey
    + @ColumnInfo(name = "userid")
      private int mId;

    + @ColumnInfo(name = "username")
      private String mUserName;

      public User(int id, String userName) {
          this.mId = id;
          this.mUserName = userName;
      }

      public int getId() { return mId; }

      public String getUserName() { returnmUserName; }}Copy the code

Create Room database

Create an abstract class that inherits RoomDatabase. In the @database annotation, list all the Entity classes you have created. Now we don’t have to create the DAO class anymore.

Update your database version number and generate a Migration object. If you haven’t changed the database schema, you still need to generate an empty Migration object for Room to keep the existing data.

@Database(entities = {<all entity classes>}, version = <incremented_sqlite_version>) public abstract class AppDatabase extends RoomDatabase { private static UsersDatabase INSTANCE; static final Migration MIGRATION_<sqlite_version>_<incremented_sqlite_version> = new Migration(<sqlite_version>, <incremented_sqlite_version>) {@override public void migrate(SupportSQLiteDatabase database) { // So there's nothing to do here}};Copy the code

Update classes that use SQLiteOpenHelper

Originally, our LocalDataSource class worked with CustomOpenHelper, now I’m going to update it to use **SupportSQLiteOpenHelper**, This class can be from RoomDatabase. GetOpenHelper ().

public class LocalUserDataSource {
    private SupportSQLiteOpenHelper mDbHelper;
    LocalUserDataSource(@NonNull SupportSQLiteOpenHelper helper) {
       mDbHelper = helper;
    }
Copy the code

Since SupportSQLiteOpenHelper doesn’t inherit SQLiteOpenHelper directly, but rather wraps it around it, we need to change the way we call to get a writable and readable database, And use SupportSQLiteDatabase instead of SQLiteDatabase.

SupportSQLiteDatabase db = mDbHelper.getWritableDatabase();
Copy the code

SupportSQLiteDatabase is a database abstraction layer that provides methods similar to those in SQLiteDatabase. Because it provides a cleaner API for inserting and querying the database, the code also needs to change a bit.

For insert operations, Room removes the optional nullColumnHack parameter. Using SupportSQLiteDatabase. Insert instead of SQLiteDatabase. InsertWithOnConflict.

@Override
public void insertOrUpdateUser(User user) {
    SupportSQLiteDatabase db = mDbHelper.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(COLUMN_NAME_ENTRY_ID, user.getId());
    values.put(COLUMN_NAME_USERNAME, user.getUserName());

    - db.insertWithOnConflict(TABLE_NAME, null, values,
    -        SQLiteDatabase.CONFLICT_REPLACE);
    + db.insert(TABLE_NAME, SQLiteDatabase.CONFLICT_REPLACE,
    + values);
    db.close();
}
Copy the code

To query, SupportSQLiteDatabase provides four methods:

Cursor query(String query);
Cursor query(String query, Object[] bindArgs);
Cursor query(SupportSQLiteQuery query);
Cursor query(SupportSQLiteQuery query, CancellationSignal cancellationSignal);
Copy the code

If you simply use the original query operation, there is nothing to change here. If your query is more complex, you must create a SupportSQLiteQuery through SupportSQLiteQueryBuilder.

For example, we have a users table and just want to get the first user in the table sorted by name. The following is the difference between SQLiteDatabase and SupportSQLiteDatabase implementations.

public User getFirstUserAlphabetically() { User user = null; SupportSQLiteDatabase db = mDbHelper.getReadableDatabase(); String[] projection = { COLUMN_NAME_ENTRY_ID, COLUMN_NAME_USERNAME }; Cursor = db.query(TABLE_NAME, projection, null, -null, null, null, COLUMN_NAME_USERNAME + "ASC", "1"); + SupportSQLiteQuery query = + SupportSQLiteQueryBuilder.builder(TABLE_NAME) + .columns(projection) + .orderby (COLUMN_NAME_USERNAME) +.limit(" 1 ") +.create(); + Cursor cursor = db.query(query);if(c ! =null && c.getCount() > 0){ //read data from cursor
              ...
        }
        if(c ! =null){ cursor.close(); } db.close();return user;
    }
Copy the code

If you haven’t already tested your SQLiteOpenHelper implementation, I highly recommend that you do so before migrating to avoid bugs.

The rest of the PR

Now that your data layer is already using Room, you can start gradually creating DAO classes (with tests) and replacing Cursor and ContentValue code with DAO calls.

Operations like querying the first user by name in the Users table should be defined in the UserDao interface.

@dao public interface UserDao {@query (" SELECT * FROM Users ORDERED BY name ASC LIMIT 1 ") User getFirstUserAlphabetically(); }Copy the code

This method is called in the LocalDataSource.

public class LocalDataSource {
     private UserDao mUserDao;
     public User getFirstUserAlphabetically() {
        returnmUserDao.getFirstUserAlphabetically(); }}Copy the code

---

Migrating a large SQLite database to Room in a single PR will result in many new and updated files. This takes some time to implement, which makes PR harder to check. In the initial PR, OpenHelper provided by RoomDatabase was used to make minimal changes to the code, and then gradually in the following PR, DAO classes were created to replace Cursor and ContentValue code.

To learn more about Room, read these articles:

• 7 Pro-tips for Room: _Learn how you can get the most out of Room_medium.com
• Understanding migrations with Room: _Performing database migrations with the SQLite API always made me feel like I was defusing a bomb — as if I was one… _medium.com
• Testing Room migrations: _In a previous post I explained how database migrations with Room work under the hood. We saw that an incorrect… _medium.com
• Room 🔗 RxJava: _Doing queries in Room with RxJava_medium.com

---

The Nuggets Translation Project is a community that translates quality Internet technical articles from English sharing articles on nuggets. The content covers Android, iOS, front-end, back-end, blockchain, products, design, artificial intelligence and other fields. If you want to see more high-quality translation, please continue to pay attention to the Translation plan of Digging Gold, the official Weibo, Zhihu column.