java - SQLiteBlobTooBigException - 华为设备、Android 9 和 10

Question

我得到 android.database.sqlite.SQLiteBlobTooBigException 仅适用于华为家庭设备，Android 9 和 10

Crash by devices

在 上升级 方法我想向表中添加新列

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
...
        case 43:
               addColumn(db, TABLE_LESSONS, _SPEED_GAME_EASY_FINISHED, " INTEGER DEFAULT 0");
               addColumn(db, TABLE_LESSONS, _SPEED_GAME_HARD_FINISHED, " INTEGER DEFAULT 0");
               addColumn(db, TABLE_LESSONS, _MEMORY_GAME_EASY_FINISHED, " INTEGER DEFAULT 0");
...
}

    private void addColumn(SQLiteDatabase db, String table, String column, String columnType) {
        boolean isColumnExist = false;
        AbstractWindowedCursor cursor = null;
        String selectQuery = "SELECT * FROM " + table + " LIMIT 0,1";

        try {
            cursor = getSqlCursorWithIncreasedWindowSize(db.rawQuery(selectQuery, null));

            if (cursor.moveToFirst()) {
                isColumnExist = cursor.getColumnIndex(column) != -1;
            }

            if (!isColumnExist) {
                db.execSQL("ALTER TABLE " + table + " ADD COLUMN " + column + columnType);
            }

        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
    }

我试图增加光标窗口大小，但没有帮助

fun getSqlCursorWithIncreasedWindowSize(cursor: Cursor): AbstractWindowedCursor {
    if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.P) {
        val cw = CursorWindow(null, 10000L)
        (cursor as AbstractWindowedCursor).window = cw
    }
    return cursor as AbstractWindowedCursor
}

TABLE_LESSONS 包含 10 列整数、长文本和短文本

中出现问题cursor.moveToFirst()

java.lang.RuntimeException: 
  at android.app.ActivityThread.performLaunchActivity (ActivityThread.java:3430)
  at android.app.ActivityThread.handleLaunchActivity (ActivityThread.java:3614)
  at android.app.servertransaction.LaunchActivityItem.execute (LaunchActivityItem.java:86)
  at android.app.servertransaction.TransactionExecutor.executeCallbacks (TransactionExecutor.java:108)
  at android.app.servertransaction.TransactionExecutor.execute (TransactionExecutor.java:68)
  at android.app.ActivityThread$H.handleMessage (ActivityThread.java:2199)
  at android.os.Handler.dispatchMessage (Handler.java:112)
  at android.os.Looper.loop (Looper.java:216)
  at android.app.ActivityThread.main (ActivityThread.java:7625)
  at java.lang.reflect.Method.invoke (Method.java)
  at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run (RuntimeInit.java:524)
  at com.android.internal.os.ZygoteInit.main (ZygoteInit.java:987)
Caused by: android.database.sqlite.SQLiteBlobTooBigException: 
  at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow (SQLiteConnection.java)
  at android.database.sqlite.SQLiteConnection.executeForCursorWindow (SQLiteConnection.java:904)
  at android.database.sqlite.SQLiteSession.executeForCursorWindow (SQLiteSession.java:851)
  at android.database.sqlite.SQLiteQuery.fillWindow (SQLiteQuery.java:62)
  at android.database.sqlite.SQLiteCursor.fillWindow (SQLiteCursor.java:149)
  at android.database.sqlite.SQLiteCursor.getCount (SQLiteCursor.java:137)
  at android.database.AbstractCursor.moveToPosition (AbstractCursor.java:220)
  at android.database.AbstractCursor.moveToFirst (AbstractCursor.java:259)
  at com.kkk.english_words.data.local.db.DatabaseHelper.addColumn (DatabaseHelper.java:31)
  at com.kkk.english_words.data.local.db.DatabaseHelper.onUpgrade (DatabaseHelper.java:161)
  at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked (SQLiteOpenHelper.java:417)
  at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase (SQLiteOpenHelper.java:313)
  at com.kkk.english_words.data.local.db.DatabaseHelper.getDb (DatabaseHelper.java:2)
  at com.kkk.english_words.data.local.db.DatabaseHelper.getActivityTimeDbHelper (DatabaseHelper.java:2)
  at com.kkk.english_words.data.local.db.DatabaseHelper.getActivityTimeDbHelper (DatabaseHelper.java:1)
  at com.kkk.english_words.ui.main.MainActivity.onCreate (MainActivity.java:187)
  at android.app.Activity.performCreate (Activity.java:7458)
  at android.app.Activity.performCreate (Activity.java:7448)
  at android.app.Instrumentation.callActivityOnCreate (Instrumentation.java:1286)
  at android.app.ActivityThread.performLaunchActivity (ActivityThread.java:3409)
  at android.app.ActivityThread.handleLaunchActivity (ActivityThread.java:3614)
  at android.app.servertransaction.LaunchActivityItem.execute (LaunchActivityItem.java:86)
  at android.app.servertransaction.TransactionExecutor.executeCallbacks (TransactionExecutor.java:108)
  at android.app.servertransaction.TransactionExecutor.execute (TransactionExecutor.java:68)
  at android.app.ActivityThread$H.handleMessage (ActivityThread.java:2199)
  at android.os.Handler.dispatchMessage (Handler.java:112)
  at android.os.Looper.loop (Looper.java:216)
  at android.app.ActivityThread.main (ActivityThread.java:7625)
  at java.lang.reflect.Method.invoke (Method.java)
  at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run (RuntimeInit.java:524)
  at com.android.internal.os.ZygoteInit.main (ZygoteInit.java:987)

您对如何解决它有任何想法吗？

问候

Answer 1

创建列时不会发生崩溃，而是在获取数据时发生，因为数据太大而无法放入游标。

如果要检查列是否存在，我建议查询 sqlite_master代替表( https://www.sqlite.org/faq.html#q7 )。

根据经验，我不建议在 Android 的 SQLite 单条记录中保存以兆字节为单位的数据。如果您别无选择，只能在本地存储此数据，我建议您将此数据写入文件并将其路径存储在数据库中。