Android database connections and cursors oh my
android, database, database-cursor, sqlite
Solution
This is actually very simple:
- explicitly close all your cursors when done (using `finally`), etc.
- don't use `startManagingCursor()`
- make your database wrapper/helper/manager/whatever class a singleton
- don't explicitly call `close()` on the database helper. I will be automatically closed when your process dies.
Additionally: frequent opening and closing is definitely a bad idea, since it carries quite a lot of overhead.
Using loaders is also an option. You definitely don't need to use a content provider to use a loader however it is not as straightforward as it could be. Using an content provider involves IPC which is usually overkill if you don't plan to export your data to other apps.
Problem
I have read plenty of blogs and tutorials on how to create and use database connections when working with android. Although I have plenty of working examples, different implementations result in different issues. Example, I use a datasource class, `Datasource` and a database helper class, `DBManagement`. DataSource ``` public class DataSource { // Database fields private SQLiteDatabase database; private DBManagement dbHelper; public SMSDataSource(Context context) { dbHelper = new DBManagement(context); } public void open() throws SQLException { if(database == null){ database = dbHelper.getWritableDatabase(); } } public Cursor exampleCursor(long constraint){ Cursor cur = database.query(DBManagement.TABLE_NAME, new String[] {DBManagement.Column}, "constraint="+constraint, null, null, null, null); return cur; } //.. other methods down here which do rawQuery, QueryBuilder, etc.. ``` DBManagement ``` public class DBManagement extends SQLiteOpenHelper{ // .. table definitions and columns etc ..// public DBManagement(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } ``` In my onCreate methods within activity, I will call `datasource.open()` and the SQL connection is open. After that I will do: ``` DataSource datasource = new DataSource(); Cursor cursor = datasource.exampleCursor(1); startManagingCursor(cursor); ``` If I navigate to a new activity, I get the following error: ``` 06-27 21:59:14.812: E/Database(13396): close() was never explicitly called on database '/data/data/com.example.package/databases/db.db' ``` If i add `datasource.close();` to the end of onCreate, none of the simple cursor adapters work, or I get errors that the db is not open if perform an action on a conextual menu. What is the best way to handle the above? So I did the following, and I am still getting the database issue: ``` @Override public void onBackPressed() { // do something on back. //Log.i(getClass().toString(), "onBackPressed"); datasource.close(); finish(); return; } @Override protected void onResume(){ super.onResume(); onCreate(null); } @Override protected void onRestart(){ datasource = new DataSource(this); datasource.open(); filters = datasource.getFilterCursor(); startManagingCursor(filters); super.onRestart(); } @Override protected void onPause(){ //Log.i(getClass().toString(), "onPause"); ((CursorAdapter) adapter).getCursor().close(); datasource.close(); super.onPause(); } @Override protected void onStop(){ //Log.i(getClass().toString(), "onStop"); datasource.close(); super.onStop(); } ``` My Datasource.java class has the following: ``` public Datasource(Context context){ dbHelper = new DBManagement(context); } public void open() throws SQLException { if( database == null ){ database = dbHelper.getWritableDatabase(); } } public void close(){ if(dbHelper != null){ dbHelper.close(); } } ```