logo标签列表

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

androidkotlinandroid-room
5

考虑到有三个表格

@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
)

如何进行查询以返回所有包含“Dav”的名称的，和中的条目，并获得类似于以下数据列表（出于简洁起见，省略了一些字段）的内容：

Pet("Dave"...) // Pet
Owner("David"...) //Owner
Owner("Davina"...) //Owner
Household("Davenport Close"...) //Address
  1. 有没有一种方式可以使用SQLite查询和自定义POJO来实现这个功能？
  2. 你是否对三个表执行三个独立的查询，并将它们在更高层级（例如Repository/Usecase with Flow）合并在一起？
请查看这个链接。 - Varsha Kulkarni
Android文档中提供的示例似乎创建了一个新的组合模型，而不是从上述3个表中返回单独的实体。 - skon
1个回答
6
如果您只想要一个列表，那么您可以使用自定义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%")
  • 注意：联合查询需要匹配初始选择的列数。
  • type 是一个常量，用于识别数据来自哪个表
  • 通过 id 可以根据它的 type 识别特定对象所在的行。

例如以下代码：

    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

如果您想要返回每个查找对象的一个有效对象和另外两个无效对象（例如空白），并带有返回类型的指示，则可以使用类似以下的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_"
    }
}
  • 由于列名的模糊性（例如命名字段/变量），因此使用前缀。

查询将成为前一个查询的扩展版本，其中三个SELECT语句为其他对象提供列，这会使情况变得更加复杂，因为需要消除列名的歧义，例如：

@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文档中提供的示例似乎创建了一个新的组合模型，而不是从上述 3 张表中返回单独的实体，使用像下面这样的 POJO：

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>

假定关系(pet->owner和pet->household)有效(在上述数据中它们不是)，如果宠物名、主人名或家庭名与搜索项匹配，则返回宠物、它的主人和它所属的家庭。例如，如果使用现有的所有者和家庭添加以下行：
    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
感谢@MikeT对我的问题给出如此深入的答案。我最感兴趣的是您提供的第一种方法，它基于“名称”查询返回列表，而不关注实体关系。 - skon
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接