时间戳

首页 > 解决方案 > 如何解决问题 SQLiteException: no such table: Cars (code 1 SQLITE_ERROR): , while compile: SELECT DISTINCT * FROM Cars？

android - 如何解决问题 SQLiteException: no such table: Cars (code 1 SQLITE_ERROR): , while compile: SELECT DISTINCT * FROM Cars？

问题描述

我在我的应用程序中使用 SQLite 并试图将备份的数据库导入应用程序。一切都适用于 Oreo 及以下 SDK 版本（直到 SDK 16），但不适用于 Android P 及更高版本。我的数据库：

    private val dbTemp = DB_NAME + "_tmp"
    private val dbBackup = context.openOrCreateDatabase(dbTemp, Context.MODE_PRIVATE, null)

    override fun onCreate(db: SQLiteDatabase) {
            db.execSQL("CREATE TABLE $TABLE ($ID INTEGER PRIMARY KEY AUTOINCREMENT, $TITLE TEXT, $CAR TEXT, $DATE TEXT);")
        }

        override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
            db.execSQL("DROP TABLE IF EXISTS $TABLE")
            onCreate(db)
        }

我正在通过替换当前数据库来应对备份数据库，如下所示：

private fun copyData() {
        db.delete(TABLE, null, null)
        val cursor = dbBackup.query(  // <- here occurs the problem!
            true,
            TABLE,
            null,
            null,
            null,
            null,
            null,
            null,
            null
        )
        cursor.moveToFirst()
        while (!cursor.isAfterLast) {
            db.insert(TABLE, null, modelToValues(cursorToModel(cursor)))
            cursor.moveToNext()
        }
        cursor.close()
        context.deleteDatabase(dataTmp)
    }

这是抛出的问题：

Process: com.example.cars, PID: 18674
        android.database.sqlite.SQLiteException: no such table: Cars (code 1 SQLITE_ERROR): , while compiling: SELECT DISTINCT * FROM Cars
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:903)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:514)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
            at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
            at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1408)
            at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1255)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1126)
            at com.easyapps.cryptnote.ListDatabase.copyData(CarsDatabase.kt:163)
            at com.easyapps.cryptnote.ListDatabase.importToApp(CarsDatabase.kt:155)
            at com.example.cars.BackupActivity$onCreate$3$$special$$inlined$apply$lambda$2.onClick(BackupActivity.kt:104)
            at com.android.internal.app.AlertController$ButtonHandler.handleMessage(AlertController.java:172)
            at android.os.Handler.dispatchMessage(Handler.java:106)
            at android.os.Looper.loop(Looper.java:193)
            at android.app.ActivityThread.main(ActivityThread.java:6669)
            at java.lang.reflect.Method.invoke(Native Method)
            at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)

我尝试将这些链接link和link的解决方案结合起来，但没有成功。有关更多信息，我的数据库解决方案的备份和导入基于这个演示项目，它太旧了，但是我可以将它用于我在 Kotlin 中的项目。

备份方法：

fun exportToSD() {
        createFolderOnSD()
        val data = Environment.getDataDirectory()
        val backupDbPath = File(sdFolder, "/(${utilities.getDate("dd_MM_yyyy_HH_mm")}) " + DB_NAME)

        try {
            val source = FileInputStream(File(data, currentDbPath)).channel
            val destination = FileOutputStream(backupDbPath).channel
            destination.transferFrom(source, 0, source.size())
            source.close()
            destination.close()
        } catch (e: IOException) {
            e.printStackTrace()
        }
    }

导入 App 方法：

fun importToApp(fileNameOnSD: String) {
        val sd = File(sdFolder)
        if (sd.canWrite()) {
            val currentDB = File(Environment.getDataDirectory(), dataTmp)
            val backupDB = File(sd, fileNameOnSD)

            if (currentDB.exists()) {
                try {
                    val src = FileInputStream(backupDB).channel
                    val dst = FileOutputStream(currentDB).channel
                    dst.transferFrom(src, 0, src.size())
                    src.close()
                    dst.close()
                } catch (e: FileNotFoundException) {
                    e.printStackTrace()
                } catch (e: IOException) {
                    e.printStackTrace()
                }
            }
        }
        copyData()
    }

标签： androidsqlitekotlin

解决方案

Write Ahead Logging您可以通过将以下内容添加到您的 SQLiteOpenHelper 类来禁用选项（注意onConfigure与onOpen其他链接的答案不同） - 这适用于我的应用程序。

// Called when the database connection is being configured.
    // Configure database settings for things like foreign key support, write-ahead logging, etc.
    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        // Later version of Android seem to enable writeAhead by default, so for consistency explicitly disable it
        db.disableWriteAheadLogging();
    }

推荐阅读