1page.title=Storing and Searching for Data
2trainingnavtop=true
3previous.title=Setting Up the Search Interface
4previous.link=setup.html
5next.title=Remaining Backward Compatible
6next.link=backward-compat.html
7
8@jd:body
9
10  <div id="tb-wrapper">
11    <div id="tb">
12      <h2>This lesson teaches you to</h2>
13
14      <ul>
15        <li><a href="{@docRoot}training/search/search.html#create">Create the Virtual
16        Table</a></li>
17
18        <li><a href="{@docRoot}training/search/search.html#populate">Populate the Virtual
19        Table</a></li>
20
21        <li><a href="{@docRoot}training/search/search.html#search">Search for the Query</a></li>
22      </ul>
23    </div>
24  </div>
25
26  <p>There are many ways to store your data, such as in an online database, in a local SQLite
27  database, or even in a text file. It is up to you to decide what is the best solution for your
28  application. This lesson shows you how to create a SQLite virtual table that can provide robust
29  full-text searching. The table is populated with data from a text file that contains a word and
30  definition pair on each line in the file.</p>
31
32  <h2 id="create">Create the Virtual Table</h2>
33
34  <p>A virtual table behaves similarly to a SQLite table, but reads and writes to an object in
35  memory via callbacks, instead of to a database file. To create a virtual table, create a class
36  for the table:</p>
37  <pre>
38public class DatabaseTable {
39    private final DatabaseOpenHelper mDatabaseOpenHelper;
40
41    public DatabaseTable(Context context) {
42        mDatabaseOpenHelper = new DatabaseOpenHelper(context);
43    }
44}
45</pre>
46
47  <p>Create an inner class in <code>DatabaseTable</code> that extends {@link
48  android.database.sqlite.SQLiteOpenHelper}. The {@link android.database.sqlite.SQLiteOpenHelper} class
49  defines abstract methods that you must override so that your database table can be created and
50  upgraded when necessary. For example, here is some code that declares a database table that will
51  contain words for a dictionary app:</p>
52  <pre>
53public class DatabaseTable {
54
55    private static final String TAG = "DictionaryDatabase";
56
57    //The columns we'll include in the dictionary table
58    public static final String COL_WORD = "WORD";
59    public static final String COL_DEFINITION = "DEFINITION";
60
61    private static final String DATABASE_NAME = "DICTIONARY";
62    private static final String FTS_VIRTUAL_TABLE = "FTS";
63    private static final int DATABASE_VERSION = 1;
64
65    private final DatabaseOpenHelper mDatabaseOpenHelper;
66
67    public DatabaseTable(Context context) {
68        mDatabaseOpenHelper = new DatabaseOpenHelper(context);
69    }
70
71    private static class DatabaseOpenHelper extends SQLiteOpenHelper {
72
73        private final Context mHelperContext;
74        private SQLiteDatabase mDatabase;
75
76        private static final String FTS_TABLE_CREATE =
77                    "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE +
78                    " USING fts3 (" +
79                    COL_WORD + ", " +
80                    COL_DEFINITION + ")";
81
82        DatabaseOpenHelper(Context context) {
83            super(context, DATABASE_NAME, null, DATABASE_VERSION);
84            mHelperContext = context;
85        }
86
87        &#64;Override
88        public void onCreate(SQLiteDatabase db) {
89            mDatabase = db;
90            mDatabase.execSQL(FTS_TABLE_CREATE);
91        }
92
93        &#64;Override
94        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
95            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
96                    + newVersion + ", which will destroy all old data");
97            db.execSQL("DROP TABLE IF EXISTS " + FTS_VIRTUAL_TABLE);
98            onCreate(db);
99        }
100    }
101}
102</pre>
103
104  <h2 id="populate">Populate the Virtual Table</h2>
105
106  <p>The table now needs data to store. The following code shows you how to read a text file
107  (located in <code>res/raw/definitions.txt</code>) that contains words and their definitions, how
108  to parse that file, and how to insert each line of that file as a row in the virtual table. This
109  is all done in another thread to prevent the UI from locking. Add the following code to your
110  <code>DatabaseOpenHelper</code> inner class.</p>
111
112  <p class="note"><strong>Tip:</strong> You also might want to set up a callback to notify your UI
113  activity of this thread's completion.</p>
114  <pre>
115private void loadDictionary() {
116        new Thread(new Runnable() {
117            public void run() {
118                try {
119                    loadWords();
120                } catch (IOException e) {
121                    throw new RuntimeException(e);
122                }
123            }
124        }).start();
125    }
126
127private void loadWords() throws IOException {
128    final Resources resources = mHelperContext.getResources();
129    InputStream inputStream = resources.openRawResource(R.raw.definitions);
130    BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));
131
132    try {
133        String line;
134        while ((line = reader.readLine()) != null) {
135            String[] strings = TextUtils.split(line, "-");
136            if (strings.length &lt; 2) continue;
137            long id = addWord(strings[0].trim(), strings[1].trim());
138            if (id &lt; 0) {
139                Log.e(TAG, "unable to add word: " + strings[0].trim());
140            }
141        }
142    } finally {
143        reader.close();
144    }
145}
146
147public long addWord(String word, String definition) {
148    ContentValues initialValues = new ContentValues();
149    initialValues.put(COL_WORD, word);
150    initialValues.put(COL_DEFINITION, definition);
151
152    return mDatabase.insert(FTS_VIRTUAL_TABLE, null, initialValues);
153}
154</pre>
155
156  <p>Call the <code>loadDictionary()</code> method wherever appropriate to populate the table. A
157  good place would be in the {@link android.database.sqlite.SQLiteOpenHelper#onCreate onCreate()}
158  method of the <code>DatabaseOpenHelper</code> class, right after you create the table:</p>
159  <pre>
160&#64;Override
161public void onCreate(SQLiteDatabase db) {
162    mDatabase = db;
163    mDatabase.execSQL(FTS_TABLE_CREATE);
164    loadDictionary();
165}
166</pre>
167
168  <h2 id="search">Search for the Query</h2>
169
170  <p>When you have the virtual table created and populated, use the query supplied by your {@link
171  android.widget.SearchView} to search the data. Add the following methods to the
172  <code>DatabaseTable</code> class to build a SQL statement that searches for the query:</p>
173  <pre>
174public Cursor getWordMatches(String query, String[] columns) {
175    String selection = COL_WORD + " MATCH ?";
176    String[] selectionArgs = new String[] {query+"*"};
177
178    return query(selection, selectionArgs, columns);
179}
180
181private Cursor query(String selection, String[] selectionArgs, String[] columns) {
182    SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
183    builder.setTables(FTS_VIRTUAL_TABLE);
184
185    Cursor cursor = builder.query(mDatabaseOpenHelper.getReadableDatabase(),
186            columns, selection, selectionArgs, null, null, null);
187
188    if (cursor == null) {
189        return null;
190    } else if (!cursor.moveToFirst()) {
191        cursor.close();
192        return null;
193    }
194    return cursor;
195}
196</pre>
197
198  <p>Search for a query by calling <code>getWordMatches()</code>. Any matching results are returned
199  in a {@link android.database.Cursor} that you can iterate through or use to build a {@link android.widget.ListView}.
200  This example calls <code>getWordMatches()</code> in the <code>handleIntent()</code> method of the searchable
201  activity. Remember that the searchable activity receives the query inside of the {@link
202  android.content.Intent#ACTION_SEARCH} intent as an extra, because of the intent filter that you
203  previously created:</p>
204  <pre>
205DatabaseTable db = new DatabaseTable(this);
206
207...
208
209private void handleIntent(Intent intent) {
210
211    if (Intent.ACTION_SEARCH.equals(intent.getAction())) {
212        String query = intent.getStringExtra(SearchManager.QUERY);
213        Cursor c = db.getWordMatches(query, null);
214        //process Cursor and display results
215    }
216}
217</pre>