android - 使用单个查询在 Android Room 中搜索多个表

Question

鉴于有 3 张 table

@Entity
data class Pet(
  @PrimaryKey
  val id: String,
  val name: String,
  val colour: String,
  height: Int
  ownerId: String,
  householdId: String
)

@Entity
data class Owner(
  @PrimaryKey
  val id: String,
  val name: String,
  val address: String,
  val telephoneNumer: Int
)

@Entity
data class Household(
  @PrimaryKey
  val id: String,
  val name: String,
  val address: String,
  val region: String
)

执行返回 Pet 中的所有条目的查询的最佳方式是什么？ , Owner和 Household在哪里 name包含“Dav”
并获取与此类似的数据列表(为简洁起见，减去一些字段)

Pet("Dave"...) // Pet
Owner("David"...) //Owner
Owner("Davina"...) //Owner
Household("Davenport Close"...) //Address

有没有办法用 SQLite 查询和自定义 POJO 来做到这一点？

您是否对 3 个表执行 3 个单独的查询并将它们进一步组合在一起(例如 Repository/Usecase with Flow)？

Answer 1

如果您想要的只是一个 LIST，那么您可以使用自定义 POJO 和使用 UNION 的查询。
例如，您可以有一个 POJO，例如:-

data class searchPOJO(
    val id: String,
    val type: String,
    val name: String
)

和一个查询，例如:-

    @Query("SELECT id,'Pet' AS type,name FROM pet WHERE name LIKE :search UNION SELECT id, 'owner' AS type, name FROM owner WHERE name LIKE :search UNION SELECT id, 'household' AS type, name FROM household WHERE name LIKE :search")
    abstract fun search(search: String): List<searchPOJO>

你会像这样调用它:-

dao.search("%dav%")

注意:-

UNION 要求列数与初始选择匹配。

type 是用于标识数据来自哪个表的文字

然后 id 将能够根据其类型识别可以从中获取特定对象的行。

例如以下代码:-

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

    dao.insert(Pet("pet1","Dave","brown",10,"Fred","1 Maria Cresent"))
    dao.insert(Pet("pet2","George","red",11,"Bert","2 Somewhere Place"))
    dao.insert(Owner("owner1","David","10 Somewhere Place",1111111111))
    dao.insert(Owner("owner2","Laura","14 Overthere Way", 222222222))
    dao.insert(Owner("owner3","Davina","3 Wayland Way",333333333))
    dao.insert(Household("h1","Davenport Close","1 Davenport Close","region 1"))
    dao.insert(Household("h2","Daintree House","100 Smith Street","region 2"))

    for(s: searchPOJO in dao.search("%dav%")) {
        Log.d("DBINFO","ID is ${s.id} Name is ${s.name} Type is ${s.type}")
    }

结果是 :-

D/DBINFO: ID is h1 Name is Davenport Close Type is household
D/DBINFO: ID is owner1 Name is David Type is owner
D/DBINFO: ID is owner3 Name is Davina Type is owner
D/DBINFO: ID is pet1 Name is Dave Type is Pet

如果您想要在每次查找时返回每个对象中的一个(1 个有效对象和其他 2 个无效对象(例如空白))并指示返回的类型，那么您可以使用类似以下的 POJO:-

data class searchPOJOAll (
    val type: String,
    @Embedded(prefix = Companion.pet_prefix)
    val pet: Pet,
    @Embedded(prefix = owner_prefix)
    val owner: Owner,
    @Embedded(prefix = household_prefix)
    val household: Household
) {

    companion object {
        const val pet_prefix: String = "pet_"
        const val owner_prefix: String = "owner_"
        const val household_prefix: String = "household_"
    }
}

请注意，由于列名不明确(如命名字段/变量)，因此使用了前缀。

然后，查询将是先前查询的扩展版本，三个 SELECTS 中的每一个都为其他对象提供列，由于需要消除列名的歧义，这再次变得复杂，例如:-

