Singleton vs a static inner class for Database Helper class in Android

Question

I am trying to see which of these implementation is better for accessing sqlite database in Android Applications

Implementation 1

Using DatabaseHelper classes extending SqliteOpenHelper and using singleton pattern. In some rare occasions I do see crashes because database being closed. Although a little irritating, I let them pass just because it was minor in the grand scheme of things and the number of projects I have.

public class DBHelper extends SQLiteOpenHelper {

        private static DBHelper instance;

        private final String CREATE_HEALTH_DATA_TABLE = "CREATE TABLE IF NOT EXISTS my_table ( " 
        + BaseColumns._ID   + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
        + MT_FIELD_NAME     + " TEXT NOT NULL );";

        private DBHelper getInstance(Context c) {
             if (instance == null) {
                  instance = new DBHelper(c);
             }
             return instance;
        }

        private DBHelper(Context c) {
             super(c, "my_database.sqlite", null, 0);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_TABLE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}

    }

Implementation 2

The other way of implementing this is to create the helper class as a static inner class within the Content Provider class. This is how it is done in the Notepad Example in the samples.

public class DataProvider extends ContentProvider {

    /*..Content provider implementation only dbhelper variable and onCreate are shown */

    private MTDBHelper dbHelper;

    @Override
    public boolean onCreate() {
         dbHelper = new DBHelper(getContext());
         return true;
    }

    private static class DBHelper extends SqliteOpenHelper {

    /// Implementation without making it a singleton

    }

}

Which among these is a better or standard way of things and why. I am guessing it is the second approach as it is an inner private class but would like to have an expert opinion.

I am using Content Providers always if that matters.

Thank you

Answer 1

I do neither. I have a non-static class that extends SqliteOpenHelper. Whenever I need the db, I open it by creating a new DBHelper instance, do what I need to do with it, and immediately close it, reopening it later only when I need it. It's possible to run into the case with this implementation where two sections of code try to modify the db at the same time. To avoid that, wrap every method in synchronized blocks. I haven't had any issues with this approach.