page.title=Storing and Searching for Data
trainingnavtop=true
previous.title=Setting Up the Search Interface
previous.link=setup.html
next.title=Remaining Backward Compatible
next.link=backward-compat.html

@jd:body

  <div id="tb-wrapper">
    <div id="tb">
      <h2>This lesson teaches you to</h2>

      <ul>
        <li><a href="{@docRoot}training/search/search.html#create">Create the Virtual
        Table</a></li>

        <li><a href="{@docRoot}training/search/search.html#populate">Populate the Virtual
        Table</a></li>

        <li><a href="{@docRoot}training/search/search.html#search">Search for the Query</a></li>
      </ul>
    </div>
  </div>

  <p>There are many ways to store your data, such as in an online database, in a local SQLite
  database, or even in a text file. It is up to you to decide what is the best solution for your
  application. This lesson shows you how to create a SQLite virtual table that can provide robust
  full-text searching. The table is populated with data from a text file that contains a word and
  definition pair on each line in the file.</p>

  <h2 id="create">Create the Virtual Table</h2>

  <p>A virtual table behaves similarly to a SQLite table, but reads and writes to an object in
  memory via callbacks, instead of to a database file. To create a virtual table, create a class
  for the table:</p>
  <pre>
public class DatabaseTable {
    private final DatabaseOpenHelper mDatabaseOpenHelper;

    public DatabaseTable(Context context) {
        mDatabaseOpenHelper = new DatabaseOpenHelper(context);
    }
}
</pre>

  <p>Create an inner class in <code>DatabaseTable</code> that extends {@link
  android.database.sqlite.SQLiteOpenHelper}. The {@link android.database.sqlite.SQLiteOpenHelper} class
  defines abstract methods that you must override so that your database table can be created and
  upgraded when necessary. For example, here is some code that declares a database table that will
  contain words for a dictionary app:</p>
  <pre>
public class DatabaseTable {

    private static final String TAG = "DictionaryDatabase";

    //The columns we'll include in the dictionary table
    public static final String COL_WORD = "WORD";
    public static final String COL_DEFINITION = "DEFINITION";

    private static final String DATABASE_NAME = "DICTIONARY";
    private static final String FTS_VIRTUAL_TABLE = "FTS";
    private static final int DATABASE_VERSION = 1;

    private final DatabaseOpenHelper mDatabaseOpenHelper;

    public DatabaseTable(Context context) {
        mDatabaseOpenHelper = new DatabaseOpenHelper(context);
    }

    private static class DatabaseOpenHelper extends SQLiteOpenHelper {

        private final Context mHelperContext;
        private SQLiteDatabase mDatabase;

        private static final String FTS_TABLE_CREATE =
                    "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE +
                    " USING fts3 (" +
                    COL_WORD + ", " +
                    COL_DEFINITION + ")";

        DatabaseOpenHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            mHelperContext = context;
        }

        &#64;Override
        public void onCreate(SQLiteDatabase db) {
            mDatabase = db;
            mDatabase.execSQL(FTS_TABLE_CREATE);
        }

        &#64;Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + FTS_VIRTUAL_TABLE);
            onCreate(db);
        }
    }
}
</pre>

  <h2 id="populate">Populate the Virtual Table</h2>

  <p>The table now needs data to store. The following code shows you how to read a text file
  (located in <code>res/raw/definitions.txt</code>) that contains words and their definitions, how
  to parse that file, and how to insert each line of that file as a row in the virtual table. This
  is all done in another thread to prevent the UI from locking. Add the following code to your
  <code>DatabaseOpenHelper</code> inner class.</p>

  <p class="note"><strong>Tip:</strong> You also might want to set up a callback to notify your UI
  activity of this thread's completion.</p>
  <pre>
private void loadDictionary() {
        new Thread(new Runnable() {
            public void run() {
                try {
                    loadWords();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
        }).start();
    }

private void loadWords() throws IOException {
    final Resources resources = mHelperContext.getResources();
    InputStream inputStream = resources.openRawResource(R.raw.definitions);
    BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));

    try {
        String line;
        while ((line = reader.readLine()) != null) {
            String[] strings = TextUtils.split(line, "-");
            if (strings.length &lt; 2) continue;
            long id = addWord(strings[0].trim(), strings[1].trim());
            if (id &lt; 0) {
                Log.e(TAG, "unable to add word: " + strings[0].trim());
            }
        }
    } finally {
        reader.close();
    }
}

public long addWord(String word, String definition) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(COL_WORD, word);
    initialValues.put(COL_DEFINITION, definition);

    return mDatabase.insert(FTS_VIRTUAL_TABLE, null, initialValues);
}
</pre>

  <p>Call the <code>loadDictionary()</code> method wherever appropriate to populate the table. A
  good place would be in the {@link android.database.sqlite.SQLiteOpenHelper#onCreate onCreate()}
  method of the <code>DatabaseOpenHelper</code> class, right after you create the table:</p>
  <pre>
&#64;Override
public void onCreate(SQLiteDatabase db) {
    mDatabase = db;
    mDatabase.execSQL(FTS_TABLE_CREATE);
    loadDictionary();
}
</pre>

  <h2 id="search">Search for the Query</h2>

  <p>When you have the virtual table created and populated, use the query supplied by your {@link
  android.widget.SearchView} to search the data. Add the following methods to the
  <code>DatabaseTable</code> class to build a SQL statement that searches for the query:</p>
  <pre>
public Cursor getWordMatches(String query, String[] columns) {
    String selection = COL_WORD + " MATCH ?";
    String[] selectionArgs = new String[] {query+"*"};

    return query(selection, selectionArgs, columns);
}

private Cursor query(String selection, String[] selectionArgs, String[] columns) {
    SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
    builder.setTables(FTS_VIRTUAL_TABLE);

    Cursor cursor = builder.query(mDatabaseOpenHelper.getReadableDatabase(),
            columns, selection, selectionArgs, null, null, null);

    if (cursor == null) {
        return null;
    } else if (!cursor.moveToFirst()) {
        cursor.close();
        return null;
    }
    return cursor;
}
</pre>

  <p>Search for a query by calling <code>getWordMatches()</code>. Any matching results are returned
  in a {@link android.database.Cursor} that you can iterate through or use to build a {@link android.widget.ListView}.
  This example calls <code>getWordMatches()</code> in the <code>handleIntent()</code> method of the searchable
  activity. Remember that the searchable activity receives the query inside of the {@link
  android.content.Intent#ACTION_SEARCH} intent as an extra, because of the intent filter that you
  previously created:</p>
  <pre>
DatabaseTable db = new DatabaseTable(this);

...

private void handleIntent(Intent intent) {

    if (Intent.ACTION_SEARCH.equals(intent.getAction())) {
        String query = intent.getStringExtra(SearchManager.QUERY);
        Cursor c = db.getWordMatches(query, null);
        //process Cursor and display results
    }
}
</pre>