Using Singleton Design Pattern for SQLitedatabase

Using Singleton design pattern for SQLiteDatabase

Click here to see my blog post on this subject.

---

Here is some sample code that illustrates three possible approaches. These will allow access to the database throughout the application.

Approach #1: have `SQLiteOpenHelper` be a static data member

This isn't the complete implementation, but it should give you a good idea on how to go about designing the DatabaseHelper class correctly. The static factory method ensures that there exists only one DatabaseHelper instance at any time.

/**
 * create custom DatabaseHelper class that extends SQLiteOpenHelper
 */
public class DatabaseHelper extends SQLiteOpenHelper { 
    private static DatabaseHelper mInstance = null;

    private static final String DATABASE_NAME = "databaseName";
    private static final String DATABASE_TABLE = "tableName";
    private static final int DATABASE_VERSION = 1;

    private Context mCxt;

    public static DatabaseHelper getInstance(Context ctx) {
        /** 
         * use the application context as suggested by CommonsWare.
         * this will ensure that you dont accidentally leak an Activitys
         * context (see this article for more information: 
         * http://android-developers.blogspot.nl/2009/01/avoiding-memory-leaks.html)
         */
        if (mInstance == null) {
            mInstance = new DatabaseHelper(ctx.getApplicationContext());
        }
        return mInstance;
    }

    /**
     * constructor should be private to prevent direct instantiation.
     * make call to static factory method "getInstance()" instead.
     */
    private DatabaseHelper(Context ctx) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.mCtx = ctx;
    }
}

Approach #2: abstract the SQLite database with a `ContentProvider`

This is the approach I would suggest. For one, the new CursorLoader class requires ContentProviders, so if you want an Activity or Fragment to implement LoaderManager.LoaderCallbacks<Cursor> with a CursorLoader (which I suggest you take advantage of, it is magical!), you'll need to implement a ContentProvider for your application. Further, you don't need to worry about making a Singleton database helper with ContentProviders. Simply call getContentResolver() from the Activity and the system will take care of everything for you (in other words, there is no need for designing a Singleton pattern to prevent multiple instances from being created).

Hope this helps!

Using Singleton pattern for Sqlite database in Android

You should make the DatabaseHelper constructor private and create instances of this class by using the getInstance method. eg: mSQLHelper = DatamabseHelper.getInstance(context).

To call a method of this class, you can do something like this.

DatabaseHelper.getInstance(context).someFunction(...);

And to use any of the DatabaseProcessor functions, you can do this:

new DatabaseProcessor(context).insertSomethingIntoDb(...);

Keep in mind that this singleton approach has some problems, for starters, it doesn't support multithreading, there is no mechanism in place to assure that if two threads ask for an instance at the same time, only one instance will be created.

Correctly open/close a database with Singleton design pattern

You should call close anytime you are done writing to your database. For example when you insert data, you will have an open connection to the database that should be closed when it is done.

Reading is different. When you create a SQLite database on your phone, the data is persistent. The database exists and the handler you create provides a convenient way to access that information. Reading the database usually takes place by getting a readable instance of the database and using a Cursor to extract values. In that case you close the cursor when you're done, not the database itself.

You're right that you should not be closing the database connection during separate activities' lifecycle methods. Instead, as suggested above, close the database connection in your handler's methods that write to the database when you are done performing that transaction.

Creating a global SQLite DB Connection with Singleton pattern in Swift

When I attempt to change the static conn to an instance variable I get the error Instance member 'conn' cannot be used on type 'DBConnection'

That makes sense considering how you're using open:

try DBConnection.open(dbPath: FileManager.default.urls(for: FileManager.SearchPathDirectory.documentDirectory, in: FileManager.SearchPathDomainMask.userDomainMask)[0])

Right now, open is a static method of DBConnection, which means that you can call it without needing an instance of the class. It's similar to a "class method" in Objective-C. But in your broken code, you have:

var conn: Connection? = nil

so conn is an instance variable, i.e. a variable that only exists in the context of an instance of the class. A quick fix would be to make open non-static:

func open(dbPath: URL) throws {

and then call it using the shared instance:

try DBConnection.shared.open(dbPath: FileManager.default.urls(for: FileManager.SearchPathDirectory.documentDirectory, in: FileManager.SearchPathDomainMask.userDomainMask)[0])

(Note the shared added there.)

However, ideally I'd like for the open function to fire when the Singleton is first created without having to explicitly call it and assign it to some variable within the class.

You could call open from inside the initializer if you want the connection to open when the singleton is created.

Another direction you could go is to leave conn and open both static so that open can access conn, and let the class itself be the singleton. Then you could eliminate the shared variable.

However, I think the best option would be to do as described above, making conn and open non-static, and get rid of the shared instance and instead just keep track of the DBConnection object that you create.

Handling SQLite singleton instances and its Context dependency in Android

You are trading lazy initialization for static initialization.

In general, lazy initialization can amortize the cost of initialization over the life of an application. In this case it seems less important, for two reasons:

1. It is almost certain that you will need this DB. It seems unlikely that by putting the initialization off, you might avoid having to do it at all.
2. The Android framework guarantees that the DBHelper constructor can be run from the UI thread: it is not what takes the time. The thing that takes the time is the first call to getWriteableDatabase. The lazy creation of the Helper accomplishes almost nothing.

You might consider making the code even less convoluted, by initializing the DB in the Application like this:

public class DBDrivenApp extends Application implements DBProvider {

  // ...

  private PostsDatabaseHelper db;

  // ...

  @Override
  public void onCreate() {
    super.onCreate();
    db = new PostsDatabaseHelper(this);
  }

  @Override
  public PostsDatabaseHelper getDB() { return db; }

  // ...
}

... and, better yet, use an IoC framework, like Dagger2, to inject the database instance, so that you can mock it in testing.

How to adapt Apple singleton pattern for accessing SQLite database?

I think you should use FMDB, it is one of good wrapper libraries around for SQLite3.

See details about FMDB here http://www.ioslib.com/library/data/fmdb/

---

Related Topics