Creating and Editing Tasks with SQLite

The first thing you need to do is create a task. To do that, you need to insert a record. After that, you need to list the task(s) on the ReminderListActivity, which in turn allows you to tap a task to edit it, or long-press a task to delete it. These user interactions describe the create, read, update, and delete operations for your Task Reminder application. The next few sections show you how to add this functionality.

Inserting your first task entry

Inserting tasks into the SQLite database is a fairly easy process after you get the hang of it. In general terms, you simply follow these steps:

1. Set up the required local variables.
2. Build the Save button click listener.
3. Retrieve values from EditText views.
4. Interact with the RemindersDbAdapter class.
5. Open and close the database.

By learning how to insert your first task, you should get a good enough grasp on the SQLiteDatabase class interaction to perform the other necessary tasks. Therefore, in this section I introduce you to the entire implementation of RemindersDbAdapter, which outlines all the CRUD operations. Afterwards, I describe the CRUD tasks in greater detail.

Saving values from the screen to the database

When the user creates a task, it takes place in the ReminderEditActivity. To create the task, you need to create a class-level RemindersDbAdapter variable that is instantiated in the onCreate() method. After it is instantiated, you open the database with a call to the RemindersDbAdapter's open() method in the onResume() method. (Type this code into your RemindersEditActivity class now.)

