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