javaandroidsqliteandroid-sqlite
Displaying data for the correct corresponding SQLite database Android/Java
I have a recyclerview with a list of items or "diaries" that have data stored in an sql database "diaries". When I click on a diary, it will take me to an activity where I can add more items or "plants" to a different sql database "plants". I want to make sure I'm displaying the right plants for the corresponding diary that was clicked.
How do I make sure I'm displaying the right plant database when I click a diary? I'm not sure how to link the databases. Thanks...
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();
}
}
Solution
There are three ways you can go about this.
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.
Have multiple open connections, typically DatabaseHelpers (subclasses of SQLiteOpenHelper) and then utilise the respective helper when accessing each database.
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) :-
- Keep numbers which appear in both columns, in J lang
- Differentiation in J
- How to reshape an array with an arbitrary size in one dimension?
- Why is Insert (fold) right associative
- Write 4 : 'x&{.&.;: y' tacitly
- Alignment issue when printing formatted prime numbers in J language
- How can I define a verb in J that applies a different verb alternately to each atom in a list?
- How to get user input in the J programming language
- How to unbox a list of boxed lists of differing lengths in J?
- How can I fix 'noun result was required' error in J?
- In j, how can I define a verb locally in one scope and pass it to a defined adverb?
- Convert boxed array to normal array?
- Read column of CSV file as array
- Replace atom in array of strings
- What does the dyad `=` do to boxed strings?
- Index of minimum element using J
- How can I take the outer product of string vectors in J?
- Building an array of verbs in J
- Reading in multidigit command line parameter
- Amend with bond to new data shows unexpected behaviour
- How to turn a table or matrix into a (flat) list in J
- How to run dissect in J?
- How to define selection using index function in J
- How to exit the J console?
- Find 4-neighbors using J
- Writing custom verbs in J
- How do I negate a selector in J lang?
- How to use arbitrary selector in interchange in J lang?
- different result once square root is added inside tacit
- Sum of arrays with repeated indices