tl;dr: What's the best way of handling instance of SQLiteOpenHelper class in an app with multiple Activities and Fragments? Should I have only one instance in the whole app and pass it between individual Activities, or can I create a new instance for every Activity and Fragment?
(Much) more detailed question: I'm having problems understanding how should I handle SQLiteOpenHelper instance in my app. I have a DBHelper (which extends the SQLiteOpenHelper class) and it's implemented as a singleton. Then I have one Activity with multiple Fragments inside (user switches between the Fragments by using a navigation drawer), and then I have some other Activities without any Fragments inside them. I read here many answers and comments here on SO that I should always have only one SQLiteOpenHelper instance, but these answers only gave examples with a single Activity. What those answers actually only said was that the SQLiteOpenHelper should be a singleton - which it already is in my app. But I couldn't find any info anywhere on how should I handle the SQLiteOpenHelper object in a bit more complex app.
I can think of three possible ways of dealing with the situation, and would really appreciate your input and guidance, as this is something that is not very well documented, in my opinion.
Possibility #1: Keeping only one instance of SQLiteOpenHelper and passing it between all Activities and Fragments in my app. For example, I would instantiate it in the first Activity that starts when the user launches the app and then I'd pass the instance to all other Activities and Fragments that need access to the database. That means that I would have only one instance of the SQLiteOpenHelper across the whole app.
Possibility #2: Creating a new instance of the SQLiteOpenHelper in every Activity that needs it. Activities are destroyed when a new one is started anyways, therefore the SQLiteOpenHelper instance from the old Activity should be garbage-collected, and so this seems like it should work. And the Fragments inside one of my Activities would simply get the SQLiteOpenHelper instance from their "parent" Activity (using an interface pattern, I guess). I read that when I keep only one instance of the SQLiteOpenHelper active at a time, there's not any problem with DB syncing. Also the database apparently never has to be closed with the singleton pattern.
Possibility #3: Creating a new instance of the SQLiteOpenHelper in every Activity and Fragment that needs it. This is how I first started writing my app, but now I'm on doubts - I have an instance of the SQLiteOpenHelper in the Activity that has some Fragments in it, and I have another instance of the database helper in the currently displayed Fragment. Therefore I have two active connections to the database at a time - one in the Fragment and one in its parent Activity.
I'm asking because I just enabled strict mode in my app, and now I'm getting crashes. Specifically:
StrictMode: Finalizing a Cursor that has not been deactivated or closed.
android.database.sqlite.DatabaseObjectNotClosedException: Application did not close the cursor or database object that was opened here
The weird thing is that the stack trace is pointing me to a line where I'm instantiating a Cursor and reading data from the database (the 5th line of the code):
public List<Long> getDrinkIdsByDayMinute(int dayOfWeek, int minuteOfDay) {
List<Long> result = new ArrayList<>();
SQLiteDatabase db = this.getReadableDatabase();
// The stack trace points to the following line:
Cursor cursor = db.rawQuery("SELECT " + DRINK_LOG_COLUMN_DRINK_ID + ", COUNT("
+ DRINK_LOG_COLUMN_DRINK_ID + ") FROM " + DRINK_LOG_TABLE_NAME + " WHERE "
+ DRINK_LOG_COLUMN_DAY_OF_WEEK + " = ? AND "
+ DRINK_LOG_COLUMN_MINUTE_OF_DAY + " BETWEEN ? AND ? AND "
+ DRINK_LOG_COLUMN_DELETED + " = ? GROUP BY " + DRINK_LOG_COLUMN_DRINK_ID
+ " ORDER BY 2 DESC",
new String[]{Integer.toString(dayOfWeek), Integer.toString(minuteOfDay - 30),
Integer.toString(minuteOfDay + 30), Integer.toString(0)});
while (cursor.moveToNext()) {
result.add(cursor.getLong(0));
}
cursor.close();
return result;
}
In the DBHelper class I have multiple methods for getting data from the database (and to write/update data as well), and in every single one of those methods I'm closing the Cursor with the data (because that's apparently the right thing to do to prevent memory leaks). But at the same time, in every single one of those methods I'm also calling getReadableDatabase() (or getWriteableDatabase()) methods, performing some queries on the database object, and then I'm NOT closing it (I read some mixed opinions on closing SQLiteDatabase objects). I actually tried to go ahead and close the SQLiteDatabase at the end of every method in my DBHelper class, but that resulted a crash with the same error in a different part of the code.
Is any of the possibilities (#1 - #3) a good one? Or should I think of some other way of dealing with the SQLiteOpenHelper instance? And, additional question, since I'm closing the Cursor in every single method in my DBHelper class, should I also close the SQLiteDatabase object? And isn't my way of handling the SQLiteDatabase object wrong? Shouldn't I perhaps create a member variable with the SQLiteDatabase object when I'm instantiating the DBHelper class, and then use only this one instead of instantiating it in every method?
