1 page.title=Storing and Searching for Data 2 trainingnavtop=true 3 previous.title=Setting Up the Search Interface 4 previous.link=setup.html 5 next.title=Remaining Backward Compatible 6 next.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> 38 public 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> 53 public 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 @Override 88 public void onCreate(SQLiteDatabase db) { 89 mDatabase = db; 90 mDatabase.execSQL(FTS_TABLE_CREATE); 91 } 92 93 @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> 115 private 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 127 private 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 < 2) continue; 137 long id = addWord(strings[0].trim(), strings[1].trim()); 138 if (id < 0) { 139 Log.e(TAG, "unable to add word: " + strings[0].trim()); 140 } 141 } 142 } finally { 143 reader.close(); 144 } 145 } 146 147 public 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 @Override 161 public 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> 174 public 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 181 private 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> 205 DatabaseTable db = new DatabaseTable(this); 206 207 ... 208 209 private 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>
