A SQLite database is a good storage solution when you have structured data that you need to store persistently and access, search, and change frequently.

When you use a SQLite database, all interactions with the database are through an instance of the SQLiteOpenHelper class which executes your requests and manages your database for you.

In this practical, you will create a SQLite database for a set of data, display retrieved data in a RecyclerView, add functionality to add, delete, and edit the data in the RecyclerView and store it in the database.

Note: A database that persistently stores your data and abstracts your data into a data model is sufficient for small Android apps with minimal complexity. In later chapters, you will learn to architect your app using loaders and content providers to further separate data from the user interface. These classes will help to move work off the UI thread to assist in making the user's experience as smooth and natural as possible. In addition to improving the user experience by removing a potential performance issue, they improve your ability to extend and maintain your app.

Important: In this practical, the SQLiteOpenHelper executes database operations in the main thread. In a production app, where database operations might take quite some time, you would perform these operations on a background thread, for example, using a loader such as AsyncTaskLoader and CursorLoader.

What you will LEARN

Use an adapter and custom click handler to let users interact with the database from the user interface.

What you will DO

You start with an app that is the same as the RecyclerView word list app you created previously, with additional user interface elements already added for you, so that you can focus on the database code.

You will extend and modify the base app to:

Implement a custom class to model your data.

Create a subclass of SQLiteOpenHelper that creates and manages your app's database.

Display data from the database in the RecyclerView.

Implement functionality to add, modify, and delete data in the UI, and store the changes in the database.

App Overview

Starting from a skeleton app, you will add functionality to:

Display words from a SQLite database in a RecyclerView.

Each word can be edited or deleted.

You can add new words and store them in the database.

Minimum SDK Version is API15: Android 4.0.3 IceCreamSandwich and *target* SDK is the current version of Android (version 23 as of the writing of this book).

Task 0. Download and run the starter code

In order to save you some work, in particular writing database-unrelated activities and user interface code, you need to get the starter code for this practical.

Run the app. You should see the UI as shown in the previous screenshot. All the displayed words should be "placeholder". Clicking the buttons does nothing.

Task 1. Extend SQLiteOpenHelper to create and populate a database

Android apps can use standard SQLite databases to store data. This practical does not teach SQLite, but shows how to use it in an Android app. For info on learning about SQLite, see the SQL Primer in the previous chapter.

SQLOpenHelper is a utility class in the Android SDK for interacting with a SQLite database object. It includes onCreate() and onUpdate() methods that you must implement, and insert, delete, update, and query convenience methods for all your database interactions.

The SQLOpenHelper class takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary.

Note: You can have more than one database per app, and more than one open helper managing them. However consider creating multiple tables in the same database instead of using multiple databases for performance and architectural simplicity

1.1 Create a skeleton WordListOpenHelper class

The first step in adding a database to your code is always to create a subclass of SQLiteOpenHelper and implement its methods.

Create a new Java class WordListOpenHelper with the following signature.

public class WordListOpenHelper extends SQLiteOpenHelper {}

In the code editor, hover over the error, then click the light bulb image and select Implement methods. Make sure both methods are highlighted and click OK.

Add the missing constructor for WordListOpenHelper. (You will define the undefined constants next.)

Fix the error by renaming the method argument from SQLiteDatabase to db.

1.4 Create the database in onCreate of the MainActivity

To create the database, create an instance of the WordListOpenHelper class you just wrote.

Open MainActivity.java and add an instance variable for the open helper:

private WordListOpenHelper mDB;

In onCreate, initialize mDB with an instance of WordListOpenHelper. This calls onCreate of the WordListOpenHelper, which creates the database.

mDB = new WordListOpenHelper(this);

Add a breakpoint, run the app with the debugger, and check that mDB is an instance for WordListOpenHelper.

1.5 Add data to the database

The list of words for your app could come from many sources. It could be completely user created, or downloaded from the internet, or generated from a file that's part of your APK. For this practical, you will seed your database with a small amount of hard-coded data.

Note that acquiring, creating, and formatting data is a whole separate topic that is not covered in this course.

Create a container for the data. The insert method that you will call next requires the values to fill a row as an instance of ContentValues. A ContentValues stores the data for one row as key-value pairs, where the key is the name of the column and the value is the value to set.

// Create a container for the data.
ContentValues values = new ContentValues();

