Android multiple databases open
I have a DatabaseAdapter class which contains two databases which are opened together.
public class DatabaseAdapter {
/** Identifier for the internal database */
public static final int INTERNAL = 0;
/** Identifier for the external database */
public static final int EXTERNAL = 1;
private final SQLiteOpenHelper[] mDatabaseManager = new SQLiteOpenHelper[2];
private final SQLiteDatabase[] mDatabases = new SQLiteDatabase[2];
/**
* Constructs the database and open it.
*/
public DatabaseAdapter() {
// Open the internal_db
mDatabaseManager[INTERNAL] = new InternalDatabaseManager(MyApplication.getInstance());
mDatabases[INTERNAL] = mDatabaseManager[INTERNAL].getWritableDatabase();
}
/**
* Checks the database state and throws an {@link IllegalStateException} if database isn't open.
* Should always be used before starting to access the database.
*
* @param type Type of the database. Can be INTERNAL or EXTERNAL.
*/
public void checkDbState(int type) {
if (mDatabases[type] == null || !mDatabases[type].isOpen()) {
throw new IllegalStateException("The database has not been opened");
}
}
/**
* Closes the database of the given type.
*
* @param type Type of the database. Can be INTERNAL or EXTERNAL.
*/
public void close(int type) {
if (mDatabases[type].isOpen()) {
mDatabases[type].close();
mDatabases[type] = null;
if (mDatabaseManager[type] != null) {
mDatabaseManager[type].close();
mDatabaseManager[type] = null;
}
}
}
/**
* @param type Type of the database. Can be INTERNAL or EXTERNAL.
* @return true if the database is open, false otherwise.
*/
public boolean isOpen(int type) {
return mDatabases[type] != null && mDatabases[type].isOpen();
}
/**
* Opens the default database.
*
* @param type Type of the database. Can be INTERNAL or EXTERNAL.
*/
public void open(int type) {
switch (type) {
case INTERNAL:
mDatabaseManager[INTERNAL] = new InternalDatabaseManager(MyApplication.getInstance());
if (!isOpen(INTERNAL)) {
mDatabases[INTERNAL] = mDatabaseManager[INTERNAL].getWritableDatabase();
}
break;
case EXTERNAL:
mDatabaseManager[EXTERNAL] = new ExternalDatabaseManager(MyApplication.getInstance(), Constants.EXTERNAL_DB_PATH, 1);
if (!isOpen(EXTERNAL)) {
mDatabases[EXTERNAL] = mDatabaseManager[EXTERNAL].getWritableDatabase();
}
break;
}
}
}
to add a third one should be easy :)
Android - Multiple database one project
It is possible.
Let's assume you have two sets of entities and two sets of DAOs. You can obtain access to two databases by:
- creating two classes that extends RoomDatabase:
AppDatabase 1:
@Database(entities = {/*... the first set of entities ...*/}, version = 1)
public abstract class AppDatabase1 extends RoomDatabase {
// the first set of DAOs
}
AppDatabase2:
@Database(entities = {/*... the second set of entities ...*/}, version = 1)
public abstract class AppDatabase2 extends RoomDatabase {
// the second set of DAOs
}
- instantiating the two databases:
Note that you'll use two different file names.
AppDatabase db1 = Room.databaseBuilder(getApplicationContext(), AppDatabase1.class, "database1.db").build();
AppDatabase db2 = Room.databaseBuilder(getApplicationContext(), AppDatabase2.class, "database2.db").build();
In this case, you can use both databases, but you won't be able to create queries between them. If you need to attach the two databases, then you should take a look at the link @Anees provided
Can Android Room manage multiple databases and create databases from a template database?
With Android Room, what is the practical way to approach creating and handling multiple databases in a single Android app?
You can certainly handle multiple databases and multiple databases based upon the same schema.
The issue is how to ascertain what databases there are that can be used. If all the databases were located in the same path (or even multiple paths) then this could be used. Another methodology could be to have a database of the databases.
Here's an example that utilises the databases of databases (the "MasterDatabase") and allows access to x databases.
So first the MasterDatabase which has a simple table with an id column (could be dispensed with) and a column for the database name. The table (@Entity) being named MasterDatabaseList as per :-
@Entity(
indices = { @Index(value = "databaseName", unique = true)
}
)
class MasterDatabaseList {
@PrimaryKey
Long id;
String databaseName;
public MasterDatabaseList() {}
@Ignore
public MasterDatabaseList(String databaseName) {
this.databaseName = databaseName;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getDatabaseName() {
return databaseName;
}
public void setDatabaseName(String databaseName) {
this.databaseName = databaseName;
}
}
- Note the unique index on the databaseName column
Accompanying the table is MasterDao an @Dao class :-
@Dao
abstract class MasterDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(MasterDatabaseList masterDatabaseList);
@Query("SELECT * FROM masterdatabaselist")
abstract List<MasterDatabaseList> getAllDatabases();
}
- allows rows to be inserted or extracted.
- a duplicated database will be ignored and thus not added.
MasterDatabase is the @Database class (which ties the previous classes to the database) and includes a method to get an instance of the database from which the MasterDao can be accessed :-
@Database(
entities = {MasterDatabaseList.class},
version = 1
)
abstract class MasterDatabase extends RoomDatabase {
abstract MasterDao getMasterDao();
static volatile MasterDatabase instance = null;
public static MasterDatabase getInstance(Context context) {
if (instance == null) {
instance = Room.databaseBuilder(context,MasterDatabase.class,"master.db")
.allowMainThreadQueries()
.build();
}
return instance;
}
}
Now the template database, Base???? (a simple single table database for the demo). First the table BaseTable @Entity class:-
@Entity
class BaseTable {
@PrimaryKey
Long id;
String mydata;
public BaseTable(){}
@Ignore
public BaseTable(String myData) {
this.mydata = myData;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getMydata() {
return mydata;
}
public void setMydata(String mydata) {
this.mydata = mydata;
}
}
- a very simple table with an id column and a column that holds some string data.
Accompanying is the @Dao class BaseDao :-
@Dao
abstract class BaseDao {
@Insert
abstract long insert(BaseTable baseTable);
@Query("SELECT * FROM basetable")
abstract List<BaseTable> getAllBaseTables();
@Update
abstract int update(BaseTable baseTable);
}
- with very basic insert, extract and update
and as before the @Database class BaseDatabase :-
@Database(
entities = {BaseTable.class},
version = 1
)
abstract class BaseDatabase extends RoomDatabase {
abstract BaseDao getBaseDao();
public static BaseDatabase getInstance(Context context, String databaseName) {
BaseDatabase instance = null;
if (databaseName != null) {
return Room.databaseBuilder(context, BaseDatabase.class, databaseName)
.allowMainThreadQueries()
.build();
}
return instance;
}
}
- note how the database name needs to be passed, that is basically the crux of catering for multiple databases.
With all of that a Demo Activity.
public class MainActivity extends AppCompatActivity {
private static final String TAG = "DBINFO";
MasterDatabase masterDB;
MasterDao masterDao;
/* 3 Lists that need to be synchronised index wise */
/* i.e. each index position should hold the respective name/databaseobject/dao
/* List of the known databases (their names) */
List<MasterDatabaseList> masterDatabaseListList = null;
/* List of the BaseDatabase objects */
ArrayList<BaseDatabase> baseDatabaseList = new ArrayList<>();
/* List of the BaseDao's */
ArrayList<BaseDao> baseDaoList = new ArrayList<>();
/* The current database */
int currentBaseIndex = -1; /* Index into the three Lists */
BaseDatabase currentDB = null;
BaseDao currentDao = null;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
masterDB = MasterDatabase.getInstance(this);
masterDao = masterDB.getMasterDao();
masterDatabaseListList = masterDao.getAllDatabases();
// Add default db1 if it does not exist
if (masterDatabaseListList.size() < 1) {
addBaseDB("db1");
}
buildBaseLists();
/* Add some data to db1 IF it exists (it should) */
setCurrentIndexDBandDao("db1");
if (currentBaseIndex > -1) {
currentDao.insert(new BaseTable("Blah for db1"));
}
/* Add some data to db2 (it will not exist) */
/* noting that the database will be created if it does not exist */
setCurrentIndexDBandDao("db2");
if (currentBaseIndex == -1) {
addBaseDB("db2");
}
if (currentBaseIndex > -1) {
currentDao.insert(new BaseTable("Blah for db2"));
}
/* Extract and Log Data for ALL the BaseDatabase databases i.e. db1 and db2 */
for(MasterDatabaseList masterdb: masterDao.getAllDatabases()) {
Log.d(TAG,"Database is " + masterdb.getDatabaseName());
setCurrentIndexDBandDao(masterdb.databaseName);
if (currentBaseIndex > -1) {
for(BaseTable bt: currentDao.getAllBaseTables()) {
Log.d(TAG,"Extracted Base Table row where MyData is" + bt.getMydata());
}
}
}
}
/* Add a new Database */
/* Note that it assumes that it will now be the current */
/* so the current values are set */
private void addBaseDB(String baseDBName) {
masterDao.insert(new MasterDatabaseList(baseDBName));
buildBaseLists();
setCurrentIndexDBandDao(baseDBName);
}
/* Build/ReBuild the 3 Lists according to the master database */
/* This could be better managed so as to not rebuild existing database/dao objects */
private void buildBaseLists() {
int ix = 0;
baseDatabaseList.clear();
baseDaoList.clear();
masterDatabaseListList = masterDao.getAllDatabases();
// Loop through the databases defined in the master database adding the database and dao to the respective lists
for (MasterDatabaseList masterDB: masterDao.getAllDatabases()) {
BaseDatabase baseDB = BaseDatabase.getInstance(this, masterDB.getDatabaseName());
baseDatabaseList.add(baseDB);
baseDaoList.add(baseDB.getBaseDao());
ix++;
}
}
/* Set the current trio according to the database name that is:*/
/* 1.the currentBaseIndex for the 3 Lists */
/* 2. the BaseDatabase object */
/* 3. the BaseDao */
/* The index value (currentBaseIndex) is also returned */
private int setCurrentIndexDBandDao(String baseDBName) {
currentBaseIndex = getListIndexByBaseDBName(baseDBName);
if (currentBaseIndex > -1) {
currentDB = baseDatabaseList.get(currentBaseIndex);
currentDao = baseDaoList.get(currentBaseIndex);
}
return currentBaseIndex;
}
/* Get the index according to the database name passed */
/* note -1 signifies not know/found */
private int getListIndexByBaseDBName(String baseDBName) {
masterDatabaseListList = masterDao.getAllDatabases(); // OverKill????
int rv = -1; // default to not found
for(int i=0; i < masterDatabaseListList.size();i++) {
if (masterDatabaseListList.get(i).databaseName.equals(baseDBName)) {
rv = i;
break;
}
}
return rv;
}
/* Output all rows from the BaseTable for data extracted by the BaseDaos getAllBaseTables */
private void logBaseData(List<BaseTable> baseTableList) {
Log.d(TAG,"Current Database Index is " + currentBaseIndex + " DB name is " + masterDatabaseListList.get(currentBaseIndex).getDatabaseName());
for(BaseTable bt: baseTableList) {
Log.d(TAG,"\tMyData value is " + bt.getMydata());
}
}
}
Result
When the above is run for the first time the log includes:-
2021-09-16 11:39:30.262 D/DBINFO: Database is db1
2021-09-16 11:39:30.278 D/DBINFO: Extracted Base Table row where MyData isBlah for db1
2021-09-16 11:39:30.278 D/DBINFO: Database is db2
2021-09-16 11:39:30.284 D/DBINFO: Extracted Base Table row where MyData isBlah for db2
And via Android Studio's App Inspector the databases :-
and for the db2 BaseTable :-
- Note The above is only intended to cover the basics of utilising multiple databases in what is intended to be a simplistic explanation, as such the code has been kept short and simple. It would probably be unacceptable, as it is, for an App that would be distribtued.
How to create more than one databases in a Single app using SQLiteOpenHelper Android
If you change the constant to a regular class member, like this:
public class DBHelper extends SQLiteOpenHelper {
public String databaseName;
public DBHelper(Context context, String databaseName) {
super(context, databaseName, null, 3);
this.databaseName = databaseName;
}
// ...
}
Then you can instantiate as many of these as you want:
DBHelper db1 = new DBHelper(context, "db1.db");
DBHelper db2 = new DBHelper(context, "db2.db");
You might have to change DATABASE_NAME to databaseName in other places in your code, but you get the idea.
Related Topics
How to Execute a Stored Procedure Inside a Select Query
SQL Not Displaying Null Values on a Not Equals Query
Running Total by Grouped Records in Table
Using Case Statement Inside in Clause
Function Return Sys_Refcursor Call from SQL with Specific Columns
What Should I Name a Table That Maps Two Tables Together
How to Simulate Deadlock on SQL Server
Generating SQL Insert into for Oracle
How to Decrypt Stored Procedure in SQL Server 2008
How to Return Default Value from SQL Query
Return All Possible Combinations of Values Within a Single Column in SQL
Datareader.Getstring() via Columnname
Pls-00201: Identifier 'User Input' Must Be Declared