@Override
protected void onCreate(Bundle savedInstanceState) {
               super.onCreate(savedInstanceState);

               mDbHelper = new RemindersDbAdapter(this);
               mDbHelper.open();

               setContentView(R.layout.reminder_edit);
               // … the remainder of the onCreate() method

At this point, you have a reference to the RemindersDbAdapter that allows you to call into the RemindersDbAdapter class to create a task. To add the task, you need the title, description, and reminder date and time. To gain access to the title and description, you need to add three class-level variables to ReminderEditActivity. Two of them are EditText type variables that reference the EditText values in the layout for the ReminderEditActivity. The remaining class-level variable is the Save button that you will click when you are ready to save the task to the SQLite database. I have included these variables at the top of my ReminderEditActivity file like this. Include these declarations in your file as well:

private EditText mTitleText;
private Button mConfirmButton;
private EditText mBodyText;

You need to instantiate those in the onCreate() method call like this:

mConfirmButton = (Button) findViewById(R.id.confirm);
mTitleText = (EditText) findViewById(R.id.title);
mBodyText = (EditText) findViewById(R.id.body);

You already have a Calendar object that was populated from the DatePicker and TimePicker; therefore, you do not need to create anything in regard to those values. The only thing left is to provide the ability to save the task after you type values into the EditText fields (title and description) by pressing the Save button on the screen. To do that, you need to attach a click listener to the Save button by typing the following code into the registerButtonListenersAndSetDefaultText() method:

mConfirmButton.setOnClickListener(new View.OnClickListener() {
               public void onClick(View view) {
                   saveState();                                      →3
                   setResult(RESULT_OK);                             →4
                   Toast.makeText(ReminderEditActivity.this,         →5
                   getString(R.string.task_saved_message),
                     Toast.LENGTH_SHORT).show();
                   finish();                                         →8
               }
});

This code is explained as follows:

You need to create the saveState() method in the ReminderEditActivity, as shown in Listing 14-3. This method communicates with the RemindersDbAdapter to save the task.

Listing 14-3: The saveState() Method

private void saveState() {
        String title = mTitleText.getText().toString();              →2
        String body = mBodyText.getText().toString();                →3

        SimpleDateFormat dateTimeFormat = new
             SimpleDateFormat(DATE_TIME_FORMAT);                     →6
        String reminderDateTime =
                    dateTimeFormat.format(mCalendar.getTime());      →8

        long id = mDbHelper.createReminder(title, body, reminderDateTime);→10

}

The lines of code are explained as follows:

public static final String DATE_TIME_FORMAT = “yyyy-MM-dd kk:mm:ss”;

The task is created by taking the values from the EditText fields and the local Calendar object and calling the createReminder() on the RemindersDbAdapter class. Following the adapter pattern has allowed you to wrap the SQLite logic behind a Java class, which allows the ReminderEditActivity to have no knowledge of the inner workings of the SQLite database.

The entire RemindersDbAdapter implementation

Have you ever bought a car after only seeing pictures of the door handle, hood, and then maybe a seat? Probably not! You'd probably never buy a car from someone who never showed you pictures of the whole thing first. Heck, you probably wouldn't even go look at it! Sometimes it's best to see everything all at once instead of in piecemeal form. Working with SQLite in the RemindersDbAdapter class is no different.

Trying to explain everything to you piece by piece first would not make a lot of sense; therefore, I'm going to show you the entire implementation of the RemindersDbAdapter in Listing 14-4 so that you can get a feel for what you're working with. Then, I explain each new area, and I cross-reference it throughout the rest of this chapter. Hopefully, this explanation helps everything gel inside that Android brain of yours.

Listing 14-4: The Full Implementation of RemindersDbAdapter

public class RemindersDbAdapter {

   private static final String DATABASE_NAME = “data”;
   private static final String DATABASE_TABLE = “reminders”;
   private static final int DATABASE_VERSION = 1;

   public static final String KEY_TITLE = “title”;
   public static final String KEY_BODY = “body”;
   public static final String KEY_DATE_TIME = “reminder_date_time”;
   public static final String KEY_ROWID = “_id”;

   private DatabaseHelper mDbHelper;
   private SQLiteDatabase mDb;

   private static final String DATABASE_CREATE =
           “create table “ + DATABASE_TABLE + ” (”
                   + KEY_ROWID + “ integer primary key autoincrement, ”
                   + KEY_TITLE + “ text not null, ”

                   + KEY_BODY + “ text not null, ”
                   + KEY_DATE_TIME + “ text not null);”;

   private final Context mCtx;

   public RemindersDbAdapter(Context ctx) {
       this.mCtx = ctx;
   }

   public RemindersDbAdapter open() throws android.database.SQLException {
       mDbHelper = new DatabaseHelper(mCtx);
       mDb = mDbHelper.getWritableDatabase();
           return this;
   }

   public void close() {
        mDbHelper.close();
   }

   public long createReminder(String title, String body, String reminderDateTime) {    →38
       ContentValues initialValues = new ContentValues();
       initialValues.put(KEY_TITLE, title);
       initialValues.put(KEY_BODY, body);
       initialValues.put(KEY_DATE_TIME, reminderDateTime);

       return mDb.insert(DATABASE_TABLE, null, initialValues);                         →44
    }

    public boolean deleteReminder(long rowId) {                                        →47
      return
        mDb.delete(DATABASE_TABLE, KEY_ROWID + “=” + rowId, null) > 0;
                                                                                       →49
    }

    public Cursor fetchAllReminders() {                                                →52
        return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,
                KEY_BODY, KEY_DATE_TIME}, null, null, null, null, null);
    }

    public Cursor fetchReminder(long rowId) throws SQLException {                      →57
        Cursor mCursor =
                mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                        KEY_TITLE, KEY_BODY, KEY_DATE_TIME}, KEY_ROWID + “=” + rowId, null,
                        null, null, null, null);                                       →61
        if (mCursor != null) {
            mCursor.moveToFirst();                                                     →63
        }
        return mCursor;

    }

    public boolean updateReminder(long rowId, String title, String body, String
               reminderDateTime) {                                                     →69
        ContentValues args = new ContentValues();                                      →70
        args.put(KEY_TITLE, title);
        args.put(KEY_BODY, body);
        args.put(KEY_DATE_TIME, reminderDateTime);

     return
      mDb.update(DATABASE_TABLE, args, KEY_ROWID + “=” + rowId, null) > 0;             →76
    }

            // The SQLiteOpenHelper class was omitted for brevity
            // That code goes here.
}

This code listing outlines the various CRUD routines. Each accepts a variety of different parameters that are explained in detail in the following sections.

Understanding the insert operation

The insert operation is a simple operation because you are just inserting a value into the database. The insert() method accepts the following parameters:

• table: This parameter is the name of the table to insert the data into. I'm using the DATABASE_TABLE constant for the value.
• nullColumnHack: SQL does not allow inserting a completely empty row, so if the ContentValues parameter (next parameter) is empty, this column is explicitly assigned a NULL value. You pass in null for this value.
• values: This parameter defines the initial values as defined as a ContentValues object. You provide the initialValues local variable as the value for this parameter. This variable contains the key-value pair information for defining a new row.

