Android SQLite修改表结构后返回陈旧数据问题求助

问题描述 #

在Android SQLite中出现异常Bug：创建数据库后关闭，重新打开并添加列，再次查询列数等元数据时返回旧值。使用最新构建工具，目标SDK版本为33。

复现测试代码 #

import android.content.ContentValues
import android.database.sqlite.SQLiteDatabase.*
import androidx.sqlite.db.SupportSQLiteDatabase
import androidx.sqlite.db.SupportSQLiteOpenHelper
import androidx.sqlite.db.framework.FrameworkSQLiteOpenHelperFactory
import androidx.test.ext.junit.runners.AndroidJUnit4
import androidx.test.platform.app.InstrumentationRegistry
import org.junit.After
import org.junit.Assert.assertArrayEquals
import org.junit.Before
import org.junit.Test
import org.junit.runner.RunWith

@RunWith(AndroidJUnit4::class)
class SmallBugTest {

    private val TEST_DB = "small-bug-test"
    private val context = InstrumentationRegistry.getInstrumentation().context
    private lateinit var db: SupportSQLiteDatabase

    @Before
    fun setup() {
        // 模拟两次会话，单次会话不会触发此Bug
        /* 创建数据库并关闭 */
        SupportSQLiteOpenHelper.Configuration(context, TEST_DB,
            object : SupportSQLiteOpenHelper.Callback(1) {
                override fun onCreate(db: SupportSQLiteDatabase) = createDb(db)
                override fun onUpgrade(
                    db: SupportSQLiteDatabase,
                    oldVersion: Int,
                    newVersion: Int
                ) = throw AssertionError()
            }).let { FrameworkSQLiteOpenHelperFactory().create(it).writableDatabase }.close()

        /* 重新打开数据库 */
        db = SupportSQLiteOpenHelper.Configuration(context, TEST_DB,
            object : SupportSQLiteOpenHelper.Callback(1) {
                override fun onCreate(db: SupportSQLiteDatabase) = throw AssertionError()
                override fun onUpgrade(
                    db: SupportSQLiteDatabase,
                    oldVersion: Int,
                    newVersion: Int
                ) = throw AssertionError()
            }).let { FrameworkSQLiteOpenHelperFactory().create(it).writableDatabase }
    }

    private fun createDb(db: SupportSQLiteDatabase) {
        db.execSQL(
            "CREATE TABLE `TEST_TABLE` (" +
                    "`column0` TEXT NOT NULL," +
                    "`column1` TEXT NOT NULL" +
                    ")"
        )
        db.execSQL("INSERT INTO `TEST_TABLE` (`column0`, `column1`) VALUES ('content0', 'content1')")
        db.version = 1
    }

    @Test
    fun causeBug() { // 测试通过则说明Bug触发
        db.query("SELECT * FROM TEST_TABLE").close()

        modifySchema(db)

        db.query("SELECT * FROM TEST_TABLE").use {
            it.moveToFirst()
            assertArrayEquals(
                arrayOf("column0", "column1"),
                it.columnNames
            ) // 实际应为 ["column0", "column1", "column2"]
        }
    }

    private fun modifySchema(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE `TEST_TABLE` RENAME TO `TEST_TABLE_OLD`")
        db.execSQL(
            "CREATE TABLE `TEST_TABLE` (" +
                    "`column0` TEXT NOT NULL," +
                    "`column1` TEXT NOT NULL," +
                    "`column2` TEXT" +
                    ")"
        )
        db.execSQL("INSERT INTO `TEST_TABLE` (`column0`, `column1`) SELECT `column0`, `column1` FROM `TEST_TABLE_OLD`")
        db.execSQL("DROP TABLE `TEST_TABLE_OLD`")
        db.update(
            "TEST_TABLE",
            CONFLICT_NONE,
            ContentValues().also { it.put("column2", "content2"); },
            null,
            null
        )
    }
}

Bug原因定位 #

• 查询发送到(Support)SQLiteDatabase时，acquirePreparedStatement会复用相同SQL文本的缓存PreparedStatement（PS）
• Cursor的getX等数据读取操作每次查询都会获取新数据，但getColumnCount、getColumnNames等元数据操作仅在PS创建时初始化，修改表结构后仍返回旧值

可行的临时解决方案 #

方案1：修改SQL文本避免缓存复用 #

对相同逻辑的查询添加无意义的注释或参数，让SQL文本唯一，强制创建新的PreparedStatement以获取最新元数据。示例：

// 修改后的查询语句，添加唯一标识注释
db.query("SELECT * FROM TEST_TABLE /* schema_${System.currentTimeMillis()} */").use {
    it.moveToFirst()
    // 此时能获取到最新的columnNames
}

方案2：手动清除PreparedStatement缓存 #

通过反射调用SQLiteDatabase的clearAllPreparedStatements()方法清空缓存（注意反射可能受SDK版本限制）：

fun clearPreparedStatements(db: SupportSQLiteDatabase) {
    if (db is FrameworkSQLiteDatabase) {
        try {
            val delegateField = FrameworkSQLiteDatabase::class.java.getDeclaredField("mDelegate")
            delegateField.isAccessible = true
            val sqliteDb = delegateField.get(db) as android.database.sqlite.SQLiteDatabase
            sqliteDb.clearAllPreparedStatements()
        } catch (e: Exception) {
            // 处理反射异常
            e.printStackTrace()
        }
    }
}

// 在修改表结构后调用
modifySchema(db)
clearPreparedStatements(db)

方案3：关闭并重新打开数据库连接 #

修改表结构后关闭当前数据库连接，重新打开实例，缓存的PreparedStatement会被清空：

modifySchema(db)
db.close()
// 重新获取数据库实例
db = SupportSQLiteOpenHelper.Configuration(context, TEST_DB,
    object : SupportSQLiteOpenHelper.Callback(1) {
        override fun onCreate(db: SupportSQLiteDatabase) = throw AssertionError()
        override fun onUpgrade(
            db: SupportSQLiteDatabase,
            oldVersion: Int,
            newVersion: Int
        ) = throw AssertionError()
    }).let { FrameworkSQLiteOpenHelperFactory().create(it).writableDatabase }

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