Pinnate: Android and SQLite. Play nice.

My focus is to look at an efficient way to approach interaction in Android using SQLite.

In the world of mobility, there is a fine balance between building responsive applications and managing the underlying data.

SQLite in Android prescribes the use of using the following components:

· SQLiteOpenHelper

· SQLiteDatabase

SQLiteOpenHandler is primarily responsible for obtaining and managing the actual database, whereas SQLiteDatabase is responsible for interaction to the data. Once the database is actually available is obtained, operations such as insert, update, delete and query can be performed with ease, after which the database should ideally be closed.

The following snippet of code provides a very basic example of a typical database implementation, responsible for storing location based information.

public class DBHelper {

private static final String databaseName = "gps.db";

private static final int databaseVersion = 2;

private static final String locationTable = "location";

private static final String idColumn = "id";

private static final String lattitudeColumn = "lattitude";

private static final String longitudeColumn = "longitude";

private static final String speedColumn = "speed";

private static final String bearingColumn = "bearing";

private static final String accuracyColumn = "accuracy";

private static final String altitudeColumn = "altitude";

private static final String createdColumn = "created";

private String[] locationColumns = new String[] { idColumn,

lattitudeColumn,

longitudeColumn,

speedColumn,

bearingColumn,

accuracyColumn,

altitudeColumn,

createdColumn };

private Context context;

private SQLiteDatabase database;

private DBOpenHelper dbOpenHelper;

private static class DBOpenHelper extends SQLiteOpenHelper {

String createTable = "CREATE TABLE " + locationTable + "( " +

idColumn + " INTEGER PRIMARY KEY autoincrement," +

lattitudeColumn + " DOUBLE, " +

longitudeColumn + " DOUBLE, " +

speedColumn + " DOUBLE, " +

bearingColumn + " FLOAT, " +

accuracyColumn + " FLOAT, " +

altitudeColumn + " DOUBLE, " +

createdColumn + " DATETIME);";

ContentValues defaultContent = new ContentValues();

DBOpenHelper(Context context) {

super(context,

databaseName,

null,

databaseVersion);

}

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL(createTable);

}

@Override

public void onUpgrade(SQLiteDatabase db,

int oldVersion,

int newVersion) {

}

public DBHelper(Context context) {

this.context = context;

this.dbOpenHelper = new DBOpenHelper(this.context);

}

public DBHelper open() {

database = dbOpenHelper.getWritableDatabase();

return this;

}

public void close() {

dbOpenHelper.close();

}

public long addLocation(double lattitude,

double longitude,

double speed,

float bearing,

float accuracy,

double altitude) {

ContentValues values = new ContentValues();

values.put(lattitudeColumn, lattitude);

values.put(longitudeColumn, longitude);

values.put(speedColumn, speed);

values.put(bearingColumn, bearing);

values.put(accuracyColumn, accuracy);

values.put(altitudeColumn, altitude);

values.put(createdColumn, System.currentTimeMillis());

return database.insert(locationTable,

null,

values);

}

public Cursor getAllLocations() {

return database.query(locationTable,

locationColumns,

null,

null);

}

On a technical level, there is really nothing wrong with this code. But, consider the following aspects of the snippet.

· It is not clean and reusable, as the caller would have to "hope" that they pick the correct ordinal when retrieving values.

· There is no separation of concerns, as the database creation, management and interaction is tossed into a single class.

· The database opening and closing is not safely managed, as the caller would have to ensure that the database is opened and closed with each interaction.

· The use of "magic" strings and data.

With me being paranoid and obsessive when it comes to coding, I take a approach that the caller is not a team player. I also do not want my caller to be burdened with the implementation and rules of the underlying database I am using.

So, what is a cleaner approach to this?

Let's approach the database interaction through the use of interfaces.

This would allow us to start using dependency injection frameworks to manage boilerplate code, separating concerns in our code while promoting reuse.

Interfaces

public interface IDBHelper {
    public enum Table {
        Location("location");

        private String name = "";

        private Table(String name) {
            this.name = name;
        }

        public String getTableName() {
            return name;
        }
    }

