|
|
Cyril Sermon (@admin) |
To extract actual values from a result Cursor, first use the moveTo<location> methods described pre-viously to position the Cursor at the correct row of the result set.
With the Cursor at the desired row, use the type-safe get methods (passing in a column index) to return the value stored at the current row for the specified column, as shown in the following snippet:
String columnValue = myResult.getString(columnIndex);Database implementations should publish static constants that provide the column indexes using more easily recognizable variables based on the column names. They are generally exposed within a database adapter as described previously.
The following example shows how to iterate over a result cursor, extracting and summing a column of floats:
int GOLD_HOARDED_COLUMN = 2;
Cursor myGold = myDatabase.query(“GoldHoards”, null, null, null, null, null, null);
float totalHoard = 0f;Make sure there is at least one row.
if (myGold.moveToFirst()) {
Iterate over each cursor. do {
float hoard = myGold.getFloat(GOLD_HOARDED_COLUMN); totalHoard += hoard;
} while(myGold.moveToNext());
}
float averageHoard = totalHoard / myGold.getCount();Because SQLite database columns are loosely typed, you can cast individual values into valid types as required. For example, values stored as floats can be read back as Strings.
The SQLiteDatabase class exposes specialized insert, delete, and update methods to encapsulate the SQL statements required to perform these actions. Nonetheless, the execSQL method lets you exe-cute any valid SQL on your database tables should you want to execute these operations manually.
Any time you modify the underlying database values, you should call refreshQuery on any Cursors that currently have a view on the table.
To create a new row, construct a ContentValues object, and use its put methods to supply values for each column. Insert the new row by passing the Content Values object into the insert method called on the target database object — along with the table name — as shown in the snippet below:
Create a new row of values to insert.
ContentValues newValues = new ContentValues();Assign values for each row.
newValues.put(COLUMN_NAME, newValue); [ ... Repeat for each column ... ]Insert the row into your table
myDatabase.insert(DATABASE_TABLE, null, newValues);Updating rows is also done using Content Values.
Create a new ContentValues object, using the put methods to assign new values to each column you want to update. Call update on the database object, passing in the table name, the updated Content Val-ues object, and a where statement that returns the row(s) to update.
The update process is demonstrated in the snippet below:
// Define the updated row content.
ContentValues updatedValues = new ContentValues();
Assign values for each row. updatedValues.put(COLUMN_NAME, newValue); [ ... Repeat for each column ... ]
String where = KEY_ID + “=” + rowId;Update the row with the specified index with the new values.
myDatabase.update(DATABASE_TABLE, updatedValues, where, null);To delete a row, simply call delete on your database object, specifying the table name and a where clause that returns the rows you want to delete, as shown in the code below:
myDatabase.delete(DATABASE_TABLE, KEY_ID + “=” + rowId, null);Previously in this chapter, you enhanced the To-Do List example to persist the Activity’s UI state across sessions. That was only half the job; in the following example, you’ll create a private database to save the to-do items:
Start by creating a new ToDoDBAdapter class. It will be used to manage your database interactions.
Create private variables to store the SQLiteDatabase object and the Context of the calling application. Add a constructor that takes the owner application’s Context, and include static class variables for the name and version of the database and a name for the to-do item table.
package com.paad.todolist;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log;
public class ToDoDBAdapter {
private static final String DATABASE_NAME = “todoList.db”; private static final String DATABASE_TABLE = “todoItems”; private static final int DATABASE_VERSION = 1;
private SQLiteDatabase db;
private final Context context;
public ToDoDBAdapter(Context _context) {
this.context = _context;
}
}Create public convenience variables that define the column names and indexes; this will make it easier to find the correct columns when extracting values from query result Cursors.
public static final String KEY_ID = “_id”;
public static final String KEY_TASK = “task”;
public static final int TASK_COLUMN = 1;
public static final String KEY_CREATION_DATE = “creation_date”; public static final int CREATION_DATE_COLUMN = 2;Create a new taskDBOpenHelper class within the ToDoDBAdapter that extends SQLiteOpenHelper. It will be used to simplify version management of your database.
Within it, overwrite the onCreate and onUpgrade methods to handle the database creation and upgrade logic.
private static class toDoDBOpenHelper extends SQLiteOpenHelper {
public toDoDBOpenHelper(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
}
// SQL Statement to create a new database.
private static final String DATABASE_CREATE = “create table “ + DATABASE_TABLE + “ (“ + KEY_ID +
“ integer primary key autoincrement, “ +
KEY_TASK + “ text not null, “ + KEY_CREATION_DATE + “ long);”;
@Override
public void onCreate(SQLiteDatabase _db) {
_db.execSQL(DATABASE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
Log.w(“TaskDBAdapter”, “Upgrading from version “ +
_oldVersion + “ to “ +
_newVersion +
“, which will destroy all old data”);
// Drop the old table.
_db.execSQL(“DROP TABLE IF EXISTS “ + DATABASE_TABLE);
Create a new one. onCreate(_db);
}
}Within the ToDoDBAdapter class, add a private instance variable to store an instance of the toDoDBOpenHelper class you just created; assign it within the constructor.
private toDoDBOpenHelper dbHelper;
public ToDoDBAdapter(Context _context) {
this.context = _context;
dbHelper = new toDoDBOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
}Still in the adapter class, create open and close methods that encapsulate the open and close logic for your database. Start with a close method that simply calls close on the database object.
public void close() {
db.close();
}The open method should use the toDoDBOpenHelper class. Call getWritableDatabase to let the helper handle database creation and version checking. Wrap the call to try to provide a readable database if a writable instance can’t be opened.
public void open() throws SQLiteException {
try {
db = dbHelper.getWritableDatabase();
} catch (SQLiteException ex) {
db = dbHelper.getReadableDatabase();
}
}Add strongly typed methods for adding, removing, and updating items.
// Insert a new task
public long insertTask(ToDoItem _task) {Create a new row of values to insert.
ContentValues newTaskValues = new ContentValues();Assign values for each row.
newTaskValues.put(KEY_TASK, _task.getTask()); newTaskValues.put(KEY_CREATION_DATE, _task.getCreated().getTime());Insert the row.
return db.insert(DATABASE_TABLE, null, newTaskValues);
}
// Remove a task based on its index
public boolean removeTask(long _rowIndex) {
return db.delete(DATABASE_TABLE, KEY_ID + “=” + _rowIndex, null) > 0;
}
// Update a task
public boolean updateTask(long _rowIndex, String _task) { ContentValues newValue = new ContentValues(); newValue.put(KEY_TASK, _task);
return db.update(DATABASE_TABLE, newValue,
KEY_ID + “=” + _rowIndex, null) > 0;
}Now add helper methods to handle queries. Write three methods — one to return all the items, another to return a particular row as a Cursor, and finally, one that returns a strongly typed ToDoItem.
public Cursor getAllToDoItemsCursor() {
return db.query(DATABASE_TABLE,
new String[] { KEY_ID, KEY_TASK, KEY_CREATION_DATE}, null, null, null, null, null);
}
public Cursor setCursorToToDoItem(long _rowIndex) throws SQLException { Cursor result = db.query(true, DATABASE_TABLE,
new String[] {KEY_ID, KEY_TASK},
KEY_ID + “=” + _rowIndex, null, null, null,
null, null);
if ((result.getCount() == 0) || !result.moveToFirst()) { throw new SQLException(“No to do items found for row: “ +
_rowIndex);
}
return result;
}
public ToDoItem getToDoItem(long _rowIndex) throws SQLException { Cursor cursor = db.query(true, DATABASE_TABLE,
new String[] {KEY_ID, KEY_TASK},
KEY_ID + “=” + _rowIndex,
null, null, null, null, null);
if ((cursor.getCount() == 0) || !cursor.moveToFirst()) { throw new SQLException(“No to do item found for row: “ +
_rowIndex);
}
String task = cursor.getString(TASK_COLUMN);
long created = cursor.getLong(CREATION_DATE_COLUMN);
ToDoItem result = new ToDoItem(task, new Date(created)); return result;
}That completes the database helper class. Return the ToDoList Activity, and update it to persist the to-do list array.
Start by updating the Activity’s onCreate method to create an instance of the toDoDBAdapter, and open a connection to the database. Also include a call to the populateTodoList method stub.
ToDoDBAdapter toDoDBAdapter;
public void onCreate(Bundle icicle) {
... existing onCreate logic ... ] toDoDBAdapter = new ToDoDBAdapter(this);
Open or create the database toDoDBAdapter.open();
populateTodoList();
}
private void populateTodoList() { }Create a new instance variable to store a Cursor over all the to-do items in the database.
Update the populateTodoList method to use the toDoDBAdapter instance to query the database, and call startManagingCursor to let the Activity manage the Cursor. It should also make a call to updateArray, a method that will be used to repopulate the to-do list array using the Cursor.
Cursor toDoListCursor;
private void populateTodoList() {Get all the todo list items from the database.
toDoListCursor = toDoDBAdapter.getAllToDoItemsCursor(); startManagingCursor(toDoListCursor);Update the array.
updateArray();
}
private void updateArray() { }Now implement the updateArray method to update the current to-do list array. Call requery on the result Cursor to ensure that it’s fully up to date, then clear the array and iterate over the result set. When complete, call notifyDataSetChanged on the Array Adapter.
private void updateArray() {
toDoListCursor.requery();
todoItems.clear();
if (toDoListCursor.moveToFirst())
do {
String task =
toDoListCursor.getString(ToDoDBAdapter.TASK_COLUMN);
long created =
toDoListCursor.getLong(ToDoDBAdapter.CREATION_DATE_COLUMN);
ToDoItem newItem = new ToDoItem(task, new Date(created)); todoItems.add(0, newItem);
} while(toDoListCursor.moveToNext()); aa.notifyDataSetChanged();
}To join the pieces together, modify the OnKeyListener assigned to the text entry box in the onCreate method, and update the removeItem method. Both should now use the toDoDBAdapter to add and remove items from the database rather than modifying the to-do list array directly.
12.1. Start with the OnKeyListener, insert the new item into the database, and refresh the array.
public void onCreate(Bundle icicle) {
super.onCreate(icicle);
setContentView(R.layout.main);
myListView = (ListView)findViewById(R.id.myListView); myEditText = (EditText)findViewById(R.id.myEditText);
todoItems = new ArrayList<ToDoItem>();
int resID = R.layout.todolist_item;
= new ToDoItemAdapter(this, resID, todoItems); myListView.setAdapter(aa);
myEditText.setOnKeyListener(new OnKeyListener() {
public boolean onKey(View v, int keyCode, KeyEvent event) { if (event.getAction() == KeyEvent.ACTION_DOWN)
if (keyCode == KeyEvent.KEYCODE_DPAD_CENTER) { ToDoItem newItem;
newItem = new ToDoItem(myEditText.getText().toString()); toDoDBAdapter.insertTask(newItem); updateArray();
myEditText.setText(“”);
aa.notifyDataSetChanged();
cancelAdd();
return true;
}
return false;
}
});
registerForContextMenu(myListView);
restoreUIState();
toDoDBAdapter = new ToDoDBAdapter(this);
Open or create the database toDoDBAdapter.open();
populateTodoList();
}12.2. Then modify the removeItem method to remove the item from the database and refresh the array list.
private void removeItem(int _index) {Items are added to the listview in reverse order, so invert the index.
toDoDBAdapter.removeTask(todoItems.size()-_index); updateArray();
}As a final step, override the onDestroy method of your Activity to close your database connection.
@Override
public void onDestroy() {
Close the database toDoDBAdapter.close();
super.onDestroy();
}Your to-do items will now be saved between sessions. As a further enhancement, you could change the Array Adapter to a Cursor Adapter and have the List View update dynamically, directly from changes to the underlying database.
By using a private database, your tasks are not available for other applications to view or add to them. To provide access to your tasks for other applications to leverage, you can expose them using a Content Provider.