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.