会议室-使用外部SQLite和内部数据库 [英] Room - Using external SQLites as well as internal DB
问题描述
在我们公司，我们目前正在评估切换到Room来管理我们的移动设备数据库.我们之所以这样做，主要是因为继承支持和可测试性增强.
At our company, we are currently evaluating switching to Room for managing our mobile devices databases. The main reasons we want to do that is because of inheritance support and increased testability.
当前，我们正在使用GreenDao访问内部数据库(存储会话数据，设置，用户数据和类似内容的内部数据库)并访问我们的外部数据库(从FTP服务器检索并存储在其中的sqlite) /sdcard/Android/app-folder/中的子文件夹；这些子文件夹提供了我们在业务逻辑中使用的数据.
Currently, we are using GreenDao to access an internal database (on which the session data, settings, user data and similar things are stored) and to access our external databases (sqlites that we retrieve from an FTP-Server and store in subfolders in /sdcard/Android/app-folder/; these provide the data we use in our business logic).
我们需要的内容:我们需要能够将外部数据库附加到内部Room数据库，而无需更改它们存储在其中的路径.添加它们之后，我们希望能够将数据库与房间实体"，"Dao"和数据库"对象一起使用. 有什么可能的方法来实现这一目标?
What we need: We need to be able to use attach the external databases to the internal Room-Database without changing the path they are stored in. After adding them, we want to be able to use the databases with the Room Entity, Dao and Database objects. Is there any possible way to achieve this?
客房版本::1.1.1(支持库)
Room-Version: 1.1.1 (Support-Libraries)
API级: 15
推荐答案
我们需要什么:
我们需要能够将外部数据库附加到 内部Room-Database，而不会更改它们存储在其中的路径. 添加它们之后，我们希望能够将数据库与 Room实体，Dao和Database对象.有什么办法可以 实现这一目标?
We need to be able to use attach the external databases to the internal Room-Database without changing the path they are stored in. After adding them, we want to be able to use the databases with the Room Entity, Dao and Database objects. Is there any possible way to achieve this?
不附加可能会更容易，原因是您可以利用一个单独的Room Database实例.如果没有这种情况，您将不需要单独的DAO来满足附加的架构名称(我相信).说下面的示例(基于我为某人玩的东西，因此列名相当混乱).
It might be easier to not attach, the reason being that you could utilise a separate Room Database instance. Without you'd need to have separate DAO's to cater for the attached schema name (I believe). Saying that the example below (based upon something I was playing with for someone and hence the rather confusing column names).
例如假设ATTACH DATABASE .... AS other(附加模式为 other )，则代替(对于主数据库)
e.g. assuming ATTACH DATABASE .... AS other (the attached schema being other) then instead of (for the main database)
@Query("SELECT * FROM AllUsers")
List<AllUsers> getAllAllUsers();
您需要一个免费的:-
@SkipQueryVerification
@Query("SELECT * FROM other.AllUsers")
List<AllUsers> getOtherAllAllUsers();
等
但是，如果您有类似(主要)的东西:-
However, if you instead had something like (for the main) :-
mLPDB = Room.databaseBuilder(this,LoanPaymentDatabase.class,"mydb").allowMainThreadQueries().build();
mLPDB_DAO = mLPDB.mDao();
与(对于另一个)一起:-
Along with (for the other) :-
mOtherDB = Room.databaseBuilder(this,LoanPaymentDatabase.class,OtherDatabaseHelper.DBNAME).allowMainThreadQueries().build();
mOtherDAO = mOtherDB.mDao();
然后，您可以使用同一个DAO访问这两者.
Then you can access both using the same DAO.
- 注意，当然，以上假设架构是互补的(不一定精确).
- Note of course the above assumes that the schema are complimentary (not necessarily exact).
也涵盖评论:-
您必须先将数据迁移到房间本身.
You have to migrate data to room itself first.
-
稍作改动，您可以通过将user_version设置为0来欺骗房间，从而绕过需要迁移的.在这种情况下，Room设置版本号(有限测试).但是，我不确定GreenDao或您的服务器将如何处理(您的作业).
Having a little play, you can get around the having to migrate by fooling room by setting the user_version to 0. In which case Room sets the version number (limited testing). However, I'm not sure what GreenDao or your Server would make of this (your homework).
我的有限测试是针对迁移具有INTEGER PRIMARY KEY(即不带AUTOINCREMENT)的列的问题.如果进行迁移，将有空间抱怨该架构不匹配.因此，我故意不编写AUTOINCREMENT代码，将user_version设置为0，并且没有抱怨通过Room访问数据库.还使用了 rumplestilskin 的列类型，没有抱怨.
My limited testing was for a common issue when migrating that of having a column with INTEGER PRIMARY KEY i.e. without AUTOINCREMENT. Room if migrating will complain that the schema's mismatch. So I intentionally did not code AUTOINCREMENT, set the user_version to 0 and no complaints accessing the database via Room. Also used a column type of rumplestilskin and no complaint.
这样，我相信您可以通过将user_version设置为0来解决可怕的预期/发现的迁移问题(因此，我认为可以绕过迁移).显然，列名称必须匹配，如果在Entity中定义并且不能忽略的话.
As such I believe that you can get around migration issues with the dreaded expected/found by setting the user_version to 0 (and hence I believe circumvent migration). Obviously though column names have to match, if defined in an Entity and not ignored.
- 我还尝试添加未定义Entity的列，并使用上述结果而不引起抱怨(这些测试在下面的代码中应该很明显).
以下是2实体会议室数据库的示例，出于测试 other 数据库的目的，该数据库建在会议室外部，该数据库与会议室数据库充分匹配，可以使用，即实体"列名称是匹配的.
The following is an example of a 2 Entity Room Database and for the purposes of testing the other database one built outside of room that sufficiently matches the room database to be able to be used i.e. Entity column names are matched.
根据 OtherDatabaseHelper.java :-
public class OtherDatabaseHelper extends SQLiteOpenHelper { public static final String DBNAME = "lpolddb"; public static final int DBVERSION = 1; public static final String ALLUSERS_TBL = "AllUsers"; public static final String PAIDUNPAID_TBL = "PaidUnpaid"; /* @PrimaryKey(autoGenerate = true) private long auid; private String Name; private int Loan; private int TimeInMonths; */ public static final String ALLUSERS_COL_AUID = "auid"; public static final String ALLUSERS_COL_NAME = "Name"; public static final String ALLUSERS_COL_LOAN = "Loan"; public static final String ALLUSERS_COL_TIMEINMONTHS = "TimeInMonths"; private static final String crt_allusers_table_sql = "CREATE TABLE IF NOT EXISTS " + ALLUSERS_TBL + "(" + //ALLUSERS_COL_AUID + " INTEGER PRIMARY KEY AUTOINCREMENT," + ALLUSERS_COL_AUID + " INTEGER PRIMARY KEY," + ALLUSERS_COL_NAME + " TEXT, " + ALLUSERS_COL_LOAN + " INTEGER, " + "someothercolumnnotdefineinroom TEXT, " + //!!!!!!!!!! not a column in an entity ALLUSERS_COL_TIMEINMONTHS + " INTEGER" + ")"; /* @PrimaryKey(autoGenerate = true) private long puid; private int TimeInMonths; private String PaidUnpaid; @ForeignKey( entity = AllUsers.class, parentColumns = {"auid"}, childColumns = {"AllUsersReference"}, onUpdate = ForeignKey.CASCADE, onDelete = ForeignKey.CASCADE) private long AllUsersReference; */ public static final String PAIDUNPAID_COL_PUID = "puid"; public static final String PAIDUNPAID_TIMEINMONTHS = ALLUSERS_COL_TIMEINMONTHS; public static final String PAIDUNPAID_COL_PAIDUNPAID = "PaidUnpaid"; public static final String PAIDUNPAID_COL_ALLUSERSREFERENCE = "AllUsersReference"; public static final String crt_paidunpaid_table_sql = "CREATE TABLE IF NOT EXISTS " + PAIDUNPAID_TBL + "(" + PAIDUNPAID_COL_PUID + " INTEGER PRIMARY KEY AUTOINCREMENT," + PAIDUNPAID_TIMEINMONTHS + " rumplestilskin, " + // !!!!!!!!!!! PAIDUNPAID_COL_PAIDUNPAID + " TEXT," + PAIDUNPAID_COL_ALLUSERSREFERENCE + " INTEGER " + " REFERENCES " + ALLUSERS_TBL + "(" + ALLUSERS_COL_AUID + ") " + "ON UPDATE CASCADE ON DELETE CASCADE" + ")"; SQLiteDatabase mDB; public OtherDatabaseHelper(Context context) { super(context, DBNAME, null, DBVERSION); mDB = this.getWritableDatabase(); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(crt_allusers_table_sql); db.execSQL(crt_paidunpaid_table_sql); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } public long insertAllUsers(String name, int loanamount, int periodofloan) { ContentValues cv = new ContentValues(); cv.put(ALLUSERS_COL_NAME,name); cv.put(ALLUSERS_COL_LOAN,loanamount); cv.put(ALLUSERS_COL_TIMEINMONTHS,periodofloan); return mDB.insert(ALLUSERS_TBL,null,cv); } public long insertPaidUnpaid(int formonth, String status, long allUserreferenced) { ContentValues cv = new ContentValues(); cv.put(PAIDUNPAID_TIMEINMONTHS,formonth); cv.put(PAIDUNPAID_COL_PAIDUNPAID,status); cv.put(PAIDUNPAID_COL_ALLUSERSREFERENCE,allUserreferenced); return mDB.insert(PAIDUNPAID_TBL,null,cv); } }- 查看注释以了解奇数/故意添加的差异
- 已填充，可通过备用Room数据库并通过下面的 MainActivity.java 中的附加DB访问
- see comments for oddities/purposefully added discrepancies
- this is popluated, accessed by alternate Room database and via attached DB in MainActivity.java below
2个实体:-
AllUsers.java
@Entity public class AllUsers { @PrimaryKey(autoGenerate = true) private long auid; private String Name; private int Loan; private int TimeInMonths; public AllUsers() { } @Ignore public AllUsers(String Name, int Loan, int TimeInMonths) { this.Name = Name; this.Loan = Loan; this.TimeInMonths = TimeInMonths; } public long getAuid() { return auid; } public void setAuid(long auid) { this.auid = auid; } public String getName() { return Name; } public void setName(String name) { Name = name; } public int getLoan() { return Loan; } public void setLoan(int loan) { Loan = loan; } public int getTimeInMonths() { return TimeInMonths; } public void setTimeInMonths(int timeInMonths) { TimeInMonths = timeInMonths; } }和 PayUnpaid.java :-
@Entity public class PaidUnpaid { @PrimaryKey(autoGenerate = true) private long puid; private int TimeInMonths; private String PaidUnpaid; @ForeignKey( entity = AllUsers.class, parentColumns = {"auid"}, childColumns = {"AllUsersReference"}, onUpdate = ForeignKey.CASCADE, onDelete = ForeignKey.CASCADE) private long AllUsersReference; public PaidUnpaid() { } @Ignore public PaidUnpaid(int TimeInMonths, String PaidUnpaid, long AllUsersreference) { this.TimeInMonths = TimeInMonths; this.PaidUnpaid = PaidUnpaid; this.AllUsersReference = AllUsersreference; } public long getPuid() { return puid; } public void setPuid(long puid) { this.puid = puid; } public int getTimeInMonths() { return TimeInMonths; } public void setTimeInMonths(int timeInMonths) { TimeInMonths = timeInMonths; } public String getPaidUnpaid() { return PaidUnpaid; } public void setPaidUnpaid(String paidUnpaid) { PaidUnpaid = paidUnpaid; } public long getAllUsersReference() { return AllUsersReference; } public void setAllUsersReference(long allUsersReference) { AllUsersReference = allUsersReference; } }一个附加的POJO类 AllUsersAndPaidUnpaidsList.java ，该类已被合并并使用:-
An additonal POJO class, AllUsersAndPaidUnpaidsList.java that was being played with so incorporated and used :-
public class AllUsersAndPaidUnpaidsList { @Embedded AllUsers allUsers; @Ignore @PrimaryKey long auid; @Ignore @Relation(entity = PaidUnpaid.class,parentColumn = "auid",entityColumn = "puid") List<PaidUnpaid> paidUnpaidList; @Ignore public AllUsersAndPaidUnpaidsList(AllUsers au, List<PaidUnpaid> pulist) { this.allUsers = au; this.paidUnpaidList = pulist; } public List<PaidUnpaid> getPaidUnpaidList() { return this.paidUnpaidList; } public void setPaidUnpaidList(List<PaidUnpaid> paidUnpaidList) { this.paidUnpaidList = paidUnpaidList; } public AllUsers getAllUsers() { return allUsers; } public void setAllUsers(AllUsers allUsers) { this.allUsers = allUsers; } public void outputToLog(String tag) { StringBuilder sb = new StringBuilder("AllUsersName = ") .append(this.allUsers.getName()) .append(" TimeInMonths = ") .append(String.valueOf(this.allUsers.getTimeInMonths())) ; for (PaidUnpaid pu: this.getPaidUnpaidList()) { sb.append("\n\t TimeInMonths = ") .append(String.valueOf(pu.getTimeInMonths())) .append(" Paid/Unpaid = ") .append(pu.getPaidUnpaid()); } Log.d(tag,sb.toString()); } }单个界面 Dao.java :-
@androidx.room.Dao public interface Dao { @Insert(onConflict = OnConflictStrategy.IGNORE) long[] insertAllUsers(AllUsers... allUsers); @Insert(onConflict = OnConflictStrategy.IGNORE) long insertAllUsers(AllUsers allUsers); @Insert(onConflict = OnConflictStrategy.IGNORE) long[] insertPaidUnpaid(PaidUnpaid... paidUnpaids); @Insert(onConflict = OnConflictStrategy.IGNORE) long insertPaidUnpaid(PaidUnpaid paidUnpaid); @Update(onConflict = OnConflictStrategy.IGNORE) int updateAllUsers(AllUsers... allUsers); @Update(onConflict = OnConflictStrategy.IGNORE) int updateAllUsers(AllUsers allUsers); @Update(onConflict = OnConflictStrategy.IGNORE) int updatePaidUnpaid(PaidUnpaid... paidUnpaids); @Update(onConflict = OnConflictStrategy.IGNORE) int updatePaidUnpaid(PaidUnpaid paidUnpaid); @Delete int deleteAllUsers(AllUsers... allUsers); @Delete int deleteAllUsers(AllUsers allUsers); @Delete int deletePaidUnpaid(PaidUnpaid... paidUnpaids); @Delete int deletePaidUnpaid(PaidUnpaid paidUnpaid); @Query("SELECT * FROM AllUsers") List<AllUsers> getAllAllUsers(); @Query("SELECT * FROM AllUsers WHERE auid = :id") List<AllUsers> getOneAllUsersById(long id); @Query("SELECT * FROM PaidUnpaid") List<PaidUnpaid> getAllPaidUnpaids(); @Query("SELECT * FROM PaidUnpaid WHERE puid = :id") List<PaidUnpaid> getOnePaidUnpaidById(long id); @Query("SELECT * FROM PaidUnpaid WHERE AllUsersReference = :allUsersid") List<PaidUnpaid> getPaidUnpaidsForAllUsersId(long allUsersid); /************* * Some Additional DAO's for attached not required for alternative helper * in practice you would likely need attached versions of all ************/ @Query("SELECT * FROM other.PaidUnpaid WHERE AllUsersReference = :allUsersid") @SkipQueryVerification List<PaidUnpaid> getOtherPaidUnpaidForAllUsersId(long allUsersid); @SkipQueryVerification @Query("SELECT * FROM other.AllUsers") List<AllUsers> getOtherAllAllUsers(); }Room数据库类 LoanPaymentDatabase.java
@Database(entities = {AllUsers.class,PaidUnpaid.class},exportSchema = false,version = 1) public abstract class LoanPaymentDatabase extends RoomDatabase { public abstract Dao mDao(); }将它们放在一起
最后一个活动:-
Putting it all together
Finally an activity that :-
-
创建并填充 other (非房间)数据库(如果不存在)进行测试. 将user_version(Android Talk中的数据库版本)设置为0.
Creates and populates the other (non-room) database (if it doesn't exist) for testing. Setting the user_version (database version in Android talk) to 0.
根据需要创建数据库的Room版本.
Creates, if needed, the Room version of the database.
MainActivity.java
public class MainActivity extends AppCompatActivity { LoanPaymentDatabase mLPDB; Dao mLPDB_DAO; LoanPaymentDatabase mOtherDB; Dao mOtherDAO; Random rnd = new Random(); @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); manageOtherDatabase(); mLPDB = Room.databaseBuilder(this,LoanPaymentDatabase.class,"mydb").allowMainThreadQueries().build(); mLPDB_DAO = mLPDB.mDao(); // Add some(2) AllUsers mLPDB_DAO.insertAllUsers(new AllUsers("Fred",5000,5)); mLPDB_DAO.insertAllUsers(new AllUsers("Mary", 4000,6)); // Add Some PaidUnpaid's for each AllUsers // Random amount with random paid or unpaid // This is just for demonstration and doesn't reflect what would typically be done List<AllUsers> allusers = mLPDB_DAO.getAllAllUsers(); for (AllUsers au: allusers) { int lc = rnd.nextInt(4) + 1; int month = 1; for (int i=0; i < lc; i++) { String paid = "Paid"; if((rnd.nextInt(2) % 2) > 0 ) { paid = "Unpaid"; } mLPDB_DAO.insertPaidUnpaid(new PaidUnpaid(month++, paid, au.getAuid())); } } //Extract all AllUsersAndPaidUnpaid (i.e each AllUsers with the related PaidUnpaid for the AllUsers) ArrayList<AllUsersAndPaidUnpaidsList> aupulist = new ArrayList<>(); for (AllUsers au: allusers) { List<PaidUnpaid> pulist = mLPDB_DAO.getPaidUnpaidsForAllUsersId(au.getAuid()); aupulist.add(new AllUsersAndPaidUnpaidsList(au,pulist)); } // Output the results for (AllUsersAndPaidUnpaidsList aupu: aupulist) { aupu.outputToLog("INITALAUPU"); } //Use separate openHelper rather than ATTACH mOtherDB = Room.databaseBuilder(this,LoanPaymentDatabase.class,OtherDatabaseHelper.DBNAME).allowMainThreadQueries().build(); mOtherDAO = mOtherDB.mDao(); ArrayList<AllUsersAndPaidUnpaidsList> otheraupulist = new ArrayList<>(); for (AllUsers oau: mOtherDAO.getAllAllUsers() ) { otheraupulist.add(new AllUsersAndPaidUnpaidsList(oau,mOtherDAO.getPaidUnpaidsForAllUsersId(oau.getAuid()))); } for (AllUsersAndPaidUnpaidsList aupu: otheraupulist) { aupu.outputToLog("ALTDBAUPU"); } // User Attach SupportSQLiteDatabase main_sdb = mLPDB.getOpenHelper().getWritableDatabase(); SupportSQLiteDatabase other_sdb = mOtherDB.getOpenHelper().getWritableDatabase(); main_sdb.execSQL("ATTACH DATABASE '" + other_sdb.getPath() + "' AS other"); ArrayList<AllUsersAndPaidUnpaidsList> attachaupulist = new ArrayList<>(); for (AllUsers aau: mLPDB_DAO.getAllAllUsers()) { attachaupulist.add(new AllUsersAndPaidUnpaidsList(aau,mLPDB_DAO.getPaidUnpaidsForAllUsersId(aau.getAuid()))); } for (AllUsers aauother: mLPDB_DAO.getOtherAllAllUsers()) { attachaupulist.add(new AllUsersAndPaidUnpaidsList(aauother,mLPDB_DAO.getOtherPaidUnpaidForAllUsersId(aauother.getAuid()))); } for (AllUsersAndPaidUnpaidsList aupu: attachaupulist) { aupu.outputToLog("ATTACHEDAUPU"); } mLPDB.close(); } /********* * For testing purposes - Populate the OTHER database to be used *********/ private void manageOtherDatabase() { OtherDatabaseHelper mODBHlpr = new OtherDatabaseHelper(this); SQLiteDatabase db = mODBHlpr.getWritableDatabase(); db.execSQL("PRAGMA user_version = 0"); if (DatabaseUtils.queryNumEntries(db,OtherDatabaseHelper.ALLUSERS_TBL) > 0) { db.close(); mODBHlpr.close(); return; } db.beginTransaction(); for (int i= 0; i < 5; i++) { long auid = mODBHlpr.insertAllUsers("AU" + String.valueOf(i),10000 + 1,5 + i); for(int ii = 0; ii < 5; ii++) { mODBHlpr.insertPaidUnpaid(ii,"Paid",auid); } } db.setTransactionSuccessful(); db.endTransaction(); db.close(); mODBHlpr.close(); } }这篇关于会议室-使用外部SQLite和内部数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
