为正确的相应SQLite数据库显示数据

Question

我有一个回收视图，有一个清单的项目或“日记”，有数据存储在sql数据库的“日记”。当我点击日记，它将带我到一个活动，我可以添加更多的项目或“植物”到一个不同的sql数据库“植物”。我想确保我显示了正确的植物对应的日记，被点击。

如何确保在单击日记时显示正确的植物数据库？我不知道如何连接数据库。谢谢..。

package com.bawp.babyneeds.data;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.bawp.babyneeds.model.Diary;
import com.bawp.babyneeds.R;
import com.bawp.babyneeds.util.Util;

import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DatabaseHandlerDiary extends SQLiteOpenHelper {

    public DatabaseHandlerDiary(Context context) {
        super(context, Util.DATABASE_NAME_DIARY, null, Util.DATA_BASE_VERSION_DIARY);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_DIARY_TABLE = "CREATE TABLE " + Util.TABLE_NAME_DIARY + " ("
                + Util.KEY_ID_DIARY + " INTEGER PRIMARY KEY," + Util.KEY_NAME_DIARY + " TEXT,"
                + Util.KEY_DESC_DIARY + " TEXT," + Util.KEY_DATE_ADDED_DIARY + " LONG);";
        db.execSQL(CREATE_DIARY_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String DROP_TABLE = String.valueOf(R.string.db_drop);
        db.execSQL(DROP_TABLE, new String[]{Util.DATABASE_NAME_DIARY});

        //create new table
        onCreate(db);
    }

    //CRUD: create read update delete

    //add diary
    public void addDiary(Diary diary) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(Util.KEY_NAME_DIARY, diary.getDiaryName());
        values.put(Util.KEY_DESC_DIARY, diary.getDiaryDesc());
        values.put(Util.KEY_DATE_ADDED_DIARY, java.lang.System.currentTimeMillis());

        //^ insert to row
        db.insert(Util.TABLE_NAME_DIARY, null, values);
        db.close();
    }

    //get diary
    public Diary getDiary(int id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(Util.TABLE_NAME_DIARY, new String[]{
                        Util.KEY_NAME_DIARY, Util.KEY_DESC_DIARY, Util.KEY_DATE_ADDED_DIARY},
                Util.KEY_ID_DIARY +"=?", new String[]{String.valueOf(id)},
                null, null, null);

        if (cursor != null) {
            cursor.moveToFirst();
        }

        Diary diary = new Diary();
        diary.setDiaryId(Integer.parseInt(cursor.getString(0)));
        diary.setDiaryName(cursor.getString(1));
        diary.setDiaryDesc(cursor.getString(2));

        return diary;
    }

    //get all diaries
    public List<Diary> getAllDiaries() {
        SQLiteDatabase db = this.getReadableDatabase();

        List<Diary> diaryList = new ArrayList<>();

        Cursor cursor = db.query(Util.TABLE_NAME_DIARY,
                new String[]{Util.KEY_ID_DIARY,
                        Util.KEY_NAME_DIARY,
                        Util.KEY_DESC_DIARY,
                        Util.KEY_DATE_ADDED_DIARY},
                null, null, null, null,
                Util.KEY_DATE_ADDED_DIARY + " DESC");

        if (cursor.moveToFirst()) { //if item exists
            do {
                Diary diary = new Diary();
                diary.setDiaryId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(Util.KEY_ID_DIARY))));
                diary.setDiaryName(cursor.getString(cursor.getColumnIndex(Util.KEY_NAME_DIARY)));
                diary.setDiaryDesc(cursor.getString(cursor.getColumnIndex(Util.KEY_DESC_DIARY)));

                //convert Timestamp to something readable
                DateFormat dateFormat = DateFormat.getDateInstance();
                String formattedDate = dateFormat.format(new Date(cursor.getLong(cursor.getColumnIndex(Util.KEY_DATE_ADDED_DIARY)))
                        .getTime()); // Feb 23, 2020
                diary.setDateDiaryAdded(formattedDate);

                //Add to arraylist
                diaryList.add(diary);
            } while (cursor.moveToNext());
        }
        return diaryList;
    }

    public int updateItem(Diary diary) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(Util.KEY_NAME_DIARY, diary.getDiaryName());
        values.put(Util.KEY_DESC_DIARY, diary.getDiaryDesc());
        values.put(Util.KEY_DATE_ADDED_DIARY, java.lang.System.currentTimeMillis());//timestamp of the system

        //update row
        return db.update(Util.TABLE_NAME_DIARY, values,
                Util.KEY_ID_DIARY + "=?",
                new String[]{String.valueOf(diary.getDiaryId())});

    }

    //Todo: Add Delete Item
    public void deleteItem(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(Util.TABLE_NAME_DIARY,
                Util.KEY_ID_DIARY + "=?",
                new String[]{String.valueOf(id)});

        //close
        db.close();

    }

    //Todo: getItemCount
    public int getItemsCount() {
        String countQuery = "SELECT * FROM " + Util.TABLE_NAME_DIARY;
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.rawQuery(countQuery, null);

        return cursor.getCount();

    }

}

