Monday, February 1, 2010

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 :)

13 comments:

  1. Thank You ....
    Its a good post...it really helped!!!!!!

    ReplyDelete
  2. how can i retrieve the selected item from the database driven spinner???
    i tried using .getSelectedItem()as used for normal xml spinner....it doesnt give the required value....gives some kind of cursor value...

    it would be helpful if you could further the tutorial to d just that.

    Thank u

    ReplyDelete
  3. To ketaki:
    The response you're getting probably looks like android.database.sqlite.SQLiteCursor@43b8a9b0 , which instead of the string in your spinner is the address of the Cursor. You can use that to derive the string. Using this example the code would look something like this:
    Cursor mCursor = (Cursor) colourSpinner.getItemAtPosition(position);
    Log.d("!!", mCursor.getString(KEY_TITLE));

    ReplyDelete
  4. Hi Ketaki,

    This works for me for a simple spinner of textviews:

    spnIngredients.setOnItemSelectedListener(new OnItemSelectedListener()
    {
    @Override
    public void onItemSelected(AdapterView parent, View vw,
    int pos, long id)
    {
    LinearLayout rowll = (LinearLayout)vw;
    TextView test = (TextView)rowll.getChildAt(0);
    Toast t = Toast.makeText(parent.getContext(), test.getText().toString(), Toast.LENGTH_SHORT);
    t.show();

    }

    @Override
    public void onNothingSelected(AdapterView arg0)
    {
    // Do nothing..
    }
    });

    ReplyDelete
  5. Great post, clear and simple. Thanks

    ReplyDelete
  6. Hi Glenn i'm new to Android development so please can i get the complete code of this example as a zip file to download and go through that or can u post tht code here.

    ReplyDelete
  7. can someone email me the complete code of this project at hmk984@hotmail.com

    ReplyDelete
  8. i need this code too, please can someone email me...
    please :)

    igor.karanovic@live.com

    ReplyDelete
  9. well you could send me the complete example code
    diapik@hotmail.it

    ReplyDelete
  10. Hi All,
    .. Sorry for the delay, but here is a project that demonstrates this:
    Spinner from Database example

    ReplyDelete
  11. Neat example.

    but... Why go from a spinner, to text, then back to the spinner? Isn't the spinner just acting as a button in this case?
    Could you show how to do this without the use of a text box?

    ReplyDelete
  12. Really awsum work man ...got lot of help from this......

    ReplyDelete