android - sqlite 数据库代码在 Android 7.0 上工作，但在 Android 8.0 上失败

Question

Android 7.0 和 Android 8.0 的 SQLite 行为是否存在差异？

我编写了一个应用程序，它将数据临时存储在 SQLite 数据库中，然后在可配置的持续时间后将其同步到 SQL Server DB。在搭载 Android 7.0 的 Galaxy J3 SM-J327U 上运行时，该应用程序按预期完美运行，但在搭载 Android 8.0 的 Galaxy J3 SM-337U 上运行时却失败了。

package com.company.batterytender.models

data class Scan(
    val session: Session,
    val scanTime: Long,
    val deviceIMEI: String,
    val scanIndex: Int,
    val batteryLevel: Int,
    val mxBatteryLevel: Int,
    val gripBatteryLevel: Int,
    val deviceBatteryLevel: Int,
    val scanValue: String,
    val scanValidity: Boolean,
    val synchronizedToNetworkDb: Boolean
)

package com.company.batterytender.models

import java.util.*

data class Session(
    val sessionUUID: UUID,
    val startTime: Long
)

package com.company.batterytender

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.util.Log
import com.company.batterytender.models.Scan
import com.company.batterytender.models.Session
import java.lang.Exception

class dataController(context: Context, factory: SQLiteDatabase.CursorFactory?) : SQLiteOpenHelper(context, DATABASE_NAME, factory, DATABASE_VERSION){

    companion object {
        private val DATABASE_VERSION = 4
        private val DATABASE_NAME = "batteryTender.db"
        val TABLE_NAME = "batteryStateData"
        val TABLE_NAME2 = "sessionData"
    }

    public override fun onCreate(db: SQLiteDatabase) {
        try{
            val CREATE_SCANS_TABLE =
                ("CREATE TABLE IF NOT EXISTS $TABLE_NAME\n" +
                        "\t(sessionUUID VARCHAR(36),\n" +
                        "\tscanTime INTEGER,\n" +
                        "\tdeviceIMEI VARCHAR(17),\n" +
                        "\tscanIndex INTEGER,\n" +
                        "\tbatteryLevel INTEGER,\n" +
                        "\tmxBatteryLevel INTEGER,\n" +
                        "\tgripBatteryLevel INTEGER,\n" +
                        "\tdeviceBatteryLevel INTEGER,\n" +
                        "\tscanValue VARCHAR(255),\n" +
                        "\tscanValidity INTEGER,\n" +
                        "\tsynchronized INTEGER)")

            val CREATE_SESSION_TABLE =
                ("CREATE TABLE IF NOT EXISTS $TABLE_NAME2\n" +
                        "\t(sessionUUID VARCHAR(36),\n" +
                        "\tstartTime INTEGER)")

            with(db) {
                execSQL(CREATE_SCANS_TABLE)

                execSQL(CREATE_SESSION_TABLE)
            }
        }
        catch (err: Exception){
            LogMessage("Exception caught in ${this.javaClass.enclosingMethod}\n${err.message}\n${err.stackTrace}")
            throw err
        }
    }

