What you will LEARN

What you will DO

In this practical you will add an item to the options menu for searching the database, and an activity that allows users to enter a search string and displays the result of the search in a text view.

Why: Users should always be able to search the data on their own terms.

Note: The focus of this practical is not optimizing the UX of the search request, but showing you how to query the database.

App Overview

You will make a copy of the finished WordListSQLInteractive app (or WordListSqlStarterCode if you didn't rename it; from a previous practical), call it WordListSQLInteractiveWithSearch, and add an activity that lets users search for partial and full words in the database. For example, entering "Android" will return all entries that contain the substring "Android".

Task 0. Download and run the base code

In order to save you some work, this practical will build on an app you have already built. In a production environment, building on existing application code is a common developer task to add features or fix problems.

Build and run your app to make sure SearchActivity is launched when the "Search" menu item is selected from the OptionsMenu.

Enter a search string and press "Search". Your app crashes.

Find out why the app has crashed, then move to the next task.

1.5. Implement the onClick handler for the Search button in the SearchActivity

Your app crashed, because the onClick handler set for the Search button in the XML code doesn't exist yet. So you will build showResult next.

When the Search button is pressed, several things need to happen:

The event handler calls public void showResult(View view) in SearchActivity.

Your app has to get the current value from the mEditWordView, which is your search string.

You print the "Result for" and the word in mTextView.

You call the (not yet written) search function on mDB (mDB.search(word) and get back a SQlite database cursor. You will implement the search function in the next task.

You process the cursor and add the result to mTextView.

In SearchActivity, create the showResult function. It is public, takes a View argument, and returns nothing.

Create a Stringvariable called word and initialize it with the contents of the input edit text view, mEditWordView.

Show the search term in the search results TextView; something like

"Search term: " + word

Search the database and get the cursor.

Cursor cursor = mDB.search(word);

To process the cursor, you need to do do the following:

Make sure the cursor is not null.

Move the cursor to the first entry.

Iterate over the cursor processing the current entry, then advancing the cursor.

Extract the word.

Display the word in the text view.

Close the cursor.

If no results are found, the user sees a blank screen with no results. You would want this to be handled in a production app.

Check the annotated code for additional details.

public void showResult(View view){
String word = mEditWordView.getText().toString();
mTextView.setText("Result for " + word + ":\n\n");
// Search for the word in the database.
Cursor cursor = mDB.search(word);
// Only process a non-null cursor with rows.
if (cursor != null & cursor.getCount() > 0) {
// You must move the cursor to the first item.
cursor.moveToFirst();
int index;
String result;
// Iterate over the cursor, while there are entries.
do {
// Don't guess at the column index.
// Get the index for the named column.
index = cursor.getColumnIndex(WordListOpenHelper.KEY_WORD);
// Get the value from the column for the current cursor.
result = cursor.getString(index);
// Add result to what's already in the text view.
mTextView.append(result + "\n");
} while (cursor.moveToNext()); // Returns true or false
cursor.close();
} // You should add some handling of null case. Right now, nothing happens.
}

Your app will not run without at least a stub for search() implemented. Android Studio will create the stub for you. In the light bulb, choose create method.

Open WordListOpenHelper.

Implement a stub for search, with a String parameter, that returns a null cursor.

Run your app and fix any errors you may have. Note that most of the code in showResult() is not exercised yet.

1.6. Implement the search method in WordListOpenHelper

The final step is to implement the actual searching of the database.

Inside the search() method, you need to build a query with the search string and send the query to the database.

A more secure way to do this is by using parameters for each part of the query.

WHY: In the previous practical, for the query in WordListOpenHelper, you could build the query string directly and submit it as a rawQuery(), because you had full control over the contents of the query. As soon as you are handling user input, you must assume that it could be malicious.

Important: For security reasons, you should always validate user input before you build your query!

You will learn more about security in the Security chapter and Security Tips.

The SQL query for searching for all entries in the wordlist matching a substring has this form:

SELECT * FROM WORD_LIST_TABLE WHERE KEY_WORD LIKE %searchString%;

The parametrized form of the query method you will call looks like this: