时间戳

首页 > 解决方案 > Android Sqlite - 异常：请求索引 -1，大小为 32 --- 由于大尺寸？

android-sqlite - Android Sqlite - 异常：请求索引 -1，大小为 32 --- 由于大尺寸？

问题描述

在我的应用程序中，我使用一种标准方式来访问我的 Sqlite 数据库文件。几个月来一切都很好，但是对于数据库中的一个特定行，出现了问题。该列中的数据长度为 7.265.161 字节。

为了阅读这一行，我得到以下错误：

请求索引 -1，大小为 32

我使用的标准代码是：

List<CText> cTexts = new ArrayList<>();
try {
    Cursor cursor;
    String[] tableColumns = new String[]{"book", "chapter", "fromvorm", "tovorm", "data"};
    String[] whereArgs = new String[]{"" + bookNumber, "" + chapterNumber};
    cursor = db.query(TABLE_CTEXT, tableColumns, "book = ? and chapter = ?", whereArgs, null, null, "fromvorm");
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        cTexts.add(new CText(cursor.getInt(0),
                cursor.getInt(1), cursor.getInt(2),
                cursor.getInt(3), cursor.getString(4)));
        cursor.moveToNext();
    }
    cursor.close();
} catch (Exception e) {
    Logger.e("Get ctext exception: " + e.getMessage(), e);
    return null;
}

你能帮忙解决吗？

更新：感谢@ekimro，我们有一个解决方案！

经过进一步调查，我发现将 7Mb 文件拆分为 1MB 的部分会导致响应。大约需要 30 秒才能得到响应。

标签： android-sqlite

解决方案

您解释的问题对于 CursorWindow 大于 4Mb。

解决是重新设计模式（将数据存储为小到不大于 4Mb 的块（如 256k））或以块的形式获取数据，如 256k 小。

像 SQL 一样做最后

WITH RECURSIVE control(startchar,endchar,limiter,book,chapter,seq) AS (
    SELECT 1,256 * 1024, (SELECT max(length(data)) FROM ctext WHERE book = 'B2' AND chapter = '1'),'B2','1',0
    UNION ALL SELECT endchar + 1,endchar + (256 * 1024), limiter,book,chapter,seq +1 FROM control WHERE endchar < limiter LIMIT 100
)
SELECT 
    seq AS sequence,
    book AS book,
    chapter AS chapter,
    (SELECT fromvorm FROM CTEXT WHERE ctext.book = control.book AND ctext.chapter = control.chapter) AS fromvorm,
    (SELECT tovorm FROM CTEXT WHERE ctext.book = control.book AND ctext.chapter = control.chapter) AS tovorm,
    (SELECT substr(ctext.data,control.startchar,control.endchar) FROM CTEXT  WHERE ctext.book = control.book AND ctext.chapter = control.chapter ) AS chunk
FROM control  ORDER BY sequence;

与测试数据相似（接近 7Mb）

在此处输入图像描述

Android 之类的（这只有 1 本书/章节需要循环书，如果更多则需要循环章节）。没有测试所以需要测试。

    List<CText> cTexts = new ArrayList<>();
    Cursor cursor;
    StringBuilder data = new StringBuilder();
    String[] whereArgs = new String[]{"" + bookNumber, "" + chapterNumber, "" + bookNumber, "" + chapterNumber};
    cursor = db.rawQuery(
            "WITH RECURSIVE control(startchar,endchar,limiter,book,chapter,seq) AS " +
                    "(" +
                    "SELECT 1,256 * 1024, " +

                    "(SELECT max(length(data)) " +
                    "FROM ctext " +
                    "WHERE book = ? " +
                    "AND chapter = ?)," +

                    "?," +
                    "?," +
                    "0 " +
                    "UNION ALL SELECT " +
                    "endchar + 1," +
                    "endchar + (256 * 1024), " +
                    "limiter," +
                    "book," +
                    "chapter," +
                    "seq +1 " +
                    "FROM control " +
                    "WHERE endchar < limiter LIMIT 100" +
                    ") SELECT " +
                    "   seq AS sequence," +
                    "   book AS book," +
                    "   chapter AS chapter," +
                    "   (SELECT fromvorm " +
                    "       FROM CTEXT" +
                    "        WHERE ctext.book = control.book " +
                    "       AND ctext.chapter = control.chapter" +
                    "   ) AS fromvorm," +

                    "   (SELECT tovorm " +
                    "       FROM CTEXT " +
                    "       WHERE ctext.book = control.book " +
                    "       AND ctext.chapter = control.chapter" +
                    "   ) AS tovorm,    " +

                    "   (SELECT substr(ctext.data,control.startchar,control.endchar) " +
                    "       FROM CTEXT " +
                    "       WHERE ctext.book = control.book " +
                    "       AND ctext.chapter = control.chapter " +
                    "   ) AS chunk " +

                    "FROM control;"
            , whereArgs
    );
    while (cursor.moveToNext()) {
        data.append(cursor.getString(cursor.getColumnIndex("chunk")));
    }
    if (cursor.moveToFirst()) {
        cTexts.add(new CText(cursor.getInt(0),
                cursor.getInt(1), cursor.getInt(2),
                cursor.getInt(3), data.toString()));
    }

推荐阅读