Data Management

Adding SQLite to a sortable ListView

Having a sortable ListView in an Android app is one thing, but its not really useful unless it is persistent across activity instances — and that's where SQLite comes in.

After reading the previous article on sortable ListViews, one reader asked about the lack of persistence in the app, and why the app failed to remember the order that the user chose when reopened.

The simple half of the answer was that the code was re-initialising its sArray object array every time it started up; the complex half comes in adding SQLite to give the app its needed persistence.

The first thing to understand about using data stores with Android is the way that the operating system handles access to structured data with content providers.

For the toy examples I have been using and will use, it would be trivial to bolt on an SQLite database and feed it a serialised version of sArray. But sooner or later on Android, you will want to expose or consume data from other applications, and content providers is the proper way to go about it.

The table structure for our ListView data will consist of an id column, a name column (which is all that will be displayed to the user), and an ordering column. The SQL for this table will be:
CREATE TABLE list_items (_id INTEGER PRIMARY KEY, name TEXT, colorder INTEGER );
We need to have one of the columns be named (or aliased) as _id to allow for ListView binding.

Fetching data from content providers is completed via content URIs. In this app, the content URI takes the form of content://com.techrepublic.sortablelistview.listdataprovider/items/<id>, with the com.techrepublic.sortablelistview.listdataprovider portion forming what is known as the authority (which we will have to use later on). To fetch the entire list of items for our list, we will use content://com.techrepublic.sortablelistview.listdataprovider/items, and it fetches the element with an _id of 4, so we use content://com.techrepublic.sortablelistview.listdataprovider/items/4. To help with digesting the URIs, we make use of a UriMatcher that maps the URI options to states.

When working with SQLite databases on Android, it's best to make use of a database helper class that extends SQLiteOpenHelper to handle opening the database, creating the database if it does not exist, and upgrading said database if a new application version requires an updated database structure. This database helper is where we will use the create table SQL from above, and we will fill that database with the default values — the default values used in this instance will be the same sArray that we used in the previous article.

All of the above concepts manifest themselves as properties in this skeleton ListDataProvider:
package com.techrepublic.sortablelistview;

public class ListDataProvider extends ContentProvider {

public static String AUTHORITY = "com.techrepublic.sortablelistview.listdataprovider";

public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/items");

// The database that the provider uses as its underlying data store

public static final String DATABASE_NAME = "sortablelistview.db";

public static final int DATABASE_VERSION = 1;

public static final String TABLE_NAME = "list_items";

// Columns

public static final String _ID = "_id";

public static final String COLUMN_NAME = "name";

public static final String COLUMN_COLORDER = "colorder";

//Default sort order for items in the list

public static final String SORT_ORDER = " colorder ASC";

// UriMatcher stuff

public static final int GET_LIST = 0;

public static final int GET_ITEM = 1;

public static final int UPDATE_INCREMENT_ITEMS = 2;

private static final UriMatcher sURIMatcher = buildUriMatcher();

private DatabaseHelper dbHelper;

private static UriMatcher buildUriMatcher() {

UriMatcher matcher = new UriMatcher(UriMatcher.NO_MATCH);

// if pattern ends in "items", sends to fetch entire list

matcher.addURI(AUTHORITY, "items", GET_LIST);

// if pattern ends in "items/[number]" fetch item denoted by [number] as id

matcher.addURI(AUTHORITY, "items/#", GET_ITEM);

matcher.addURI(AUTHORITY, "items/update_increment", UPDATE_INCREMENT_ITEMS);

return matcher;

}

// Methods from ContentProvider that must be overwritten

@Override

public int delete(Uri arg0, String arg1, String[] arg2) {return 0;}

@Override

public String getType(Uri uri) {return null;}

@Override

public Uri insert(Uri uri, ContentValues values) {return null;}

@Override

public boolean onCreate() {return false;}

@Override

public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {return null;}

@Override

public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {return 0;}

// DatabaseHelper to help create and update SQLite database

private static class DatabaseHelper extends SQLiteOpenHelper {

private static final String CREATE_TABLE =

"CREATE TABLE " + TABLE_NAME + "( "

+ _ID + " INTEGER PRIMARY KEY,"

+ COLUMN_NAME +" TEXT, "

+ COLUMN_COLORDER + " INTEGER );";

DatabaseHelper(Context context) {

// calls the super constructor, requesting the default cursor factory.

super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL(CREATE_TABLE);

loadDefaults(db);

}

@Override

public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) { }

public void loadDefaults(SQLiteDatabase db){

Object[] sArray = {"Item 0", "Item 1", "Item 2", 42, false, "Item 5", "Item 6"};

for(int i=0;i<sArray.length;i++){

ContentValues initialValues = new ContentValues();

initialValues.put(COLUMN_NAME, sArray[i].toString());

initialValues.put(COLUMN_COLORDER, i);

db.insert(TABLE_NAME, COLUMN_NAME, initialValues);

}

}

}

}

