- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
鉴于有 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
最佳答案
如果您想要的只是一个 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%")
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_"
}
}
@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 名称、所有者的姓名或家庭名称匹配搜索项。
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
关于android - 使用单个查询在 Android Room 中搜索多个表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/69245197/
我有三张 table 。表 A 有选项名称(即颜色、尺寸)。表 B 有选项值名称(即蓝色、红色、黑色等)。表C通过将选项名称id和选项名称值id放在一起来建立关系。 我的查询需要显示值和选项的名称,而
在mysql中,如何计算一行中的非空单元格?我只想计算某些列之间的单元格,比如第 3-10 列之间的单元格。不是所有的列...同样,仅在该行中。 最佳答案 如果你想这样做,只能在 sql 中使用名称而
关闭。这个问题需要多问focused 。目前不接受答案。 想要改进此问题吗?更新问题,使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
我正在为版本7.6进行Elasticsearch查询 我的查询是这样的: { "query": { "bool": { "should": [ {
关闭。这个问题需要多问focused 。目前不接受答案。 想要改进此问题吗?更新问题,使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
是否可以编写一个查询来检查任一子查询(而不是一个子查询)是否正确? SELECT * FROM employees e WHERE NOT EXISTS (
我找到了很多关于我的问题的答案,但问题没有解决 我有表格,有数据,例如: Data 1 Data 2 Data 3
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
我从 EditText 中获取了 String 值。以及提交查询的按钮。 String sql=editQuery.getText().toString();// SELECT * FROM empl
我有一个或多或少有效的查询(关于结果),但处理大约需要 45 秒。这对于在 GUI 中呈现数据来说肯定太长了。 所以我的需求是找到一个更快/更高效的查询(几毫秒左右会很好)我的数据表大约有 3000
这是我第一次使用 Stack Overflow,所以我希望我以正确的方式提出这个问题。 我有 2 个 SQL 查询,我正在尝试比较和识别缺失值,尽管我无法将 NULL 字段添加到第二个查询中以识别缺失
什么是动态 SQL 查询?何时需要使用动态 SQL 查询?我使用的是 SQL Server 2005。 最佳答案 这里有几篇文章: Introduction to Dynamic SQL Dynami
include "mysql.php"; $query= "SELECT ID,name,displayname,established,summary,searchlink,im
我有一个查询要“转换”为 mysql。这是查询: select top 5 * from (select id, firstName, lastName, sum(fileSize) as To
通过我的研究,我发现至少从 EF 4.1 开始,EF 查询上的 .ToString() 方法将返回要运行的 SQL。事实上,这对我来说非常有用,使用 Entity Framework 5 和 6。 但
我在构造查询来执行以下操作时遇到问题: 按activity_type_id过滤联系人,仅显示最近事件具有所需activity_type_id或为NULL(无事件)的联系人 表格结构如下: 一个联系人可
如何让我输入数据库的信息在输入数据 5 分钟后自行更新? 假设我有一张 table : +--+--+-----+ |id|ip|count| +--+--+-----+ |
我正在尝试搜索正好是 4 位数字的 ID,我知道我需要使用 LENGTH() 字符串函数,但找不到如何使用它的示例。我正在尝试以下(和其他变体)但它们不起作用。 SELECT max(car_id)
我有一个在 mysql 上运行良好的 sql 查询(查询 + 连接): select sum(pa.price) from user u , purchase pu , pack pa where (
我是一名优秀的程序员,十分优秀!