@Query("SELECT 'PET' AS type, " +
        " id AS " + searchPOJOAll.pet_prefix + "id," +
        " name AS " + searchPOJOAll.pet_prefix + "name," +
        " colour AS " + searchPOJOAll.pet_prefix + "colour," +
        " height AS " + searchPOJOAll.pet_prefix + "height," +
        " ownerId AS " + searchPOJOAll.pet_prefix + "ownerId," +
        " householdId AS " + searchPOJOAll.pet_prefix + "householdId," +
        " '' AS " + searchPOJOAll.owner_prefix + "id," +
        " '' AS " + searchPOJOAll.owner_prefix + "name," +
        " '' AS " + searchPOJOAll.owner_prefix + "address," +
        " '' AS " + searchPOJOAll.owner_prefix + "telephoneNumer," +
        " '' AS " + searchPOJOAll.household_prefix + "id," +
        " '' AS " + searchPOJOAll.household_prefix + "name," +
        " '' AS " + searchPOJOAll.household_prefix + "address," +
        " '' AS " + searchPOJOAll.household_prefix + "region" +
        " FROM pet WHERE name LIKE :search " +
        " UNION SELECT 'OWNER' AS type, " +
        " '' AS " + searchPOJOAll.pet_prefix + "id," +
        " '' AS " + searchPOJOAll.pet_prefix + "name," +
        " '' AS " + searchPOJOAll.pet_prefix + "colour," +
        " '' AS " + searchPOJOAll.pet_prefix + "height," +
        " '' AS " + searchPOJOAll.pet_prefix + "ownerId," +
        " '' AS " + searchPOJOAll.pet_prefix + "householdId," +
        " id AS " + searchPOJOAll.owner_prefix + "id," +
        " name AS " + searchPOJOAll.owner_prefix + "name," +
        " address AS " + searchPOJOAll.owner_prefix + "address," +
        " telephoneNumer AS " + searchPOJOAll.owner_prefix + "telephoneNumer," +
        " '' AS " + searchPOJOAll.household_prefix + "id," +
        " '' AS " + searchPOJOAll.household_prefix + "name," +
        " '' AS " + searchPOJOAll.household_prefix + "address," +
        " '' AS " + searchPOJOAll.household_prefix + "region" +
        " FROM owner WHERE name LIKE :search " +
        " UNION SELECT 'HOUSEHOLD' AS type," +
        " '' AS " + searchPOJOAll.pet_prefix + "id," +
        " '' AS " + searchPOJOAll.pet_prefix + "name," +
        " '' AS " + searchPOJOAll.pet_prefix + "colour," +
        " '' AS " + searchPOJOAll.pet_prefix + "height," +
        " '' AS " + searchPOJOAll.pet_prefix + "ownerId," +
        " '' AS " + searchPOJOAll.pet_prefix + "householdId," +
        " '' AS " + searchPOJOAll.owner_prefix + "id," +
        " '' AS " + searchPOJOAll.owner_prefix + "name," +
        " '' AS " + searchPOJOAll.owner_prefix + "address," +
        " '' AS " + searchPOJOAll.owner_prefix + "telephoneNumer," +
        " id AS " + searchPOJOAll.household_prefix + "id," +
        " name AS " + searchPOJOAll.household_prefix + "name," +
        " address AS " + searchPOJOAll.household_prefix + "address," +
        " region AS " + searchPOJOAll.household_prefix + "region" +
        " FROM household WHERE name LIKE :search"
)
abstract fun searchAll(search: String): List<searchPOJOAll>

扩展之前的演示代码:-

    /* List of all 3 object types (only one of which is of use) */
    for(s: searchPOJOAll in dao.searchAll("%dav%")) {
        if(s.type == "PET") {
            Log.d("DBINFO","${s.type}>>>" + getPetString(s.pet))
        }
        if (s.type == "OWNER") {
            Log.d("DBINFO","${s.type}>>>" + getOwnerString(s.owner))
        }
        if (s.type == "HOUSEHOLD") {
            Log.d("DBINFO","${s.type}>>>" +getHouseholdString(s.household))
        }
    }

并具有以下功能:-

fun getPetString(pet: Pet):String {
    return "ID is ${pet.id} Petname = ${pet.name} colour is ${pet.colour} height is ${pet.height} ownerID is ${pet.ownerId} householId is ${pet.householdId}"
}
fun getOwnerString(owner: Owner): String {
    return "ID is ${owner.id} Ownername is ${owner.name} address is ${owner.address} telno is ${owner.telephoneNumer}"
}
fun getHouseholdString(household: Household): String {
    return "ID is ${household.id} name is ${household.name} address is ${household.address} region is ${household.region}"
}

结果(对于相同的数据)将是:-

D/DBINFO: HOUSEHOLD>>>ID is h1 name is Davenport Close address is 1 Davenport Close region is region 1
D/DBINFO: OWNER>>>ID is owner1 Ownername is David address is 10 Somewhere Place telno is 1111111111
D/DBINFO: OWNER>>>ID is owner3 Ownername is Davina address is 3 Wayland Way telno is 333333333
D/DBINFO: PET>>>ID is pet1 Petname = Dave colour is brown height is 10 ownerID is Fred householId is 1 Maria Cresent

尽管根据评论Android文档中提供的示例似乎创建了一个新的组合模型，而不是使用POJO从上述3个表中返回单独的实体，例如:-

data class PetWithOwnerAndHousehold (
    @Embedded
    val pet: Pet,
    @Relation( entity = Owner::class, parentColumn = "ownerId",entityColumn = "id")
    val owner: Owner,
    @Relation(entity = Household::class, parentColumn = "householdId", entityColumn = "id")
    val household: Household
        )

和一个查询/道，例如:-

@Query("SELECT pet.* FROM pet JOIN owner ON owner.id = pet.ownerId JOIN household ON household.id = pet.householdId WHERE pet.name LIKE :search OR owner.name LIKE :search OR household.name LIKE :search")
abstract fun getPetWithOwnerAndHousehold(search: String): List<PetWithOwnerAndHousehold> 

将返回宠物、它的所有者和它的 houdehold(假设关系(pet->owener 和 pet->houdhold)是有效的(在上面的数据中它们不是))如果是 pat 名称、所有者的姓名或家庭名称匹配搜索项。
例如如果添加了以下行(使用现有的所有者和 houdeholds):-

    dao.insert(Pet("p1","adava","grey",12,"owner1","h2")) /* multiple hits pet name and owner name*/
    dao.insert(Pet("p2","lady","blue",13,"owner2","h2")) /* no hits */
    dao.insert(Pet("p3","X","pink",14,"owner2","h1")) /* one hit household name */

然后使用 查询的结果%dav% 因为搜索字符串是:-

D/DBINFO: Pet ID is p1 pet's name is adava OwnerID is owner1  owner's name is David etc. HouseholdID is h2 household's name is Daintree House
D/DBINFO: Pet ID is p3 pet's name is X OwnerID is owner2  owner's name is Laura etc. HouseholdID is h1 household's name is Davenport Close