DbTradeAlert for Android: Add Security and Watchlist Management – Part 1

First post in this series: Introduction to DbTradeAlert

Previous post: DbTradeAlert for Android: Schedule Quote Updates – Part 3

By now DbTradeAlert does what it’s supposed to do – inform the user about triggered signals. What’s missing is the ability to manage its entities:

  • Add, edit and delete securities
  • Add, edit and delete watchlists

For both entities the adding and deleting will be started in one activity and the actual editing will be done in a second activity.

Managing watchlists is easier so let’s start with this.

1. Add Watchlists Management

The first step will be to create the Watchlists Management screen and add code to invoke and dismiss it. In the Watchlists Management screen users see a list of all their watchlists and can Tap Edit or New to go to the – not yet existing – Edit Watchlist screen. They can also Tap Delete to get rid of an unused watchlist.

1.1 Add the Watchlists Management Screen

Add a new empty activity named “WatchlistsManagementActivity” (notice plural). After that add 3 Buttons, a ListView, and a TextView to activity_watchlists_management.xml:

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:paddingBottom="@dimen/activity_vertical_margin" android:paddingLeft="@dimen/activity_horizontal_margin" android:paddingRight="@dimen/activity_horizontal_margin" android:paddingTop="@dimen/activity_vertical_margin" tools:context=".WatchlistsManagementActivity">

    <Button android:id="@+id/newButton" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignParentTop="true" android:layout_centerHorizontal="true" android:onClick="onNewButtonClick" android:text="New" />

    <Button android:id="@+id/okButton" style="android:buttonStyle" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignParentBottom="true" android:layout_alignParentRight="true" android:onClick="onOkButtonClick" android:text="@android:string/ok" />

    <Button android:id="@+id/cancelButton" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignParentBottom="true" android:layout_alignParentLeft="true" android:onClick="onCancelButtonClick" android:text="Cancel" />

    <ListView android:id="@+id/watchlistsListView" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_above="@+id/okButton" android:layout_alignParentLeft="true" android:layout_below="@+id/newButton" />

    <TextView android:id="@+id/emptyTextView" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignParentLeft="false" android:layout_alignParentTop="false" android:layout_centerInParent="true" android:text="(Click 'New' to create a new watchlist)" android:textAppearance="?android:attr/textAppearanceSmall" />

As the OK button shows Android provides translations for general strings like “OK”, “Cancel” or “New” that it will automatically use depending on the device’s language setting. But internationalizing an app properly is a huge effort which doesn’t make sense for DbTradeAlert and so I use hard-coded strings. The rest of the layout should look familiar by now.

Android Studio will automatically add the activity to AndroidManifest.xml.

Then add code for the Cancel and OK buttons and extend onCreate() to set the screen’s title. Using “android:label” in “layout/activity_watchlists_management.xml” would not work because “android:label” in “AndroidManifest.xml” will overrule it. setTitle() does the trick.

public class WatchlistsManagementActivity extends AppCompatActivity {
    // ...

    public void onCancelButtonClick(View view) {
        setResult(RESULT_CANCELED, getIntent());
    } // onCancelButtonClick()

    protected void onCreate(Bundle savedInstanceState) {
        setTitle("Manage Watchlists");
    } // onCreate()

