Searching a SQLite Database

Task 1. Add Search

1.1. Add an Options Menu with Search item

Use the OptionsMenuSample code from your previous practicals if you need an example of how to do this.

• In your project, create an Android Resource directory and call it menu with “menu” as the resource type (res > menu).
• Add a main_menu.xml menu resource file to res > menu.
• Create a menu with one item Search. Reference the code snippet for values.

<menu
   xmlns:android="http://schemas.android.com/apk/res/android"
   xmlns:app = "http://schemas.android.com/apk/res-auto"
   xmlns:tools="http://schemas.android.com/tools"
   tools:context="com.android.example.wordlistsqlsearchable.MainActivity">

   <item
       android:id="@+id/action_search"
       android:title="Search..."
       android:orderInCategory="1"
       app:showAsAction="never" />
</menu>

• In MainAcvitiy, inflate the menu by overriding onCreateOptionsMenu.

@Override
public boolean onCreateOptionsMenu(Menu menu) {
   getMenuInflater().inflate(R.menu.menu_main, menu);
   return true;
}

• verride onOptionsItemSelected method. Switch on action_search, and just return true.

@Override
public boolean onOptionsItemSelected(MenuItem item) {
   switch (item.getItemId()) {
       case R.id.action_search:
           return true;
   }
   return super.onOptionsItemSelected(item);
}

• Run your app. You should see the dots for the options menu. When you click it, you should see one menu item for search that does nothing.

1.2. Create the layout for the search activity

This layout is similar to activity_edit_word, so you can take advantage of existing code and copy it.

1. Create a copy of activity_editword and call it activity_search.xml.
2. In activity_search.xml, change the id’s and strings to be representative of searching.
3. Change the onClick method for the button to showResult.
4. Add a TextView with an id of search_result, at least 300dp height, and 18sp font size.
5. Run your app. You should notice no difference.

1.3. Add an Activity for searching

1. Create a new activity, SearchActivity. If your create it by New > Android > Activity then DON’T generate the layout file because we created it in the previous task.
2. Add a private TextView class variable mTextView.
3. Add a private EditText class variable mEditWordView.
4. Add a private WordListOpenHelper variable mDB.
5. In onCreate, initialize mDB with a new WordListOpenHelper(this).
6. In onCreate, initialize mTextView and mEditWordView to their respective views.

public class SearchActivity extends AppCompatActivity {

   private static final String TAG = EditWordActivity.class.getSimpleName();

   private TextView mTextView;
   private EditText mEditWordView;
   private WordListOpenHelper mDB;

   @Override
   public void onCreate(Bundle savedInstanceState) {
       super.onCreate(savedInstanceState);
       setContentView(R.layout.activity_search);

       mEditWordView = ((EditText) findViewById(R.id.search_word));
       mTextView = ((TextView) findViewById(R.id.search_result));
       mDB = new WordListOpenHelper(this);
   }
}

• Add the activity to the AndroidManifest.

<activity
    android:name="com.android.example.wordlistsqlsearchable.SearchActivity">
</activity

1.4. Trigger SearchActivity from the menu

• To start SearchActivity when the menu item is selected, insert code to start SearchActivity into the switch statement in the onOptionSelected() method in MainActivity.

Intent intent = new Intent(getBaseContext(), SearchActivity.class);
startActivity(intent);

• 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

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.

1.6. Implement the search method in WordListOpenHelper

1. The table is already defined as the WORD_LIST_TABLE constant.
2. In search(), create a variable for the columns. You need only the value from the KEY_WORD column.

• String[] columns = new String[]{KEY_WORD};

Add the % to the searchString parameter.

• searchString = "%" + searchString + "%";

Create the where clause. Omit “WHERE” as it’s implied. Use a question mark for the argument to LIKE. Make sure you have the correct spacing.

• String where = KEY_WORD + " LIKE ?";

Specify the argument to the where clause, which is the searchString.

• String[] whereArgs = new String[]{searchString};

Add a Cursor cursor variable and initialize it to null.

In a try/catch block.

Get a readable database if mReadable is not set yet.

Query the database using the above form of the query. Pass null for the unused parameters.

Handle the exception. You can just log it.

Return the cursor.

Run your app and search for some strings.