    long insert(Table table,

String nullColumnHack,

ContentValues values);

long update(Table table,

ContentValues values,

String whereClause,

String[] whereArgs);

<T> ArrayList<T> query(Table table,

String[] columns,

String selection,

String[] selectionArgs,

String groupBy,

String having,

String orderBy,

ICursorHandler<T> cursorHandler);
}

IDBHelper is solely responsible to facilitate direct database interactions, but also to host the list of tables in the system.

public interface ILocationRepository {
public enum Column { Id("id"),

Lattitude("lattitude"),

Longitude("longitude"),

Speed("speed"),

Bearing("bearing"),

Accuracy("accuracy"),

Altitude("altitude"),

                                        Created("created");

        private String columnName = "";

        private Column(String columnName) {
            this.columnName = columnName;
        }

        public String getColumnName() {
            return columnName;
        }
    }

    Boolean submit(Location location);

    ArrayList<Location> getList();
}

ILocationRepository is responsible to handle interactions between the actual database and the caller, but also to host the list of columns available for Location.

ILocationRepository also employs the use of method level generics when data is queried, which forms the basis of all database control in this post.

public interface ICursorHandler<T> {
ArrayList<T> getList(Cursor cursor);
}

ICursorHandler is a generic implementation which will allow us to separate our caller from the database, while ensuring that the caller do lose functionality.

Implementations

public class DBHelper implements IDBHelper {
    private DBOpenHelper dbOpenHelper;

    public DBHelper() {
        dbOpenHelper = new DBOpenHelper();
    }

    @Override
    public long insert(Table table,

String nullColumnHack,

                                ContentValues values) {
        SQLiteDatabase database = dbOpenHelper.getWritableDatabase();

        long result = database.insert(table.getTableName(),

nullColumnHack,

                                                      values);

        if (database.isOpen())
            database.close();

        return result;
    }

    @Override
    public long update(Table table,

ContentValues values,

String whereClause,

                                  String[] whereArgs) {
        SQLiteDatabase database = dbOpenHelper.getWritableDatabase();

        long result = database.update(table.getTableName(),

values,

whereClause,

                                                        whereArgs);

        if (database.isOpen())
            database.close();

        return result;
    }

    @Override
    public <T> ArrayList<T> query(Table table,

String[] columns,

String selection,

String[] selectionArgs,

String groupBy,

String having,

String orderBy,

                                                     ICursorHandler<T> cursorHandler) {
        SQLiteDatabase database = dbOpenHelper.getReadableDatabase();

        Cursor cursor = database.query(table.getTableName(),

columns,

selection,

selectionArgs,

groupBy,

having,

                                                           orderBy);

        ArrayList<T> result = cursorHandler.getList(cursor);

        if (!cursor.isClosed())
            cursor.close();

        if (database.isOpen())
            database.close();

        return result;
    }
}

DBHelper is the final responsible implementation for the SQLite database, and its sole intention is to keep the database responsive and stable. Both the insert and update members immediately closes the database when the operation is complete, before the result is returned. The query member is a bit trickier and will be covered in detail shortly.

public class DBOpenHelper extends SQLiteOpenHelper {

String createTable = "CREATE TABLE " + IDBHelper.Table.Location.getTableName() + "( " +

ILocationRepository.Column.Lattitude.getColumnName() + " DOUBLE, " +

ILocationRepository.Column.Longitude.getColumnName() + " DOUBLE, " +

ILocationRepository.Column.Speed.getColumnName() + " DOUBLE, " +

ILocationRepository.Column.Bearing.getColumnName() + " FLOAT, " +

ILocationRepository.Column.Accuracy.getColumnName() + " FLOAT, " +

ILocationRepository.Column.Altitude.getColumnName() + " DOUBLE, " +

        ILocationRepository.Column.Created.getColumnName() + " DATETIME);";

    ContentValues defaultContent = new ContentValues();

    DBOpenHelper(Context context) {
        super(context, databaseName, null, databaseVersion);
    }

    @Inject
    private static Provider<Context> contextProvider;

    private static String databaseName = "gps.db";
    private static int databaseVersion = 2;

    DBOpenHelper() {
        super(contextProvider.get(),

databaseName,

null,

                  databaseVersion);
    }

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

