There are three ways you can go about this.

1. attach the database(s) to an already open connection, e.g.

   ATTACH DATABASE 'the_path_to_database' AS 'plant_database';

   • You will have to then use the schema name plant_database when referring to the plant database.

2. Have multiple open connections, typically DatabaseHelpers (subclasses of SQLiteOpenHelper) and then utilise the respective helper when accessing each database.

3. Use a design that incorporates all database entities (tables, views, triggers etc) into a single database, having columns that indicate differentiation according to diary. The latter typically being what would be considered as the norm.

Working Example of All 3

Here's an example using all 3, each with a Spinner to select the Diary with a ListView that displays the respective plants :-

e.g. :-

Select Mary (only 2 Diaries) for each selects the Plants for the mary Diary :-

The code is a little complicated but hopefully the realtively meaningful names should help to unravel.

The Diary class Diary.java (used by all)

public class Diary {
    public static final String TABLENAME = "diary";
    public static final String COLUMN_ID = BaseColumns._ID;
    public static final String COLUMN_DIARYNAME = "diaryName";

    private static final String crt_sql = "CREATE TABLE IF NOT EXISTS " + TABLENAME + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY NOT NULL, " +
            COLUMN_DIARYNAME + " TEXT UNIQUE" +
            ")";

    private long id;
    private String diaryName;

    public Diary(String diaryName) {
        this.diaryName = diaryName;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getDiaryName() {
        return diaryName;
    }

    public void setDiaryName(String diaryName) {
        this.diaryName = diaryName;
    }

    public static String getCrt_sql() {
        return crt_sql;
    }
}

The MultiDBHelper and attached DB's use the same Diary DatabaseHelper DiaryDatabaseHelper.java BUT different constructors so that the attached Database for the attached Diary has a different name to the MultiDBHelper database.

public class DiaryDatabaseHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "diary";
    public static final int DBVERSION = 1;
    public static final String DBATTACH = "attached";
    boolean attached = false;

    SQLiteDatabase sqLiteDatabase;

    public DiaryDatabaseHelper(Context context, boolean attached) {
        super(context, DBATTACH + DBNAME, null, DBVERSION);
        attached = true;
        sqLiteDatabase = this.getWritableDatabase();
    }

    public DiaryDatabaseHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        sqLiteDatabase = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(Diary.getCrt_sql());
        /* Add some diaries */
        Diary ent1 = new Diary("Fred");
        Diary ent2 = new Diary("Mary");
        ContentValues cv = new ContentValues();
        cv.put(Diary.COLUMN_DIARYNAME,ent1.getDiaryName());
        db.insert(Diary.TABLENAME,null,cv);
        cv.clear();
        cv.put(Diary.COLUMN_DIARYNAME,ent2.getDiaryName());
        db.insert(Diary.TABLENAME,null,cv);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public Cursor getAllDiaryRows() {
        return sqLiteDatabase.query(Diary.TABLENAME,null,null,null,null,null,null);
    }
}

The MultiDB and attached DB use GenericPlantDatabaseHelper.java

• This is a little convuluted so that it can give the plant database different names (i.e. the attachable databases are prefixed with attached).

• Data is inserted when onCreate is run to make the Demo shorter/less complex.

:-

public class GenericPlantDatabaseHelper extends SQLiteOpenHelper {

    public static final int DBVERSION = DiaryDatabaseHelper.DBVERSION; /* Match database version */
    public static final String BASEDBNAME = "plantdatabasefor";

    public static final String ATTACHEXTRA = "attached";
    public String attachedexta = "";

    SQLiteDatabase database;
    String currentDatabaseName;

