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:

In Android Studio’s Project view open the main package – de.dbremes.dbtradealert in my case – folder’s context menu and select New | Package

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.

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:

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.

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():

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:

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:

Open cmd.exe

Navigate to the directory where you installed the Android SDK

Have ADB (Android Debug Bridge) pull the database file (lands in current directory which will be SDK directory):

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.

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:

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:

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.

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