您可以通过多种方式存储数据,例如存储在在线数据库中、在本地 SQLite 中。 甚至存储在文本文件中由您决定哪种解决方案最适合您的 应用。本课将向您介绍如何创建能够提供强大的 SQLite 虚拟表 全文搜索。该表使用文本文件(其中包含单词和 定义对。
创建虚拟表
虚拟表的行为与 SQLite 表类似, 而不是通过回调发送到数据库文件如需创建虚拟表,请创建一个类 表:
Kotlin
class DatabaseTable(context: Context) { private val databaseOpenHelper = DatabaseOpenHelper(context) }
Java
public class DatabaseTable { private final DatabaseOpenHelper databaseOpenHelper; public DatabaseTable(Context context) { databaseOpenHelper = new DatabaseOpenHelper(context); } }
在 DatabaseTable
中创建一个扩展 SQLiteOpenHelper
的内部类。SQLiteOpenHelper
类
定义了抽象方法,您必须替换这些方法,以便创建和
必要时进行升级。例如,以下代码声明了一个数据库表,
包含字典应用的字词:
Kotlin
private const val TAG = "DictionaryDatabase" // The columns we'll include in the dictionary table const val COL_WORD = "WORD" const val COL_DEFINITION = "DEFINITION" private const val DATABASE_NAME = "DICTIONARY" private const val FTS_VIRTUAL_TABLE = "FTS" private const val DATABASE_VERSION = 1 private const val FTS_TABLE_CREATE = "CREATE VIRTUAL TABLE $FTS_VIRTUAL_TABLE USING fts3 ($COL_WORD, $COL_DEFINITION)" class DatabaseTable(context: Context) { private val databaseOpenHelper: DatabaseOpenHelper init { databaseOpenHelper = DatabaseOpenHelper(context) } private class DatabaseOpenHelper internal constructor(private val helperContext: Context) : SQLiteOpenHelper(helperContext, DATABASE_NAME, null, DATABASE_VERSION) { private lateinit var mDatabase: SQLiteDatabase override fun onCreate(db: SQLiteDatabase) { mDatabase = db mDatabase.execSQL(FTS_TABLE_CREATE) } override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) { 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) } } }
Java
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 databaseOpenHelper; public DatabaseTable(Context context) { databaseOpenHelper = new DatabaseOpenHelper(context); } private static class DatabaseOpenHelper extends SQLiteOpenHelper { private final Context helperContext; 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); helperContext = 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); } } }
填充虚拟表
现在该表需要存储的数据。以下代码展示了如何读取文本文件
(位于 res/raw/definitions.txt
中)包含字词及其定义,
解析该文件,以及如何将该文件的每一行作为一行插入虚拟表中。这个
都是在另一个线程中完成的,以防止界面锁定。将以下代码添加到您的
DatabaseOpenHelper
内部类。
提示:您可能还需要设置回调来通知界面 此线程完成时的任何活动
Kotlin
private fun loadDictionary() { Thread(Runnable { try { loadWords() } catch (e: IOException) { throw RuntimeException(e) } }).start() } @Throws(IOException::class) private fun loadWords() { val inputStream = helperContext.resources.openRawResource(R.raw.definitions) BufferedReader(InputStreamReader(inputStream)).use { reader -> var line: String? = reader.readLine() while (line != null) { val strings: List<String> = line.split("-").map { it.trim() } if (strings.size < 2) continue val id = addWord(strings[0], strings[1]) if (id < 0) { Log.e(TAG, "unable to add word: ${strings[0]}") } line = reader.readLine() } } } fun addWord(word: String, definition: String): Long { val initialValues = ContentValues().apply { put(COL_WORD, word) put(COL_DEFINITION, definition) } return database.insert(FTS_VIRTUAL_TABLE, null, initialValues) }
Java
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 = helperContext.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 database.insert(FTS_VIRTUAL_TABLE, null, initialValues); }
在适当的位置调用 loadDictionary()
方法以填充表。答
最好在onCreate()
方法中 DatabaseOpenHelper
类:
Kotlin
override fun onCreate(db: SQLiteDatabase) { database = db database.execSQL(FTS_TABLE_CREATE) loadDictionary() }
Java
@Override public void onCreate(SQLiteDatabase db) { database = db; database.execSQL(FTS_TABLE_CREATE); loadDictionary(); }
搜索查询
创建并填充虚拟表后,可以使用您的 SearchView
提供的查询来搜索数据。将以下方法添加到
DatabaseTable
类来构建用于搜索查询的 SQL 语句:
Kotlin
fun getWordMatches(query: String, columns: Array<String>?): Cursor? { val selection = "$COL_WORD MATCH ?" val selectionArgs = arrayOf("$query*") return query(selection, selectionArgs, columns) } private fun query( selection: String, selectionArgs: Array<String>, columns: Array<String>? ): Cursor? { val cursor: Cursor? = SQLiteQueryBuilder().run { tables = FTS_VIRTUAL_TABLE query(databaseOpenHelper.readableDatabase, columns, selection, selectionArgs, null, null, null) } return cursor?.run { if (!moveToFirst()) { close() null } else { this } } ?: null }
Java
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(databaseOpenHelper.getReadableDatabase(), columns, selection, selectionArgs, null, null, null); if (cursor == null) { return null; } else if (!cursor.moveToFirst()) { cursor.close(); return null; } return cursor; }
通过调用 getWordMatches()
搜索查询。系统会返回所有匹配的结果
位于 Cursor
中,您可以迭代它或使用它来构建 ListView
。
此示例在可搜索项的 handleIntent()
方法中调用 getWordMatches()
活动。请记住,可搜索 activity 会将 ACTION_SEARCH
intent 内部的查询作为 extra 接收,因为您之前执行的 intent 过滤器
之前创建的:
Kotlin
private val db = DatabaseTable(this) ... private fun handleIntent(intent: Intent) { if (Intent.ACTION_SEARCH == intent.action) { val query = intent.getStringExtra(SearchManager.QUERY) val c = db.getWordMatches(query, null) // process Cursor and display results } }
Java
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 } }