SQLite Database

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.

What you should already KNOW

For this practical you should be familiar with:

• Creating, building, and running apps in Android Studio.
• Displaying data in a RecyclerView.
• Using adapters as intermediaries between data and views.
• Adding onClick event handlers to views and dynamically creating onClick handlers.
• Starting a second activity and returning data from a it.
• Passing data between activities using intent extras.
• Using an EditText view to get data entered by the user.

What you will LEARN

In this practical you will learn to:

• Create and manage a SQLite database with an SQLiteOpenHelper.
• Implement insert, delete, update, and query functionality through your open helper.
• Use an adapter and custom click handler to let users interact with the database from the user interface.

What you will DO

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.

Task 0. Download and run the starter code

1. Download the WordListSqlStarterCode starter code.
2. Open the app in Android Studio
3. 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.

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.)

public WordListOpenHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); }

1.2. Add database constants to WordListOpenHelper

• At the top of the WordListOpenHelper class, define the constants for the tables, rows, and columns as shown in the code below. This should get rid of all the errors.

// It's a good idea to always define a log tag like this.
private static final String TAG = WordListOpenHelper.class.getSimpleName();
// has to be 1 first time or app will crash
private static final int DATABASE_VERSION = 1;
private static final String WORD_LIST_TABLE = "word_entries";
private static final String DATABASE_NAME = "wordlist";
// Column names...
public static final String KEY_ID = "_id";
public static final String KEY_WORD = "word";
// ... and a string array of columns.
private static final String[] COLUMNS = { KEY_ID, KEY_WORD };

• Run your code to make sure it has no more errors.

1.3. Build the SQL query and code to create the database

Continue adding code to WordListOpenHelper.java:

• Below the constants, add the following code to construct the query. Refer to the SQLite Primer if you need help understanding this query.

// Build the SQL query that creates the table.
private static final String WORD_LIST_TABLE_CREATE =
       "CREATE TABLE " + WORD_LIST_TABLE + " (" +
               KEY_ID + " INTEGER PRIMARY KEY, " +
               // id will auto-increment if no value passed
               KEY_WORD + " TEXT );";

• Add instance variables for the references to writable and readable databases. Storing these references saves you to work of getting a database reference every time you need to read or write.

private SQLiteDatabase mWritableDB;
private SQLiteDatabase mReadableDB;

• In the onCreate method, add code to create a database and the table (The helper class does not create another database, if one already exists.)

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(WORD_LIST_TABLE_CREATE);
}

• 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

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

• Open WordListOpenHelper.java.
• In onCreate, after creating the database, add a function call to

fillDatabaseWithData(db);

Next, implement the fillDatabaseWithData()method in WordListOpenHelper.

• Implement the method stub.

private void fillDatabaseWithData(SQLiteDatabase db){}

• Inside the method, declare a string of words as your mock data.

String[] words = {"Android", "Adapter", "ListView", "AsyncTask",
    "Android Studio", "SQLiteDatabase", "SQLOpenHelper",
    "Data model", "ViewHolder","Android Performance",
    "OnClickListener"};

• 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.

for (int i=0; i < words.length; i++) {
     // Put column/value pairs into the container.
     // put() overrides existing values.
     values.put(KEY_WORD, words[i]);
     db.insert(WORD_LIST_TABLE, null, values);
 }

• 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.

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.

Task 3. Implement the query() method in WordListOpenHelper

3.1. Implement the query() method

• Create a query method that takes an integer position argument and returns a WordItem.

public WordItem query(int position) { }

• Construct a query that returns only the nth row of the result. Use LIMIT with position as the row, and 1 as the number of rows.

String query = "SELECT  * FROM " + WORD_LIST_TABLE +
                " ORDER BY " + KEY_WORD + " ASC " +
                "LIMIT " + position + ",1";

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

Cursor cursor = null;

• 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);

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.

entry.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID)));
entry.setWord(cursor.getString(cursor.getColumnIndex(KEY_WORD)));

• In the catch block, log the exception.

Log.d(TAG, "EXCEPTION! " + e);

• In the finally block, close the cursor and return the WordItem entry.

cursor.close();
return entry;

3.2. The onUpgrade method

Boilerplate code for onUpgrade():

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   Log.w(WordListOpenHelper.class.getName(),
           "Upgrading database from version " + oldVersion + " to "
                   + newVersion + ", which will destroy all old data");
   db.execSQL("DROP TABLE IF EXISTS " + WORD_LIST_TABLE);
   onCreate(db);
}

