Lecture 5 - SQLite, Lists, Menus

< CS193A Android Programming

(Instructions for the homework and how to download today's example project at the end of this doc.)

SQLite Database Layer

package edu.stanford.nick;

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;

/**
 * This class manages a connection to the database, providing
 * convenience methods to create/update/delete, and centralizing the
 * constants used in the database.
 * 
 * It should be possible to adapt this class for common android/db applications
 * by changing the constants and a few methods.
 * 
 * This class is released into the public domain, free for any purpose.
 * Nick Parlante 2011
 *
 */
public class TodoDB {
	public static final int DATABASE_VERSION = 1;
	public static final String DATABASE_NAME = "tododb";
	public static final String DATABASE_TABLE = "todo";

	// Field names -- use the KEY_XXX constants here and in
	// client code, so it's all consistent and checked at compile-time.

	public static final String KEY_ROWID = "_id";  // Android requires exactly this key name
	public static final int INDEX_ROWID = 0;
	public static final String KEY_TITLE = "title";
	public static final int INDEX_TITLE = 1;
	public static final String KEY_BODY = "body";
	public static final int INDEX_BODY = 2;
	public static final String KEY_STATE = "state";
	public static final int INDEX_STATE = 3;
	
	public static final String[] KEYS_ALL =
		{ TodoDB.KEY_ROWID, TodoDB.KEY_TITLE, TodoDB.KEY_BODY, TodoDB.KEY_STATE };


	private Context mContext;
	private SQLiteDatabase mDatabase;
	private TodoDBHelper mHelper;

	/** Construct DB for this activity context. */
	public TodoDB(Context context) {
		mContext = context;
	}

	/** Opens up a connection to the database. Do this before any operations. */
	public void open() throws SQLException {
		mHelper = new TodoDBHelper(mContext);
		mDatabase = mHelper.getWritableDatabase();
	}

	/** Closes the database connection. Operations are not valid after this. */
	public void close() {
		mHelper.close();
		mHelper = null;
		mDatabase = null;
	}

	
	/**
	  Creates and inserts a new row using the given values.
	  Returns the rowid of the new row, or -1 on error.
	  todo: values should not include a rowid I assume.
	 */
	public long createRow(ContentValues values) {
		return mDatabase.insert(DATABASE_TABLE, null, values);
	}

	/**
	 Updates the given rowid with the given values.
	 Returns true if there was a change (i.e. the rowid was valid).
	 */
	public boolean updateRow(long rowId, ContentValues values) {
		return mDatabase.update(DATABASE_TABLE, values,
				TodoDB.KEY_ROWID + "=" + rowId, null) > 0;
	}

	/**
	 Deletes the given rowid.
	 Returns true if any rows were deleted (i.e. the id was valid).
	*/
	public boolean deleteRow(long rowId) {
		return mDatabase.delete(DATABASE_TABLE,
				TodoDB.KEY_ROWID + "=" + rowId, null) > 0;
	}

	
	/** Returns a cursor for all the rows. Caller should close or manage the cursor. */
	public Cursor queryAll() {
		return mDatabase.query(DATABASE_TABLE,
			KEYS_ALL,  // i.e. return all 4 columns 
			null, null, null, null,
			TodoDB.KEY_TITLE + " ASC"  // order-by, "DESC" for descending
		);
		
		// Could pass for third arg to filter in effect:
		// TodoDatabaseHelper.KEY_STATE + "=0"
		
		// query() is general purpose, here we show the most common usage.
	}

	/** Returns a cursor for the given row id. Caller should close or manage the cursor. */
	public Cursor query(long rowId) throws SQLException {
		Cursor cursor = mDatabase.query(true, DATABASE_TABLE,
			KEYS_ALL,
			KEY_ROWID + "=" + rowId,  // select the one row we care about
			null, null, null, null, null);
		
		// cursor starts before first -- move it to the row itself.
		cursor.moveToFirst();
		return cursor;
	}

	/** Creates a ContentValues hash for our data. Pass in to create/update. */
	public ContentValues createContentValues(String title, String body, int state) {
		ContentValues values = new ContentValues();
		values.put(TodoDB.KEY_TITLE, title);
		values.put(TodoDB.KEY_BODY, body);
		values.put(TodoDB.KEY_STATE, state);
		return values;
	}
	
	// Helper for database open, create, upgrade.
	// Here written as a private inner class to TodoDB.
	private static class TodoDBHelper extends SQLiteOpenHelper {
		// SQL text to create table (basically just string or integer)
		private static final String DATABASE_CREATE =
			"create table " + DATABASE_TABLE + " (" +
			TodoDB.KEY_ROWID + " integer primary key autoincrement, " +
			TodoDB.KEY_TITLE + " text not null, " +
			TodoDB.KEY_BODY + " text not null," +
			TodoDB.KEY_STATE + " integer " +
			");";
		
		// SQLITE does not have a complex type system, so although "done" is a boolean
		// to the app, here we store it as an integer with (0 = false)
		
		
		public TodoDBHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		/** Creates the initial (empty) database. */
		@Override
		public void onCreate(SQLiteDatabase database) {
			database.execSQL(DATABASE_CREATE);
		}

		
		/** Called at version upgrade time, in case we want to change/migrate
		 the database structure. Here we just do nothing. */
		@Override
		public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
			// we do nothing for this case
		}
	}
}