package com.bawp.babyneeds.data;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.bawp.babyneeds.model.Diary;
import com.bawp.babyneeds.R;
import com.bawp.babyneeds.model.Plant;
import com.bawp.babyneeds.util.PlantDatabase;
import com.bawp.babyneeds.util.Util;

import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DatabaseHandlerPlant extends SQLiteOpenHelper {

    public DatabaseHandlerPlant(Context context) {
        super(context, PlantDatabase.DATABASE_NAME_PLANT, null, PlantDatabase.DATA_BASE_VERSION_PLANT);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_PLANT_TABLE = "CREATE TABLE " + PlantDatabase.TABLE_NAME_PLANT + " ("
                + PlantDatabase.KEY_ID_PLANT + " INTEGER PRIMARY KEY," + PlantDatabase.KEY_NAME_PLANT + " TEXT,"
                + PlantDatabase.KEY_PLANT_MEDIUM + " TEXT," + PlantDatabase.KEY_PLANT_POT_SIZE + " TEXT,"
                + PlantDatabase.KEY_PLANT_WATTAGE + " TEXT," + PlantDatabase.KEY_PLANT_MISC_NOTES + " TEXT,"
                + PlantDatabase.KEY_PLANT_SPECIES + " TEXT" + ")";
        db.execSQL(CREATE_PLANT_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String DROP_TABLE = String.valueOf(R.string.db_drop);
        db.execSQL(DROP_TABLE, new String[]{PlantDatabase.DATABASE_NAME_PLANT});

        //create new table
        onCreate(db);
    }

    //CRUD: create read update delete

    //add diary
    public void addPlant(Plant plant) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(PlantDatabase.KEY_NAME_PLANT, plant.getPlantName());
        values.put(PlantDatabase.KEY_PLANT_MEDIUM, plant.getPlantMedium());
        values.put(PlantDatabase.KEY_PLANT_POT_SIZE, plant.getPlantPotSize());
        values.put(PlantDatabase.KEY_PLANT_WATTAGE, plant.getPlantWattage());
        values.put(PlantDatabase.KEY_PLANT_MISC_NOTES, plant.getPlantDesc());
        values.put(PlantDatabase.KEY_PLANT_SPECIES, plant.getPlantSpecies());

        //^ insert to row
        db.insert(PlantDatabase.TABLE_NAME_PLANT, null, values);
        db.close();
    }

    //get plant
    public Plant getPlant(int id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(Util.TABLE_NAME_DIARY, new String[]{
                        Util.KEY_NAME_DIARY, Util.KEY_DESC_DIARY, Util.KEY_DATE_ADDED_DIARY},
                Util.KEY_ID_DIARY +"=?", new String[]{String.valueOf(id)},
                null, null, null);

        if (cursor != null) {
            cursor.moveToFirst();
        }

        Plant plant = new Plant();
        plant.setPlantId(Integer.parseInt(cursor.getString(0)));
        plant.setPlantName(cursor.getString(1));
        plant.setPlantMedium(cursor.getString(2));
        plant.setPlantPotSize(cursor.getString(3));
        plant.setPlantWattage(cursor.getString(4));
        plant.setPlantDesc(cursor.getString(5));
        plant.setPlantSpecies(cursor.getString(6));

        return plant;
    }

    //get all plants
    public List<Plant> getAllPlants() {
        SQLiteDatabase db = this.getReadableDatabase();

        List<Plant> plantList = new ArrayList<>();

        Cursor cursor = db.query(PlantDatabase.TABLE_NAME_PLANT,
                new String[]{PlantDatabase.KEY_ID_PLANT,
                        PlantDatabase.KEY_NAME_PLANT,
                        PlantDatabase.KEY_PLANT_MEDIUM,
                        PlantDatabase.KEY_PLANT_POT_SIZE,
                        PlantDatabase.KEY_PLANT_WATTAGE,
                        PlantDatabase.KEY_PLANT_MISC_NOTES,
                        PlantDatabase.KEY_PLANT_SPECIES},
                null, null, null,
                null, null);



       /* Cursor cursor = db.query(Util.TABLE_NAME_DIARY,
                new String[]{Util.KEY_ID_DIARY,
                        Util.KEY_NAME_DIARY,
                        Util.KEY_DESC_DIARY,
                        Util.KEY_DATE_ADDED_DIARY},
                null, null, null, null,
                Util.KEY_DATE_ADDED_DIARY + " DESC");*/

        if (cursor.moveToFirst()) { //if item exists
            do {
                Plant plant = new Plant();
                plant.setPlantId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(PlantDatabase.KEY_ID_PLANT))));
                plant.setPlantName(cursor.getString(cursor.getColumnIndex(PlantDatabase.KEY_NAME_PLANT)));
                plant.setPlantMedium(cursor.getString(cursor.getColumnIndex(PlantDatabase.KEY_PLANT_MEDIUM)));
                plant.setPlantPotSize(cursor.getString(cursor.getColumnIndex(PlantDatabase.KEY_PLANT_POT_SIZE)));
                plant.setPlantWattage(cursor.getString(cursor.getColumnIndex(PlantDatabase.KEY_PLANT_WATTAGE)));
                plant.setPlantDesc(cursor.getString(cursor.getColumnIndex(PlantDatabase.KEY_PLANT_MISC_NOTES)));
                plant.setPlantSpecies(cursor.getString(cursor.getColumnIndex(PlantDatabase.KEY_PLANT_SPECIES)));

                /*//convert Timestamp to something readable
                DateFormat dateFormat = DateFormat.getDateInstance();
                String formattedDate = dateFormat.format(new Date(cursor.getLong(cursor.getColumnIndex(Util.KEY_DATE_ADDED_DIARY)))
                        .getTime()); // Feb 23, 2020
                diary.setDateDiaryAdded(formattedDate);*/

                //Add to arraylist
                plantList.add(plant);
            } while (cursor.moveToNext());
        }
        return plantList;
    }

    public int updateItem(Plant plant) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(PlantDatabase.KEY_NAME_PLANT, plant.getPlantName());
        values.put(PlantDatabase.KEY_PLANT_MEDIUM, plant.getPlantMedium());
        values.put(PlantDatabase.KEY_PLANT_POT_SIZE, plant.getPlantPotSize());
        values.put(PlantDatabase.KEY_PLANT_WATTAGE, plant.getPlantWattage());
        values.put(PlantDatabase.KEY_PLANT_MISC_NOTES, plant.getPlantDesc());
        values.put(PlantDatabase.KEY_PLANT_SPECIES, plant.getPlantSpecies());

        //update row
        return db.update(PlantDatabase.TABLE_NAME_PLANT, values,
                PlantDatabase.KEY_ID_PLANT + "=?",
                new String[]{String.valueOf(plant.getPlantId())});

    }

    //Todo: Add Delete Item
    public void deleteItem(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(PlantDatabase.TABLE_NAME_PLANT,
                PlantDatabase.KEY_ID_PLANT + "=?",
                new String[]{String.valueOf(id)});

        //close
        db.close();

    }

    //Todo: getItemCount
    public int getItemsCount() {
        String countQuery = "SELECT * FROM " + PlantDatabase.TABLE_NAME_PLANT;
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.rawQuery(countQuery, null);

        return cursor.getCount();

    }

}

