'How to select from multiple databases in Android Room(How to attach databases)

As you know we can select from multiple databases using attach command like this:

String path = DBHelper.getDatabasePath(context);
String sql = "ATTACH DATABASE '" + path + "/" + dbname.toString()
                + ".db' AS \"" + dbname.toString() + "\";";
db.execSQL(sql);

Then by using Cursor, we can do select from them.

By using Android Room, How can I do this? Is there any attachment or similar command to do this?

javaandroidsqliteandroid-roomandroid-architecture-components


Solution 1:[1]

Can use this code for attach another database

@Database(entities = {Book.class, User.class}, version = 1)
public abstract class LoanDatabase extends RoomDatabase {

    public abstract UserDao userDao();

    public abstract BookDao bookDao();

    private static LoanDatabase INSTANCE;

    public static LoanDatabase getInstance(Context context,final String attachDatabaseName) {
        if (INSTANCE == null) {{
            INSTANCE = Room.databaseBuilder(context,
                    LoanDatabase.class, "LoanDatabase").addCallback(new Callback() {
                @Override
                public void onOpen(@NonNull SupportSQLiteDatabase db) {
                    attach(attachDatabaseName,"/data/data/com.test.roomwithdagger/databases/");
                    super.onOpen(db);
                }
            })
                    .build();
        }}
        return INSTANCE;
    }

    private static void attach(final String databaseName, final String databasePath) {
        String sql = "ATTACH DATABASE '" + databasePath + databaseName
                + "' AS \"" + databaseName + "\";";
        INSTANCE. mDatabase.execSQL(sql);
    }
}

public void attachDatabase(String databaseName,String databasePath){

    String sql = "ATTACH DATABASE '" + databasePath + "/" + databaseName
            + ".db' AS \"" + databaseName + "\";";
    INSTANCE.mDatabase.execSQL(sql);
}
}

In Dao interface use @SkipQueryVerification for skip query verification like this.

@Dao
public interface BookDao {

...

    @SkipQueryVerification
    @Query("SELECT * FROM main.Book b INNER JOIN LoanDatabase1.Loan l on b.Id=l.BookId where b.Id=:bookId")
    Book getBookAndLoan(int bookId);
...

}

Use :

LoanDatabase db = LoanDatabase.getInstance(this,"LoanDatabase1")

Book book= db.bookDao().getBookAndLoan(1)

Solution 2:[2]

While building room database using the DatabaseBuilder class, you have an option to register a callback which has methods that'd be called everytime your database is opened:

 /**
         * Called when the database has been opened.
         *
         * @param db The database.
         */
        public void onOpen(@NonNull SupportSQLiteDatabase db) {
        }

One option is to register this callback and ATTACH your other database using the db parameter. Then in dao, you can have queries which reference the other table.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2

Related Questions