Android 5.0 API21下SQLite查询报错，API33正常问题排查

这段代码在Android API 33上运行正常，但仅在Android 5.0 API 21环境中出现异常。我用SimpleSQLiteQuery编写了查询逻辑，具体实现和实体类定义如下：

查询函数实现 #

override fun getPropertyBySearch(
    type: String,
    city: String,
    minSurface: Int,
    maxSurface: Int,
    minPrice: Int,
    maxPrice: Int,
    onTheMarketLessALastWeek: Boolean,
    soldOn3LastMonth: Boolean,
    min3photos: Boolean,
    schools: Boolean,
    shops: Boolean
): LiveData<List<RealEstateDatabase>> {

    val iso = ISOChronology.getInstance()
    val today = LocalDate(iso)

    Log.e("today",today.toString())

    val dateMinusThreeMonth = today.minusMonths(3)
    val dateMinus1Week = today.minusDays(7)

    Log.e("dateMinusThreeMonth",dateMinusThreeMonth.toString())
    Log.e("dateMinus1Week",dateMinus1Week.toString())

    val query = """SELECT * FROM RealEstateDatabase WHERE 
                    ('$type' ='' OR type LIKE '%$type%' ) AND 
                    ('$city' ='' OR city LIKE '%$city%' ) AND
                    ($schools = false OR schoolsNear = $schools ) AND 
                    ($shops = false OR shopsNear = $shops ) AND 
                    ($min3photos = false OR count_photo >= 3 ) AND
                    ($minSurface =0 AND $maxSurface = 0  OR  area BETWEEN $minSurface AND $maxSurface  ) AND 
                    ($minPrice =0 AND $maxPrice = 0  OR  price BETWEEN $minPrice AND $maxPrice ) AND 
                    ($onTheMarketLessALastWeek = false  OR  dateOfEntry BETWEEN '$dateMinus1Week' AND '$today' ) AND 
                    ($soldOn3LastMonth = false  OR dateOfSale BETWEEN '$dateMinusThreeMonth' AND '$today') """

    Log.e("query", query)


    return realEstateDao.getPropertyBySearch(SimpleSQLiteQuery(query))
}

实体类定义 #

@Entity
@Parcelize
data class RealEstateDatabase(
@PrimaryKey
var id: String,
var type: String? = null,
var price: Int? = null,
var area: Int? = null,
var numberRoom: String? = null,
var description: String? = null,
var numberAndStreet: String? = null,
var numberApartment: String? = null,
var city: String? = null,
var region: String? = null,
var postalCode: String? = null,
var country: String? = null,
var status: String? = null,
var dateOfEntry: String? = null,
var dateOfSale: String? = null,
var realEstateAgent: String? = null,
var lat: Double ?=null,
var lng: Double ?=null,
var hospitalsNear : Boolean = false,
var schoolsNear : Boolean = false,
var shopsNear : Boolean = false,
var parksNear : Boolean = false,
@ColumnInfo(name = "listPhotoWithText")
var listPhotoWithText : List<PhotoWithTextFirebase> ?=null,
var count_photo : Int? = listPhotoWithText?.size,
)

兼容性问题原因分析 #

1. SQLite版本差异：Android 5.0搭载SQLite 3.8.6，而API 33对应更高版本（约3.39.x），旧版本对布尔值解析、字符串拼接的容错性更低。
2. 布尔值处理错误：SQLite无原生布尔类型，依赖0/1存储。API 21中直接在SQL语句写false/true会被当作字符串，导致逻辑判断失效。
3. 字符串拼接风险：直接将变量拼入SQL语句，不仅存在注入风险，还可能因特殊字符导致语法错误，旧版本SQLite对这类错误更敏感。
4. 派生字段映射问题：实体类中count_photo依赖listPhotoWithText?.size作为默认值，Room在旧版本中无法自动计算该值，导致查询时字段值异常。

修复方案 #

1. 改用参数化查询 #

避免字符串拼接，用SimpleSQLiteQuery的参数传递方式，同时将布尔值转为0/1适配SQLite存储规则：

override fun getPropertyBySearch(
    type: String,
    city: String,
    minSurface: Int,
    maxSurface: Int,
    minPrice: Int,
    maxPrice: Int,
    onTheMarketLessALastWeek: Boolean,
    soldOn3LastMonth: Boolean,
    min3photos: Boolean,
    schools: Boolean,
    shops: Boolean
): LiveData<List<RealEstateDatabase>> {

    val iso = ISOChronology.getInstance()
    val today = LocalDate(iso)
    val dateMinusThreeMonth = today.minusMonths(3)
    val dateMinus1Week = today.minusDays(7)

    // 参数化查询语句
    val query = """SELECT * FROM RealEstateDatabase WHERE 
                    (? = '' OR type LIKE '%' || ? || '%' ) AND 
                    (? = '' OR city LIKE '%' || ? || '%' ) AND
                    (? = 0 OR schoolsNear = ? ) AND 
                    (? = 0 OR shopsNear = ? ) AND 
                    (? = 0 OR count_photo >= 3 ) AND
                    (? = 0 AND ? = 0  OR  area BETWEEN ? AND ?  ) AND 
                    (? = 0 AND ? = 0  OR  price BETWEEN ? AND ? ) AND 
                    (? = 0  OR  dateOfEntry BETWEEN ? AND ? ) AND 
                    (? = 0  OR dateOfSale BETWEEN ? AND ?)"""

    // 整理参数，布尔值转整数
    val params = arrayOf(
        type, type,
        city, city,
        if (schools) 1 else 0, if (schools) 1 else 0,
        if (shops) 1 else 0, if (shops) 1 else 0,
        if (min3photos) 1 else 0,
        minSurface, maxSurface, minSurface, maxSurface,
        minPrice, maxPrice, minPrice, maxPrice,
        if (onTheMarketLessALastWeek) 1 else 0, dateMinus1Week.toString(), today.toString(),
        if (soldOn3LastMonth) 1 else 0, dateMinusThreeMonth.toString(), today.toString()
    )

    return realEstateDao.getPropertyBySearch(SimpleSQLiteQuery(query, params))
}

2. 修复实体类派生字段 #

去掉count_photo的默认值，改为在插入/更新数据时主动设置该字段值，或在查询时通过json_array_length(listPhotoWithText)计算（需确保listPhotoWithText以JSON格式存储）：

@Entity
@Parcelize
data class RealEstateDatabase(
    // ... 其他字段不变 ...
    @ColumnInfo(name = "listPhotoWithText")
    var listPhotoWithText : List<PhotoWithTextFirebase> ?=null,
    var count_photo : Int? = null, // 去掉默认值
)

3. 日期字段优化（可选） #

将dateOfEntry、dateOfSale改为Long类型存储时间戳，避免字符串格式解析的兼容性问题：

var dateOfEntry: Long? = null,
var dateOfSale: Long? = null,

查询时直接用时间戳范围判断，无需处理字符串格式。

内容的提问来源于stack exchange，提问作者TheRealAzubal