DbTradeAlert for Android: Add a Database

Update 2017-06-14: Android finally got a persistence library! It’s named Room and relieves us of the dull tasks listed below. See

Android also got data binding now so you don’t have to fiddle around with a ViewHolder anymore. Unfortunately data binding doesn’t work with Room yet.


First post in this series: Introduction to DbTradeAlert

Previous post: Create the Basic Watchlist UI

Bad start: I forgot to take a screenshot of this post’s visual results.

And somewhat worse: I wouldn’t advise you to use a database for a project like this. So let’s discuss why I used one so you can see how that applies to your project.

The first question should be what data needs to be stored. Most users won’t follow more than a few dozend securities and DbTradeAlert doesn’t accumulate historical data. So there’s just a few kilobytes of data to store and it get’s updated only once per hour. And finally the data model is quite simple: securities, watchlists, quotes, reminders – that’s it. So one could probably get away with some JSON documents. My reason to use a database despite these considerations was to learn what SQLite can and can’t do – SQLite is the only database that Android supports out of the box.

The next question will be where to put the business logic: client side or database? With the rise of persistence frameworks like Hibernate or Entity Framework most projects move their logic to the client side and some even don’t touch the database at all. Expressing yourself in a high level language is easier and thus cheaper than using SQL. But I originally planned to reuse the database for developing DbTradeAlert on Windows Phone and therefore keeping the business logic in the database code made sense.

The last question is special to Android: how to create the database? You normally use some graphical database management tool for this and then install the database with your program. But Android apps are designed to create their database by issuing SQL statements. An app that is supposed to have a database automatically creates it when started without one. Meanwhile people have found ways to get around this by storing a database in the app’s resources and copying the file on install. But this solution has its own drawbacks like using twice the space and having to copy all the data when a new database version gets installed. Needles to say I went the official way.

While the decisions still make sense I now cringe at the thought of ending up with 1.400 lines of Java code intermingled with SQL strings …

So why does this brand new version of DbTradeAlert keep the old database?

  • To show how to use a SQLite database in your app
  • To compare it to a different storage solution in upcoming versions of DbTradeAlert for different platforms

1. Add a Java Package for Database Access

The first step is to add a new package to isolate all the incoming Java classes:

  1. In Android Studio’s Project view open the main package – de.dbremes.dbtradealert in my case – folder’s context menu and select New | Package
  2. In the New Package window enter Name as “DbAccess” and click OK

The main package just got a new sub-package.

2. Define the Tables

Most classes in the new package will hold constants for the respective table’s column names. This makes it a lot easier to find what references a column or to change a column name.

To create for example a class defining the Security table:

  1. In Android Studio’s Project view open DbAccess folder’s context menu and select New | Java Class
  2. In the Create New Class window enter a Name of “SecurityContract” and click OK

Then implement the new class like this:

package de.dbremes.dbtradealert.DbAccess;

import android.provider.BaseColumns;

public final class SecurityContract {
    // Empty constructor prevents accidentally instantiating the contract class
    public SecurityContract() {}

    // Inner class defines column names
    public static abstract class Security {
        public static final String TABLE = "security";
        public static final String BASE_PRICE = "base_price";
        public static final String BASE_PRICE_DATE = "base_price_date";
        public final static String ID = BaseColumns._ID;
        public final static String LOWER_TARGET = "lower_target";
        public final static String MAX_PRICE = "max_price";
        public final static String MAX_PRICE_DATE = "max_price_date";
        public final static String NOTES = "notes";
        public final static String SYMBOL = "symbol";
        public final static String TRAILING_TARGET = "trailing_target";
        public final static String UPPER_TARGET = "upper_target";
    }
}

“BaseColumns._ID” simply results in the string “_ID” – Android classes like CursorAdapter need a primary key with this exact name.

I didn’t like that Android Studio adds a comment like this at the top of each new Java file by the way:
/**
* Created by on .
*/

It’s the VCS’ business to keep tabs on who did what and when. To stop the automatic insertion of these comments:

  1. In Android Studio select File | Settings
  2. In the Settings window select Editor | File and Code Templates
  3. Switch to the Includes tab
  4. Select the File Header item
  5. Remove everything in the right pane and click OK

Classes for quotes and watchlists are needed too (reminders will be added later). And finally a class SecuritiesInWatchlists to manage the n:m relationship between securities and watchlists.

3. Add Code to Create the Tables