TodoListActivity

package edu.stanford.nick;

import android.app.ListActivity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.ContextMenu;
import android.view.ContextMenu.ContextMenuInfo;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView.AdapterContextMenuInfo;
import android.widget.Button;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;

// Main activity -- shows data list, has a few controls.
public class TodoListActivity extends ListActivity {
	private TodoDB mDB;  // Our connection to the database.
	private SimpleCursorAdapter mCursorAdapter;
	
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        
        setContentView(R.layout.main);
        
        Button button = (Button) findViewById(R.id.button1);
        button.setOnClickListener(new OnClickListener() {
			public void onClick(View v) {
				startDetail(0, true);  // true = create new
			}
		});
		
        // Start up DB connection (closed in onDestroy).
        mDB = new TodoDB(this);
        mDB.open();

        // Get the "all rows" cursor. startManagingCursor() is built in for the common case,
        // takes care of closing etc. the cursor.
		Cursor cursor = mDB.queryAll();
		startManagingCursor(cursor);

		// Adapter: maps cursor keys, to R.id.XXX fields in the row layout.
		String[] from = new String[] { TodoDB.KEY_TITLE, TodoDB.KEY_STATE };
		int[] to = new int[] { R.id.rowtext, R.id.rowtext2 };
		mCursorAdapter = new SimpleCursorAdapter(this, R.layout.row2, cursor, from, to);
		
		// Map "state" int to text in the row -- intercept the setup of each row view,
		// fiddle with the data for the state column.
		mCursorAdapter.setViewBinder(new SimpleCursorAdapter.ViewBinder() {
			public boolean setViewValue(View view, Cursor cursor, int columnIndex) {
				if (columnIndex == TodoDB.INDEX_STATE) {
					TextView textView = (TextView) view;
					if (cursor.getInt(TodoDB.INDEX_STATE) > 0) {
						textView.setText(" (done) ");
					}
					else {
						textView.setText("");
					}
					return true;  // i.e. we handled it
			    }
			    return false;  // i.e. the system should handle it
			}
			});
		
		// Alternative: also have row.xml layout with just one text field. No ViewBinder
		// needed for that simpler approach.

		setListAdapter(mCursorAdapter);
		registerForContextMenu(getListView());

		// Placing a clickable control inside a list is nontrivial unfortunately.
		// see bug: http://code.google.com/p/android/issues/detail?id=3414
		
    }
    
    // Placing this next to onCreate(), help to remember to mDB.close().
	@Override
	protected void onDestroy() {
		super.onDestroy();
		mDB.close();
	}
    
    // Create menu when the select the menu button.
    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        MenuInflater inflater = getMenuInflater();
        inflater.inflate(R.menu.pref_menu, menu);
        return true;
    }
    
    // Called for menu item select. Return true if we handled it.
    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        // Handle item selection
        switch (item.getItemId()) {
        	case R.id.prefs:
        		// open prefs, previous lecture
        		return true;
        	
            default:
                return super.onOptionsItemSelected(item);

        }
    }
    
    
    // Create context menu for click-hold in list.
	@Override
	public void onCreateContextMenu(ContextMenu menu, View v,
	                                ContextMenuInfo menuInfo) {
		super.onCreateContextMenu(menu, v, menuInfo);
		MenuInflater inflater = getMenuInflater();
		inflater.inflate(R.menu.list_menu, menu);
	}
    
    // Context menu item-select.
	@Override
	public boolean onContextItemSelected(MenuItem item) {
		AdapterContextMenuInfo info = (AdapterContextMenuInfo) item.getMenuInfo();
		switch (item.getItemId()) {
			case R.id.menu_detail:
				startDetail(info.id, false);
				return true;
				
			case R.id.menu_delete:
				remove(info.id);
				return true;				
			default:
				return super.onContextItemSelected(item);
		}
	}
    

    // Removes the given rowId from the database, updates the UI.
    public void remove(long rowId) {
		mDB.deleteRow(rowId);
		//mCursorAdapter.notifyDataSetChanged();  // confusingly, this does not work
		mCursorAdapter.getCursor().requery();  // need this
    }

	
	public static final String EXTRA_ROWID = "rowid";
	
	@Override
	protected void onListItemClick(ListView l, View v, int position, long rowId) {
		super.onListItemClick(l, v, position, rowId);
		startDetail(rowId, false);
	}
	
	// Starts the detail activity, either edit existing or create new.
	public void startDetail(long rowId, boolean create) {
		Intent intent = new Intent(this, DetailActivity.class);
		// Our convention: add rowId to edit existing. To create add nothing.
		if (!create) {
			intent.putExtra(EXTRA_ROWID, rowId);
		}
		startActivity(intent);
		// Easy bug: remember to add to add a manifest entry for the detail activity
	}
}


