ListView Example 5 – Using Sqlite Database

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:

  1. You have configured your Android development environment on Eclipse or other tools
  2. You know how to create a new Android project
  3. You have gone through the example code for the previous posts in the series

What you will learn:

  1. How to load a ListView based on Sqlite database resource file
  2. 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
  3. 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
  4. 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
  5. 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:

  1. Create an Activity [Start.java] with layout containing one button
  2. 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
  3. When user clicks each item to check the box, we will save the items that are checked in Sqlite database
  4. When user clicks Clear, we will clear the selection on listview and saved items
  5. 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.

http://downloadpart.com