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:

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

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.

1public class ThingDao implements MySQLiteDao {
2 
3    private SQLiteOpenHelper db;
4 
5    public ThingDao(SQLiteOpenHelper db) {
6        this.db = db;
7    }
8 
9    @Override
10    public void onCreate(SQLiteDatabase sqlDb) {
11        sqlDb.execSQL("CREATE TABLE ...)");
12    }
13 
14    @Override
15    public void onUpdate(SQLiteDatabase sqlDb, int oldVersion, int newVersion) {
16        if (newVersion = 2) {           
17            sqlDb.execSQL("...");
18        }
19    }
20 
21    public List<Thing> getThings() {
22        List<Thing> things = new ArrayList<>();
23        Cursor c = db.getReadableDatabase().rawQuery("SELECT ... ", new String[]{});
24 
25        for (int i = 0; i < c.getCount(); i++) {
26           c.moveToPosition(i);
27           things.add(new Thing(...);
28        }
29         
30        c.close();
31        return things;
32    }
33 
34    public Thing getThing(int id) {
35       ...
36    }
37 
38    public void deleteThing(Thing thing) {
39       ...
40    }
41    ...
42}

Then the SQLiteOpenHelper implementation itself is much easier to read

1public class MySqlHelper extends SQLiteOpenHelper {
2 
3    private static MySqlHelper instance;
4 
5    private static final String DATABASE_NAME = "my.db";
6    private static final int SCHEMA = 1;
7 
8 
9    private ThingDao thingDao;
10 
11    public synchronized static MySqlHelper getInstance(Context ctxt) {
12        if (instance == null) {
13            instance = new MySqlHelper(ctxt.getApplicationContext());
14        }
15 
16        return (instance);
17    }
18 
19    public MySqlHelper(Context context) {
20        super(context, DATABASE_NAME, null, SCHEMA);
21    }
22 
23    @Override
24    public void onCreate(SQLiteDatabase db) {
25        getThingDao().onCreate(db);
26        //other DAOs called here
27    }
28 
29    @Override
30    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
31        getThingDao().onUpdate(db, oldVersion, newVersion);
32        //other DAOs called here
33    }
34 
35    public ThingDao getThingDao() {
36        if (thingDao == null) {
37            thingDao = new ThingDao(this);
38        }
39        return thingDao;
40    }
41}

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.

`1`	`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…

139

anorak:girl

Refactoring SQLiteOpenHelper so it isn’t a mile long

Leave a Reply Cancel reply