I checked this link: 
Within the class there will be a method name createAllTables :-
@SuppressWarnings({"unchecked", "deprecation"})
public final class TheDatabase_Impl extends TheDatabase {
@Override
protected SupportSQLiteOpenHelper createOpenHelper(DatabaseConfiguration configuration) {
final SupportSQLiteOpenHelper.Callback _openCallback = new RoomOpenHelper(configuration, new RoomOpenHelper.Delegate(1) {
@Override
public void createAllTables(SupportSQLiteDatabase _db) {
_db.execSQL("CREATE TABLE IF NOT EXISTS `Table1` (`id` INTEGER, `text_column` TEXT, `notnull_text_column` TEXT NOT NULL, `real_column` REAL NOT NULL, `blob_column` BLOB, `notnull_real_column_also_indexed` REAL NOT NULL, PRIMARY KEY(`id`))");
_db.execSQL("CREATE INDEX IF NOT EXISTS `index_Table1_notnull_real_column_also_indexed` ON `Table1` (`notnull_real_column_also_indexed`)");
_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, 'a6ca75e8ee6037ad13c258cdc0405ef1')");
}
@Override
public void dropAllTables(SupportSQLiteDatabase _db) {
_db.execSQL("DROP TABLE IF EXISTS `Table1`");
if (mCallbacks != null) {
for (int _i = 0, _size = mCallbacks.size(); _i < _size; _i ) {
mCallbacks.get(_i).onDestructiveMigration(_db);
}
}
}
....
As can be seen there is an execSQL for the table1 table, another for the index (as index = true was specified in the @ColumnInfo annotation).
the room_master_table is used by room to store a hash of the schema, this is not needed, and should NOT be created in the pre-packaged database that will be copied into the assets folder.
- The hash will change if the schema changes (the @Entity annotated classes)
Nuances
- if you look closely, you will see that both the real_column and the notnull_real_column have the NOT NULL constraint, but only the latter has the
@NonNullannotation. This is because double, is a primitive and ALWAYS has a value, so Room implicitly applies the NOT NULL constraint. If the NOT NULL constraint is not coded when creating the pre-packaged database then after the asset has been copied, when running the App, an exception will occur as the database that was found (the one copied from the asset) will be different (in Room's view) from what Room expected (the schema according to the @Entity annotated classed defined in the list of entities in the @Database annotation). Hence, why it is suggested to create the schema via room, extract the generated SQL and use this to create the pre-packaged database. This ensures that the database schema is as expected. - Note this is just one example of the nuances
- if you look closely, you will see that both the real_column and the notnull_real_column have the NOT NULL constraint, but only the latter has the
Continuing with a working example
One thing that often trips new users of SQLite and also Room is that when you instantiate the Database class, is that, it does not then create or open the database. It is not until an attempt is made to access the database (changing or extracting data from the database) that the database is opened and if necessary created and in the case of a pre-populated database copied from the asset (or file, normally the former).
As such, in preparation, for this a (can be one or more), an interface or abstract class annotated with @Dao is created. In this case AllDAO as per:-
@Dao
abstract class AllDAO {
@Insert
abstract long insert(Table1 table1);
@Query("SELECT * FROM table1")
abstract List<Table1> getAllTable1s();
}
- using either the
insertor thegetAllTable1swould access the database.
The @Dao annotated class(es) have to be known/defined to Room, typically the @Database class includes this so the TheDatabase class could then be:-
@Database(entities = {Table1.class}, version = 1, exportSchema = false)
abstract class TheDatabase extends RoomDatabase {
abstract AllDAO getAllDAO(); //<<<<< ADDED to allow use of the database
private volatile static TheDatabase instance = null;
public static TheDatabase getInstance(Context context) {
if (instance == null) {
instance = Room.databaseBuilder(context,TheDatabase.class,"the_database.db")
.allowMainThreadQueries() /* for convenience and brevity */
.createFromAsset("the_database.db")
.build();
}
return instance;
}
}
So the App is virtually ready (using it in an activity will be dealt with later).
Now the pre-packaged database can be perpared/built using an SQLite tool (Navicat for SQLite has been used in this case, it shouldn't matter which).
A connection is made and opened, this detailing where the database file will be stored. (see the tool's help if needed). In this case the database is named SOQuestions (already exists) :-
New Query is clicked, and the SQL for the user defined tables is pasted, as well as the indexes. e.g.:-
So the table(s) and indexes now exist but are unpopulated. So now to populate the database by inserting some records. A query will be used (in this case as it's only an example queries won't be saved, they could if desired).
So the existing SQL is deleted and replaced with (not deletes all rows, so it is rerunnable) and then run:-
The resultant data being :-
The database should be saved. It is suggested that the database/connection is closed and then reopened to check that the data has been saved and then finally closed again (this was done).
The database is now ready to be copied into the assets folder (which currently doesn't exist). So create the assets folder in the project (back to Android Studio). File/Directory was used to select src\main\assets :-
to get :-
The file is copied, from Windows Explorer (right click on the file and copy)
and pasted (right click on the assets folder in Andriod Studio and Paste), renaming it to the_database.db (the database name, as per the createFromAsset (could use soquestions.db as the asset file name))
resulting in :-
Now to running the App by using the database in an activity (note that for brevity and convenience this is run on the main thread).
The activity code :-
public class MainActivity extends AppCompatActivity {
TheDatabase dbInstance;
AllDAO dao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbInstance = TheDatabase.getInstance(this); /* get DB Instance */
dao = dbInstance.getAllDAO(); /* get the appropriate Dao */
logAllRowsFromTable1("PRENEWDATA"); /* WILL INITIATE THE ASSET COPY (if DB does not exist) */
Table1 newTable1Row = new Table1();
newTable1Row.text_column = "a new row";
newTable1Row.blob_column = new byte[30];
newTable1Row.notnull_text_column = " the new nottnull_text_column";
newTable1Row.real_column = 4444.55555;
newTable1Row.notnull_real_column_also_indexed = 7777.8888;
dao.insert(newTable1Row); /* IF NOT INITIATED ABOVE WILL INITIATE THE ASSET COPY (if DB does not exist)*/
logAllRowsFromTable1("POSTNEWDATA");
}
void logAllRowsFromTable1(String suffix) {
for (Table1 t: dao.getAllTable1s()) {
Log.d("DB-" suffix,
"ID is " t.real_column
"\n\tTEXT_COLUMN is " t.text_column
"\n\t NOTNULL_TEXT_COLUMN is " t.notnull_text_column
"\n\t REAL_COLUMN is " t.real_column
"\n\t NOTNULL_REAL_COLUMN... is " t.notnull_real_column_also_indexed
/* not doing the blob so as not to complicate matters */
);
}
}
This will first output some of the data, for all rows, from the pre-packaged database to the log. It will then add a new run (each run, it is only a demo/example) and then output some of the data for all rows, from the updated (new row) database.
e.g. :-
2022-04-22 11:00:43.689 D/DB-PRENEWDATA: ID is 10.3333
TEXT_COLUMN is some text
NOTNULL_TEXT_COLUMN is some notnull text
REAL_COLUMN is 10.3333
NOTNULL_REAL_COLUMN... is 3.1
2022-04-22 11:00:43.689 D/DB-PRENEWDATA: ID is 11.3333
TEXT_COLUMN is null
NOTNULL_TEXT_COLUMN is more not null text
REAL_COLUMN is 11.3333
NOTNULL_REAL_COLUMN... is 4.1
2022-04-22 11:00:43.692 D/DB-POSTNEWDATA: ID is 10.3333
TEXT_COLUMN is some text
NOTNULL_TEXT_COLUMN is some notnull text
REAL_COLUMN is 10.3333
NOTNULL_REAL_COLUMN... is 3.1
2022-04-22 11:00:43.692 D/DB-POSTNEWDATA: ID is 11.3333
TEXT_COLUMN is null
NOTNULL_TEXT_COLUMN is more not null text
REAL_COLUMN is 11.3333
NOTNULL_REAL_COLUMN... is 4.1
2022-04-22 11:00:43.692 D/DB-POSTNEWDATA: ID is 4444.55555
TEXT_COLUMN is a new row
NOTNULL_TEXT_COLUMN is the new nottnull_text_column
REAL_COLUMN is 4444.55555
NOTNULL_REAL_COLUMN... is 7777.8888
- blank lines added to distinguish between the two sets of output
Android Studio's App Inspection can be used to see the actual data:-