Add key/value for the first row to values, then insert that row into the database. Repeat for all the words in your array of words.

db.insert is a SQLiteDatabase convenience method to insert one row into the database. (It's a convenience method, because you do not have to write the SQL query yourself.)

The first argument to db.insert is the table name, WORD_LIST_TABLE.

The second argument is a String nullColumnHack. It's a SQL workaround that allows you to insert empty rows. See the documentation for insert(). Use null for this argument.

The third argument must be a ContentValues container with values to fill the row. This sample only has one column "words" as represented by the constant KEY_WORD set earlier; for tables with multiple columns, add the values for each column to this container.

Before you run and test your app, you should clear the data from your SQLite database and delete the database. Then we can run our app and recreate it so that the database is initialized with the seed data. You can uninstall the app from your device, or you can clear all the data in the app from Settings > Apps > WordList > Storage > Clear Data on your Android emulator or physical device

Run your app. You will not see any changes in the user interface.

Check the logs and make sure there are no errors before you continue. If you encounter errors, read the logcat messages carefully and use resources, such as Stack Overflow, if you get stuck.

You can also check in settings, that the app users storage.

Task 2. Create a data model for a single word

A data model is a class that encapsulates a complex data structure and provides an API for accessing and manipulating the data in that structure. You need a data model to pass data retrieved from the database to the UI.

For this practical, the data model only contains the word and its id. While the unique id will be generated by the database, you need a way of passing the id to the user interface. This will identify the word the user is changing.

2.1. Create a data model for your word data

Create a new class and call it WordItem.

Add the following class variables.

private int mId;
private String mWord;

Add an empty constructor.

Add getters and setters for the id and word.

Run your app. You will not see any visible UI changes, but there should be no errors.

Instantiate a Cursor variable to null to hold the result from the database.

Cursor cursor = null;

The SQLiteDatabase always presents the results as a Cursor in a table format that resembles of a SQL database.

A cursor is a pointer into a row of structured data. You can think of it as an array of rows. The Cursor class provides methods for moving the cursor through that structure, and methods to get the data from the columns of each row.

Instantiate a WordItem entry.

WordItem entry = new WordItem();

Add a try/catch/finally block.

try {} catch (Exception e) {} finally {}

Inside the try block,

get a readable database if it doesn't exist.

if (mReadableDB == null) {
mReadableDB = getReadableDatabase();
}

send a raw query to the database and store the result in a cursor.

cursor = mReadableDB.rawQuery(query, null);

The open helper query method can construct a SQL query string and send it as a rawQuery to the database which returns a cursor. If your data is supplied by your app, and under your full control, you can use raw query().

Move the cursor to the first item.

cursor.moveToFirst();

Set the the id and word of the WordItem entry to the values returned by the cursor.

3.2. The onUpgrade method

Every SQLiteOpenHelper must implement the onUpgrade() method, which determines what happens if the database version number changes. This may happen if you have existing users of your app that use an older version of the database. This method is triggered when a database is first opened. The customary default action is to delete the current database and recreate it.

Important: While it's OK to drop the table in a sample app, In a production app you need to carefully migrate the user's valuable data.

You can use the code below to implement the onUpgrade() method for this sample.

This generates an error in MainActivity, because you added an argument to the WordListAdapter constructor.

Open MainActivity and add the missing mDB argument.

mAdapter = new WordListAdapter (this, mDB);

Run your app. You should see all the words from the database.

Task 5. Add new words to the database

When the user clicks the FAB, an activity opens that lets them enter a word that gets added to the database when they click save.

The starter code provides you with the click listener and the EditWordActivity started by clicking the FAB. You will add the database specific code and tie the pieces together, from the bottom up, like you just did with the query method.

5.1. Write the insert() method

In WordListOpenHelper:

Create the insert() method with the following signature. The user supplies a word, and the method returns the id for the new entry. Generated id's can be big, so insert returns a number of type long.

public long insert(String word){}

Declare a variable for the id. If the insert operation fails, the method returns 0.

5.3. Implement getItemCount()

In order for the new items to be displayed properly, getItemCount in WordListAdapter has to return the actual number of entries in the database instead of the number of words in the starter list of words.

Change getItemCount to the code below, which will trigger an error.

return (int) mDB.count();

Open WordListOpenHelper and implement count() to return the number of entries in the database.

Task 6. Delete words from the database

6.1. Write the delete() method

You use the delete() method on SQLiteDatabase to delete an entry in the database.

Add a method delete to the WordListOpenHelper that:

Create the method stub for delete(), which takes an int argument for the id of the item to delete, and returns the number of rows deleted.

public int delete(int id) {}

Declare a variable to hold the result.

int deleted = 0;

As for insert, add a try block.

try {} catch (Exception e) {}

Get a writable database, if necessary.

if (mWritableDB == null) {
mWritableDB = getWritableDatabase();
}

Call delete on the WORD_LIST_TABLE, selecting by KEY_ID and passing the value of the id as the argument. The "?" is a placeholder that gets filled with the string. This is a more secure way of building queries.

6.2. Add a click handler to DELETE button

You can now add a click handler to the DELETE button that calls the delete() method you just wrote.

Take a look at the MyButtonOnClickListener class in your starter code. The MyButtonOnClickListener class implements a click listener that stores the id, and the word that you need to make changes to the database.

Each view holder, when attached (bound) to the RecyclerView in the onBindViewHolder method of WordListAdapter, needs to also attach a click listener to the DELETE button, passing the id, and word to the MyButtonOnClickListener constructor. These values are then used by the onClick handler to delete the correct item and notify the adapter, which item has been removed.

Note that you cannot use the position argument passed into onBindViewHolder, because it may be stale by the time the click handler is called. You have to keep a reference to the view holder and get the position with getAdapterPosition().

Solution:

// Keep a reference to the view holder for the click listener
final WordViewHolder h = holder; // needs to be final for use in callback
// Attach a click listener to the DELETE button.
holder.delete_button.setOnClickListener(
new MyButtonOnClickListener(current.getId(), null) {
@Override
public void onClick(View v ) {
int deleted = mDB.delete(id);
if (deleted >= 0)
notifyItemRemoved(h.getAdapterPosition());
}
});

Task 7. Update words in the database

To update existing words you have to:

Add an update() method to WordListOpenHelper.

Add a click handler to the EDIT button of your view.

7.1. Write the update() method

You use the update() method on SQLiteDatabase to update an existing entry in the database.

Add a method to the WordListOpenHelper that:

Takes an integer id and a String word for its arguments and returns an integer.

public int update(int id, String word)

Initializes int mNumberOfRowsUpdated to -1.

int mNumberOfRowsUpdated = -1;

Inside a try block, do the following steps:

Get a writable SQLiteDatabase db if there isn't one already.

if (mWritableDB == null) {
mWritableDB = getWritableDatabase();
}

Create a new instance of ContentValues and at the KEY_WORD word to it.

7.2. Add a click listener to the EDIT button

And here is the code for the Edit click listener when we bind the View in the onBindViewHolder method of WordListAdapter. This listener has nothing database specific. It starts the EditWordActivity Activity using an Intent and passes it the current id, position, and word in the Extras.

If you get an error on the EXTRA_POSITION constant, add it with a value of "POSITION",

7.3. Add updating to onActivityResult

As implemented, clicking edit starts an activity that shows the user the current word, and they can edit it. To make the update happen,

Add one line of code to the onActivityResult method in your MainActivity.

else if (id >= 0) {
mDB.update(id, word);
}

Run your app and play with it!

7.4. Design and error considerations

The methods you wrote to add, update and delete entries in the database all assume that their input is valid. This is acceptable for sample code because the purpose of this sample code is to teach you the basic functionality of a SQLite database, and so not every edge case is considered, not every value is tested, and everybody is assumed to be well behaved. If this were a production app, you would have greater security considerations, and content would need to be tested for validity until you know it is not malicious.

In a production app, you must catch specific exceptions and handle them appropriately.

You tested the correct functioning of the app by running it. For a production app with real data, you will need more thorough testing, for example, using unit and interface testing.

For this practical, you created the the database schema/tables from the SQLiteOpenHelper class. This is sufficient for a simple example, like this one. For a more complex app, it is a better practice to separate the schema definitions from the rest of the code in a helper class that cannot be instantiated. You will learn how to do that in the chapter on content providers.

As mentioned above, some database operations can be lengthy and should be done on a background thread. Use AsyncTask for operations that take a long time. Use loaders to load large amounts of data.