android数据库存储查询,存储和搜索数据 | Android 开发者 | Android Developers

您可以通过多种方式存储数据，例如存储到在线数据库、本地 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 中创建一个扩展

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 内部类中。

提示：您可能还需要设置一个回调，以在该线程完成时通知您的界面 Activity。

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 = 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() 方法以填充表。建议的位置是创建表后在 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();

}

搜索查询

创建并填充虚拟表后，可以使用您的 DatabaseTable 类，以构建一个用于搜索查询的 SQL 语句：

Kotlin

fun getWordMatches(query: String, columns: Array?): Cursor? {

val selection = "$COL_WORD MATCH ?"

val selectionArgs = arrayOf("$query*")

return query(selection, selectionArgs, columns)

}

private fun query(

selection: String,

selectionArgs: Array,

columns: Array?

): 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() 搜索查询。所有匹配的结果都会在一个 handleIntent() 方法中调用 getWordMatches()。请注意，由于您之前创建的 intent 过滤器，可搜索 Activity 会将

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

}

}