The DatabaseHelper merely calls its super-class constructor, executes the CREATE TABLE SQL that now uses the properties at the start of the ListDataProvider, and loads the list_item table with the contents of the sArray as the row's name, and its index as the row's initial order in the list. The _id field is not set, since it provides an alias to the SQLite's underlying ROWID — be aware, though, that SQLite's selection of ROWID is a bit more complex than a simple ROWID+1.

Let's flesh out the abstract methods that need overwriting in ListDataProvider:
@Override

public boolean onCreate() {

dbHelper = new DatabaseHelper(getContext());

return true;

}

@Override

public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {

SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

qb.setTables(TABLE_NAME);

switch (sURIMatcher.match(uri)) {

case GET_LIST:

break;

case GET_ITEM:

qb.appendWhere(_ID + "=" + uri.getLastPathSegment());

break;

default:

throw new IllegalArgumentException("Unknown Uri: " + uri);

}

Cursor c = qb.query(dbHelper.getReadableDatabase(), null, selection, selectionArgs, null, null, SORT_ORDER);

return c;

}

@Override

public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

int count = -1;

switch (sURIMatcher.match(uri)) {

case UPDATE_INCREMENT_ITEMS:

//Constructs custom query

String rawsql = "UPDATE "+TABLE_NAME+" SET "+COLUMN_COLORDER+"="+COLUMN_COLORDER+"+"+values.getAsString("direction")+" WHERE "+selection;

dbHelper.getWritableDatabase().execSQL(rawsql);

break;

case GET_ITEM:

String rowID = uri.getLastPathSegment();

String finalSelection = _ID +" = " + rowID;

// If there were additional selection criteria, append them to the final WHERE clause

if (selection !=null) {

finalSelection = finalSelection + " AND " + selection;

}

// Does the update and returns the number of rows updated.

count = db.update( TABLE_NAME, values, finalSelection, selectionArgs );

break;

default:

throw new IllegalArgumentException("Unknown URI " + uri);

}

getContext().getContentResolver().notifyChange(uri, null);

// Returns the number of rows updated.

return count;

}

The query method makes use of an SQLiteQueryBuilder to append a restriction to a single list item id for URIs that take the format of content://com.techrepublic.sortablelistview.listdataprovider/items/[id], and returns a Cursor.

The update method is not as straight up and down as it would seem. Earlier, a URI pattern went unmentioned, which equates to UPDATE_INCREMENT_ITEMS. This state is needed, because, when a list item is moved, some of the items in the list need to have their order value incremented or decremented, depending on the direction of the changing item's movement. To replicate this movement in the database, it is a nice and simple SQL UPDATE statement, but Android does not provide any convenience classes or concepts to account for incrementing a number of rows — therefore, we need to create a special URI that activates a call on the database containing the customised SQL. This technique means that we only have to execute two SQL statements; one to increment the list items to make room for the moved list item, and the other to update the moved item to its new place in the list order. In a worst-case scenario, we would need to issue one update query for each list item if we chose to avoid hand-coded SQL.