Understanding the query (read) operation

The query operation is also known as the read operation because most of the time, you will be reading data from the database with the query() method. The query method is responsible for providing a result set based upon a list of criteria that you provide. This method returns a Cursor that provides random read-write access to the result set returned by the query. The query method accepts the following parameters:

• distinct: You want each row to be unique, so you provide true for this value.
• table: The name of the database table to perform the query against. The value you provide comes from the DATABASE_TABLE constant.
• columns: A list of columns to return from the query. Passing null returns all columns, which is normally discouraged to prevent reading and returning data that is not needed. If you need all columns, it's valid to pass in null. You provide a string array of columns to return.
• selection: A filter describing what rows to return formatted as an SQL WHERE clause (excluding the WHERE itself). Passing a null returns all rows in the table. Depending on the situation, you provide either the rowId of the task you would like to fetch or you provide a null to return all tasks.
• selectionArgs: You may include question marks (?) in the selection, which are replaced by the values from selectionArgs in the order they appear in the selection. These values are bound as string types. You do not need selectionArgs; therefore, you pass in null.
• groupBy: A filter describing how to filter rows formatted as an SQL GROUP BY clause (excluding the GROUP BY). Passing null causes the rows not to be grouped. You pass a null value because you don't care how the results are grouped.
• having: This filter describes row groups to include in the cursor, if row grouping is being used. Passing null causes all row groups to be included, and is required when row grouping is not being used. You pass in a null value.
• orderBy: How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null uses the default sort order, which may be unordered. You pass in a null value because you aren't concerned with the order in which the results are returned.
• limit: Limits the number of rows returned by the query by utilizing a LIMIT clause. Passing null states that you do not have a LIMIT clause. You don't want to limit the number of rows returned; therefore, you pass in null to return all the rows that match your query.

Understanding the update operation

Updating a record in a database simply takes the incoming parameters and replaces them in the destination cell inside the row specified (or in the rows if many rows are updated). As with the following delete operation, the update can affect many rows. It is important to understand the update method's parameters and how they can affect the records in the database. The update() method accepts the following parameters:

• table: The table to update. The value that you use is provided by the DATABASE_TABLE constant.
• values: The ContentValues object, which contains the fields to update. Use the args variable, which you constructed on line 70 of Listing 14-4.
• whereClause: The WHERE clause, which restricts which rows should get updated. Here you inform the database to update the row whose ID is equal to rowId by providing the following string value: KEY_ROWID + “=” + rowId.
• whereArgs: Additional WHERE clause arguments. Not used in this call; therefore, null is passed in.

Understanding the delete operation

When using the delete() method, various parameters are used to define the deletion criteria in the database. A delete statement can affect none or all of the records in the database. It is important to understand the parameters of the delete call to ensure that you do not mistakenly delete data. The parameters for the delete() method are as follows:

• table: The table to delete the rows from. The value of this parameter is provided by the DATABASE_TABLE constant.
• whereClause: This is the optional WHERE clause to apply when deleting rows. If you pass null, all rows will be deleted. This value is provided by manually creating the WHERE clause with the following string: KEY_ROWID + “=” + rowId.
• whereArgs: The optional WHERE clause arguments. Not needed in this call because everything is provided through the WHERE clause itself. You pass in a null value because you do not need to use this parameter.

Returning all the tasks with a cursor

You can create a task, but what good is it if you can't see the task in the task list? No good at all, really. Therefore, you must list the tasks that currently exist in the database in the ListView in the ReminderListActivity.

Listing 14-5 outlines the entire ReminderListActivity with the new code that can read the list of tasks from the database into the ListView.

Listing 14-5: The Entire ReminderListActivity with Connections to SQLite

public class ReminderListActivity extends ListActivity {
    private static final int ACTIVITY_CREATE=0;
    private static final int ACTIVITY_EDIT=1;

