Refactoring SQLiteOpenHelper so it isn’t a mile long

If, like me, you are new to Android Development you may find that your SQLiteOpenHelper implementation can quickly get long and disorganised, as data access methods for tables are added organically during development. It gets hard to see which methods are related, and starts to feel cumbersome. You can only have one SQLiteOpenHelper per database, but it isn’t difficult to refactor the single SQLiteOpenHelper into multiple classes with distinct responsibilities.

To keep things really tidy, I defined an interface for my new classes:

public interface MySQLiteDao {
    void onCreate(SQLiteDatabase db);
    void onUpdate(SQLiteDatabase db, int oldVersion, int newVersion);
}

The individual DAO classes are constructed with an instance of SQLiteOpenHelper which is used in data access methods. onCreate and onUpgrade contain the create/update sql specific to this table.

public class ThingDao implements MySQLiteDao {

    private SQLiteOpenHelper db;

    public ThingDao(SQLiteOpenHelper db) {
        this.db = db;
    }

    @Override
    public void onCreate(SQLiteDatabase sqlDb) {
        sqlDb.execSQL("CREATE TABLE ...)");
    }

    @Override
    public void onUpdate(SQLiteDatabase sqlDb, int oldVersion, int newVersion) {
        if (newVersion = 2) {           
            sqlDb.execSQL("...");
        }
    }

    public List<Thing> getThings() {
        List<Thing> things = new ArrayList<>();
        Cursor c = db.getReadableDatabase().rawQuery("SELECT ... ", new String[]{});

        for (int i = 0; i < c.getCount(); i++) {
           c.moveToPosition(i);
           things.add(new Thing(...);
        }
        
        c.close();
        return things;
    }

    public Thing getThing(int id) {
       ...
    }

    public void deleteThing(Thing thing) {
       ...
    }
    ...
}

Then the SQLiteOpenHelper implementation itself is much easier to read

public class MySqlHelper extends SQLiteOpenHelper {

    private static MySqlHelper instance;

    private static final String DATABASE_NAME = "my.db";
    private static final int SCHEMA = 1;


    private ThingDao thingDao;

    public synchronized static MySqlHelper getInstance(Context ctxt) {
        if (instance == null) {
            instance = new MySqlHelper(ctxt.getApplicationContext());
        }

        return (instance);
    }

    public MySqlHelper(Context context) {
        super(context, DATABASE_NAME, null, SCHEMA);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        getThingDao().onCreate(db);
        //other DAOs called here
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        getThingDao().onUpdate(db, oldVersion, newVersion);
        //other DAOs called here
    }

    public ThingDao getThingDao() {
        if (thingDao == null) {
            thingDao = new ThingDao(this);
        }
        return thingDao;
    }
}

You can group the data access methods into DAO classes in whatever way makes sense for the application – it doesn’t have to be one per table. In your application code, you can easily get an instance of the DAO you want.

ThingDao = MySqlHelper.getInstance(this).getThingDao();

This means you are working with smaller classes containing related methods – much easier to maintain than a sprawling mess containing every DAO method you dashed out in order to get the thing working…

  

Leave a Reply

Your email address will not be published. Required fields are marked *