(Instructions for the homework and how to download today's example project at the end of this doc.)
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
}
}
}
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;
}
});
*/
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);
}
}
Now we have a real homework to bring the material together. This homework is due by class in 2 weeks, Nov 9th.