    @Override
    public void onUpgrade(SQLiteDatabase db,

int oldVersion,

int newVersion) {
}
}

DBOpenHelper would be solely responsible for the creation of the database, but it would be evident that the class do not know the exact table and column names, as that data is seperated into specific enumerations residing within the interfaces created earlier.

public class LocationRepository implements ILocationRepository, ICursorHandler<Location> {

@Inject

private IDBHelper dbHelper;

private final IDBHelper.Table table = IDBHelper.Table.Location;

private String[] columns = new String[] { Column.Lattitude.getColumnName(), Column.Longitude.getColumnName(),

Column.Speed.getColumnName(), Column.Bearing.getColumnName(), Column.Accuracy.getColumnName(), Column.Altitude.getColumnName(),

Column.Created.getColumnName() };

@Override

public Boolean submit(Location location) {

return insert(location);

}

private Boolean insert(Location location) {

ContentValues values = new ContentValues();

values.put(Column.Lattitude.getColumnName(), location.getLatitude());

values.put(Column.Longitude.getColumnName(), location.getLongitude());

values.put(Column.Speed.getColumnName(), location.getSpeed());

values.put(Column.Bearing.getColumnName(), location.getBearing());

values.put(Column.Accuracy.getColumnName(), location.getAccuracy());

values.put(Column.Altitude.getColumnName(), location.getAltitude());

values.put(Column.Created.getColumnName(), System.currentTimeMillis());

long result = dbHelper.insert(table, null, values);

return (result > 0);

}

@Override

public ArrayList<Location> getList() {

ArrayList<Location> returnList = dbHelper.query(table,

columns,

null,

this);

return returnList;

}

@Override

public ArrayList<Location> getList(Cursor cursor) {

ArrayList<Location> returnList = new ArrayList<Location>();

cursor.moveToFirst();

while (cursor.isAfterLast() == false) {

Location location = new Location("");

location.setLatitude(cursor.getDouble(Column.Lattitude.ordinal()));

location.setLongitude(cursor.getDouble(Column.Longitude.ordinal()));

location.setSpeed(cursor.getFloat(Column.Speed.ordinal()));

location.setBearing(cursor.getFloat(Column.Bearing.ordinal()));

location.setAccuracy(cursor.getFloat(Column.Accuracy.ordinal()));

location.setAltitude(cursor.getDouble(Column.Altitude.ordinal()));

returnList.add(location);

cursor.moveToNext();

}

return returnList;

}

LocationRepository will be handling all database interactions between IDBHelper and the caller. It is important to notice in this code that the use of dependency injection is utilised to obtain the dbHelper.

It would be evident where the advantage of the column enumeration is experienced, as no "magic" string exists.

All right, now let’s have a look at what the elusive query method implementation gives use with regards to a cursor.

The objective is to ensure that the caller (LocationRepository) is burdened with the responsibility of opening and closing databases, but still ensuring that the caller has the absolute control of handling the data.

This is achieved through the implementation of the generic ICursorHandler interface, which accepts a Cursor as a parameter when calling the getList member.

This member is solely responsible to utilise the incoming cursor, iterate through it and extract the data, again using the column enumeration defined in the ILocationRepository interface. The member at this stage only knows about the Cursor, not the database or state thereof.

When the dbHelper is queried, the current LocationRepository is passed in as the last parameter, allowing the dbHelper to have access to the members defined in ICursorHandler.

The dbHelper query member is then responsible for the following:

1. Obtain the database

2. Query the database and obtain the relevant cursor.

3. Since the dbHelper cannot generically populate the return list, it utilises the getList member in ICursorHandler, to populate and return the results. Which in this case is done by the member in LocationRepository?

4. Checks the state of the cursor and closes it if necessary.

5. Checks the state of the database and closes it if necessary.

6. Return the results.

This ensures that by the time the caller receives the results, the database is already stabilised and closed at all times, and the caller has no idea of what the database is or where it exists.

This type of approach is reusable in many different scenarios and languages, especially when utilising direct database access such as ADO.NET, allowing the "swapping" of databases without the change in calling code.

In my next post, I will cover dependency injection in Android.

Pinnate

Thursday, 29 September 2011

Android and SQLite. Play nice.

No comments:

Post a Comment