Task 4. Display data in the RecyclerView

4.1. Update WordListAdapter to display WordItems

• Open WordListAdapter.
• In onBindViewHolder replace the code that displays mock data with code to get an item from the database and display it. You will notice an error on mDB.

WordItem current = mDB.query(position);
holder.wordItemView.setText(current.getWord());

• Declare mDB as an instance variable.

WordListOpenHelper mDB;

• To get the value for mDB, change the constructor for WordListAdapter and add a second parameter for the WordListOpenHelper.
• Assign the value of the parameter to mDB. Your constructor should look like this:

public WordListAdapter(Context context, WordListOpenHelper db) {
        mInflater = LayoutInflater.from(context);
        mContext = context;
        mDB = db;
}

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

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.

long newId = 0;

• As before, create a ContentValues value for the row data.

ContentValues values = new ContentValues();
values.put(KEY_WORD, word);

• Put your database operation into a try/catch block.

try {} catch (Exception e) {}

• Get a writable database if one doesn’t already exist.

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

• Insert the row.

newId = mWritableDB.insert(WORD_LIST_TABLE, null, values);

• Log the exception.

Log.d(TAG, "INSERT EXCEPTION! " + e.getMessage());

• Return the id.

return newId;

5.2. Get the word to insert from the user and update the database

• Check to ensure the result is from the correct activity and get the word that the user entered from the extras.

if (requestCode == WORD_EDIT) {
   if (resultCode == RESULT_OK) {
        String word = data.getStringExtra(EditWordActivity.EXTRA_REPLY);

• If the word is not empty, check whether we have been passed an id with the extras. If there is no id, insert a new word. In the next task, you will update the existing word if an id is passed.

if (!TextUtils.isEmpty(word)) {
    int id = data.getIntExtra(WordListAdapter.EXTRA_ID, -99);
    if (id == WORD_ADD) {
        mDB.insert(word);
}

• To update the UI, notify the adapter that the underlying data has changed.

mAdapter.notifyDataSetChanged();

• If the word is empty because the user didn’t enter anything, show a toast letting them know. And don’t forget to close all the parentheses.

} else {
    Toast.makeText(
        getApplicationContext(),
        R.string.empty_not_saved,
        Toast.LENGTH_LONG).show();
        }
     }
}

5.3. Implement getItemCount()

• 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.

public long count(){
    if (mReadableDB == null) {
        mReadableDB = getReadableDatabase();
    }
    return DatabaseUtils.queryNumEntries(mReadableDB, WORD_LIST_TABLE);
}

• Run your app and add some words.

Task 6. Delete words from the database

6.1. Write the delete() method

• 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.

deleted = mWritableDB.delete(WORD_LIST_TABLE,
    KEY_ID + " = ? ", new String[]{String.valueOf(id)});

• Print a log message for exceptions.

Log.d (TAG, "DELETE EXCEPTION! " + e.getMessage());

• Return the number of rows deleted.

return deleted;

6.2. Add a click handler to DELETE button

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

// 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

7.1. Write the update() method

• 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.

ContentValues values = new ContentValues();
values.put(KEY_WORD, word);

Call db.update using the following arguments:

mNumberOfRowsUpdated = db.update(WORD_LIST_TABLE,
       values, // new values to insert
       // selection criteria for row (the _id column)
       KEY_ID + " = ?",
       //selection args; value of id
       new String[]{String.valueOf(id)});

In the catch block, print a log message if any exceptions are encountered.

Log.d (TAG, "UPDATE EXCEPTION: " + e.getMessage());

Return the number of rows updated, which should be -1 (fail), 0 (nothing updated), or 1 (success).

return mNumberOfRowsUpdated;

7.2. Add a click listener to the EDIT button

Solution code :

// Attach a click listener to the EDIT button.
holder.edit_button.setOnClickListener(new MyButtonOnClickListener(
        current.getId(), current.getWord()) {

    @Override
    public void onClick(View v) {
        Intent intent = new Intent(mContext, EditWordActivity.class);

        intent.putExtra(EXTRA_ID, id);
        intent.putExtra(EXTRA_POSITION, h.getAdapterPosition());
        intent.putExtra(EXTRA_WORD, word);

        // Start an empty edit activity.
        ((Activity) mContext).startActivityForResult(
                intent, MainActivity.WORD_EDIT);
    }
});

7.3. Add updating to onActivityResult

• 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!