    public GenericPlantDatabaseHelper(Context context, String owningDiaryName,String attachedexta) {
        super(context, attachedexta+BASEDBNAME+owningDiaryName, null, DBVERSION);
        if (attachedexta == null) {
            attachedexta = "";
        }
        this.attachedexta = attachedexta;
        database = this.getWritableDatabase();
        currentDatabaseName = this.attachedexta + BASEDBNAME+owningDiaryName;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(Plant.crt_sql);

        /* Add some plants for DEMO */
        /* Different plants if DiaryName contains Fred */
        Plant ent1 = new Plant("Diasy"), ent2 = new Plant("Petunia");
        ContentValues cv = new ContentValues();
        if(db.getPath().indexOf("Fred") > 0) {
            ent1.setPlantName("Rose");
            ent2.setPlantName("Daffodil");
        }
        cv.put(Plant.COLUMN_PLANTNAME,ent1.getPlantName());
        db.insert(Plant.TABLENAME,null,cv);
        cv.clear();
        cv.put(Plant.COLUMN_PLANTNAME,ent2.getPlantName());
        db.insert(Plant.TABLENAME,null,cv);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public String getCurrentDatabaseName() {
        return currentDatabaseName;
    }

    public void logAllPlants() {
        Cursor csr = database.query(Plant.TABLENAME,null,null,null,null,null,null);
        DatabaseUtils.dumpCursor(csr);
    }

    public Cursor getAllPlants() {
        return database.query(Plant.TABLENAME,null,null,null,null,null,null);
    }

    public class Plant {
        public static final String TABLENAME = "plant";
        public static final String COLUMN_ID = BaseColumns._ID;
        public static final String COLUMN_PLANTNAME = "plantName";

        private static final String crt_sql = "CREATE TABLE IF NOT EXISTS " + TABLENAME +
                "(" +
                COLUMN_ID + " INTEGER PRIMARY KEY NOT NULL, " +
                COLUMN_PLANTNAME + " TEXT UNIQUE" +
                ")";

        private long id;
        private String plantName;

        public Plant(){}

        public Plant(String plantName) {
            this.plantName = plantName;
        }

        public long getId() {
            return id;
        }

        public void setId(long id) {
            this.id = id;
        }

        public String getPlantName() {
            return plantName;
        }

        public void setPlantName(String plantName) {
            this.plantName = plantName;
        }
    }
}

The All-in-One database has it's own DatabaseHelper, noting that the plant table includes an ownerId (the id of the diary (for simplicity Foreign Keys have not been used)) AllinOneDatabaseHelper.java

public class AllinOneDatabaseHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "SingleDiaryPlantDatabase";
    public static final int DBVERSION = 1;

    SQLiteDatabase sqLiteDatabase;

    public AllinOneDatabaseHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        sqLiteDatabase = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(Diary.getCrt_sql());
        db.execSQL(singleDBPlant.crt_sql);
        /* Add some diaries */
        Diary ent1 = new Diary("Fred");
        Diary ent2 = new Diary("Mary");
        ContentValues cv = new ContentValues();
        cv.put(Diary.COLUMN_DIARYNAME,ent1.getDiaryName());
        ent1.setId(db.insert(Diary.TABLENAME,null,cv));
        cv.clear();
        cv.put(singleDBPlant.COLUMN_PLANTOWNERID,ent1.getId());
        cv.put(singleDBPlant.COLUMN_PLANTNAME,"Rose");
        db.insert(singleDBPlant.TABLENAME,null,cv);
        cv.clear();
        cv.put(singleDBPlant.COLUMN_PLANTNAME,"Daffodil");
        cv.put(singleDBPlant.COLUMN_PLANTOWNERID,ent1.getId());
        db.insert(singleDBPlant.TABLENAME,null,cv);

        cv.clear();
        cv.put(Diary.COLUMN_DIARYNAME,ent2.getDiaryName());
        ent2.setId(db.insert(Diary.TABLENAME,null,cv));
        cv.clear();
        cv.put(singleDBPlant.COLUMN_PLANTOWNERID,ent2.getId());
        cv.put(singleDBPlant.COLUMN_PLANTNAME,"Tulip");
        db.insert(singleDBPlant.TABLENAME,null,cv);
        cv.clear();
        cv.put(singleDBPlant.COLUMN_PLANTOWNERID,ent2.getId());
        cv.put(singleDBPlant.COLUMN_PLANTNAME,"Daisy");
        db.insert(singleDBPlant.TABLENAME,null,cv);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public Cursor getAllDiaries() {
        return sqLiteDatabase.query(Diary.TABLENAME,null,null,null,null,null,null);
    }

