android - 在 Android Room 数据库中查询全文搜索表

Question

我正在尝试使用 Android 的 Room 库 fts4 在我的应用中创建搜索功能。由于某种原因，查询总是返回空结果。而且我似乎无法弄清楚问题是什么。下面是我的 DAO 和实体数据类。我在这里做错了什么吗？

@Dao
interface HymnDao {
    @Query("SELECT * FROM hymns_table")
    suspend fun getAllHymns(): List<HymnEntity>

    @Query("SELECT * FROM hymns_table WHERE :id = _id ")
    suspend fun getHymn(id: Int): HymnEntity

    @Query(
        """SELECT hymns_table.* 
                 FROM hymns_fts 
                 JOIN hymns_table ON (hymns_fts.rowid = _id )
                 WHERE hymns_fts MATCH :query """
    )
    suspend fun search(query: String): List<HymnEntity>
}

@Entity(tableName = "hymns_table")
data class HymnEntity(
    @PrimaryKey
    @ColumnInfo(name = "_id")
    val id: Int,
    val title: String,
    val author: String,
    val lyrics: String,
)

@Entity(tableName = "hymns_fts")
@Fts4(contentEntity = HymnEntity::class)
data class HymnFts(
    val title: String,
    val lyrics: String
)

Answer 1

额外的

考虑评论:-

It turns out, it was because I was using a prepopulated database.

这是一个基于上述答案但具有合适的预填充数据库的示例。

通过适当的，一个是根据 Room 期望的创建的，它本身是基于实体的。

• 注意，因为原始答案用于提供另一个答案，数据库包含额外的表和索引。

创建合适的数据库变得相对容易，就好像您使用实体和@Database(指的是适当的实体)编译项目 (CTRL+F9)，然后 Room 生成 java(Android View 显示了这一点)。与后缀为_Impl 的@Database 类同名的文件有一个名为createAllTables 的方法，它是可以在任何SQLite 工具(假设该工具支持FTS)中很容易使用的SQL。

创建合适的预填充数据库

1. 在 Android Studio 中找到生成的 java 文件 TheDatabase_Impl 和其中的 createAllTables 方法:-

1. 使用 SQLite 工具基本上从生成的 java 中复制 SQL，例如

:-

CREATE TABLE IF NOT EXISTS `hymns_table` (`_id` INTEGER, `title` TEXT NOT NULL, `author` TEXT NOT NULL, `lyrics` TEXT NOT NULL, PRIMARY KEY(`_id`));
CREATE VIRTUAL TABLE IF NOT EXISTS `hymns_fts` USING FTS4(`title` TEXT NOT NULL, `lyrics` TEXT NOT NULL, content=`hymns_table`);
CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_hymns_fts_BEFORE_UPDATE BEFORE UPDATE ON `hymns_table` BEGIN DELETE FROM `hymns_fts` WHERE `docid`=OLD.`rowid`; END;
CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_hymns_fts_BEFORE_DELETE BEFORE DELETE ON `hymns_table` BEGIN DELETE FROM `hymns_fts` WHERE `docid`=OLD.`rowid`; END;
CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_hymns_fts_AFTER_UPDATE AFTER UPDATE ON `hymns_table` BEGIN INSERT INTO `hymns_fts`(`docid`, `title`, `lyrics`) VALUES (NEW.`rowid`, NEW.`title`, NEW.`lyrics`); END;
CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_hymns_fts_AFTER_INSERT AFTER INSERT ON `hymns_table` BEGIN INSERT INTO `hymns_fts`(`docid`, `title`, `lyrics`) VALUES (NEW.`rowid`, NEW.`title`, NEW.`lyrics`); END;