    public override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        try{
            if(oldVersion < newVersion) {
                val DROP_SCANS_TABLE =
                    ("DROP TABLE IF EXISTS $TABLE_NAME")

                val DROP_SESSION_TABLE =
                    ("DROP TABLE IF EXISTS $TABLE_NAME2")

                with(db) {
                    execSQL(DROP_SCANS_TABLE)

                    execSQL(DROP_SESSION_TABLE)
                }

                onCreate(db)
            }
        }
        catch (err: Exception){
            LogMessage("Exception caught in ${this.javaClass.enclosingMethod}\n${err.message}\n${err.stackTrace}")
            throw err
        }
    }

    internal fun addSession(session: Session){
        try{
            ContentValues().let {
                it.put("sessionUUID", session.sessionUUID.toString())
                it.put("startTime", session.startTime)

                with(this.writableDatabase) {
                    insert(TABLE_NAME2, null, it)
                    close()
                }
            }
        }
        catch (err: Exception){
            LogMessage("Exception caught in ${this.javaClass.enclosingMethod}\n${err.message}\n${err.stackTrace}")
            throw err
        }
    }

    internal fun addScan(scan: Scan){
        try{
            ContentValues().let {
                with(scan) {
                    it.put("sessionUUID", session.sessionUUID.toString())
                    it.put("scanTime", scanTime)
                    it.put("deviceIMEI", deviceIMEI)
                    it.put("scanIndex", scanIndex)
                    it.put("batteryLevel", batteryLevel)
                    it.put("mxBatteryLevel", mxBatteryLevel)
                    it.put("gripBatteryLevel", gripBatteryLevel)
                    it.put("deviceBatteryLevel", deviceBatteryLevel)
                    it.put("scanValue", this.scanValue)
                    it.put("scanValidity", scanValidity)
                    it.put("synchronized", synchronizedToNetworkDb)
                }

                with(this.writableDatabase) {
                    insert(TABLE_NAME, null, it)
                    close()
                }
            }
        }
        catch (err: Exception){
            LogMessage("Exception caught in ${this.javaClass.enclosingMethod}\n${err.message}\n${err.stackTrace}")
            throw err
        }
    }

    private fun LogMessage(message: String){
        Log.d(this.javaClass.simpleName, message)
    }

    internal fun markAsSynchronized(scan: Scan){
        try {
            ContentValues().let {
                it.put("synchronized", 1)

                with(this.writableDatabase) {
                    update(TABLE_NAME, it, with(scan){"sessionUUID='${session.sessionUUID}' AND scanIndex=${scanIndex}"} , null)
                    close()
                }
            }
        }
        catch (err: Exception){
            LogMessage("Exception caught in ${this.javaClass.enclosingMethod}\n${err.message}\n${err.stackTrace}")
            throw err
        }
    }

    internal fun getAllScans(): Cursor? {
        try{
            with(this.readableDatabase) {
                return rawQuery("SELECT * FROM $TABLE_NAME", null)
            }
        }
        catch (err: Exception){
            LogMessage("Exception caught in ${this.javaClass.enclosingMethod}\n${err.message}\n${err.stackTrace}")
            throw err
        }
    }

    internal fun getUnsynchronizedScans(): Cursor? {
        try{
            with(this.readableDatabase) {
                return rawQuery(
                    "SELECT scans.sessionUUID,startTime,scanTime,deviceIMEI,scanIndex,batteryLevel,mxBatteryLevel,gripBatteryLevel,deviceBatteryLevel,scanValue,scanValidity FROM $TABLE_NAME scans JOIN $TABLE_NAME2 sessions ON sessions.sessionUUID=scans.sessionUUID WHERE synchronized != 1",
                    null
                )
            }
        }
        catch (err: Exception){
            LogMessage("Exception caught in ${this.javaClass.enclosingMethod}\n${err.message}\n${err.stackTrace}")
            throw err
        }
        catch (err: Error){
            LogMessage("Error caught in ${this.javaClass.enclosingMethod}\n${err.message}\n${err.stackTrace}")
            throw err
        }
    }

    internal fun getAllScansFromSession(session: String): Cursor? {
        try{
            with(this.readableDatabase) {
                return rawQuery("SELECT * FROM $TABLE_NAME WHERE sessionUUID='${session}'", null)
            }
        }
        catch (err: Exception){
            LogMessage("Exception caught in ${this.javaClass.enclosingMethod}\n${err.message}\n${err.stackTrace}")
            throw err
        }
    }

    internal fun getAllSessions(): Cursor? {
        try{
            with(this.readableDatabase) {
                return rawQuery("SELECT * FROM $TABLE_NAME2", null)
            }
        }
        catch (err: Exception){
            LogMessage("Exception caught in ${this.javaClass.enclosingMethod}\n${err.message}\n${err.stackTrace}")
            throw err
        }
    }
}

经过大量测试后，我确定它将写入 SQLite 数据库并同步第一个请求，然后每次都失败。对 getUnsynchronizedScans() 的调用总是返回一个 Cursor？后续请求中有 0 个条目。我也没有收到任何错误或异常。

标签： androiddatabasesqlitekotlin