    private RemindersDbAdapter mDbHelper;                            →5

    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.reminder_list);
        mDbHelper = new RemindersDbAdapter(this);
        mDbHelper.open();
        fillData();                                                 →14
        registerForContextMenu(getListView());

    }

     private void fillData() {
        Cursor remindersCursor = mDbHelper.fetchAllReminders();     →20
        startManagingCursor(remindersCursor);                       →21

        // Create an array to specify the fields we want (only the TITLE)
        String[] from = new String[]{RemindersDbAdapter.KEY_TITLE}; →24

        // and an array of the fields we want to bind in the view
        int[] to = new int[]{R.id.text1};                           →27

        // Now create a simple cursor adapter and set it to display
        SimpleCursorAdapter reminders =
                new SimpleCursorAdapter(this, R.layout.reminder_row,
                        remindersCursor, from, to);                 →32
        setListAdapter(reminders);                                  →33
    }

    // Menu Code removed for brevity

    @Override
    protected void onListItemClick(ListView l, View v, int position, long id) {
        super.onListItemClick(l, v, position, id);
        Intent i = new Intent(this, ReminderEditActivity.class);
        i.putExtra(RemindersDbAdapter.KEY_ROWID, id);               →42
        startActivityForResult(i, ACTIVITY_EDIT);
    }

    @Override
    protected void onActivityResult(int requestCode, int resultCode, Intent intent) {
        super.onActivityResult(requestCode, resultCode, intent);
        fillData();                                                 →49
    }

    @Override
    public boolean onContextItemSelected(MenuItem item) {           →53
        switch(item.getItemId()) {
            case R.id.menu_delete:
                AdapterContextMenuInfo info =
                     (AdapterContextMenuInfo)   item.getMenuInfo(); →57
                mDbHelper.deleteReminder(info.id);                  →58
                fillData();                                         →59
                return true;
        }
        return super.onContextItemSelected(item);
    }

}

The code for reading the list of tasks is explained as follows:

Understanding the SimpleCursorAdapter

In line 32 of Listing 14-5, you created a SimpleCursorAdapter. The SimpleCursorAdapter does a lot of the hard work for you when you want to bind data from a Cursor object to a list view. To set up a SimpleCursorAdapter, you need to provide the following parameters:

• this: Context: The context that is associated with the adapter.
• R.layout.reminder_row - layout: The layout resource identifier that defines the file to use for this list item.
• reminderCursor - c: The database Cursor.
• from - from: An array of column names that are used to bind data from the cursor to the view. This is defined on line 24.
• to - to: An array of view IDs that should display the column information from the from parameter. The To field is defined on line 27.
• The to and from parameters create a mapping informing the SimpleCursorAdapter how to map data in the cursor to views in the row layout.

Now, when you start the application, you see a list of items that you have created. These items are being read from the SQLite database. If you do not see a list of items, create one by pressing the Add Reminder button in the action bar that allows you to add a new task.

Deleting a task

To the end user, deleting a task is as simple as long-pressing an item in the ReminderListActivity and selecting the delete action, but to actually delete the task from the database, you need to use the delete() method on the SQLite database object. This method is called in Listing 14-4 on line 49.

The RemindersDbAdapter deleteReminder() method is called from within the onContextSelectedItem() method call on line 58 of Listing 14-5. The one item that is needed prior to deleting the task from the database is the rowId of the task in the database. To obtain the rowId, you must use the AdapterContextMenuInfo object, which provides extra menu information. This information is provided to the context menu selection when a menu is brought up for the ListView. Because you load the list with a database cursor, the ListView contains the rowId that you're looking for — yes, it's that simple! On line 57 of Listing 14-5, you obtain the AdapterContextMenuInfo object, and on line 58, you call the delete() method with the rowId as a parameter. Afterward, you call the fillData() method to reload the tasks to the screen. You can now create, list (read), and delete the task. The only thing left is updating the task.

Updating a task

When it comes down to it, updating a task is a fairly trivial process. However, it can get a bit tricky because you must use the same activity to update a task as you used to create the task. Therefore, logic has to be put into place to determine whether you are editing an existing task or creating a new one. This logic is based on the intent that was used to start the activity. In the ReminderListActivity, when an item is tapped, the following activity is started:

Intent i = new Intent(this, ReminderEditActivity.class);
i.putExtra(RemindersDbAdapter.KEY_ROWID, id);
startActivityForResult(i, ACTIVITY_EDIT);