/*
Customizing how the data goes into each list/row (use with row2 layout)

		mCursorAdapter.setViewBinder(new SimpleCursorAdapter.ViewBinder() {
			public boolean setViewValue(View view, Cursor cursor, int columnIndex) {
				if (columnIndex == TodoDB.INDEX_STATE) {
					TextView tv = (TextView) view;
					if (cursor.getInt(TodoDB.INDEX_STATE) > 0) {
						tv.setText(" (done) ");
					}
					else {
						tv.setText("");
					}
					return true;
			    }
			    return false;
			}
			});
*/

Detail Activity

package edu.stanford.nick;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.CheckBox;
import android.widget.EditText;

// Shows/edits the data for one row.
public class DetailActivity extends Activity {
	private TodoDB mDB;
	private Long mRowId;
	
	private EditText mEditText1;
	private EditText mEditText2;
	private CheckBox mCheckBox;

	@Override
	protected void onCreate(Bundle bundle) {
		super.onCreate(bundle);
		
		setContentView(R.layout.detail);
		
		mEditText1 = (EditText) findViewById(R.id.editText1);
		mEditText2 = (EditText) findViewById(R.id.editText2);
		mCheckBox = (CheckBox) findViewById(R.id.checkBox1);

		mRowId = null;

		if (bundle == null) {  // initially, Intent -> extras -> rowID
			Bundle extras = getIntent().getExtras();
			if (extras != null && extras.containsKey(TodoListActivity.EXTRA_ROWID)) {
				mRowId = extras.getLong(TodoListActivity.EXTRA_ROWID);
			}
		}
		else {  // tricky: recover mRowId from kill destroy/create cycle
			mRowId = bundle.getLong(SAVE_ROW);
		}
		
		mDB = new TodoDB(this);
		mDB.open();

		dbToUI();
		
		
		Button button = (Button) findViewById(R.id.button1);
		button.setOnClickListener(new View.OnClickListener() {
			public void onClick(View view) {
				finish();  // same as "back" .. either way we get onPause() to save
			}
		});
		// todo: could ponder having a cancel button, where throw away data

	}
	
	// note: put this next to onCreate, to remember to balance things
	@Override
	protected void onDestroy() {
		super.onDestroy();
		mDB.close();
	}

	// Copies database state up to the UI.
	private void dbToUI() {
		if (mRowId != null) {
			Cursor cursor = mDB.query(mRowId);
			// Note: a cursor should be closed after use, or "managed".
			
			// Could use cursor.getColumnIndex(columnName) to look up 0, 1, ... index
			// for each column name. Here use INDEX_ consts from TodoDB.
			mEditText1.setText(cursor.getString(TodoDB.INDEX_TITLE));
			mEditText2.setText(cursor.getString(TodoDB.INDEX_BODY));
			mCheckBox.setChecked(cursor.getInt(TodoDB.INDEX_STATE) > 0);
			
			cursor.close();
		}
	}

	@Override
	protected void onPause() {
		super.onPause();
		save();
	}


	/** Save the state in the UI to the database, creating a new row or updating
	 * an existing row.
	 */
	private void save() {
		String title = mEditText1.getText().toString();
		String body = mEditText2.getText().toString();
		int done = 0;
		if (mCheckBox.isChecked()) done = 1;

		// Not null = edit of existing row, or it's new but we saved it previously,
		// so now it has a rowId anyway.
		if (mRowId != null) {
			mDB.updateRow(mRowId, mDB.createContentValues(title, body, done));
		}
		else {
			mRowId = mDB.createRow(mDB.createContentValues(title, body, done));
		}
	}

	// Tricky: preserve mRowId var when this activity is killed.
	// Note that the UI state is all saved automatically, so we just have to
	// save mRowID. See code in onCreate() that matches this save.
	public static final String SAVE_ROW = "saverow";
	protected void onSaveInstanceState(Bundle outState) {
		super.onSaveInstanceState(outState);
		outState.putLong(SAVE_ROW, mRowId);
	}
}

Menus / remove

Bonus Topic - Custom Row


Homework

Now we have a real homework to bring the material together. This homework is due by class in 2 weeks, Nov 9th.