I have an project that deals with an external database (sqlite database file) and I want to move to the room database as follows
- The contents of the database (student_database.db) are initially entered by a third-party application, for example (DB Browser for SQLite).
- When I run my app the datebase room is created and put the data from student_database.db 3 My app displays data from (room db) in Recycler View
- New data can be added to the database (room)
Here are some files of my project
@Entity(tableName = "books_table")
public class Book {
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "book_id")
private long id;
@ColumnInfo(name = "book_name")
private String name;
@ColumnInfo(name = "all")
private long all;
@ColumnInfo(name = "profile_id")
private long profileId;
public Book() {
}
public Book(String name, long all, long profileId) {
this.name = name;
this.all = all;
this.profileId = profileId;
}
@Dao
public interface BookDAO {
@Insert
void insertBook(Book... book);
@Update
void updateBook(Book... book);
@Entity (tableName = "profiles_table")
public class Profile {
@PrimaryKey(autoGenerate = true)
long id;
@ColumnInfo (name = "profile_name")
String profileName;
public Profile() {
}
public Profile(long id, String profileName) {
this.id = id;
this.profileName = profileName;
}
public Profile(String profileName) {
this.profileName = profileName;
}
@Dao
public interface ProfileDAO {
@Insert
void insertProfile(Profile... profile);
@Update
void updateProfile(Profile... profile);
@Database(entities = {Book.class , Profile.class}, version = 1, exportSchema = false)
public abstract class MyRoomDatabase extends RoomDatabase {
public abstract BookDAO bookDao();
public abstract ProfileDAO profileDAO();
private static volatile MyRoomDatabase INSTANCE;
private static final int NUMBER_OF_THREADS = 4;
static final ExecutorService databaseWriteExecutor =
Executors.newFixedThreadPool(NUMBER_OF_THREADS);
static MyRoomDatabase getDatabase(final Context context) {
if (INSTANCE == null) {
synchronized (MyRoomDatabase.class) {
if (INSTANCE == null) {
INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
MyRoomDatabase.class, "student_prepare_room_database")
.addCallback(sRoomDatabaseCallback)
.build();
}
}
}
return INSTANCE;
}
private static RoomDatabase.Callback sRoomDatabaseCallback = new RoomDatabase.Callback() {
@Override
public void onCreate(@NonNull SupportSQLiteDatabase db) {
super.onCreate(db);
// If you want to keep data through app restarts,
// comment out the following block
databaseWriteExecutor.execute(() -> {
// Populate the database in the background.
// If you want to start with more words, just add them.
});
}
};
}
student_database.db
CREATE TABLE "profile_table" ("id" INTEGER,"profile_name" TEXT,PRIMARY KEY("id"));
[1]: 
Step 2 - Create the Asset The assets directory was created and the database copied and pasted as student_database.db :-
- Note a copy of this was made for re-runability
Step 3 add createFromAsset and other changes to run initial test
The MyRoomDatabase class was changed to add .createFromAsset :-
if (INSTANCE == null) {
INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
MyRoomDatabase.class, "student_prepare_room_database")
.createFromAsset("student_database.db") //<<<<< STEP 3 - ADDED
.addCallback(sRoomDatabaseCallback) //<<<< added for convenience/brevity
.allowMainThreadQueries()
.build();
}
Additionally BookDAO was amended by adding:-
@Query("SELECT * FROM books_table")
List<Book> getAllBooks();
- to allow simple access to the database (unless accessed the database won't be opened checked etc.).
Lastly MainActivity was coded as:-
public class MainActivity extends AppCompatActivity {
MyRoomDatabase db;
BookDAO bookDAO;
ProfileDAO profileDAO;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = MyRoomDatabase.getDatabase(this);
bookDAO = db.bookDao();
profileDAO = db.profileDAO();
for (Book b: bookDAO.getAllBooks()) {
Log.d("BOOKINFO","Book is " b.getName());
}
}
}
Step 4 Room SQL
The project was then compiled (getters/setters added) the createALLTables being:-
- Note AUTOINCREMENT on the profile table.
STEP 5 RUN (see Note)
- Note This step is purely to demonstrate what happens if the database is not altered.
With all the above, if the App is then run (not that it should be as it will fail). As expected it fails with the Expected/Found mismatch issue BUT clearly it has copied the asset:-
2022-01-05 07:59:02.200 1941-1941/? D/AndroidRuntime: Shutting down VM
2022-01-05 07:59:02.202 1941-1941/? E/AndroidRuntime: FATAL EXCEPTION: main
Process: a.a.so70580333prepackageddatabase, PID: 1941
java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so70580333prepackageddatabase/a.a.so70580333prepackageddatabase.MainActivity}: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: books_table(a.a.so70580333prepackageddatabase.Book).
Expected:
TableInfo{name='books_table', columns={all=Column{name='all', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, book_id=Column{name='book_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, book_name=Column{name='book_name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, profile_id=Column{name='profile_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
Found:
TableInfo{name='books_table', columns={all=Column{name='all', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, profile_id=Column{name='profile_id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, book_id=Column{name='book_id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, book_name=Column{name='book_name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2913)
STEP 6 Amending the pre-packaged database
The 3rd party tool is revisited and the following is used:-
DROP TABLE IF EXISTS books_table_original;
DROP TABLE IF EXISTS profile_table_original;
ALTER TABLE books_table RENAME TO books_table_original;
ALTER TABLE profile_table RENAME TO profile_table_original;
/* COPIED FROM createAllTables method */
CREATE TABLE IF NOT EXISTS `books_table` (`book_id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `book_name` TEXT, `all` INTEGER NOT NULL, `profile_id` INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS `profiles_table` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `profile_name` TEXT);
INSERT INTO profiles_table SELECT * FROM profile_table_original;
INSERT INTO books_table SELECT * FROM books_table_original;
/* CLEANUP */
DROP TABLE IF EXISTS books_table_original;
DROP TABLE IF EXISTS profile_table_original;
VACUUM;
note that the expected/found issues were not even looked at
- if they were looked at you can see that the All column is expected to be NOT NULL but it found that the All column was defined without and thus allows NULLS (mismatch).
- likewise for the book_id column (another mismatch)
- likewise for the profile_id column (another mismatch)
- if these are fixed then the profiles_table would then result in more mismatches as the table names are different (altered/corrected in the above).
note that if foreign key constraints are introduced then the order in which tables are deleted matters as does the order in which they are created (but I believe room sorts the tables accordingly).
The above assumes that the column sequence is the same for the tables in the pre-packaged database as they are in SQL built by Room. If the sequence differs then the INSERT can be modified to either:-
specify the the columns to be inserted in the order they are selected e.g.
INSERT INTO profiles_table (id,profile_name) SELECT * FROM profile_table_originalspecify the SELECT columns in the order according to definition e.g.
INSERT INTO profiles_table SELECT (id,profile_name) FROM profile_table_originalspecify columns rather than * for both the INSERT and the SELECT e.g.
INSERT INTO profiles_table (profile_name,id) SELECT (profile_name,id) FROM profile_table_original- Note that this last example purposefully uses a different column order to demonstrate a different order. It does not affect the order of the columns in either table just the order of the columns in the intermediate output that is built within SQLite.
After running the above, the database is saved and copied to the assets folder.
- in this case the database was initially pasted as AlteredFroRoom_student_database.db, the student_database.db ass deleted and then the AlterForRoom_student_database.db copied and then pasted as student_database.db. Note for an App that is to be distributed you would not want to have additional copies of the database as this increases the size of the distribution.
The App is then uninstalled and then rerun and it runs successfully.
- Note that as the database has been copied, the onCreate callback is not invoked (the onOpen would be).
The Log includes:-
2022-01-05 08:56:15.975 D/BOOKINFO: Book is Book1
2022-01-05 08:56:15.975 D/BOOKINFO: Book is Book2
2022-01-05 08:56:15.975 D/BOOKINFO: Book is Book3
2022-01-05 08:56:15.975 D/BOOKINFO: Book is Book4
2022-01-05 08:56:15.975 D/BOOKINFO: Book is Book5
2022-01-05 08:56:15.975 D/BOOKINFO: Book is Book6
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book7
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book8
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book9
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book10
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book11
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book12
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book13
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book14
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book15
2022-01-05 08:56:15.976 D/BOOKINFO: Book is Book16
....