Room entity with two tables?

Question

Is it possible to create Room Entities with two tables? I know it's not best practice to store duplicate data, but I need a backup table to restore data to its original state.

@Entity(tableName = "models", "backupModels")
public class Entity {

    @PrimaryKey
    @ColumnInfo(name = "id")
    private int id;

    @ColumnInfo(name = "model")
    private String model;

    public Entity() {

    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getModel() {
        return model;
    }

    public void setModel(String model) {
        this.model = model;
    }
}

Answer 1

Is it possible to create Room Entities with two tables?

No the @Entity defines a single table only.

You could just copy the Entity renaming the class and using another set of Dao's.

However, if it's only for back purposes then perhaps consider the following working example (designed to just run once).

This is based upon the CREATE TABLE ? AS SELECT statements and for the restore INSERT INTO table SELECT ...;

You only need the single Entity without any changes (other than removal of the 2nd table name). Entity :-

@androidx.room.Entity(tableName = "models"/* can't do, "backupModels"*/)
public class Entity {

    @PrimaryKey
    @ColumnInfo(name = "id")
    private int id;

    @ColumnInfo(name = "model")
    private String model;

    public Entity() {

    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getModel() {
        return model;
    }

    public void setModel(String model) {
        this.model = model;
    }
}

A very basic @Dao AllDao (to allow demonstration):-

@Dao
abstract class AllDao {

    @Insert
    abstract long insert(Entity entity);

    @Query("SELECT * FROM models")
    abstract List<Entity> getAllModels();
}

The backup/restore code is coded within the @Database TheDatabase :-

@Database(entities = {Entity.class},version = 1)
abstract class TheDatabase extends RoomDatabase {
    abstract AllDao getAllDao();

    private static final String SUFFIX_BACKUP = "_backup", SUFFIX_OLD = "_old";

    private static volatile TheDatabase instance = null;

    public static TheDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(context,TheDatabase.class,"my.db")
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }

    public static void backupTable(String table) {
        SupportSQLiteDatabase db = instance.getOpenHelper().getWritableDatabase();
        db.beginTransaction();
        db.execSQL("DROP TABLE IF EXISTS `" + table + SUFFIX_BACKUP + "`;");
        db.execSQL("CREATE TABLE `" + table + SUFFIX_BACKUP + "` AS SELECT * FROM `" + table + "`");
        db.setTransactionSuccessful();
        db.endTransaction();
    }

    public static void restoreTable(String table) {
        SupportSQLiteDatabase db = instance.getOpenHelper().getWritableDatabase();
        db.beginTransaction();
        db.execSQL("DROP TABLE IF EXISTS `" + table + SUFFIX_OLD + "`");
        db.execSQL("CREATE TABLE `" + table + SUFFIX_OLD + "` AS SELECT * FROM `" + table +"`;");
        db.execSQL("DELETE FROM `" + table + "`;");
        db.execSQL("INSERT INTO `" + table + "` SELECT * FROM `" +table + SUFFIX_BACKUP + "`;");
        db.execSQL("DROP TABLE IF EXISTS `" + table + SUFFIX_OLD + "`;");
        db.setTransactionSuccessful();
        db.endTransaction();
    }
}

• Note the restore and backup methods can cater for any table. However, adherence to constraints may have to be considered (e.g. if using Foreign Key constraints).

• The restore method creates a further backup (optional) as a just-in-case backup (models_old). DROPping the models_old table is optional.

Putting it all together and demonstrating is MainActivity :-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = TheDatabase.getInstance(this);
        dao = db.getAllDao();

        Entity e = new Entity();
        e.setId(1);
        e.setModel("Model1");
        dao.insert(e);
        TheDatabase.backupTable("models");
        e.setId(2);
        e.setModel("Model2");
        dao.insert(e);
        for(Entity entity: dao.getAllModels()) {
            Log.d("PRERESTORE","ID = " + entity.getId() + " Model = " + entity.getModel());
        }
        TheDatabase.restoreTable("models");
        for(Entity entity: dao.getAllModels()) {
            Log.d("POSTRESTORE","ID = " + entity.getId() + " Model = " + entity.getModel());
        }
    }
}

• So this:-

  1. adds 1 row to the models table.
  2. does the backup.
  3. adds a 2nd row to the models table.
  4. writes the contents of the models table to the log (2 rows).
  5. restores the models table (to when 1 row existed).
  6. writes the contents of the restore models table to the log (1 row).

Result

When run the log includes :-

2021-07-27 07:41:11.991 D/PRERESTORE: ID = 1 Model = Model1
2021-07-27 07:41:11.992 D/PRERESTORE: ID = 2 Model = Model2


2021-07-27 07:41:11.996 D/POSTRESTORE: ID = 1 Model = Model1

• NOTE Please consider the restrictions when using CREATE TABLE ... AS SELECT ....