如果您只想要一个列表,那么您可以使用自定义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>
继续使用先前示例代码添加如下内容:
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"))
dao.insert(Pet("p2","lady","blue",13,"owner2","h2"))
dao.insert(Pet("p3","X","pink",14,"owner2","h1"))
使用%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