android room 动态化sql查询实践

实践就是自己一个一个的拼凑....

fun getSiteListByDB(
        event: CloseSiteFilterEvent,
        mWarmBean: BookCaseWarmBean?
    ) {
        val mDataList = arrayListOf<SiteBean>()
        val stringBuilder = StringBuilder(" SELECT * FROM site ")
        if (event.siteName.isNotEmpty() || event.bookState.any { it != 0 } || event.siteState.any { it != 0 }) {
            stringBuilder.append(" WHERE ")
            if (event.siteName.isNotEmpty()) {
                stringBuilder.append(" deviceName like '%${event.siteName}%' ")
            }
            if (event.siteName.isNotEmpty() && event.bookState.any { it != 0 }) {
                stringBuilder.append(" and ")
            }
            event.bookState.forEachIndexed { index, bookState ->
                if (index > 0) {
                    stringBuilder.append(" or ")
                }
                when (bookState) {
                    1 -> stringBuilder.append(
                        " inventoryExistCount >= (${mWarmBean?.minBookCount
                            ?: 40} * doorCount) and inventoryExistCount <= (${mWarmBean?.maxBookCount
                            ?: 70} * doorCount) "
                    )
                    2 -> stringBuilder.append(
                        " inventoryExistCount < (${mWarmBean?.minBookCount
                            ?: 40} * doorCount) "
                    )
                    3 -> stringBuilder.append(
                        " inventoryExistCount > (${mWarmBean?.maxBookCount ?: 70} * doorCount) "
                    )
                }
            }

            if ((event.siteName.isNotEmpty() || event.bookState.any { it != 0 }) && event.siteState.any { it != 0 }) {
                stringBuilder.append(" and ")
            }
            event.siteState.forEachIndexed { index, siteState ->
                if (index > 0) {
                    stringBuilder.append(" or ")
                }
                when (siteState) {
                    1 -> stringBuilder.append(" deviceState = 'CONNECT_ONLINE' ")
                    2 -> stringBuilder.append(" deviceState = 'CONNECT_OFFLINE' ")
                }
            }
        }
        when (event.sort) {
            1 -> stringBuilder.append(" order by deviceState")
            2 -> stringBuilder.append(" order by inventoryExistCount")
            3 -> stringBuilder.append(" order by inventoryExistCount desc")
        }
        d(stringBuilder)
        DBHelper.instance.siteDao().find(SimpleSQLiteQuery(stringBuilder.toString()))?.also { mDataList.addAll(it) }
        d(mDataList)
        mDBSiteList.value = mDataList
    }

dao中的代码:

@RawQuery
    abstract fun find(sql:SupportSQLiteQuery): List<SiteBean>?