We want to query a database table and display the rows in the form of a ListView.
In my previous articles on ListView, we used in-memory list as the data source.
Please see Android ListView Tutorial to know how to build a ListView.
Since the data source is a database, we need to create an SQL query, execute it, retrieve the rows and somehow bind each database row to a ListView item.
Let’s see how we can do it.
Store data using SQLiteDatabase
Before we query the database, we need some sample data. We will insert some programming language names into a table called ‘languages’. To keep things simple, table ‘languages’ has got couple of columns ‘_id’ to store primary key and ‘lang_name’ to store the language name.
DatabaseConstants:
package com.javarticles.android.database; public interface DatabaseConstants { public static String DB_NAME = "list_example.db"; public static final String TABLE_LANG = "languages"; public static final String COL_LANG_ID = "_id"; public static final String COL_LANG_NAME = "lang_name"; }
We will use Android provided helper class SQLiteOpenHelper
to manage database creation and read/write operations. Our class SQLiteHelper
extends SQLiteOpenHelper
. The database is created the first time getWritableDatabase()
or getReadableDatabase()
is called which in turn returns use SQLiteDatabase
an object wrapper around SQLite database.
Once the database is created, the framework will call onCreate()
, onUpgrade()
or onDowngrade()
and onOpen()
. It will pass the SQLiteDatabase
instance to these method calls.
Method onUpgrade() will be called only when an upgrade is required which the framework figures out by comparing the database version with the one passed in by the application code to the current SQLiteOpenHelper
instance. If there is an increment in version onUpgrade()
is called else onDowngrade()
will be called.
In onCreate
method, we will use the SQLiteDatabase
instance passed in to create some test data. We will call execSQL()
method to create the table and some test data.
SQLiteHelper:
package com.javarticles.android; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import com.javarticles.android.database.DatabaseConstants; public class SQLiteHelper extends SQLiteOpenHelper implements DatabaseConstants { public static final int DB_VERSION = 2; public SQLiteHelper(Context context) { super(context, DB_NAME, null, DB_VERSION); getWritableDatabase(); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE " + TABLE_LANG + "(" + COL_LANG_ID + " INTEGER PRIMARY KEY NOT NULL, " + " " + COL_LANG_NAME + " VARCHAR(50) NOT NULL);"); insertLanguage(db, "Java"); insertLanguage(db, "Perl"); insertLanguage(db, "Python"); insertLanguage(db, "Ruby"); insertLanguage(db, "Scala"); insertLanguage(db, "C"); insertLanguage(db, "C++"); } private static void insertLanguage(SQLiteDatabase db, String language) { db.execSQL("INSERT INTO " + TABLE_LANG + " (" + COL_LANG_NAME + ") VALUES ('" + language + "');"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }
See Android SQLite Example for more details.
Query the Database
In order to display the database rows in a list, we need to be able to query the database.
Android relies on another important abstract object called ContentProvider
to provide content to the application. The idea is to decouple the application components that from the type of underlying data sources. It provides a generic mechanism through which applications can share their data or consume data provided by others.
To create a new Content Provider we need extend the abstract ContentProvider
and implement query()
, insert()
, update()
and delete
.
In onCreate
, we create an instance of SQLiteHelper
, this will automatically create a database for us.
The purpose of this article is to display database records in a list view so we only have to implement the query
. We can either use Android’s SQLiteQueryBuilder
helper class to build the query or directly execute the SQL using SQLiteDatabase.rawQuery()
. Either way, the queries return a Cursor
object.
Cursor
is another abstraction around the database result set to navigate through the results and retrieve specific data values.
LanguageContentProvider:
package com.javarticles.android; import android.content.ContentProvider; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteQueryBuilder; import android.net.Uri; import com.javarticles.android.database.DatabaseConstants; public class LanguageContentProvider extends ContentProvider { private static final String AUTHORITY = "com.javarticles.android.provider.LanguageContentProvider"; public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/" + DatabaseConstants.TABLE_LANG); private SQLiteHelper dbHelper; @Override public boolean onCreate() { dbHelper = new SQLiteHelper(getContext()); return true; } @Override public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) { SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); qb.setTables(DatabaseConstants.TABLE_LANG); String orderBy = DatabaseConstants.COL_LANG_NAME + " asc"; Cursor cursor = qb.query(dbHelper.getReadableDatabase(), new String[] { DatabaseConstants.COL_LANG_ID, DatabaseConstants.COL_LANG_NAME }, null, null, null, null, orderBy); return cursor; } @Override public String getType(Uri uri) { return "vnd.android.cursor.dir/vnd.com.javarticles.android.provider.LanguageContentProvider"; } @Override public Uri insert(Uri uri, ContentValues values) { return null; } @Override public int delete(Uri uri, String selection, String[] selectionArgs) { return 0; } @Override public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) { return 0; } }
We will also have to declare our content provider in androidManfest.xml
.
androidManifest.xml:
<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.javarticles.android" android:versionCode="1" android:versionName="1.0" > <uses-sdk android:maxSdkVersion="22" android:minSdkVersion="14" android:targetSdkVersion="22" /> <application android:allowBackup="true" android:icon="@drawable/ic_launcher_sandbox" android:label="@string/app_name" > <activity android:name="com.javarticles.android.LanguageListActivity" > <intent-filter> <action android:name="android.intent.action.MAIN" /> <category android:name="android.intent.category.LAUNCHER" /> </intent-filter> </activity> <provider android:name=".LanguageContentProvider" android:authorities="com.javarticles.android.provider.LanguageContentProvider" android:exported="false" /> </application> </manifest>
SimpleCursorAdapter to bind Cursor and view
The SimpleCursorAdapter
is used to bind a Cursor to an Adapter View using the list item view layout. The content of each row’s View is populated using the column values of the
corresponding row in the underlying Cursor returned from a Content Provider query.
Below diagram show the relation between SimpleCursorAdapter
, ContentProvider()
, Cursor
and the view.
We create SimpleCursorAdapter
object in the onCreate()
method of our ListActivity
and then set the list adapter calling setListAdapter(adapter)
.
R.layout.container_list_item_view
is the list item view. We want to fetch the language name DatabaseConstants.COL_LANG_NAME
and set it to the view identified by R.id.list_item
.
new SimpleCursorAdapter(this, R.layout.container_list_item_view, null, new String[] { DatabaseConstants.COL_LANG_NAME }, new int[] { R.id.list_item }); setListAdapter(_adapter);
Here is the ListView layout.
container_list.xml:
<?xml version="1.0" encoding="utf-8"?> <ListView xmlns:android="http://schemas.android.com/apk/res/android" android:id="@android:id/list" android:layout_width="fill_parent" android:layout_height="fill_parent" />
and here is the list item’s layout.
container_list_item_view.xml:
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="fill_parent" android:layout_height="fill_parent" android:gravity="center_vertical" android:orientation="horizontal" android:padding="2dip" > <TextView android:id="@+id/list_item" android:layout_width="match_parent" android:layout_height="wrap_content" android:padding="4dip" /> </LinearLayout>
Asynchronously load data using LoaderManager
If you notice, our ListActivity
implements LoaderCallbacks<Cursor>
. This to make sure that the query is run in background in a separate thread.
We have three main components related asynchronous loading.
- A
Loader
is an object which knows its data source through theContentProvider
and is designed to asynchronously load data and monitor the underlying data source
for changes. ALoader
is identified by an integer ID. - A
LoaderManager
manages one or moreLoader
objects. We will use it initiate the loading process.getLoaderManager().initLoader(LOADER_ID, null, this);
- A
CursorLoader
is aLoader
object that performs asynchronous queries against Content Providers, returning a resultCursor
.
To integrate the activity with the LoaderManager
, we make it implement LoaderCallbacks<Cursor>
. We also need to implement methods onCreateLoader()
, onLoaderFinished()
and onLoaderReset()
. A bit about these callbacks:
onCreateLoader()
– this is called when the loader is initialized. Remember we have initialized the loading process inonCreate()
of our list activity. This should create and return newCursorLoader
object.onLoadFinished()
– this is called when the Loader Manager has completed the asynchronous query.onLoaderReset()
– this is called when the Loader Manager resets your Cursor Loader.
List Activity
Our ListActivity
implements LoaderCallbacks<Cursor>
so that we can load the data asynchronously.
In onCreate
method, we set the content view to list view layout R.layout.container_list
.
We then create SimpleCursorAdapter
and set using setListAdapter
.
Next, we initiate the loader manager using call
getLoaderManager().initLoader(LOADER_ID, null, this);
What follows next is the loader manager calling the callback methods onCreateLoader()
and onLoadFinished()
.
In onLoadFinished()
, we call _adapter.swapCursor(data)
. This will bind the Cursor
data to the list item view for each database row fetched.
LanguageListActivity:
package com.javarticles.android; import android.app.ListActivity; import android.app.LoaderManager.LoaderCallbacks; import android.content.CursorLoader; import android.content.Loader; import android.database.Cursor; import android.os.Bundle; import android.widget.CursorAdapter; import android.widget.SimpleCursorAdapter; import com.javarticles.android.database.DatabaseConstants; public class LanguageListActivity extends ListActivity implements LoaderCallbacks<Cursor> { private static final int LOADER_ID = 42; private CursorAdapter _adapter; public void onCreate(final Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.container_list); _adapter = new SimpleCursorAdapter(this, R.layout.container_list_item_view, null, new String[] { DatabaseConstants.COL_LANG_NAME }, new int[] { R.id.list_item }); setListAdapter(_adapter); getLoaderManager().initLoader(LOADER_ID, null, this); } @Override public Loader<Cursor> onCreateLoader(int id, Bundle args) { if (id != LOADER_ID) { return null; } return new CursorLoader(LanguageListActivity.this, LanguageContentProvider.CONTENT_URI, new String[] { DatabaseConstants.COL_LANG_ID, DatabaseConstants.COL_LANG_NAME }, null, null, null); } @Override public void onLoadFinished(Loader<Cursor> loader, Cursor data) { _adapter.swapCursor(data); } @Override public void onLoaderReset(Loader<Cursor> loader) { _adapter.swapCursor(null); } }
Run the application
Download the source code
This was an example about building ListView using SimpleCursorAdapter. We showed you how to create your own ContentProvider
and override query()
to create Cursor
. We also showed you how to load data asynchronously using LoaderManager
.