Given the intended functionality of the code, we do not need to implement the insert, delete, or getType methods beyond their skeleton forms at this point in time.

The last abstract method we need to implement is onCreate, and it only calls the DatabaseHelper constructor.

One final thing we need to do is add the ContentProvider to the application node of the AndroidManifest file:
<provider android:name="ListDataProvider" android:authorities="com.techrepublic.sortablelistview.listdataprovider"></provider>

And that wraps up the ListDataProvider. Now, to make use of the database functionality it offers, follow the instructions below.

Take the SortableListViewActivity from last time, and in the onCreate method, rip out the ArrayAdapter:

ArrayAdapter adp = new ArrayAdapter(this, R.layout.listrow, sArray);
setListAdapter(adp);
and replace it with:
cursor = managedQuery(ListDataProvider.CONTENT_URI, null, null,

null, ListDataProvider.SORT_ORDER);

ListAdapter adapter=new SimpleCursorAdapter(this,

R.layout.listrow, cursor,

new String[] {ListDataProvider.COLUMN_NAME},

new int[] {R.id.title});

setListAdapter(adapter);
In the new code, cursor is a private Cursor property that is set from a managed query that handles the closing of cursor when its lifetime has ended. The adapter is created by using the same list-row layout from before, and binding the name column on the cursor to the title property in the layout. The adapter is then set as the list adapter for the Activity.

The selection string inside the onListItemClick changes from String selection = sArray[position].toString(); to String selection = cursor.getString(1);. This is the same functionality that we had previously, which showed the name of the clicked item. In the new code, we use cursor.getString(1) to extract the String represented at the column of index 1 in the row; ie, the name column.

Then the final big changes happen within the DropListener of the TouchInterceptor. The whole code is:
private TouchInterceptor.DropListener mDropListener =

new TouchInterceptor.DropListener() {

public void drop(int from, int to) {

System.out.println("Droplisten from:"+from+" to:"+to);

cursor.moveToPosition(from);

int orig_rowid = cursor.getInt(0);

//Assuming that item is moved up the list

int direction = 1;

int loop_start = to;

int loop_end = from-1;

int new_colorder = loop_start;

//For instance where item is dragged down the list

if(from < to) {

direction = -1;

loop_start = from+1;

loop_end = to;

new_colorder = loop_end;

}

ContentValues mUpdateValues = new ContentValues();

mUpdateValues.put("direction", direction);

getContentResolver().update(

Uri.parse(ListDataProvider.CONTENT_URI+"/update_increment"),

mUpdateValues,

"colorder >="+loop_start+" AND colorder<="+loop_end,

null

);

ContentValues mUpdateValues2 = new ContentValues();

mUpdateValues2.put(ListDataProvider.COLUMN_COLORDER, new_colorder);

getContentResolver().update(

ContentUris.withAppendedId(Uri.parse(ListDataProvider.CONTENT_URI+"/"), orig_rowid),

mUpdateValues2,

null,

null

);

cursor.requery();

}

};

We need to first grab the ROWID of the item that is being moved, so that we can set its order in the second update, and then we need to get the parameters correct that determine which rows need to be incremented to make way for the dragged row's movement.

Then we call the first update, using a ContentValue key/value store to pass through the direction parameter to the increment query.

The second update is far more orthodox, and sends through a URI to update, and a corresponding ContentValue providing the new list ordering to set in the dragged row.

When completed, the cursor is refreshed by a requery call.

And finally we have reached the end &mdash the ListView's ordering of items is now persistent across instances.

Note that when testing this code, the database is persistent across multiple instances of the simulator. To start with a blank database, go into Settings > Applications > Manage Applications > SortableListView [or your application's name] > Clear Data. Choosing Uninstall will also clear the data.

About

Some would say that it is a long way from software engineering to journalism, others would correctly argue that it is a mere 10 metres according to the floor plan.During his first five years with CBS Interactive, Chris started his journalistic advent...

0 comments

Editor's Picks