我有一個處理外部資料庫（sqlite 資料庫檔案）的專案，我想移動到房間資料庫，如下所示
- 資料庫 (student_database.db) 的內容最初由第三方應用程式輸入，例如 (DB Browser for SQLite)。
- 當我運行我的應用程式時，會創建資料庫房間并將資料從 student_database.db 3 我的應用程式在 Recycler View 中顯示來自 (room db) 的資料
- 可以將新資料添加到資料庫（房間）
這是我專案的一些檔案
@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]：
第 2 步 - 創建資產 資產目錄已創建，資料庫復制并粘貼為 student_database.db ：-
- 請注意，此副本是為了可重新運行而制作的
第 3 步添加createFromAsset和其他更改以運行初始測驗
MyRoomDatabase 類已更改為添加.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();
}
此外， BookDAO還通過添加以下內容進行了修改：-
@Query("SELECT * FROM books_table")
List<Book> getAllBooks();
- 允許對資料庫的簡單訪問（除非訪問資料庫不會打開檢查等）。
最后MainActivity被編碼為：-
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());
}
}
}
步驟 4 房間 SQL
然后編譯專案（添加了 getter/setter）createALLTables 是：-
- 請注意組態檔表上的 AUTOINCREMENT。
步驟 5 運行（見注）
- 注意此步驟純粹是為了演示如果資料庫未更改會發生什么。
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.
- 請注意，由于已復制資料庫，因此不會呼叫 onCreate 回呼（將呼叫 onOpen）。
日志包括：-
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
....
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/410121.html
標籤：