Managing SQLite Connections In Android

development

It can be difficult to correctly manage SQLite database connections in Android. Memory leak issues can occur when opening more database connections than have been closed. Also, some database writes may fail when creating and using more than one SQLiteOpenHelper object in your project.

There are a couple solutions to this problem. One is to wrap the SQLiteDatabase in a ContentProvider. I don't have much experience with this so I'm not sure if it's a good option or not, but it's worth looking into. The other option is to use the singleton pattern for your SQLiteOpenHelper class.

The singleton pattern is a design pattern that restricts the instantiation of a class to one object. Doing this ensures safe access to the database as all database writes can be managed by Android through the single database connection. See Wikipedia for more information on the singleton pattern.

Below is example code to get you started:

public class DatabaseHelper extends SQLiteOpenHelper {

    private static DatabaseHelper instance;

    private static final String DATABASE_NAME = "enter database name here";
    private static final int DATABASE_VERSION = 1;

    private static final String TABLE_NAME = "enter table name here";
    private static final String SQL_CREATE_TABLE = "enter sql to create table";

    /**
     * Use getInstance() whenever you need a new DatabaseHelper.
     */
    public static synchronized DatabaseHelper getInstance(Context context) {
        // A new instance will only be created once otherwise it will be reused.
        if (instance == null) {
            instance = new DatabaseHelper(context.getApplicationContext());
        }
        return instance;
    }

    /**
     * Instead of directly instantiating make a call to getInstance().
     */
    private DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(SQL_CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        String logMessage = String.format(
            "Upgrading database from version %d to %d.", oldVersion, newVersion);
        Log.w(DatabaseHelper.class.getName(), logMessage);

        database.execSQL(String.format("DROP TABLE IF EXISTS %s;", TABLE_NAME));
        this.onCreate(database);
    }

}

View other posts