如何在我的 Android 应用程序中更高效地编写 SQLite 查询?

How can I write my SQLite queries more efficiently in my Android app?

提问人:OldDogNewTricks 提问时间:10/29/2023 最后编辑:Colonel Thirty TwoOldDogNewTricks 更新时间:10/31/2023 访问量:47

问:

我正在使用 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)
                )
            }


        }

所以,这种方法有效。它最终为我显示了一个很好的图表,但我也意识到这是一种真正的蛮力方法,我相信有一种更有效的方法可以做到这一点。我真的很感激任何帮助改进这一点。

Android SQLite Kotlin

评论

0赞 Colonel Thirty Two 10/31/2023
如果您已经有工作代码,并且只想获得反馈,请考虑发布到 codereview.stackexchange.com

答:

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