Close SQLite Database in app with several activities

Question

I have class for work with SQLite database(PlaceDbProvider). It is singletone. The problem is that I have three activities that are use PlaceDbProvider. When it is better to call destroy method for PlaceDbProvider? I am confused because every activity has own onDestroy method.

public class PlaceDbProvider {
private static final String DB_NAME = "com.placesmanager";
private static final String TABLE_NAME = "places";
private static final int DB_VESION = 1;
private static final String KEY_ID = "_id";
private static final int ID_COLUMN = 0;
private static final String KEY_NAME = "name";
private static final int NAME_COLUMN = 1;

private Context context;
private Cursor cursor;
private SQLiteDatabase database;
private DbOpenHelper dbOpenHelper;

private static PlaceDbProvider mInstance = null;

private PlaceDbProvider(Context context) {
    this.context = context;
    init();
}

public static PlaceDbProvider getInstance(Context context) {
    if(mInstance == null) {
        mInstance = new PlaceDbProvider(context);
    }
    return mInstance;
}

public int getCount() {
    return cursor.getCount();
}


public Place getItem(int position) {
    if (cursor.moveToPosition(position)) {
        Place placeOnPositon = new Place();     
        placeOnPositon.setId(cursor.getLong(ID_COLUMN));
        placeOnPositon.setName(cursor.getString(NAME_COLUMN));
        return placeOnPositon;
    } else {
        throw new CursorIndexOutOfBoundsException(
                "Cant move cursor to postion");
    }
}


public long getItemId(int position) {
    if (cursor.moveToPosition(position)) {  
        return cursor.getLong(ID_COLUMN);
    } else {
        throw new CursorIndexOutOfBoundsException(
                "Cant move cursor to postion");
    }
}
public long addItem(Place place) {
    ContentValues values = new ContentValues();
    values.put(KEY_NAME, place.getName());
    long id = database.insert(TABLE_NAME, null, values);
    refresh();
    return id;
}
public boolean removeItem(Place placeToRemove) {
    boolean isDeleted = (database.delete(TABLE_NAME, KEY_NAME + "=?",
            new String[] { placeToRemove.getName() })) > 0;
    refresh();
    return isDeleted;
}

public boolean updateItem(long id, String key,String newValue) {
    ContentValues values = new ContentValues();
    values.put(key, newValue);
    boolean isUpdated = (database.update(TABLE_NAME, values, KEY_ID + "=?",
            new String[] {id+""})) > 0;
    return isUpdated;
}


public void destroy() {
    dbOpenHelper.close();
    mInstance = null;
}

private void refresh() {
    cursor = getAllEntries();
}

public Cursor getAllEntries() {

    String[] columnsToTake = { KEY_ID, KEY_NAME, KEY_LAT, KEY_LNG, KEY_TYPE, KEY_INFO, KEY_OWNER};

    return database.query(TABLE_NAME, columnsToTake,
            null, null, null, null, KEY_ID);
}

private void init() {
    dbOpenHelper = new DbOpenHelper(context, DB_NAME, null, DB_VESION);
    try {
        database = dbOpenHelper.getWritableDatabase();
    } catch (SQLException e) {
        Log.e(this.toString(), "Error while getting database");
        throw new Error("The end");
    }
    cursor = getAllEntries();
}

//class for creation, opening and db version control
private static class DbOpenHelper extends SQLiteOpenHelper {

    public DbOpenHelper(Context context, String name,
            CursorFactory factory, int version) {
        super(context, name, factory, version);
    }  

    @Override
    public void onCreate(SQLiteDatabase db) {
        final String CREATE_DB = "CREATE TABLE " + TABLE_NAME + " ("
                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + KEY_NAME + " TEXT NOT NULL);";
        db.execSQL(CREATE_DB);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }
}

}

Answer 1

I don't see a particular problem with using a singleton pattern, as long as you implement it correctly. It's true that an Activity or Service that uses the singleton could be destroyed, but this doesn't matter if you can re-create the singleton. You can have a database reference as a singleton, as long as you remember to keep it thread-safe. If the reference gets destroyed, simply re-open the database.

To close it regardless of where you are, you can call Database.close() from wherever you want, and then check in the singleton to see if the database is closed before calling SQLiteDatabase.close(). You may decide that you want to close the database every time you're finished with it, even if your app is still open.

I agree that you wait to open the database until you're ready to read/write.

As a note, if you call SQLiteDatabase.isOpen() before you call SQLiteDatabase.close(), it shouldn't matter where you try to close it.

Answer 2

It's better to implement methods open and close like this:

public DbAdapter open() throws SQLException
{
    dbHelper = new DbHelper(mCtx);
    db = dbOpenHelper.getWritableDatabase();
    return this;
}

public void close()
{
    dbHelper.close();
}

Note: DbAdapter it's your class for working with database. You shoud open your database just before the moment you want to read/insert/update data from database and close after this immediately, unnecessary in onDestroy().

Answer 3

The Singleton Pattern is not recommended for Android because Activities or Services holding the Singleton object could be destroyed according to Android's Apps Life Cycle. I would suggest you extend the Application Object of your app and deploy a static reference to the database.

On the other hand, try using a ContentProvider for database handling. At first it may sound too much work for simple tasks but the ContentProvider-ContentResolver combo provides great help when displaying your SQLite data with CursorAdapters and Loaders. If you use this method, no database initialization or closure is required.

Hope it helps.

Answer 4

1)Instanciate PlaceDbProvider with application context (context.getApplicationContext() )

2)Create your application class

3)in your application class, in onTerminate() method, call (PlaceDbProvider) onDestroy() method

(see my other answer at this link for the creation of the application class : https://stackoverflow.com/a/13994622/1789730)