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

Creating multiple sqlite database tables in Android

Most of the Android database examples you will find on the web will usually contain only one table to demonstrate the basic database concepts.

That's great, the only problem with this is that most non-trivial database implementations will contain more than one table.

The standard database creation string for a single table will probably look a lot like the below:


private static final String CREATE_TABLE_1 =
" create table " + table1 +
" (_id integer primary key autoincrement," +
" title text not null, body text not null);";

Which is called in your DB Adapter class like this:


@Override
public void onCreate(SQLiteDatabase db) {

db.execSQL(CREATE_TABLE_1);
}

So what to do if you want to create more than one table?
You may do the below.. but will it work?
Note that this is one big string containing three separate create statements...


private static final String DATABASE_CREATE_MULTIPLE_TABLES =
" create table " + ITEMS_TABLE +
" (_id integer primary key autoincrement," +
" title text not null)" +

" create table " + TAGS_TABLE +
" (_id integer primary key autoincrement," +
" tagName text not null)" +

" create table " + LOCATIONS_TABLE +
" (_id integer primary key autoincrement," +
" locationName text not null, gpsCoOrds text);"
;

And then you try calling them in your DB Adapter class like so:


@Override
public void onCreate(SQLiteDatabase db) {

db.execSQL(DATABASE_CREATE_MULTIPLE_TABLES);
}

So you're trying to create the three tables in one call to db.execSQL.


This appears to compile and run successfully, you can even read and write to the FIRST table that is created, but..

..when you try to read or write to any other table you will see the dreaded

'Sorry! The application Kdkddfblah (process test.Kdkddfblah) has stopped unexpectedly. Please try again'

..error message.

Uh-oh.

If you debug your application, you might see references to syntax errors 'near create', and possible a reference that the table doesn't exist.

Hmm... What went wrong?

The answer is that sqlite, and therefore the db.exec method, only lets you execute one sql command at a time. We were trying to run three sql statements in one go in db.execSQL(DATABASE_CREATE_MULTIPLE_TABLES);.

So what you need to do to fix this is move each of the above table create statements into their own strings, like this (Note that this now creates three seperate strings, unlike above):


private static final String CREATE_TABLE_1 =
" create table " + table1 +
" (_id integer primary key autoincrement," +
" title text not null, body text not null);";

private static final String CREATE_TABLE_2 =
" create table " + TAGS_TABLE +
" (_id integer primary key autoincrement," +
" tagName text not null)";

private static final String CREATE_TABLE_3 =
" create table " + LOCATIONS_TABLE +
" (_id integer primary key autoincrement," +
" locationName text not null, gpsCoOrds text);";

.. and then use these strings in your onCreate method like below, this then works.


@Override
public void onCreate(SQLiteDatabase db) {

db.execSQL(CREATE_TABLE_1);
db.execSQL(CREATE_TABLE_2);
db.execSQL(CREATE_TABLE_3);
}

If you find this doesn't work for you, try dropping all the tables in your database and try again, or give the database a different name, or different version number. The SQLiteOpenHelper seems to have some troubles registering that the database is to be changed. It finds a db with the same name and version number, goes 'meh' and doesn't look to see if the structure is different at all.

You can also pull the sqlite db file right off your device (or emulator) by going into the DDMS perspective in Eclipse (Window menu\ Open perspective \ Other \ DDMS), navigating to the database file which will probably be at \data\data\*Your Application Name*\databases.

There's a 'pull file' button on the top left as seen highlighted below:



.. You can then open the DB in your favourite sqlite manager (I like Sqliteman) and play around. Can can also of course, push the file back to the device if you wish.