Android provides a class named SQLiteOpenHelper to … well, help with SQLite databases. You extend that class and create the database in the onCreate() handler:

public class DbHelper extends SQLiteOpenHelper {
    private static final String CLASS_NAME = "DbHelper";
    private static final String DB_NAME = "dbtradealert.db";
    private static final int DB_VERSION = 1;

    private SQLiteDatabase dummyDb;

    public DbHelper(Context context) {
        super(context, DbHelper.DB_NAME, null, DbHelper.DB_VERSION);
        // onCreate() will be called on 1st use of the database
        // this will use it for testing purposes:
        dummyDb = getReadableDatabase();
    }

    private void createTables(SQLiteDatabase db) {
        try {
            db.beginTransaction();
            createSecurityTable(db);
            createWatchListTable(db);
            createQuoteTable(db);
            createSecuritiesInWatchListsTable(db);
            db.setTransactionSuccessful();
            Log.d(DbHelper.CLASS_NAME, "createTables(): success!");
        } finally {
            db.endTransaction();
        }
    } // createTables()

    @Override
    public void onCreate(SQLiteDatabase db) {
        createTables(db);
    }
// ...

The createTables() method orchestrates the creation of DbTradeAlert’s tables in the correct order – a table has to be created before other tables can reference it. The methods to create tables look like this:

    private void createQuoteTable(SQLiteDatabase db) {
        String columnDefinitions = (Quote.ASK + " REAL, ") +
                Quote.AVERAGE_DAILY_VOLUME + " INTEGER, " +
                Quote.BID + " REAL, " +
                Quote.CURRENCY + " TEXT, " +
                Quote.DAYS_HIGH + " REAL, " +
                Quote.DAYS_LOW + " REAL, " +
                Quote.ID + " INTEGER PRIMARY KEY, " +
                Quote.LAST_TRADE + " REAL, " +
                Quote.LAST_TRADE_DATE_TIME + " TEXT, " +
                Quote.NAME + " TEXT, " +
                Quote.OPEN + " REAL, " +
                Quote.PERCENT_CHANGE + " REAL, " +
                Quote.PREVIOUS_CLOSE + " REAL, " +
                Quote.SECURITY_ID
                + " INTEGER REFERENCES " + Security.TABLE + ", " +
                Quote.STOCK_EXCHANGE_NAME + " TEXT, " +
                Quote.SYMBOL + " TEXT, " +
                Quote.VOLUME + " INTEGER";
        String sql = String.format("CREATE TABLE %s (%s);",
                Quote.TABLE, columnDefinitions);
        db.execSQL(sql);
        Log.d(CLASS_NAME, "createQuoteTable(): table created with SQL = " + sql);
    } // createQuoteTable()

The first step is to “import de.dbremes.dbtradealert.DbAccess.QuoteContract.Quote” so you can refer to the columns without specifying the outer class. The next step is to create a string with all column definitions. In most cases that’s just the column name followed by its type and a comma to separate it from the next column definition. The quote table has an additional column definition specifying that it references the security table. The original code used a StringBuilder for this but Android Studio found that using String was more efficient – that’s slick!

Some notes about SQLite specialities:

  • Specifying a field as INTEGER PRIMARY KEY creates an autoincrement field and allows SQLite to reuse its values. Adding AUTOINCREMENT to that would stop the reuse but comes with a performance penalty.
  • A foreign key doesn’t default to a non-null column for SQLite.
  • ” INTEGER REFERENCES ”
    is a shorthand form of
    “FOREIGN KEY () REFERENCES ()”.
  • Don’t let the column definitions fool you – SQLite doesn’t have a static type system but determines the type dynamically when storing or retrieving a value. Specifying a column type only determines the column’s type affinity which controls the conversions SQLite tries and in what order. I don’t rely on automatic conversions and use SQLite like a statically typed database system.

The next step is to concat “CREATE TABLE ” to the table’s name and to the string just created and to execute the resulting SQL statement. That’s done by passing it to execSQL() which is provided by the SQLiteDatabase instance passed to the onCreate() handler. Additionally the SQL statement is logged to make finding errors easier.

4. Connect the Code to Create the Tables

For now DbHelper and with it the database will be created in WatchlistListActivity.onCreate():

import de.dbremes.dbtradealert.DbAccess.DbHelper;

public class WatchlistListActivity extends AppCompatActivity
        implements WatchlistFragment.OnListFragmentInteractionListener {
    private DbHelper dbHelper = null;

    // ...

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_watchlist_list);

