Android, ListView and Databases
Here I’ll show how to get data from the Sqlite database and show them into ListView. I am intending to query a database using a SQLiteDatabase API. The results I obtain are in a Cursor object that I iterate and create an ArrayList that is passed to the ListView. Let us see the steps involved in this exercise:
What you will learn –
ListView
ListView icon
ListAdapter
ListActivity
ListActivity and Layout
What is Adapter
ListViews and performance
Create ListActivity Show Data from Array without Database
ListActivity with own layout
Implementing your own ListAdapter
Implementing your own adapter
Rows interacting with the data model – CheckBox
Use of SimpleCursorAdapter – Database
What is SQLite
SQLite in Android – brief
rawQuery() Example
query() Example
What is Cursor?
ListViews, ListActivities and SimpleCursorAdapter –Details
ListView with Database
ListView
The display of Lists is a very common pattern in mobile applications. The user gets a list of items can scroll through them and select one item. This selection is used to trigger something else.
Android provides the view ListView which is capable of displaying a scrollable list of items.
A view that shows items in a vertically scrolling list. The items come from the ListAdapter associated with this view.
You can directly use the ListView in your layout as any other user interface component.
ListView Icon –
List view icons look a lot like dialog icons, but they use an inner shadow effect where the light source is above the object.
ListAdapter
Extended Adapter that is the bridge between a ListView and the data that backs the list. Frequently that data comes from a Cursor, but that is not required. The ListView can display any data provided that it is wrapped in a ListAdapter.
ListActivity
In case your Activity is primary showing a list you can extend the activity ListActivity which simplifies the handling of a ListView.
android.R.layout.simple_list_item1.
ListActivity and Layout
What is Adapter ?
ListViews and performance
Mobile Memory and processor is very less compare to desktop, so performance keep in mind to develop an application.
Create ListActivity Show Data from Array without Database
package com.sst.example.listactivity;
import android.app.ListActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.Toast;
public class MyListActivity extends ListActivity {
public void onCreate(Bundle icicle) {
super.onCreate(icicle);
String[] values = new String[] { "Android", "iPhone", "WindowsMobile",
"Blackberry", "WebOS", "Ubuntu", "Windows7", "Max OS X",
"Linux", "OS/2" };
ArrayAdapter adapter = new ArrayAdapter(this,
android.R.layout.simple_list_item_1, values);
setListAdapter(adapter);
}
@Override
protected void onListItemClick(ListView l, View v, int position, long id) {
String item = (String) getListAdapter().getItem(position);
Toast.makeText(this, item + " selected", Toast.LENGTH_LONG).show();
}
}
ListActivity with own layout
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="wrap_content"
android:layout_height="wrap_content" >
<ImageView
android:id="@+id/icon"
android:layout_width="22px"
android:layout_height="22px"
android:layout_marginLeft="4px"
android:layout_marginRight="10px"
android:layout_marginTop="4px"
android:src="@drawable/ic_launcher" >
</ImageView>
<TextView
android:id="@+id/label"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@+id/label"
android:textSize="20px" >
</TextView>
</LinearLayout>
package com.sst.example.listactivity;
import android.app.ListActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.Toast;
public class MyListActivity extends ListActivity {
public void onCreate(Bundle icicle) {
super.onCreate(icicle);
String[] values = new String[] { "Android", "iPhone", "WindowsMobile",
"Blackberry", "WebOS", "Ubuntu", "Windows7", "Max OS X",
"Linux", "OS/2" };
// Use your own layout
ArrayAdapter adapter = new ArrayAdapter(this,
R.layout.rowlayout, R.id.label, values);
setListAdapter(adapter);
}
@Override
protected void onListItemClick(ListView l, View v, int position, long id) {
String item = (String) getListAdapter().getItem(position);
Toast.makeText(this, item + " selected", Toast.LENGTH_LONG).show();
}
}
Implementing your own ListAdapter
Implementing your own adapter
public class MySimpleArrayAdapter extends ArrayAdapter {
private final Context context;
private final String[] values;
public MySimpleArrayAdapter(Context context, String[] values) {
super(context, R.layout.rowlayout, values);
this.context = context;
this.values = values;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
LayoutInflater inflater = (LayoutInflater) context
.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
View rowView = inflater.inflate(R.layout.rowlayout, parent, false);
TextView textView = (TextView) rowView.findViewById(R.id.label);
ImageView imageView = (ImageView) rowView.findViewById(R.id.icon);
textView.setText(values[position]);
// Change the icon for Windows and iPhone
String s = values[position];
if (s.startsWith("Windows7") || s.startsWith("iPhone")
|| s.startsWith("Solaris")) {
imageView.setImageResource(R.drawable.no);
} else {
imageView.setImageResource(R.drawable.ok);
}
return rowView;
}
}
public class MyListActivity extends ListActivity {
public void onCreate(Bundle icicle) {
super.onCreate(icicle);
String[] values = new String[] { "Android", "iPhone", "WindowsMobile",
"Blackberry", "WebOS", "Ubuntu", "Windows7", "Max OS X",
"Linux", "OS/2" };
MySimpleArrayAdapter adapter = new MySimpleArrayAdapter(this, values);
setListAdapter(adapter);
}
}
Adding a longclick listener to the list items
import android.app.ListActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemLongClickListener;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.Toast;
public class MyList extends ListActivity {
/** Called when the activity is first created. */
public void onCreate(Bundle icicle) {
super.onCreate(icicle);
// Create an array of Strings, that will be put to our ListActivity
String[] names = new String[] { “Linux”, “Windows7”, “Eclipse”, “Suse”,
“Ubuntu”, “Solaris”, “Android”, “iPhone” };
ArrayAdapter adapter = new MyPerformanceArrayAdapter(this, names);
setListAdapter(adapter);
ListView list = getListView();
list.setOnItemLongClickListener(new OnItemLongClickListener() {
@Override
public boolean onItemLongClick(AdapterView parent, View view,
int position, long id) {
Toast.makeText(MyList.this,
“Item in position ” + position + ” clicked”,
Toast.LENGTH_LONG).show();
// Return true to consume the click event. In this case the
// onListItemClick listener is not called anymore.
return true;
}
});
}
@Override
protected void onListItemClick(ListView l, View v, int position, long id) {
super.onListItemClick(l, v, position, id);
// Get the item that was clicked
Object o = this.getListAdapter().getItem(position);
String keyword = o.toString();
Toast.makeText(this, “You selected: ” + keyword, Toast.LENGTH_SHORT)
.show();
}
}
Rows interacting with the data model – CheckBox
Your row layout can also contain views which interact with the underlying data model.
For example you can have a “Checkbox” view in your row and if the checkbox is selected you change the data which is displayed in the row.
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="wrap_content" >
<TextView
android:id="@+id/label"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@+id/label"
android:textSize="30px" >
</TextView>
<CheckBox
android:id="@+id/check"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentRight="true"
android:layout_marginLeft="4px"
android:layout_marginRight="10px" >
</CheckBox>
</RelativeLayout >
public class Model {
private String name;
private boolean selected;
public Model(String name) {
this.name = name;
selected = false;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public boolean isSelected() {
return selected;
}
public void setSelected(boolean selected) {
this.selected = selected;
}
}
import android.app.Activity;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.CheckBox;
import android.widget.CompoundButton;
import android.widget.TextView;
public class InteractiveArrayAdapter extends ArrayAdapter {
private final List list;
private final Activity context;
public InteractiveArrayAdapter(Activity context, List list) {
super(context, R.layout.rowbuttonlayout, list);
this.context = context;
this.list = list;
}
static class ViewHolder {
protected TextView text;
protected CheckBox checkbox;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
View view = null;
if (convertView == null) {
LayoutInflater inflator = context.getLayoutInflater();
view = inflator.inflate(R.layout.rowbuttonlayout, null);
final ViewHolder viewHolder = new ViewHolder();
viewHolder.text = (TextView) view.findViewById(R.id.label);
viewHolder.checkbox = (CheckBox) view.findViewById(R.id.check);
viewHolder.checkbox
.setOnCheckedChangeListener(new CompoundButton.OnCheckedChangeListener() {
@Override
public void onCheckedChanged(CompoundButton buttonView,
boolean isChecked) {
Model element = (Model) viewHolder.checkbox
.getTag();
element.setSelected(buttonView.isChecked());
}
});
view.setTag(viewHolder);
viewHolder.checkbox.setTag(list.get(position));
} else {
view = convertView;
((ViewHolder) view.getTag()).checkbox.setTag(list.get(position));
}
ViewHolder holder = (ViewHolder) view.getTag();
holder.text.setText(list.get(position).getName());
holder.checkbox.setChecked(list.get(position).isSelected());
return view;
}
}
SimpleCursorAdapter – Database
import android.app.ListActivity;
import android.database.Cursor;
import android.net.Uri;
import android.os.Bundle;
import android.provider.ContactsContract;
import android.widget.ListAdapter;
import android.widget.SimpleCursorAdapter;
public class MyListActivity extends ListActivity {
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
Cursor mCursor = getContacts();
startManagingCursor(mCursor);
// Now create a new list adapter bound to the cursor.
// SimpleListAdapter is designed for binding to a Cursor.
ListAdapter adapter = new SimpleCursorAdapter(this, // Context.
android.R.layout.two_line_list_item, // Specify the row template
// to use (here, two
// columns bound to the
// two retrieved cursor
// rows).
mCursor, // Pass in the cursor to bind to.
// Array of cursor columns to bind to.
new String[] { ContactsContract.Contacts._ID,
ContactsContract.Contacts.DISPLAY_NAME },
// Parallel array of which template objects to bind to those
// columns.
new int[] { android.R.id.text1, android.R.id.text2 });
// Bind to our new adapter.
setListAdapter(adapter);
}
private Cursor getContacts() {
// Run query
Uri uri = ContactsContract.Contacts.CONTENT_URI;
String[] projection = new String[] { ContactsContract.Contacts._ID,
ContactsContract.Contacts.DISPLAY_NAME };
String selection = ContactsContract.Contacts.IN_VISIBLE_GROUP + " = '"
+ ("1") + "'";
String[] selectionArgs = null;
String sortOrder = ContactsContract.Contacts.DISPLAY_NAME
+ " COLLATE LOCALIZED ASC";
return managedQuery(uri, projection, selection, selectionArgs,
sortOrder);
}
}
What is SQLite
SQLite in Android – Brief
rawQuery() Example
Cursor cursor = getReadableDatabase().
rawQuery("select * from todo where _id = ?", new String[] { id });
query() Example
return database.query(DATABASE_TABLE,
new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION },
null, null, null, null, null);
What is Cursor?
To get the number of elements of the resulting query use the getCount() method.
ListViews, ListActivities and SimpleCursorAdapter -Details
ListView with Database
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class MySQLiteHelper extends SQLiteOpenHelper {
public static final String TABLE_COMMENTS = "comments";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_COMMENT = "comment";
private static final String DATABASE_NAME = "commments.db";
private static final int DATABASE_VERSION = 1;
// Database creation sql statement
private static final String DATABASE_CREATE = "create table "
+ TABLE_COMMENTS + "( " + COLUMN_ID
+ " integer primary key autoincrement, " + COLUMN_COMMENT
+ " text not null);";
public MySQLiteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase database) {
database.execSQL(DATABASE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(MySQLiteHelper.class.getName(),
"Upgrading database from version " + oldVersion + " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS" + TABLE_COMMENTS);
onCreate(db);
}
}
public class Comment {
private long id;
private String comment;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getComment() {
return comment;
}
public void setComment(String comment) {
this.comment = comment;
}
// Will be used by the ArrayAdapter in the ListView
@Override
public String toString() {
return comment;
}
}
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
public class CommentsDataSource {
// Database fields
private SQLiteDatabase database;
private MySQLiteHelper dbHelper;
private String[] allColumns = { MySQLiteHelper.COLUMN_ID,
MySQLiteHelper.COLUMN_COMMENT };
public CommentsDataSource(Context context) {
dbHelper = new MySQLiteHelper(context);
}
public void open() throws SQLException {
database = dbHelper.getWritableDatabase();
}
public void close() {
dbHelper.close();
}
public Comment createComment(String comment) {
ContentValues values = new ContentValues();
values.put(MySQLiteHelper.COLUMN_COMMENT, comment);
long insertId = database.insert(MySQLiteHelper.TABLE_COMMENTS, null,
values);
// To show how to query
Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
null, null, null);
cursor.moveToFirst();
return cursorToComment(cursor);
}
public void deleteComment(Comment comment) {
long id = comment.getId();
System.out.println("Comment deleted with id: " + id);
database.delete(MySQLiteHelper.TABLE_COMMENTS, MySQLiteHelper.COLUMN_ID
+ " = " + id, null);
}
public List getAllComments() {
List comments = new ArrayList();
Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
allColumns, null, null, null, null, null);
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
Comment comment = cursorToComment(cursor);
comments.add(comment);
cursor.moveToNext();
}
// Make sure to close the cursor
cursor.close();
return comments;
}
private Comment cursorToComment(Cursor cursor) {
Comment comment = new Comment();
comment.setId(cursor.getLong(0));
comment.setComment(cursor.getString(1));
return comment;
}
}
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical" >
<LinearLayout
android:id="@+id/group"
android:layout_width="wrap_content"
android:layout_height="wrap_content">
<Button
android:id="@+id/add"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Add New"
android:onClick="onClick"/>
<Button
android:id="@+id/delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Delete First"
android:onClick="onClick"/>
</LinearLayout>
<ListView
android:id="@android:id/list"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/hello" />
</LinearLayout>
import java.util.List;
import java.util.Random;
import android.app.ListActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
public class TestDatabaseActivity extends ListActivity {
private CommentsDataSource datasource;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
datasource = new CommentsDataSource(this);
datasource.open();
List values = datasource.getAllComments();
// Use the SimpleCursorAdapter to show the
// elements in a ListView
ArrayAdapter adapter = new ArrayAdapter(this,
android.R.layout.simple_list_item_1, values);
setListAdapter(adapter);
}
// Will be called via the onClick attribute
// of the buttons in main.xml
public void onClick(View view) {
@SuppressWarnings("unchecked")
ArrayAdapter adapter = (ArrayAdapter) getListAdapter();
Comment comment = null;
switch (view.getId()) {
case R.id.add:
String[] comments = new String[] { "Cool", "Very nice", "Hate it" };
int nextInt = new Random().nextInt(3);
// Save the new comment to the database
comment = datasource.createComment(comments[nextInt]);
adapter.add(comment);
break;
case R.id.delete:
if (getListAdapter().getCount() > 0) {
comment = (Comment) getListAdapter().getItem(0);
datasource.deleteComment(comment);
adapter.remove(comment);
}
break;
}
adapter.notifyDataSetChanged();
}
@Override
protected void onResume() {
datasource.open();
super.onResume();
}
@Override
protected void onPause() {
datasource.close();
super.onPause();
}
}
Conclusion
In onCreate() Method create two methods name –
private void openAndQueryDatabase() {
try {
DBHelper dbHelper = new DBHelper(this.getApplicationContext());
newDB = dbHelper.getWritableDatabase();
Cursor c = newDB.rawQuery(“SELECT FirstName, Age FROM ” +
tableName +
” where Age > 10 LIMIT 4″, null);
if (c != null ) {
if (c.moveToFirst()) {
do {
String firstName = c.getString(c.getColumnIndex(“FirstName”));
int age = c.getInt(c.getColumnIndex(“Age”));
results.add(“Name: ” + firstName + “,Age: ” + age);
}while (c.moveToNext());
}
}
} catch (SQLiteException se ) {
Log.e(getClass().getSimpleName(), “Could not create or Open the database”);
} finally {
if (newDB != null)
newDB.execSQL(“DELETE FROM ” + tableName);
newDB.close();
}
}
how do I display the results in a ListView.
private void displayResultList() {
TextView tView = new TextView(this);
tView.setText(“This data is retrieved from the database and only 4 ” +
“of the results are displayed”);
getListView().addHeaderView(tView);
setListAdapter(new ArrayAdapter(this,
android.R.layout.simple_list_item_1, results));
getListView().setTextFilterEnabled(true);
}
How can I get the id from the table on clicking on a list item as list item shows the name not the id.
Thr cursor.