    public Cursor getPlantsPerDiaryId(long diaryId) {
        String whereclause = singleDBPlant.COLUMN_PLANTOWNERID + "=?";
        String[] whereargs = new String[]{String.valueOf(diaryId)};
        return sqLiteDatabase.query(singleDBPlant.TABLENAME,null,whereclause,whereargs,null,null,null);
    }

    public class singleDBPlant {
        public static final String TABLENAME = "plant";
        public static final String COLUMN_ID = BaseColumns._ID;
        public static final String COLUMN_PLANTOWNERID = "plantOwnerid";
        public static final String COLUMN_PLANTNAME = "plantName";


        private static final String crt_sql = "CREATE TABLE IF NOT EXISTS " + TABLENAME +
                "(" +
                COLUMN_ID + " INTEGER PRIMARY KEY NOT NULL, " +
                COLUMN_PLANTOWNERID + " INTEGER, " + //<<<<<<< Owner (Diary)
                COLUMN_PLANTNAME + " TEXT UNIQUE" +
                ")";

        private long id;
        private long plantOwnerId;
        private String plantName;

        public singleDBPlant(){}

        public singleDBPlant(long plantOwnerId, String plantName) {
            this.plantOwnerId = plantOwnerId;
            this.plantName = plantName;
        }

        public long getId() {
            return id;
        }

        public void setId(long id) {
            this.id = id;
        }

        public String getPlantName() {
            return plantName;
        }

        public void setPlantName(String plantName) {
            this.plantName = plantName;
        }

        public long getPlantOwnerId() {
            return plantOwnerId;
        }

        public void setPlantOwnerId(long plantOwnerId) {
            this.plantOwnerId = plantOwnerId;
        }
    }

}

Finally MainActivity.java

public class MainActivity extends AppCompatActivity {

    DiaryDatabaseHelper diaryDatabaseHelper, attachedDiaryDatabaseHelper;

    String currentAttachedDBName;

    AllinOneDatabaseHelper allinOneDatabaseHelper;
    ArrayList<GenericPlantDatabaseHelper> multiDBGenericPlantDatabaseHelperArrayList = new ArrayList<>();

    Spinner multiDBHelpersSpinner, attachedDBSpinner, singleDBSpinner;
    SimpleCursorAdapter multiDBHelpersDiaryAdapter, attachedDBDiaryAdapter, singleDBDiaryAdapter,
            multiDBHelpersPlantAdapter, attachedDBPlantAdapter, singleDBPlantAdapter;
    ListView multiDBHelpersListView, attachedDBListView, singleDBListView;
    Cursor multiDBHelpersDiaryCursor, attachDBDiaryCursor, singleDBDiaryCursor,
            multiDBHelpersPlantCursor, attachedDBPlantCursor, singleDBPlantCursor
            ;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        /* Get View Id's */
        multiDBHelpersSpinner = this.findViewById(R.id.multiDBHelpersSpinner);
        attachedDBSpinner = this.findViewById(R.id.attachedDatabasesSpinner);
        singleDBSpinner = this.findViewById(R.id.singleDatabaseSpinner);
        multiDBHelpersListView = this.findViewById(R.id.multiDBHelpersListView);
        attachedDBListView = this.findViewById(R.id.attachedDatabasesListView);
        singleDBListView = this.findViewById(R.id.singleDatabaseListView);

        allinOneDatabaseHelper = new AllinOneDatabaseHelper(this);
        attachedDiaryDatabaseHelper = new DiaryDatabaseHelper(this,true);
        diaryDatabaseHelper = new DiaryDatabaseHelper(this);
        multiDBHelpersDiaryCursor = diaryDatabaseHelper.getAllDiaryRows();
        while (multiDBHelpersDiaryCursor.moveToNext()) {
            multiDBGenericPlantDatabaseHelperArrayList.add(new GenericPlantDatabaseHelper(
                    this,
                    multiDBHelpersDiaryCursor.getString(multiDBHelpersDiaryCursor.getColumnIndex(Diary.COLUMN_DIARYNAME)),
                    "")
            );
        }