This code informs Android to start the ReminderEditActivity with the i parameter (the intent), which contains extra information — the row id of the task that you would like to edit. On the ReminderEditActivity side, you inspect the receiving intent to determine whether it contains the extra id information. If it does, you then consider this an edit action and load the task information into the form to allow the user to edit the information. If the extra information is not there (which would happen if the user elected to add a new task from the menu), you present the user with an empty form to fill out to create a new task.

See Listing 14-6 for an implementation of the previously described logic. The bolded sections outline the new code.

Listing 14-6: The ReminderEditActivity That Supports Inserting and Updating a Task

public class ReminderEditActivity extends Activity {

    // Other Class level variables go here. Removed for brevity
    private Long mRowId;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        mDbHelper = new RemindersDbAdapter(this);

        setContentView(R.layout.reminder_edit);

        mCalendar = Calendar.getInstance();
        mTitleText = (EditText) findViewById(R.id.title);
        mBodyText = (EditText) findViewById(R.id.body);
        mDateButton = (Button) findViewById(R.id.reminder_date);
        mTimeButton = (Button) findViewById(R.id.reminder_time);

        mConfirmButton = (Button) findViewById(R.id.confirm);

        mRowId = savedInstanceState != null                                    →22
            ? savedInstanceState.getLong(RemindersDbAdapter.KEY_ROWID)
: null;

        registerButtonListenersAndSetDefaultText();
    }

  private void setRowIdFromIntent() {                                          →28
        if (mRowId == null) {
            Bundle extras = getIntent().getExtras();
            mRowId = extras != null
? extras.getLong(RemindersDbAdapter.KEY_ROWID)
            : null;
        }
}

@Override
protected void onPause() {
super.onPause();
mDbHelper.close();
                                                                              →40
}
@Override
protected void onResume() {                                                   →44
super.onResume();
mDbHelper.open();
                                                                              →46
setRowIdFromIntent();
                                                                              →47
populateFields();
                                                                              →48
}

    // Date picker, button click events, and buttonText updating, createDialog
    // left out for brevity
    // they normally go here …

private void populateFields()  {                                              →55
if (mRowId != null) {
Cursor reminder = mDbHelper.fetchReminder(mRowId);
                                                                              →57
startManagingCursor(reminder);
                                                                              →58
mTitleText.setText(reminder.getString(
          reminder.getColumnIndexOrThrow(RemindersDbAdapter.KEY_TITLE)));
                                                                              →60
mBodyText.setText(reminder.getString(
            reminder.getColumnIndexOrThrow(RemindersDbAdapter.KEY_BODY)));
                                                                              →62
            SimpleDateFormat dateTimeFormat =
                    new SimpleDateFormat(DATE_TIME_FORMAT);                   →64
Date date = null;
                                                                              →65
            try {
            String dateString =    reminder.getString(
                      reminder.getColumnIndexOrThrow(
                                RemindersDbAdapter.KEY_DATE_TIME));           →69
            date = dateTimeFormat.parse(dateString);                          →70
            mCalendar.setTime(date);                                          →71
            } catch (ParseException e) {                                      →72
                Log.e(“ReminderEditActivity”, e.getMessage(), e);
                                                                              →73
            }
}
updateDateButtonText();
updateTimeButtonText();
}

@Override
protected void onSaveInstanceState(Bundle outState) {
super.onSaveInstanceState(outState);
outState.putLong(RemindersDbAdapter.KEY_ROWID, mRowId);
                                                                              →84
}
    private void saveState() {
        String title = mTitleText.getText().toString();
        String body = mBodyText.getText().toString();

        SimpleDateFormat dateTimeFormat = new SimpleDateFormat(DATE_TIME_FORMAT);
                String reminderDateTime =
                        dateTimeFormat.format(mCalendar.getTime());

if (mRowId == null) {
                                                                               →96
long id = mDbHelper.createReminder(title, body, reminderDateTime);             →97
if (id > 0) {
                                                                               →98
 mRowId = id;
                                                                               →99
}
} else {
mDbHelper
                .updateReminder(mRowId, title, body, reminderDateTime);        →103
}
    }
}

Each line of code is explained as follows:

When you fire up the application in the emulator, you can now create, read, update, and delete tasks! The only things left to build are the reminders' status bar notifications!