Answer 1

有三种方法你可以做到这一点。

1. 将数据库附加到已经打开的连接，例如 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.

​

1. 有多个打开的连接，通常是DatabaseHelpers (SQLiteOpenHelper的子类)，然后在访问每个数据库时使用相应的助手。
2. 使用将所有数据库实体(表、视图、触发器等)合并到一个数据库中的设计，其列根据日记指示差异。后者通常被视为规范。

所有三个的工作示例

下面是一个使用全部3的示例，每个示例都有一个旋转器来选择带有显示各自植物的ListView的日记:-

例如:-

​

​

选择“玛丽日记”(只有2篇日记)，为“玛丽日记”选择植物:-

​

​

代码有点复杂，但希望有实际意义的名称能够帮助解开。

日记类Diary.java (供所有人使用)

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;
    }
}

MultiDBHelper和附加数据库使用相同的日记DatabaseHelper DatabaseHelper，但使用不同的构造函数，以便附件日记的附加数据库与MultiDBHelper数据库有不同的名称。

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);
    }
}

使用GenericPlantDatabaseHelper.java的MultiDB及其附加数据库

• 这是有点抽搐，以便它可以给植物数据库不同的名称(即附加数据库前缀附加)。
• 在运行onCreate时插入数据，以使演示更短/更不复杂。

:-

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;
        }
    }
}

全合一数据库有它自己的DatabaseHelper，注意到植物表包含一个ownerId (日记的id (为了简单起见，没有使用外键))ownerId

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;
        }
    }

}

最后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);
        }
    }
}

从下面可以看出，单一数据库方法使用的磁盘空间较少(28 to，而不是60 to)：

​

​