How to Display Listview using CursorAdapter and Sqlite Database in Android

How to Display Listview using CursorAdapter and Sqlite Database in Android

The objective of this article is to get data from SQLite database and then attach that to the Listview.

Source for Activity – AndroidListViewCursorAdaptorActivity.java

package com.mycodetipsandroid;

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.mycodetipsandroid;

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 Country.java

package com.mycodetipsandroid;

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 – landingscreen.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 country name!
</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”.mycodetipsandroid 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>