How to populate a Spinner widget from a database

Yesterday we saw how to populate a Spinner widget from an Array, and today, as promised, I'll show you how to populate a Spinner widget from a database.

This tutorial assumes that you're using the same code we used previously (link above), essentially the only differences are that we're using a different type of Adapter (a SimpleCursorAdapter this time) and populating it with the results of a query from a database table, and we're using a separate layout item to put our colour names

Here is our new layout, called db_view_row.xml:

<LinearLayout android:id="@+id/LinearLayout01"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
xmlns:android="http://schemas.android.com/apk/res/android">

<TextView android:text=""
android:id="@+id/tvDBViewRow"
android:layout_width="wrap_content"
android:layout_height="wrap_content">
</TextView>
</LinearLayout>

put this file in your res/layout directory in your project.


Let's assume for this example that you've already populated your database table with the same list of colours that we used previously.

Here's the statement to create our table structure:


CREATE TABLE "colours" (
"_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"colourName" TEXT NOT NULL
)


In our database adapter class we have a method called fetchAllColours(), which has the responsibility (probably no surprises here) of fetching all our colours ;).


At the top of our class we declare some static variables we're going to use :


public class TestDBAdapter {

public static final String KEY_TITLE = "colourName";
public static final String KEY_ROWID = "_id";

//..rest of class continues from here..


Here is this method also from our TestDBAdapter class:

public Cursor fetchAllColours()
{
if (mDb == null)
{
this.open();
}

/* here we check if our db exists as the connection might have been closed unexpectedly... and open it if it doesn't already exist*/


String tableName = "colours";

return mDb.query(tableName, new String[] { KEY_ROWID, KEY_TITLE}, null, null, null, null, null);

}


... which returns a database Cursor to our method below..


private void fillData() {
Cursor coloursCursor;
Spinner colourSpinner = (Spinner) findViewById(R.id.my_colour_spinner);
coloursCursor = thisTestDBAdapter.fetchAllColours();

startManagingCursor( coloursCursor);
/*Create an array to specify the fields we want to display in the list (only the 'colourName' column in this case) */

String[] from = new String[]{TestDBAdapter.KEY_TITLE};
/* and an array of the fields we want to bind those fields to (in this case just the textView 'tvDBViewRow' from our new db_view_row.xml layout above) */
int[] to = new int[]{R.id.tvDBViewRow};

/* Now create a simple cursor adapter.. */
SimpleCursorAdapter colourAdapter =
new SimpleCursorAdapter(this, R.layout.db_view_row, coloursCursor, from, to);

/* and assign it to our Spinner widget */
colourSpinner.setAdapter(colourAdapter);
}




Update: Due to popular demand, here is a project that demonstrates this concept:
Spinner from Database example.


.. Let me know if you're having any access issues, it's just hosted publicly from my google docs.


I've learnt my lesson, I'm going to be including an example project with all future posts :)