Storing and Searching for Data

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.

Create the Virtual Table

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:

       public class DatabaseTable {
    private final DatabaseOpenHelper mDatabaseOpenHelper;

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

Create an inner class in DatabaseTable that extends SQLiteOpenHelper . The 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:

       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;
        }

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

        @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);
        }
    }
}
      

Populate the Virtual Table

The table now needs data to store. The following code shows you how to read a text file (located in res/raw/definitions.txt ) 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 DatabaseOpenHelper inner class.

Tip: You also might want to set up a callback to notify your UI activity of this thread's completion.

       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 < 2) continue;
            long id = addWord(strings[0].trim(), strings[1].trim());
            if (id < 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);
}
      

Call the loadDictionary() method wherever appropriate to populate the table. A good place would be in the onCreate() method of the DatabaseOpenHelper class, right after you create the table:

       @Override
public void onCreate(SQLiteDatabase db) {
    mDatabase = db;
    mDatabase.execSQL(FTS_TABLE_CREATE);
    loadDictionary();
}
      

Search for the Query

When you have the virtual table created and populated, use the query supplied by your SearchView to search the data. Add the following methods to the DatabaseTable class to build a SQL statement that searches for the query:

       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;
}
      

Search for a query by calling getWordMatches() . Any matching results are returned in a Cursor that you can iterate through or use to build a ListView . This example calls getWordMatches() in the handleIntent() method of the searchable activity. Remember that the searchable activity receives the query inside of the ACTION_SEARCH intent as an extra, because of the intent filter that you previously created:

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