        this.dbHelper =  new DbHelper(this);
        // ...
    }

This won’t work on its own as DbHelper.onCreate() is only called when an instance of the database is requested. To request one I temporarily added a private field dummyDb of type SQLiteDatabase to DbHelper and in DbHelper’s constructor call dummyDb.getReadableDatabase() after passing the parameters to super.

When you start the app now it will look as before but there will be log messages like this in Android Studio’s logcat window:

... DbHelper: createQuoteTable: table created with SQL = CREATE TABLE quote (ask REAL, average_daily_volume INTEGER, bid REAL, currency TEXT, days_high REAL, days_low REAL, _id INTEGER PRIMARY KEY, last_trade REAL, last_trade_date_time TEXT, name TEXT, open REAL, percent_change REAL, previous_close REAL, security_id INTEGER REFERENCES security, stock_exchange_name TEXT, symbol TEXT, volume INTEGER);

Unfortunately you cannot get hold of the database on a physical device unless it is rooted or you extract it from a backup. So the log messages may be the only proof of your database’s existence on a physical device.

And while it seems the IntelliJ IDEA can connect to Android SQLite databases that option (View | Tool Windows | Database | …) is missing from Android Studio.

On a virtual device you can use Android Studio’s file explorer (Tools | Android | Android Device Monitor | File Explorer tab). The database will be at data/data/de.dbremes.dbtradealert/databases/dbtradealert.db. Note that you can either connect Android Studio’s debugger or Android Device Monitor to the device but not both.

You can also copy the database from a virtual device using the command line:

  1. Open cmd.exe
  2. Navigate to the directory where you installed the Android SDK
  3. Have ADB (Android Debug Bridge) pull the database file (lands in current directory which will be SDK directory):

cd C:\Users\Admin\AppData\Local\Android\sdk\platform-tools
adb pull /data/data/de.dbremes.dbtradealert/databases/dbtradealert.db

When you run the app again it will find an existing database and not create a new one. Just delete the app to recreate the database. SQLite’s update mechanism is only useful once the app is installed on user’s phones but not during development.

5. Create Sample Data

Let’s fill those shiny new tables with data. Creating sample data is straight forward – the order of tables is the same as when creating them. This code also shows how to create folding code regions in Android Studio.