    public void onOkButtonClick(View view) {
        setResult(RESULT_OK, getIntent());
    } // onOkButtonClick()

Now add a new entry to the app’s menu:

And finally extend WatchlistListActivity to start the new activity and retrieve its result:

public class WatchlistListActivity extends AppCompatActivity
        implements WatchlistFragment.OnListFragmentInteractionListener {
    private static final int WATCHLISTS_MANAGEMENT_REQUEST = 2;
    private WatchlistListPagerAdapter watchlistListPagerAdapter;
    // ...

    protected void onActivityResult(int requestCode, int resultCode, Intent data) {
        final String methodName = "onActivityResult";
        switch (requestCode) {
                Log.e(CLASS_NAME, String.format("%s(): unexpected requestCode = ",
                        methodName, requestCode));
        super.onActivityResult(requestCode, resultCode, data);
    } // onActivityResult()

    // ...

    public boolean onOptionsItemSelected(MenuItem item) {
        // Handle action bar item clicks here. The action bar will
        // automatically handle clicks on the Home/Up button, so long
        // as you specify a parent activity in AndroidManifest.xml.
        int id = item.getItemId();
        switch (id) {
            case R.id.action_refresh: {
                Context context = getApplicationContext();
                Intent service = new Intent(context, QuoteRefresherService.class);
                service.putExtra(QuoteRefresherService.INTENT_EXTRA_IS_MANUAL_REFRESH, true);
                return true;
            case R.id.action_watchlists_management: {
                Intent intent = new Intent(this, WatchlistsManagementActivity.class);
                startActivityForResult(intent, WATCHLISTS_MANAGEMENT_REQUEST);
                return true;
            case R.id.action_settings: {
                return true;
                return super.onOptionsItemSelected(item);

    // ...

In onOptionsItemSelected() you start the activity by calling the superclass’ startActivityForResult() method with an intent specifying the activity’s class. That method also takes an ID that will be passed back to the superclass’ onActivityResult() method once the user has clicked OK or Cancel in the activity. Regardless of resultCode onActivityResult() will initiate a refresh of watchlistListPagerAdapter because while the user may have tapped Cancel in the Manage Watchlists screen he may have OK’d changes in the Edit Watchlist screen.

Not informing watchlistListPagerAdapter about this will result in an exception: “IllegalStateException: The application’s PagerAdapter changed the adapter’s contents without calling PagerAdapter#notifyDataSetChanged!”

To make this possible watchlistListPagerAdapter’s declaration needs to be moved to class level.

Manage Watchlists screen with empty list

Manage Watchlists screen with empty list

Now try out the additions:

  1. Start the app
  2. In its overflow menu tap “Manage Watchlists” – the “Manage Watchlists” screen appears
  3. Note the hint about creating a new watchlist when there is none; that’s the only work emptyTextView will ever do
  4. Note that there is no menu but a distinct title for the screen
  5. In the “Manage Watchlists” screen tap either OK or Cancel – tapping “New” will crash the app
  6. Optional: commit the changes

1.2 Fill List of Existing Watchlists

The next step will be to list the available watchlists. For that DbTradeAlert needs a layout to show each watchlist. To add one:

  1. In the res/layout folder’s context menu select New | XML | Layout XML File
  2. In the Configure Component window:
    1. Layout File Name: “layout_watchlists_management_detail”
    2. Root Tag: “RelativeLayout”
    3. Click Finish

Complete the layout by adding 2 Buttons and a textView:

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent">

    <Button android:id="@+id/editButton" style="?android:attr/buttonStyleSmall" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignParentRight="true" android:layout_alignParentTop="true" android:text="Edit" />

    <Button android:id="@+id/deleteButton" style="?android:attr/buttonStyleSmall" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignParentTop="true" android:layout_toLeftOf="@+id/editButton" android:text="Delete" />

    <TextView android:id="@+id/nameTextView" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignBaseline="@+id/deleteButton" android:layout_alignBottom="@+id/deleteButton" android:layout_alignParentLeft="true" android:text="Default watch list" android:textAppearance="?android:attr/textAppearanceMedium" />


Like for the previously created lists you’ll need an adapter to marry the ListView with its cursor and its detail layout. Only this time it will be more involved because of the edit and delete actions provided by the layout.

Create a new class named “WatchlistManagementCursorAdapter” extending CursorAdapter (android.support.v4.widget.CursorAdapter). Let Android Studio create the missing methods and a constructor with “Context context, Cursor c, boolean autoRequery” parameters. The next step is to extend the class like shown below including the private WatchListManagementDetailViewHolder class:

package de.dbremes.dbtradealert;

import android.content.Context;
import android.database.Cursor;
import android.view.View;
import android.view.ViewGroup;
import android.widget.Button;
import android.widget.CursorAdapter;
import android.widget.TextView;

import de.dbremes.dbtradealert.DbAccess.DbHelper;
import de.dbremes.dbtradealert.DbAccess.WatchlistContract;

public class WatchListManagementCursorAdapter extends CursorAdapter {
    private Context context;
    DbHelper dbHelper;

    public WatchListManagementCursorAdapter(Context context, Cursor c, boolean autoRequery) {
        super(context, c, autoRequery);
        this.context = context;
        this.dbHelper = new DbHelper(this.context);
    } // ctor()

    public void bindView(View view, Context context, Cursor cursor) {
        WatchListManagementDetailViewHolder holder
                = (WatchListManagementDetailViewHolder) view.getTag();
        holder.watchListId = cursor.getLong(cursor
    } // bindView()

    public View newView(Context context, Cursor cursor, ViewGroup parent) {
        View view = View.inflate(context, R.layout.layout_watchlists_management_detail, null);
        // could replace WatchListManagementDetailViewHolder in ICS and above with
        // view.setTag(R.id.my_view, myView);
        WatchListManagementDetailViewHolder holder = new WatchListManagementDetailViewHolder();
        //holder.deleteButton = (Button) view.findViewById(R.id.deleteButton);
        holder.editButton = (Button) view.findViewById(R.id.editButton);
        holder.nameTextView = (TextView) view.findViewById(R.id.nameTextView);
        holder.watchListId = cursor.getLong(cursor.getColumnIndex(WatchlistContract.Watchlist.ID));
        return view;
    } // newView()

    private class WatchListManagementDetailViewHolder {
        public Button deleteButton;
        public Button editButton;
        public TextView nameTextView;
        public long watchListId;
    } // class WatchListManagementDetailViewHolder
} // class WatchListManagementCursorAdapter

As always the ViewHolder’s job is to save the time required for finding controls with findViewById(). newView() creates a new WatchListManagementDetailViewHolder instance, stores references to the controls in it and saves it to the View’s tag. After that bindView() is called and uses those references to update the control’s data. Because bindView() is called way more often than newView() the time savings from storing references to the views add up. Even more important: bindView() is called when the screen updates and that’s where split seconds count.

Finally connect WatchlistManagementCursorAdapter to watchListsListView in WatchlistsManagementActivity.onCreate(). Again, the pattern should be familiar by now.

public class WatchlistsManagementActivity extends AppCompatActivity {
    private Cursor cursor;
    private DbHelper dbHelper;
    private WatchListManagementCursorAdapter watchListManagementCursorAdapter
    // ...

    protected void onCreate(Bundle savedInstanceState) {
        dbHelper = new DbHelper(this);
        this.cursor = dbHelper.readAllWatchlists();
                = new WatchListManagementCursorAdapter(this, this.cursor, false);
        ListView watchListsListView = (ListView) findViewById(R.id.watchListsListView);
        TextView emptyTextView = (TextView) findViewById(R.id.emptyTextView);
    } // onCreate()

    // ...
Watchlists Management screen showing watchlists

Watchlists Management screen showing watchlists

Now start the app and test again: its watchlists show up in the Manage Watchlists screen – be aware that tapping the Edit or Delete buttons will crash the app. Again a good time to check in the changes.

A prerequisite for working New and Edit buttons is yet another new activity: one to edit a watchlist’s details. So let’s create that activity.

1.3 Add an Activity to Edit Watchlists

Add a new empty activity named “WatchlistEditActivity” (notice singular). The layout needs 2 Buttons, a ListView, a TextEdit, and 3 TextViews. That ends up being 250 lines of xml so I’ll not post it – just get the code from GitHub or look at the screenshot below.

The first step is to provide code for WatchlistsManagementActivity’s New button:

public class WatchlistsManagementActivity extends AppCompatActivity {
    // ...

    public void onNewButtonClick(View view) {
        Intent intent = new Intent(this, WatchlistEditActivity.class);
    } // onNewButtonClick()

} // class WatchlistsManagementActivity()

The pattern around startActivityForResult() is as used before. But this time an ID is transferred in the intent’s extras so the target activity knowns which watchlist to work on. As onNewButtonClick() starts the creation of a new watchlist it transfers a special ID that DbTradeAlert uses to signal a new item.

And WatchlistEditActivity’s required extensions:

public class WatchlistEditActivity extends AppCompatActivity {
    private final static String CLASS_NAME = "WatchlistEditActivity";
    public final static int CREATE_WATCHLIST_REQUEST_CODE = 0;
    public final static String INTENT_EXTRA_WATCHLIST_ID = "de.dbremes.dbtradealert.watchlistId";
    private DbHelper dbHelper;
    private long watchlistId = DbHelper.NewItemId;

    public void onCancelButtonClick(View view) {
        setResult(RESULT_CANCELED, new Intent());
    } // onCancelButtonClick()

    protected void onCreate(Bundle savedInstanceState) {
        final String methodName = "onCreate";
        this.dbHelper = new DbHelper(this);
        Bundle extras = getIntent().getExtras();
        if (extras != null) {
            this.watchlistId = extras.getLong(INTENT_EXTRA_WATCHLIST_ID);
            EditText nameEditText = (EditText) findViewById(R.id.nameEditText);
            if (this.watchlistId == DbHelper.NewItemId) {
                // Create mode
                setTitle("Create Watchlist");
    } // onCreate()

    private void refreshSecuritiesList(long watchListId) {
        final String methodName = "showStockList";
        SimpleCursorAdapter adapter;
        // Connect securities list to cursor
        String[] fromColumns = { SecurityContract.Security.SYMBOL };
        int[] toViews = { android.R.id.text1 };
        Cursor securitiesCursor = this.dbHelper.getAllSecuritiesAndMarkIfInWatchlist(watchListId);
        int flags =0;
        adapter = new SimpleCursorAdapter(this,
                securitiesCursor, fromColumns, toViews, flags);
        ListView securitiesToIncludeListView
                = (ListView) findViewById(R.id.securitiesToIncludeListView);
        TextView emptyTextView = (TextView) findViewById(R.id.emptyTextView);
        // Mark securities that are included in this watchlist
        int isInWatchListIncludedPosition = securitiesCursor
        for (int i = 0; i < securitiesCursor.getCount(); i++) {
            Log.v(CLASS_NAME, String.format("%s(): securitiesCursor[%d].%s = %d",
                    methodName, i, DbHelper.IS_SECURITY_IN_WATCHLIST_ALIAS,
            if (securitiesCursor.getInt(isInWatchListIncludedPosition) == 1) {
                securitiesToIncludeListView.setItemChecked(i, true);
    } // refreshSecuritiesList()
} // class WatchlistEditActivity

WatchlistEditActivity.onCreate() checks the watchlist ID from the intent’s extras. If it’s the special one for a new watchlist it empties the Name TextView and sets the screen’s title appropriately. Finally it calls refreshSecuritiesList() which updates securitiesListView with a cursor containing all the securities from the database and marks those securities with DbHelper.IS_SECURITY_IN_WATCHLIST_ALIAS == 1 (see below). Of course the cursor cannot be closed as it has been passed over to the SimpleCursorAdapter.

Finally DbHelper’s required extensions:

public class DbHelper extends SQLiteOpenHelper {
    public final static long NEW_ITEM_ID = -1L;
    // Alias for generated columns of getAllSecuritiesAndMarkIfInWatchlist() and readAllWatchlists()
    public final static String IS_SECURITY_IN_WATCHLIST_ALIAS = "isSecurityInWatchlist";
    // ...

     * Gets a list of all securities with those in the specified watchlist marked,
     * ordered by symbol
     * @param idOfWatchlistToMark
     *            If a stock is included in this watchlist,
     *            is_in_watchlist_included will be 1, otherwise 0
     * @return A list (_id, is_included_in_watchlist, symbol) of all securities
     * with those in the specified watchlist marked, ordered by symbol
    public Cursor getAllSecuritiesAndMarkIfInWatchlist(long idOfWatchlistToMark) {
        final String methodName = "getAllSecuritiesAndMarkIfInWatchlist";
        Cursor cursor = null;
        Log.v(CLASS_NAME, String.format("%s(): idOfWatchlistToMark = %d",
                methodName, idOfWatchlistToMark));
        SQLiteDatabase db = this.getReadableDatabase();
        String sql = "SELECT tmp._id AS "
                + Security.ID
                + ", tmp.symbol AS "
                + Security.SYMBOL
                + ", q.name AS "
                + Quote.NAME
                + ", MAX(tmp.isInWatchList) AS "
                + "\nFROM ("
                + "\n\tSELECT " + Security.ID + ", " + Security.SYMBOL + ", 1 AS isInWatchList"
                + "\n\tFROM " + Security.TABLE + " s"
                + "\n\t\tLEFT JOIN " + SecuritiesInWatchlists.TABLE + " siwl ON "
                + SecuritiesInWatchlists.SECURITY_ID + " = " + Security.ID
                + "\n\tWHERE siwl." + SecuritiesInWatchlists.WATCHLIST_ID + " = ?"
                + "\n\tUNION ALL"
                + "\n\tSELECT " + Security.ID + ", " + Security.SYMBOL + ", 0 AS isInWatchList"
                + "\n\tFROM " + Security.TABLE + " s"
                + "\n) AS tmp"
                + "\n\tLEFT OUTER JOIN " + Quote.TABLE + " q ON q." + Quote.SECURITY_ID + " = tmp._id"
                + "\nGROUP BY tmp._id, tmp.symbol, " + Quote.NAME
                + "\nORDER BY tmp.symbol ASC";
        String[] selectionArgs = new String[] { String.valueOf(idOfWatchlistToMark) };
        logSql(methodName, sql, selectionArgs);
        cursor = db.rawQuery(sql, selectionArgs);
        Log.v(CLASS_NAME, String.format(
                CURSOR_COUNT_FORMAT, methodName, cursor.getCount()));
        return cursor;
    } // getAllSecuritiesAndMarkIfInWatchlist()

    // ...

This is the SQL generated by DbHelper.getAllSecuritiesAndMarkIfInWatchlist():

 SELECT tmp._id AS _id, tmp.symbol AS symbol, q.name AS name, MAX(tmp.isInWatchlist) AS isSecurityInWatchlist
	SELECT _id, symbol, 1 AS isInWatchlist
	FROM security s
		LEFT JOIN securities_in_watchlists siwl ON security_id = _id
	WHERE siwl.watchlist_id = -1
	SELECT _id, symbol, 0 AS isInWatchlist
	FROM security s
 ) AS tmp
	LEFT OUTER JOIN quote q ON q.security_id = tmp._id
 GROUP BY tmp._id, tmp.symbol, name
 ORDER BY tmp.symbol ASC

The SQL contains an outer and 2 inner SELECTs. The first inner SELECT lists all the securities that are in the watchlist – none in this case as this is a new watchlist – and sets isInWatchlist to 1 for them. The second inner SELECT lists all the securities no matter if they are included in any watchlist and sets isInWatchlist to 0 for them.

Both lists of securities are then combined into a new temporary list by a UNION. The temporary list will have two items for each security in the watchlist – one with isInWatchlist == 0 and one with isInWatchlist == 1. By selecting only the one with MAX(tmp.isInWatchlist) into the final list it will contain a single item for each security and those in the watchlist will have isInWatchlist == 1.

New Edit Watchlist screen

New Edit Watchlist screen

Run the app again:

  1. Open the Manage Watchlists screen
  2. Tap New:
    1. The new WatchlistEditActivity shows up listing all securities and ready to receive a name
    2. Again no menu but a distinct title
  3. Tap Cancel – tapping OK will crash the app
  4. Optional: commit changes

Note that that I closed the automatically displayed on-screen keyboard so it doesn’t obstruct the activity’s screen.

For now watchlist management is all form but no function. Time to make it operational.

Next post: DbTradeAlert for Android: Add Security and Watchlist Management – Part 2

This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s