This is the fifth blog post in series of posts that will demonstrate how Android ListView can be used in your application. We started the series off with the most basic loading of hard coded arraylist in a ListView. In the second post we loaded the data from the Xml file. In the third post, we loaded the listview from an Xml resource file and displayed it as multiple selection checkboxes which user can select and unselect and also save/load their selections. In the fourth post we took it one step further and used custom layout and custom adapter to render checkbox, textView and Image for each item in list box and persist our selection to SharedPreferences.
In this post we will take it one step further and bind the ListView custom adapter that extends SimpleCursorAdapter based on Sqlite database cursor. We will still persist and reload the data for selected checkboxes from Sqlite database.
Skill Assumptions:
- You have configured your Android development environment on Eclipse or other tools
- You know how to create a new Android project
- You have gone through the example code for the previous posts in the series
What you will learn:
- How to load a ListView based on Sqlite database resource file
- How to use a custom layout for single row to display CheckBox, TextView and ImageButton for each item in the list and allow user to select multiple items
- How to implement a custom Adapter by extending SimpleCursorAdapter that is based on a Sqlite database cursor and bind a it to the ListView and allow user to select multiple items
- How to save user selections to Sqlite database so that the selected items can be loaded back as checked items when the activity starts again
- How to handle onClick event so that selections are saved user clicks on each individual item in ListView
What are we doing in this project:
- Create an Activity [Start.java] with layout containing one button
- When the button is clicked we will launch another activity [CustomListViewDB.java] that will load the listview from Sqlite database file using custom adapter derived from ArrayAdapter that binds with custom layout for single row
- When user clicks each item to check the box, we will save the items that are checked in Sqlite database
- When user clicks Clear, we will clear the selection on listview and saved items
- When user clicks back button or restarts the application, we will load any previously checked items from the database and set them as checked
Lets jump into the example.
Step 1 – Create a new Eclipse project
Step 2 -Â Add a default Activity named Start which will add main.xml under layout folder and Start.java under your namespace folder under src.
Step 3 – In your eclipse project under the res/assets folder create a new Sqlite database file named TODOList. You can either run the sql to create the database at the command line or use a visual tool like SQLite Database Browser. Create a new Sqlite database with following tables defined in it. Run the following SQL script to create the database needed for this example.
//sql for creating database
BEGIN TRANSACTION;
CREATE TABLE ToDoItems (_id INTEGER PRIMARY KEY, title TEXT, description TEXT, selected INTEGER);
INSERT INTO ToDoItems VALUES(1,'Fill up Gasoline',NULL,NULL);
INSERT INTO ToDoItems VALUES(2,'Wash car',NULL,NULL);
INSERT INTO ToDoItems VALUES(3,'Dinner with friends',NULL,NULL);
INSERT INTO ToDoItems VALUES(4,'Watch Movie',NULL,NULL);
CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US');
INSERT INTO android_metadata VALUES('en_US');
COMMIT;
After you have created the database, save the database file with name as TODOList or copy the TODOList file to res/assets folder under your Android project.
If you want to learn more about how to include Sqlite database files as resource files into your application here is a very nice article – Using your own SQLite database in Android applications
Here is how your folder structure will look like:
Step 4 – Copy paste following Xml on your main.xml file.
// layout/main.xml
Step 5 – Copy paste following code in your AndroidManifest.xml file.
Also, notice the additional activity node we have added that registers the activity with Android – activity android:name=”.CustomListViewDB”. We will add the CustomListViewDB class and related layout in next steps.
// AndroidManifest.xml>
Step 6 – Copy and paste following code in your Start.java class file.
// src/Start.java
package com.appfulcrum.blog.examples.listviewcustomdb;
import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;
public class Start extends Activity {
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
Button btnSimple = (Button) findViewById(R.id.btnSimple);
btnSimple.setOnClickListener(new OnClickListener() {
public void onClick(View v) {
Toast.makeText(getApplicationContext(),
" You clicked ListView From DB button", Toast.LENGTH_SHORT).show();
Intent intent = new Intent(v.getContext(), CustomListViewDB.class);
startActivityForResult(intent, 0);
}
});
}
}
Step 7 – Add a new Android layout file in your res/layout folder named simple.xml. Then copy and paste following xml in simple.xml file.
// res/layout/simple.xml
Step 8 – Add a new Android layout file in your res/layout folder named single_item.xml. Then copy and paste following xml in single_item.xml file.
// res/layout/single_item.xml
Step 9 – Add a new class file named CustomListViewDB.java to your project in your src folder. Then copy and paste following code in the CustomListViewDB.java file.
// src/CustomListViewDB.java
package com.appfulcrum.blog.examples.listviewcustomdb;
import android.app.ListActivity;
import android.database.Cursor;
import android.database.SQLException;
import android.os.AsyncTask;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.ListView;
import android.widget.Toast;
public class CustomListViewDB extends ListActivity {
private ListView mainListView = null;
CustomSqlCursorAdapter adapter = null;
private SqlHelper dbHelper = null;
private Cursor currentCursor = null;
private ListView listView = null;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.simple);
if (this.dbHelper == null) {
this.dbHelper = new SqlHelper(this);
}
listView = getListView();
listView.setItemsCanFocus(false);
listView.setChoiceMode(ListView.CHOICE_MODE_MULTIPLE);
Button btnClear = (Button) findViewById(R.id.btnClear);
btnClear.setOnClickListener(new OnClickListener() {
public void onClick(View v) {
Toast.makeText(getApplicationContext(),
" You clicked Clear button", Toast.LENGTH_SHORT).show();
ClearDBSelections();
}
});
new SelectDataTask().execute();
this.mainListView = getListView();
mainListView.setCacheColorHint(0);
}
@Override
protected void onRestart() {
super.onRestart();
new SelectDataTask().execute();
}
@Override
protected void onPause() {
super.onPause();
this.dbHelper.close();
}
protected void ClearDBSelections() {
this.adapter.ClearSelections();
}
private class SelectDataTask extends AsyncTask {
protected void onPreExecute() {
// this.dialog.setMessage("Getting Names...");
// this.dialog.show();
}
protected String doInBackground(final String... args) {
try {
CustomListViewDB.this.dbHelper.createDatabase();
CustomListViewDB.this.dbHelper.openDataBase();
CustomListViewDB.this.currentCursor = CustomListViewDB.this.dbHelper
.getCursor();
} catch (SQLException sqle) {
throw sqle;
}
return null;
}
// can use UI thread here
protected void onPostExecute(final String result) {
startManagingCursor(CustomListViewDB.this.currentCursor);
int[] listFields = new int[] { R.id.txtTitle };
String[] dbColumns = new String[] { SqlHelper.COLUMN_TITLE };
CustomListViewDB.this.adapter = new CustomSqlCursorAdapter(
CustomListViewDB.this, R.layout.single_item,
CustomListViewDB.this.currentCursor, dbColumns, listFields,
CustomListViewDB.this.dbHelper);
setListAdapter(CustomListViewDB.this.adapter);
}
}
}
Step – 10 Add a new class file named CustomSqlCursorAdapter.java to your project in your src folder. Then copy and paste following code in the CustomSqlCursorAdapter.java file.
Few important things to notice in this class:
1. It extends SimpleSqlAdapter
2. It implements OnClickListener which we bind with Click event for checkbox within each row
3. It overrides getView method and sets the value for the TextView and state of the CheckBox from the Sqlite datbase
// src/CustomSqlCursorAdapter.java
package com.appfulcrum.blog.examples.listviewcustomdb;
import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.view.View.OnClickListener;
import android.widget.CheckBox;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;
public class CustomSqlCursorAdapter extends SimpleCursorAdapter implements
OnClickListener {
private Context context;
private SqlHelper dbHelper;
private Cursor currentCursor;
public CustomSqlCursorAdapter(Context context, int layout, Cursor c,
String[] from, int[] to, SqlHelper dbHelper) {
super(context, layout, c, from, to);
this.currentCursor = c;
this.context = context;
this.dbHelper = dbHelper;
}
public View getView(int pos, View inView, ViewGroup parent) {
View v = inView;
if (v == null) {
LayoutInflater inflater = (LayoutInflater) context
.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
v = inflater.inflate(R.layout.single_item, null);
}
this.currentCursor.moveToPosition(pos);
CheckBox cBox = (CheckBox) v.findViewById(R.id.bcheck);
cBox.setTag(Integer.parseInt(this.currentCursor
.getString(this.currentCursor
.getColumnIndex(SqlHelper.COLUMN_ID))));
if (this.currentCursor.getString(this.currentCursor
.getColumnIndex(SqlHelper.COLUMN_SELECTED)) != null
&& Integer.parseInt(this.currentCursor
.getString(this.currentCursor
.getColumnIndex(SqlHelper.COLUMN_SELECTED))) != 0) {
cBox.setChecked(true);
} else {
cBox.setChecked(false);
}
cBox.setOnClickListener(this);
TextView txtTitle = (TextView) v.findViewById(R.id.txtTitle);
txtTitle.setText(this.currentCursor.getString(this.currentCursor
.getColumnIndex(SqlHelper.COLUMN_TITLE)));
return (v);
}
public void ClearSelections() {
this.dbHelper.clearSelections();
this.currentCursor.requery();
}
@Override
public void onClick(View v) {
CheckBox cBox = (CheckBox) v;
Integer _id = (Integer) cBox.getTag();
ContentValues values = new ContentValues();
values.put(" selected", cBox.isChecked() ? 1 : 0);
this.dbHelper.dbSqlite.update(SqlHelper.TABLE_NAME, values, "_id=?",
new String[] { Integer.toString(_id) });
}
}
Step – 11 Add a new class file named SqlHelper.java to your project in your src folder. Then copy and paste following code in the SqlHelper.java file.
//src/SqlHelper.java
package com.appfulcrum.blog.examples.listviewcustomdb;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Locale;
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.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;
public class SqlHelper extends SQLiteOpenHelper {
public static final String DATABASE_PATH = "/data/data/com.appfulcrum.blog.examples.listviewcustomdb/databases/";
public static final String DATABASE_NAME = "TODOList";
public static final String TABLE_NAME = "ToDoItems";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_TITLE = "title";
public static final String COLUMN_NAME_DESC = "description";
public static final String COLUMN_SELECTED = "selected";
public SQLiteDatabase dbSqlite;
private final Context myContext;
public SqlHelper(Context context) {
super(context, DATABASE_NAME, null, 1);
this.myContext = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
// check if exists and copy database from resource
createDB();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w("SqlHelper", "Upgrading database from version " + oldVersion
+ " to " + newVersion + ", which will destroy all old data");
onCreate(db);
}
public void createDatabase() {
createDB();
}
private void createDB() {
boolean dbExist = DBExists();
if (!dbExist) {
copyDBFromResource();
}
}
private boolean DBExists() {
SQLiteDatabase db = null;
try {
String databasePath = DATABASE_PATH + DATABASE_NAME;
db = SQLiteDatabase.openDatabase(databasePath, null,
SQLiteDatabase.OPEN_READWRITE);
db.setLocale(Locale.getDefault());
db.setLockingEnabled(true);
db.setVersion(1);
} catch (SQLiteException e) {
Log.e("SqlHelper", "database not found");
}
if (db != null) {
db.close();
}
return db != null ? true : false;
}
private void copyDBFromResource() {
InputStream inputStream = null;
OutputStream outStream = null;
String dbFilePath = DATABASE_PATH + DATABASE_NAME;
try {
inputStream = myContext.getAssets().open(DATABASE_NAME);
outStream = new FileOutputStream(dbFilePath);
byte[] buffer = new byte[1024];
int length;
while ((length = inputStream.read(buffer)) > 0) {
outStream.write(buffer, 0, length);
}
outStream.flush();
outStream.close();
inputStream.close();
} catch (IOException e) {
throw new Error("Problem copying database from resource file.");
}
}
public void openDataBase() throws SQLException {
String myPath = DATABASE_PATH + DATABASE_NAME;
dbSqlite = SQLiteDatabase.openDatabase(myPath, null,
SQLiteDatabase.OPEN_READWRITE);
}
@Override
public synchronized void close() {
if (dbSqlite != null)
dbSqlite.close();
super.close();
}
public Cursor getCursor() {
SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
queryBuilder.setTables(TABLE_NAME);
String[] asColumnsToReturn = new String[] { COLUMN_ID, COLUMN_TITLE,
COLUMN_NAME_DESC, COLUMN_SELECTED };
Cursor mCursor = queryBuilder.query(dbSqlite, asColumnsToReturn, null,
null, null, null, "title ASC");
return mCursor;
}
public void clearSelections() {
ContentValues values = new ContentValues();
values.put(" selected", 0);
this.dbSqlite.update(SqlHelper.TABLE_NAME, values, null, null);
}
}
Step 12 – Run the application as Android Application and you should see below for output.