private void createSampleData(SQLiteDatabase db) {
	final String methodName = "createSampleData";
	try {
		db.beginTransaction();
		// region Create sample security data
		// region - BAYN.DE
		ContentValues contentValues = new ContentValues();
		contentValues.put(Security.BASE_DATE, (Byte) null);
		contentValues.put(Security.BASE_VALUE, Float.NaN);
		contentValues.put(Security.GENERATE_STOP_LOSS_SIGNAL, 0);
		contentValues.put(Security.LOWER_TARGET, Float.NaN);
		contentValues.put(Security.MAX_HIGH, 138.34);
		contentValues.put(Security.MAX_HIGH_DATE, "2015-07-16T12:00");
		contentValues.put(Security.NOTES, "Sample stock");
		contentValues.put(Security.SYMBOL, "BAYN.DE");
		contentValues.put(Security.UPPER_TARGET, 125);
		long baydeSecurityID = db.insert(Security.TABLE, null, contentValues);
		Log.v(DbHelper.CLASS_NAME, "Sample stock 'BAYN.DE' created");
		// endregion - BAYN.DE
		// region - NESN.VX
		contentValues.clear();
		contentValues.put(Security.BASE_DATE, (Byte) null);
		contentValues.put(Security.BASE_VALUE, Float.NaN);
		contentValues.put(Security.GENERATE_STOP_LOSS_SIGNAL, 0);
		contentValues.put(Security.LOWER_TARGET, Float.NaN);
		contentValues.put(Security.MAX_HIGH, 76.95);
		contentValues.put(Security.MAX_HIGH_DATE, "2015-12-02T12:00");
		contentValues.put(Security.NOTES, "Sample stock");
		contentValues.put(Security.SYMBOL, "NESN.VX");
		contentValues.put(Security.UPPER_TARGET, Float.NaN);
		long nesnSecurityID = db.insert(Security.TABLE, null, contentValues);
		Log.v(DbHelper.CLASS_NAME, "Sample stock 'NESN.VX' created");
		// endregion - NESN.VX
		// ...

Missing Date values are represented by a null value. For non-nullable Real / Numeric columns SQLite’s support of IEEE Standard 754 floating-point representation which includes “NaN” (Not a Number) is used.

Call createSampleData() from onCreate() after creating the tables. Remember to delete the app before installing the new version or onCreate() will not get called. Of course the app shows no visual changes yet. Either check the log for the respective messages or install the app on a virtual / rooted device and download the database for inspection. Here are the Quote Table’s contents in DB Browser for SQLite:

DbTradeAlert - Android - Add Database01

Quote table’s contents in DB Browser for SQLite

6. Show Data in the UI

OK, now there is data in the database. To have the user actually see the data DbTradeAltert still needs:

  • some methods that provide data access in DbHelper
  • a connection of those methods to the UI

6.1 Add Data Access Methods to DbHelper

DbHelper will need two methods to provide access to the sample data:

  • readAllWatchlists()
  • readAllQuotesForWatchlist()
public Cursor readAllQuotesForWatchlist(long watchlistId) {
	final String methodName = "readAllQuotesForWatchlist";
	Cursor cursor = null;
	SQLiteDatabase db = this.getReadableDatabase();
	String sql = "SELECT q.*, s." + Security.BASE_VALUE + ", s."
			+ Security.GENERATE_STOP_LOSS_SIGNAL + ", s."
			+ Security.LOWER_TARGET + ", s." + Security.MAX_HIGH
			+ ", s." + Security.UPPER_TARGET + "\nFROM "
			+ SecuritiesInWatchlists.TABLE + " siwl" + "\n\tINNER JOIN "
			+ Quote.TABLE + " q ON q." + Quote.SECURITY_ID
			+ " = " + "siwl." + SecuritiesInWatchlists.SECURITY_ID + "\n\tINNER JOIN "
			+ Security.TABLE + " s ON s." + Security.ID + " = " + "q."
			+ Quote.SECURITY_ID + "\nWHERE siwl."
			+ SecuritiesInWatchlists.WATCHLIST_ID + " = ?" + "\nORDER BY q."
			+ Quote.NAME + " ASC";
	String[] selectionArgs = new String[] { String.valueOf(watchlistId) };
	logSql(methodName, sql, selectionArgs);
	cursor = db.rawQuery(sql, selectionArgs);
	Log.v(DbHelper.CLASS_NAME,
			String.format(DbHelper.CURSOR_COUNT_FORMAT, methodName,
					cursor.getCount()));
	return cursor;
} // readAllQuotesForWatchlist()

public Cursor readAllWatchlists() {
	Cursor cursor = null;
	SQLiteDatabase db = this.getReadableDatabase();
	String[] columns = new String[] { Watchlist.ID, Watchlist.NAME };
	String groupBy = null;
	String having = null;
	String orderBy = Watchlist.NAME;
	String selection = null;
	String[] selectionArgs = null;
	String table = Watchlist.TABLE;
	cursor = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
	return cursor;
} // readAllWatchlists()

As readAllWatchlists() queries a single table it can use query() just specifying the table and which columns to return. But readAllQuotesForWatchlist() queries multiple tables and therefore needs to call rawQuery(). To prevent SQL injection attacks the parameters aren’t just concatenated to the query in both cases.

6.2 Connect Data Access Methods to the UI

The WatchlistListPagerAdapter class is responsible for creating WatchlistFragments and provides the number of watchlists as well as the title for each.

The first step to use data from the database is to acquire and store a DbHelper instance in the constructor. After that getCount() can simply return the number of records retrieved by dbHelper.readAllWatchlists(). getPageTitle() calls the same method, moves to the respective record, and returns the Name field’s value. getItem() needs to query DbHelper for the new watchlist’s ID and pass that to WatchlistFragment.newInstance(). This will not compile yet because WatchlistFragment.newInstance() expects an integer parameter.

package de.dbremes.dbtradealert;

import android.database.Cursor;
import android.support.v4.app.Fragment;
import android.support.v4.app.FragmentManager;
import android.support.v4.app.FragmentPagerAdapter;
import android.util.Log;

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

/**
 * A {@link FragmentPagerAdapter} that returns a fragment corresponding to
 * one of the watchlists.
 */
public class WatchlistListPagerAdapter extends FragmentPagerAdapter {
    // Logging tag can be at most 23 characters
    private static final String CLASS_NAME = "WatchlistListPagerAd.";

    private final DbHelper dbHelper;

    public WatchlistListPagerAdapter(FragmentManager fm, DbHelper dbHelper) {
        super(fm);
        this.dbHelper = dbHelper;
    }

    @Override
    public Fragment getItem(int position) {
        // getItem is called to instantiate the fragment for the given page.
        // Return a WatchlistFragment.
        return WatchlistFragment.newInstance(position + 1);
    }

    @Override
    public int getCount() {
        int result = -1;
        Cursor cursor = dbHelper.readAllWatchlists();
        result = cursor.getCount();
        cursor.close();
        return result;
    } // getCount()

    @Override
    public CharSequence getPageTitle(int position) {
        String result = "";
        final String methodName = "getPageTitle";
        Log.v(CLASS_NAME,
                String.format("%s: position = %d", methodName, position));
        Cursor cursor = dbHelper.readAllWatchlists();
        if (cursor.getCount() >= position) {
            cursor.moveToPosition(position);
            result = cursor.getString(cursor
                    .getColumnIndex(WatchlistContract.Watchlist.NAME));
        } else {
            Log.w(CLASS_NAME, String.format(
                    "%s: cannot move to position = %d; cursor.getCount() = %d",
                    methodName, position, cursor.getCount()));
        }
        cursor.close();
        return result;
    } // getPageTitle()
}

The WatchlistFragment class stores the displayed watchlist’s ID which is of type Long. This leads to a new signature for newInstance() which fixes the compile error in WatchlistListPagerAdapter.getItem().

WatchlistRecyclerViewAdapter’s change to using data from an actual database are mostly just replacements and the general structure remains unchanged:

  • Its constructor receives a cursor instad of a list of strings
  • onBindViewHolder() moves that cursor to the respective row and then provides the ViewHolder’s fields with their values
  • getItemCount() has changes similar to WatchlistListPagerAdapter.getCount()
  • the internal ViewHolder class has no changes, just a few renames unrelated to data access
// ...
public class WatchlistRecyclerViewAdapter
  extends RecyclerView.Adapter<WatchlistRecyclerViewAdapter.ViewHolder> {
  private static final String CLASS_NAME = "WatchlistRecyclerViewAdapter";
  private final Cursor cursor;
  private final OnListFragmentInteractionListener listener;

  public WatchlistRecyclerViewAdapter(Cursor cursor, OnListFragmentInteractionListener listener) {
    this.cursor = cursor;
    this.listener = listener;
  }

  @Override
  public ViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
    View view = LayoutInflater.from(parent.getContext())
      .inflate(R.layout.fragment_quote, parent, false);
    return new ViewHolder(view);
  }

  @Override
  public void onBindViewHolder(final ViewHolder viewHolder, int quotePosition) {
    if (this.cursor.moveToPosition(quotePosition) == false) {
      throw new IllegalStateException(
        String.format(
        "%s.%s: cannot move to position = %d; cursor.getCount() = %d",
        WatchlistRecyclerViewAdapter.CLASS_NAME, "onBindViewHolder",
        quotePosition, cursor.getCount()));
    }
    viewHolder.Symbol = cursor.getString(cursor.getColumnIndex(
    SecurityContract.Security.SYMBOL));
    viewHolder.SymbolTextView.setText(
      cursor.getString(cursor.getColumnIndex(
      SecurityContract.Security.SYMBOL)));
    viewHolder.LastTradeTextView.setText(
      cursor.getString(cursor.getColumnIndex(
      QuoteContract.Quote.LAST_TRADE)));
    viewHolder.View.setOnClickListener(new View.OnClickListener() {
      @Override
      public void onClick(View v) {
        if (listener != null) {
          // Notify the active callbacks interface (the activity, if the
          // fragment is attached to one) that an item has been selected.
          listener.onListFragmentInteraction(viewHolder.Symbol);
        }
      }
    });
  }

  @Override
  public int getItemCount() {
    int result = 0;
    if (this.cursor != null) {
      result = cursor.getCount();
    }
    return result;
  }

  // ...

Now dummyDb.getReadableDatabase() in DbHelper’s constructor isn’t needed anymore. Delete the call and the dummyDb field.

When you run the app now it shows proper values for symbol and last trade.
Optional: commit the changes

Next post: Finish the Watchlist UI

Additional Resources

Advertisements
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