Android Listview Example using CursorAdapter and SQLite database

The objective of this tutorial is to get data from SQLite database by extending the SimpleCursorAdapter and then attach that to the Listview. In this example we create a database of countries and then insert some countries when the activity starts. After that we create a SimpleCursorAdapter and attach that to cursor returned from our database custom query selection. The data columns returned from the cursor is then mapped to our custom view for display.

Android Listview CursorAdapter SQLite Example
Android Listview CursorAdapter SQLite Example

Source for Activity - AndroidListViewCursorAdaptorActivity.java

package com.as400samplecode;


import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.text.Editable;
import android.text.TextWatcher;
import android.view.View;
import android.widget.AdapterView;
import android.widget.EditText;
import android.widget.FilterQueryProvider;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.Toast;
import android.widget.AdapterView.OnItemClickListener;

public class AndroidListViewCursorAdaptorActivity extends Activity {

 private CountriesDbAdapter dbHelper;
 private SimpleCursorAdapter dataAdapter;

 @Override
 public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.main);

  dbHelper = new CountriesDbAdapter(this);
  dbHelper.open();

  //Clean all data
  dbHelper.deleteAllCountries();
  //Add some data
  dbHelper.insertSomeCountries();

  //Generate ListView from SQLite Database
  displayListView();

 }

 private void displayListView() {


  Cursor cursor = dbHelper.fetchAllCountries();

  // The desired columns to be bound
  String[] columns = new String[] {
    CountriesDbAdapter.KEY_CODE,
    CountriesDbAdapter.KEY_NAME,
    CountriesDbAdapter.KEY_CONTINENT,
    CountriesDbAdapter.KEY_REGION
  };

  // the XML defined views which the data will be bound to
  int[] to = new int[] { 
    R.id.code,
    R.id.name,
    R.id.continent,
    R.id.region,
  };

  // create the adapter using the cursor pointing to the desired data 
  //as well as the layout information
  dataAdapter = new SimpleCursorAdapter(
    this, R.layout.country_info, 
    cursor, 
    columns, 
    to,
    0);

  ListView listView = (ListView) findViewById(R.id.listView1);
  // Assign adapter to ListView
  listView.setAdapter(dataAdapter);


  listView.setOnItemClickListener(new OnItemClickListener() {
   @Override
   public void onItemClick(AdapterView<?> listView, View view, 
     int position, long id) {
   // Get the cursor, positioned to the corresponding row in the result set
   Cursor cursor = (Cursor) listView.getItemAtPosition(position);

   // Get the state's capital from this row in the database.
   String countryCode = 
    cursor.getString(cursor.getColumnIndexOrThrow("code"));
   Toast.makeText(getApplicationContext(),
     countryCode, Toast.LENGTH_SHORT).show();

   }
  });

  EditText myFilter = (EditText) findViewById(R.id.myFilter);
  myFilter.addTextChangedListener(new TextWatcher() {

   public void afterTextChanged(Editable s) {
   }

   public void beforeTextChanged(CharSequence s, int start, 
     int count, int after) {
   }

   public void onTextChanged(CharSequence s, int start, 
     int before, int count) {
    dataAdapter.getFilter().filter(s.toString());
   }
  });
  
  dataAdapter.setFilterQueryProvider(new FilterQueryProvider() {
         public Cursor runQuery(CharSequence constraint) {
             return dbHelper.fetchCountriesByName(constraint.toString());
         }
     });

 }
}

Source for SQLite DB Adapter - CountriesDbAdapter.java