        manageMultiDBHelpersSpinner();
        manageAttachedDBSpinner();
        manageSingleDBSpinner();
    }

    @Override
    protected void onResume() {
        super.onResume();
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        if (multiDBHelpersDiaryCursor != null && !multiDBHelpersDiaryCursor.isClosed()) {
            multiDBHelpersDiaryCursor.close();
        }
        if (attachDBDiaryCursor != null && !attachDBDiaryCursor.isClosed()) {
            attachDBDiaryCursor.close();
        }
        if (singleDBDiaryCursor != null && !singleDBDiaryCursor.isClosed()) {
            singleDBDiaryCursor.close();
        }
        allinOneDatabaseHelper.close();
        diaryDatabaseHelper.close();
    }

    private void manageMultiDBHelpersSpinner() {
        multiDBHelpersDiaryCursor = diaryDatabaseHelper.getAllDiaryRows();
        if (multiDBHelpersDiaryAdapter == null) {
            multiDBHelpersDiaryAdapter = new SimpleCursorAdapter(
                    this,
                    android.R.layout.simple_list_item_1,
                    multiDBHelpersDiaryCursor,
                    new String[]{Diary.COLUMN_DIARYNAME},
                    new int[]{android.R.id.text1},
                    0
            );
            multiDBHelpersSpinner.setAdapter(multiDBHelpersDiaryAdapter);
            multiDBHelpersSpinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
                @Override
                public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
                    manageMultiDBHelpersListView(
                            multiDBHelpersDiaryCursor.getString(
                                    multiDBHelpersDiaryCursor.getColumnIndex(
                                            Diary.COLUMN_DIARYNAME)
                            )
                    );
                }

                @Override
                public void onNothingSelected(AdapterView<?> parent) {

                }
            });
        } else {
            multiDBHelpersDiaryAdapter.swapCursor(multiDBHelpersDiaryCursor);
        }
    }

    private void manageMultiDBHelpersListView(String diaryName) {
        for (GenericPlantDatabaseHelper g: multiDBGenericPlantDatabaseHelperArrayList) {
            if (g.getCurrentDatabaseName().equals(GenericPlantDatabaseHelper.BASEDBNAME + diaryName)) {
                multiDBHelpersPlantCursor = g.getAllPlants();
            }
        }
        if (multiDBHelpersPlantAdapter == null) {
            multiDBHelpersPlantAdapter = new SimpleCursorAdapter(
                    this,
                    android.R.layout.simple_list_item_1,
                    multiDBHelpersPlantCursor,
                    new String[]{GenericPlantDatabaseHelper.Plant.COLUMN_PLANTNAME},
                    new int[]{android.R.id.text1},
                    0
            );
            multiDBHelpersListView.setAdapter(multiDBHelpersPlantAdapter);
        } else {
            multiDBHelpersPlantAdapter.swapCursor(multiDBHelpersPlantCursor);
        }
    }

    private void manageAttachedDBSpinner() {
        attachDBDiaryCursor = attachedDiaryDatabaseHelper.getAllDiaryRows();
        if (attachedDBDiaryAdapter == null) {
            attachedDBDiaryAdapter = new SimpleCursorAdapter(
                    this,
                    android.R.layout.simple_list_item_1,
                    attachDBDiaryCursor,
                    new String[]{Diary.COLUMN_DIARYNAME},
                    new int[]{android.R.id.text1},
                    0
            );
            attachedDBSpinner.setAdapter(attachedDBDiaryAdapter);
            attachedDBSpinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
                @Override
                public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
                    manageAttachedListView(
                            attachDBDiaryCursor.getString(
                                    attachDBDiaryCursor.getColumnIndex(Diary.COLUMN_DIARYNAME)
                            )
                    );
                }

                @Override
                public void onNothingSelected(AdapterView<?> parent) {

                }
            });
        } else {
            attachedDBDiaryAdapter.swapCursor(attachDBDiaryCursor);
        }
    }

    private void manageAttachedListView(String diaryName) {
        String dbfilename;
        String dbname;
        SQLiteDatabase db = attachedDiaryDatabaseHelper.getWritableDatabase();
        GenericPlantDatabaseHelper g = new GenericPlantDatabaseHelper(this,diaryName,GenericPlantDatabaseHelper.ATTACHEXTRA);
        dbfilename = this.getDatabasePath(g.getDatabaseName()).getPath();
        dbname = g.getDatabaseName();
        g.close();
        if (currentAttachedDBName != null && !dbname.equals(currentAttachedDBName)) {
            detachAll(db);
        }
        currentAttachedDBName = dbname;
        db.execSQL("ATTACH DATABASE '" + dbfilename + "' AS '" + dbname + "'");
        attachedDBPlantCursor = db.query(GenericPlantDatabaseHelper.Plant.TABLENAME,null,null,null,null,null,null);
        if (attachedDBPlantAdapter == null) {
            attachedDBPlantAdapter = new SimpleCursorAdapter(
                    this,
                    android.R.layout.simple_list_item_1,
                    attachedDBPlantCursor,
                    new String[]{GenericPlantDatabaseHelper.Plant.COLUMN_PLANTNAME},
                    new int[]{android.R.id.text1},
                    0
            );
            attachedDBListView.setAdapter(attachedDBPlantAdapter);
        } else {
            attachedDBPlantAdapter.swapCursor(attachedDBPlantCursor);
        }

    }

    private void detachAll(SQLiteDatabase db) {
        db =  attachedDiaryDatabaseHelper.getWritableDatabase();
        Cursor csr = db.query("pragma_database_list",null,"lower(name) <> 'main' AND name <> 'temp'",null,null,null,null);
        while (csr.moveToNext()) {
            db.execSQL("DETACH DATABASE " + csr.getString(csr.getColumnIndex("name")));
        }
    }

    private void manageSingleDBSpinner() {
        singleDBDiaryCursor = allinOneDatabaseHelper.getAllDiaries();
        if (singleDBDiaryAdapter == null) {
            singleDBDiaryAdapter = new SimpleCursorAdapter(
                    this,
                    android.R.layout.simple_list_item_1,
                    singleDBDiaryCursor,
                    new String[]{Diary.COLUMN_DIARYNAME},
                    new int[]{android.R.id.text1},
                    0
            );
            singleDBSpinner.setAdapter(singleDBDiaryAdapter);
            singleDBSpinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
                @Override
                public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
                    manageSingleDBListView(id);
                }

                @Override
                public void onNothingSelected(AdapterView<?> parent) {

                }
            });
        } else {
            singleDBDiaryAdapter.swapCursor(singleDBDiaryCursor);
        }
    }

    private void manageSingleDBListView(long diaryId) {
        singleDBPlantCursor = allinOneDatabaseHelper.getPlantsPerDiaryId(diaryId);
        if (singleDBPlantAdapter == null) {
            singleDBPlantAdapter = new SimpleCursorAdapter(
                    this,
                    android.R.layout.simple_list_item_2,
                    singleDBPlantCursor,
                    new String[]{AllinOneDatabaseHelper.singleDBPlant.COLUMN_PLANTNAME,AllinOneDatabaseHelper.singleDBPlant.COLUMN_PLANTOWNERID},
                    new int[]{android.R.id.text1, android.R.id.text2},
                    0
            );
            singleDBListView.setAdapter(singleDBPlantAdapter);
        } else {
            singleDBPlantAdapter.swapCursor(singleDBPlantCursor);
        }
    }
}

As can be seen from the following, the Single database approach uses less disk space (28Mb as opposed to 60Mb) :-