CREATE TABLE IF NOT EXISTS `application_table` (`id` INTEGER, `name` TEXT NOT NULL, PRIMARY KEY(`id`));
CREATE UNIQUE INDEX IF NOT EXISTS `index_application_table_name` ON `application_table` (`name`);
CREATE TABLE IF NOT EXISTS `brand_table` (`id` INTEGER, `path` TEXT NOT NULL, `code` TEXT NOT NULL, `value` TEXT NOT NULL, PRIMARY KEY(`id`));
CREATE TABLE IF NOT EXISTS `Model` (`id` INTEGER, `path` TEXT NOT NULL, `code` TEXT NOT NULL, `value` TEXT NOT NULL, `brandCreatorId` INTEGER NOT NULL, PRIMARY KEY(`id`), FOREIGN KEY(`brandCreatorId`) REFERENCES `brand_table`(`id`) ON UPDATE CASCADE ON DELETE CASCADE );
CREATE INDEX IF NOT EXISTS `index_Model_brandCreatorId` ON `Model` (`brandCreatorId`);
CREATE TABLE IF NOT EXISTS `ApplicationBrandCrossRef` (`appId` INTEGER NOT NULL, `brandId` INTEGER NOT NULL, PRIMARY KEY(`appId`, `brandId`), FOREIGN KEY(`appId`) REFERENCES `application_table`(`id`) ON UPDATE CASCADE ON DELETE CASCADE , FOREIGN KEY(`brandId`) REFERENCES `brand_table`(`id`) ON UPDATE CASCADE ON DELETE CASCADE );
CREATE INDEX IF NOT EXISTS `index_ApplicationBrandCrossRef_brandId` ON `ApplicationBrandCrossRef` (`brandId`);

• 注意不要包含创建 room_master_table 或将行插入表中的 SQL。

1. 在 SQLite 工具中填充数据库(为此示例插入了 2 行)例如

:-

INSERT INTO `hymns_table` (title,author,lyrics) VALUES
    ('All things bright and beautiful','Fred','All things bright and beautiful,\nAll creatures great and small,\nAll things wise and wonderful:\nThe Lord God made them all.\nEach little flower that opens,\nEach little bird that sings,\nHe made their glowing colors,\nHe made their tiny wings.\n'),
    ('Onward Christian Soldiers','Mary','Onward, Christian soldiers, marching as to war,\nWith the cross of Jesus going on before.\nChrist, the royal Master, leads against the foe;\nForward into battle see His banners go!\nblah the great')
;

• 添加了 2 行 All things bright and beautiful 和 Onward Christian Soldiers(后者有额外的行 blah the great，所以两者都有一个常用词)

1. 保存/关闭数据库，再次打开并保存以确保它已被保存。

2. 在项目中创建 Assets 文件夹并将数据库文件(如果 -wal 和 -shm 文件存在(如果数据库已关闭则不存在))复制到 Assets 文件夹中。

• 在示例中，文件被命名为 soanswers.db，因为这是我使用的连接。

例如:-

1. 修改 Room.databaseBuilder 调用以包含 `.createFromAsset("the_filename_copied_into_the_assets_folder") 方法调用。

例如

        instance = Room.databaseBuilder(context, TheDatabase::class.java,"hymn.db")
                .createFromAsset("soanswers.db") //<<<<<< ADDED
                .allowMainThreadQueries()
                .build()

1. 现在应该一切正常。

---

在上一个答案的示例中，在执行上述步骤后， Activity 中使用的代码更改为:-

    db = TheDatabase.getInstance(this)
    dao = db.getHymnDao()

    for(hymn: HymnEntity in dao.search("small")) {
        Log.d("HYMNINFOR1","Hymn is ${hymn.title}")
    }
    for(hymn: HymnEntity in dao.search("on")) {
        Log.d("HYMNINFOR2","Hymn is ${hymn.title}")
    }
    for(hymn: HymnEntity in dao.search("great")) {
        Log.d("HYMNINFOR3","Hymn is ${hymn.title}")
    }

Result输出到日志:-

2021-08-11 11:08:44.691 D/HYMNINFOR1: Hymn is All things bright and beautiful

2021-08-11 11:08:44.693 D/HYMNINFOR2: Hymn is Onward Christian Soldiers

2021-08-11 11:08:44.694 D/HYMNINFOR3: Hymn is All things bright and beautiful
2021-08-11 11:08:44.694 D/HYMNINFOR3: Hymn is Onward Christian Soldiers

即查询的前两次调用找到了赞美诗的唯一匹配项，第三次调用匹配了两首赞美诗(因此 great wass 添加到 Onward Christian Soldiers)。