提问人:OldDogNewTricks 提问时间:10/29/2023 最后编辑:Colonel Thirty TwoOldDogNewTricks 更新时间:10/31/2023 访问量:47
如何在我的 Android 应用程序中更高效地编写 SQLite 查询?
How can I write my SQLite queries more efficiently in my Android app?
问:
我正在使用 Jetpack Compose 为 Android 编写一个小应用程序。我的应用程序中有一个 SQL 数据库,其中包含一个名为“findDay”的字段,其中包含一周中的几天,即周一到周日。
在我的 DAO 中,我为一周中的每一天调用一个单独的 SQL 查询。我敢肯定这是低效的。
@Query("SELECT COUNT() FROM finds WHERE findDay=\"MONDAY\"")
suspend fun getMondayTotal():Float
@Query("SELECT COUNT() FROM finds WHERE findDay=\"TUESDAY\"")
suspend fun getTuesdayTotal():Float
@Query("SELECT COUNT() FROM finds WHERE findDay=\"WEDNESDAY\"")
suspend fun getWednesdayTotal():Float
@Query("SELECT COUNT() FROM finds WHERE findDay=\"THURSDAY\"")
suspend fun getThursdayTotal():Float
@Query("SELECT COUNT() FROM finds WHERE findDay=\"FRIDAY\"")
suspend fun getFridayTotal():Float
@Query("SELECT COUNT() FROM finds WHERE findDay=\"SATURDAY\"")
suspend fun getSaturdayTotal():Float
@Query("SELECT COUNT() FROM finds WHERE findDay=\"SUNDAY\"")
suspend fun getSundayTotal():Float
这是我的存储库。
suspend fun getMondayTotal():Float {
return findDao.getMondayTotal()
}
suspend fun getTuesdayTotal():Float {
return findDao.getTuesdayTotal()
}
suspend fun getWednesdayTotal():Float {
return findDao.getWednesdayTotal()
}
suspend fun getThursdayTotal():Float {
return findDao.getThursdayTotal()
}
suspend fun getFridayTotal():Float {
return findDao.getFridayTotal()
}
suspend fun getSaturdayTotal():Float {
return findDao.getSaturdayTotal()
}
suspend fun getSundayTotal():Float {
return findDao.getSundayTotal()
}
这是我的 Viewmodel。
suspend fun getMondayTotal(): Float {
return repository.getMondayTotal()
}
suspend fun getTuesdayTotal(): Float {
return repository.getTuesdayTotal()
}
suspend fun getWednesdayTotal(): Float {
return repository.getWednesdayTotal()
}
suspend fun getThursdayTotal(): Float {
return repository.getThursdayTotal()
}
suspend fun getFridayTotal(): Float {
return repository.getFridayTotal()
}
suspend fun getSaturdayTotal(): Float {
return repository.getSaturdayTotal()
}
suspend fun getSundayTotal(): Float {
return repository.getSundayTotal()
}
这是我的用户界面。
@Composable
fun FindsGraph(viewModel: MainViewModel) {
var mondayval: Float by remember { mutableStateOf(0f) }
var tuesdayval: Float by remember { mutableStateOf(0f) }
var wednesdayval: Float by remember { mutableStateOf(0f) }
var thursdayval: Float by remember { mutableStateOf(0f) }
var fridayval: Float by remember { mutableStateOf(0f) }
var saturdayval: Float by remember { mutableStateOf(0f) }
var sundayval: Float by remember { mutableStateOf(0f) }
val scope2 = rememberCoroutineScope()
scope2.launch {
mondayval = viewModel.getMondayTotal()
tuesdayval = viewModel.getTuesdayTotal()
wednesdayval = viewModel.getWednesdayTotal()
thursdayval = viewModel.getThursdayTotal()
fridayval = viewModel.getFridayTotal()
saturdayval = viewModel.getSaturdayTotal()
sundayval = viewModel.getSundayTotal()
}
val bardata = listOf(
BarData(mondayval, "MONDAY", color = Color(0xFF00BCD4)),
BarData(tuesdayval, "TUESDAY", color = Color(0xFFFFEB3B)),
BarData(wednesdayval, "WEDNESDAY", color = Color(0xFFF44336)),
BarData(thursdayval, "THURSDAY", color = Color(0xFF4CAF50)),
BarData(fridayval, "FRIDAY", color = Color(0xFF673AB7)),
BarData(saturdayval, "SATURDAY", color = Color(0xFFDF780B)),
BarData(sundayval, "SUNDAY", color = Color(0xffed615d))
)
Column (modifier = Modifier.fillMaxHeight()){
BarChart(
dataCollection = ChartDataCollection(bardata),
barSpacing = 10.dp,
axisConfig = AxisConfig(
showAxes = true,
axisColor = Color.Black,
axisStroke = 10f,
minLabelCount = 3,
showGridLabel = true,
showGridLines = false
),
modifier = Modifier
.fillMaxWidth()
.height(400.dp)
)
}
}
所以,这种方法有效。它最终为我显示了一个很好的图表,但我也意识到这是一种真正的蛮力方法,我相信有一种更有效的方法可以做到这一点。我真的很感激任何帮助改进这一点。
答:
1赞
Ayman Ait
10/29/2023
#1
首先,我们都去过那里,没有人会对你的代码生气,只要你想了解所有重要的事情。
其次,是的,实际上有一种更好的方法来制作你所做的事情,那就是将参数传递给你的函数,其中包含所需的一天:Dao
@Query("SELECT COUNT() FROM finds WHERE findDay= :dayOfWeek")
suspend fun getDayOccurrences(dayOfWeek : String):Float
现在,您必须将此参数从UI一直传递到this 。Dao
存储 库:
suspend fun getDayOccurrences(dayOfWeek : String):Float {
return findDao.getDayOccurrences(dayOfWeek)
}
然后你继续这样,这样你需要做的就是从UI中度过正确的一天。
注意:我强烈建议您在 viewModel 中为每天设置一个变量
或为所有日期设置一个列表
,然后使用 ViewModelScope
收集视图模型
中的出现次数,因为您当前执行此操作的方式将导致每次
重构
发生时都会触发 scope2
。
1赞
Jan Bína
10/29/2023
#2
您可以使用子句。这样的陈述:GROUP BY
SELECT findDay, COUNT() FROM finds GROUP BY findDay;
将返回包含日期名称和出现次数的行。要将其与房间一起使用,您必须使用@RawQuery
。整个代码如下所示:
data class DayWithCount(
val findDay: String,
val count: Int,
)
@Dao
interface MyDao {
@RawQuery
suspend fun getDaysWithCount(query: SupportSQLiteQuery): List<DayWithCount>
}
class Repository {
suspend fun getDaysWithCount(): List<DayWithCount> {
val query = SimpleSQLiteQuery(
"SELECT findDay, COUNT() AS count FROM finds GROUP BY findDay"
)
return dao.getDaysWithCount(query)
}
}
评论