Android Room: Create table based on external input-CodePudding

I am developing a Java-based Android App where I am using Room. The App is connected to a server from which it downloads project specific configurations. One of these configurations is the setup of a table. I have a table which number and types of columns differ for each project. I need to have a local copy of this table on the phone to store data in case no internet connection is available. The configuration of the table contains the name of the table and the column composition like

[{
    "name":"column1",
    "datatype":"VARCHAR(20)"
},
{
    "name":"column2",
    "datatype":"INT(5)"
},
{
    "name":"column3",
    "datatype":"DOUBLE"
}]

How can I generate such a table with Room? Generating the create query is not a problem but where should I execute it. Additionally, How can I insert, update and query data from the table? Is it possible to generate such SQL queries and execute them? Is there something like a row mapper which can be used to read the queried data from the table? If this is not possible, any idea how I can solve it otherwise? Thank you for your support.

CodePudding user response：

You won't be able to do this with Room and use Room's object mapping because Room builds tables according to the mapping of objects.

That is a table is defined according to a class annotated with @Entity and defined as an Entity to the database.

It undertakes much at compile time such as verification of queries building it's component creation SQL. At run time as part of opening the database it checks for the expected components and if it finds a difference will fail/crash.

I did at one time have a project ongoing that built entities/classes based upon a database which could be copied into a project but with Room changing and introducing features (e.g. DEFAULT constraints which were ignored but then introduced).

You can have components, including tables, that are not controlled/known by Room which will not violate the run time schema checking but then you would have to use a SupportSQLiteDatabase so you might as well use the native SQLite.

How can I generate such a table with Room?

In the case of the example then you would need a class annotated with @Entity. However, one rule that Room imposes is that there must be a PRIMARY KEY. So an option could be to introduce one. Lets say a column called id

Another rule that Room enforces are that column types can only be INTEGER, TEXT, REAL or BLOB. However this is determined from the variable type.

So VARCHAR(50) would be for a String,
INT(5) would likely store long or int long would cover all.
DOUBLE would likely store a DOUBLE (REAL) so double.

So the class(entity) could be :-

@Entity
class TableX {
    @PrimaryKey
    Long id = null;
    String column1;
    long column2;
    double column3;
}

the table name would be TableX

How can I insert, update and query data from the table?

You use an interface or abstract class annotated with @Dao, so for the above you could, for example have:-

@Dao
abstract class TableXDao {
    @Insert
    abstract long insert(TableX tableX);
    @Insert
    abstract long[] insert(TableX...tableX);
    @Query("INSERT INTO TableX (column1,column2,column3) VALUES(:column1,:column2,:column3)")
    abstract long insert(String column1,long column2, double column3);
    @Update
    abstract int update(TableX tableX);
    @Update
    abstract int update(TableX...tableX);
    @Query("UPDATE tablex set column1=:newColumn1, column2=:newColumn2,column3=:newColumn3 WHERE id=:id")
    abstract int update(long id, String newColumn1, long newColumn2, double newColumn3);
    @Query("SELECT * FROM tablex")
    abstract List<TableX> getAllTableXRows();
    
}

Note the 3 forms of Insert/Update the @Insert/@Update uses the convenience methods (based upon passing the object or objects) the @Query uses a more free-format/adaptable approach.

Although not asked for Room needs to know about the Database itself so another class, annotated with @Database is required, this will annotation defines the Entities that form the database, the version number (and other options). The class should extend the RoomDatabase class, it must be an abstract class or implement the abstract method createOpenHelper (typically the former). So :-

@Database(entities = {TableX.class},version = 1)
abstract class TheDatabase extends RoomDatabase {
    abstract TableXDao getTableXDao();
    
    /* Often :- */
    private static volatile TheDatabase instance = null;
    public TheDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(
                    context,TheDatabase.class,"thedatabase.db"
            )
                    .build();
        }
        return instance;
    }
}

When the above is compiled much is undertaken, the build will include a warning:-

    E:\AndroidStudioApps\SO70351715JavaSQLite\app\src\main\java\a\a\so70351715javasqlite\TheDatabase.java:10: warning: Schema export directory is not provided to the annotation processor so we cannot export the schema. You can either provide `room.schemaLocation` annotation processor argument OR set exportSchema to false.
abstract class TheDatabase extends RoomDatabase {
         ^
1 warning

purposefully allowed to happen to demonstrate the extensive compile time checking

Additionally it will generated quite a bit of java code :-

the TableXDao_Impl class being code that is invoked when the Dao's are used the TheDatabase_Impl class being code for accessing the database, including the creation of the tables in the createAllTables method:-

  @Override
  public void createAllTables(SupportSQLiteDatabase _db) {
    _db.execSQL("CREATE TABLE IF NOT EXISTS `TableX` (`id` INTEGER, `column1` TEXT, `column2` INTEGER NOT NULL, `column3` REAL NOT NULL, PRIMARY KEY(`id`))");
    _db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
    _db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '5f1c580621c8b86aef3b3cccc44d8d76')");
  }

As you can see the room_master_table is created and populated with a row that stores a hash, this is part of the verification, if the hash is changed then room will know that the schema has changed (the source code has been changed).

Is there something like a row mapper which can be used to read the queried data from the table?

As can be seen its all done with the compiled code via the annotations so there is not a map but the expectation that everything is known/defined at compile time.

If this is not possible, any idea how I can solve it otherwise? Use Native SQLite or manage the server database and the room database as a whole.