package com.as400samplecode;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class CountriesDbAdapter {

 public static final String KEY_ROWID = "_id";
 public static final String KEY_CODE = "code";
 public static final String KEY_NAME = "name";
 public static final String KEY_CONTINENT = "continent";
 public static final String KEY_REGION = "region";

 private static final String TAG = "CountriesDbAdapter";
 private DatabaseHelper mDbHelper;
 private SQLiteDatabase mDb;

 private static final String DATABASE_NAME = "World";
 private static final String SQLITE_TABLE = "Country";
 private static final int DATABASE_VERSION = 1;

 private final Context mCtx;

 private static final String DATABASE_CREATE =
  "CREATE TABLE if not exists " + SQLITE_TABLE + " (" +
  KEY_ROWID + " integer PRIMARY KEY autoincrement," +
  KEY_CODE + "," +
  KEY_NAME + "," +
  KEY_CONTINENT + "," +
  KEY_REGION + "," +
  " UNIQUE (" + KEY_CODE +"));";

 private static class DatabaseHelper extends SQLiteOpenHelper {

  DatabaseHelper(Context context) {
   super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }


  @Override
  public void onCreate(SQLiteDatabase db) {
   Log.w(TAG, DATABASE_CREATE);
   db.execSQL(DATABASE_CREATE);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
     + newVersion + ", which will destroy all old data");
   db.execSQL("DROP TABLE IF EXISTS " + SQLITE_TABLE);
   onCreate(db);
  }
 }

 public CountriesDbAdapter(Context ctx) {
  this.mCtx = ctx;
 }

 public CountriesDbAdapter open() throws SQLException {
  mDbHelper = new DatabaseHelper(mCtx);
  mDb = mDbHelper.getWritableDatabase();
  return this;
 }

 public void close() {
  if (mDbHelper != null) {
   mDbHelper.close();
  }
 }

 public long createCountry(String code, String name, 
   String continent, String region) {

  ContentValues initialValues = new ContentValues();
  initialValues.put(KEY_CODE, code);
  initialValues.put(KEY_NAME, name);
  initialValues.put(KEY_CONTINENT, continent);
  initialValues.put(KEY_REGION, region);

  return mDb.insert(SQLITE_TABLE, null, initialValues);
 }

 public boolean deleteAllCountries() {

  int doneDelete = 0;
  doneDelete = mDb.delete(SQLITE_TABLE, null , null);
  Log.w(TAG, Integer.toString(doneDelete));
  return doneDelete > 0;

 }

 public Cursor fetchCountriesByName(String inputText) throws SQLException {
  Log.w(TAG, inputText);
  Cursor mCursor = null;
  if (inputText == null  ||  inputText.length () == 0)  {
   mCursor = mDb.query(SQLITE_TABLE, new String[] {KEY_ROWID,
     KEY_CODE, KEY_NAME, KEY_CONTINENT, KEY_REGION}, 
     null, null, null, null, null);

  }
  else {
   mCursor = mDb.query(true, SQLITE_TABLE, new String[] {KEY_ROWID,
     KEY_CODE, KEY_NAME, KEY_CONTINENT, KEY_REGION}, 
     KEY_NAME + " like '%" + inputText + "%'", null,
     null, null, null, null);
  }
  if (mCursor != null) {
   mCursor.moveToFirst();
  }
  return mCursor;

 }

 public Cursor fetchAllCountries() {

  Cursor mCursor = mDb.query(SQLITE_TABLE, new String[] {KEY_ROWID,
    KEY_CODE, KEY_NAME, KEY_CONTINENT, KEY_REGION}, 
    null, null, null, null, null);

  if (mCursor != null) {
   mCursor.moveToFirst();
  }
  return mCursor;
 }

 public void insertSomeCountries() {

  createCountry("AFG","Afghanistan","Asia","Southern and Central Asia");
  createCountry("ALB","Albania","Europe","Southern Europe");
  createCountry("DZA","Algeria","Africa","Northern Africa");
  createCountry("ASM","American Samoa","Oceania","Polynesia");
  createCountry("AND","Andorra","Europe","Southern Europe");
  createCountry("AGO","Angola","Africa","Central Africa");
  createCountry("AIA","Anguilla","North America","Caribbean");

 }

}

Source for POJO - Country.java

package com.as400samplecode;

public class Country {
 
 String code = null;
 String name = null;
 String continent = null;
 String region = null;
 
 public String getCode() {
  return code;
 }
 public void setCode(String code) {
  this.code = code;
 }
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 public String getContinent() {
  return continent;
 }
 public void setContinent(String continent) {
  this.continent = continent;
 }
 public String getRegion() {
  return region;
 }
 public void setRegion(String region) {
  this.region = region;
 }

 
}

Source for Main Screen Layout - main.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:orientation="vertical">

 <TextView android:layout_width="fill_parent"
  android:layout_height="wrap_content" android:padding="10dp"
  android:text="@string/some_text" android:textSize="20sp" />

 <EditText android:id="@+id/myFilter" android:layout_width="match_parent"
  android:layout_height="wrap_content" android:ems="10" 
  android:hint="@string/some_hint">
  <requestFocus />
 </EditText>

 <ListView android:id="@+id/listView1" android:layout_width="fill_parent"
  android:layout_height="fill_parent" />

</LinearLayout>

Source for List Layout - country_info.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:orientation="vertical"
    android:padding="6dip" >

    <TextView
        android:id="@+id/textView1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_alignParentTop="true"
        android:text="Code: "
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/textView1"
        android:layout_below="@+id/textView1"
        android:text="Name: "
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <TextView
        android:id="@+id/textView3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/textView2"
        android:layout_below="@+id/textView2"
        android:text="Continent: "
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <TextView
        android:id="@+id/textView4"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/textView3"
        android:layout_below="@+id/textView3"
        android:text="Region: "
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <TextView
        android:id="@+id/continent"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/textView3"
        android:layout_alignBottom="@+id/textView3"
        android:layout_toRightOf="@+id/textView3"
        android:text="TextView" />

    <TextView
        android:id="@+id/region"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/textView4"
        android:layout_alignBottom="@+id/textView4"
        android:layout_alignLeft="@+id/continent"
        android:text="TextView" />

    <TextView
        android:id="@+id/name"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_above="@+id/textView3"
        android:layout_toRightOf="@+id/textView3"
        android:text="TextView" />

    <TextView
        android:id="@+id/code"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_above="@+id/textView2"
        android:layout_alignLeft="@+id/name"
        android:text="TextView" />

</RelativeLayout>

Source for application variables - strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>

    <string name="app_name">ListView CusrsorAdapter</string>
 <string name="some_text">
     Some North American Countries!
 </string>
 <string name="some_hint">
  Type here to filter&#8230;
 </string>
 
</resources>

Source for application manifest - AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
 package="com.as400samplecode" android:versionCode="1"
 android:versionName="1.0">

 <uses-sdk android:minSdkVersion="15" />

 <application android:icon="@drawable/ic_launcher"
  android:label="@string/app_name" android:theme="@android:style/Theme.Light">
  <activity android:name=".AndroidListViewCursorAdaptorActivity"
   android:label="@string/app_name">
   <intent-filter>
    <action android:name="android.intent.action.MAIN" />

    <category android:name="android.intent.category.LAUNCHER" />
   </intent-filter>
  </activity>
 </application>

</manifest>

References