java - 无法从 SQLite 数据库中选择图像 (Android)

Question

我正在尝试从 SQLite 数据库检索图像，但 CURSOR 始终得不到结果。如果我尝试在没有图像列的情况下进行选择，它就可以正常工作。

 Livro selecionarLivro(String titulo, String autor)

{
    SQLiteDatabase db = this.getReadableDatabase();

    String[] dados = new String[2];
    dados[0] = titulo;
    dados[1] = autor;

    Livro livro1 = null;

    Cursor cursor;
    String[] campos =  {COLUNA_CODIGO, COLUNA_TITULO, COLUNA_AUTOR, COLUNA_IMG};

    cursor = db.query(TABELA_LIVROS, campos, COLUNA_TITULO + "=? AND " + COLUNA_AUTOR + "=?", dados, null, null, null, null);

    if(cursor.moveToNext()){
        livro1 = new Livro();
        livro1.setTitulo(cursor.getString(cursor.getColumnIndex(COLUNA_TITULO)));
        livro1.setAutor(cursor.getString(cursor.getColumnIndex(COLUNA_AUTOR)));
        livro1.setImgFile(cursor.getBlob(cursor.getColumnIndex(COLUNA_IMG)));
    }
    db.close();

    return livro1;
}

它返回“null”，因为 CURSOR 没有结果。 INSERT 方法正在运行。

Answer 1

有两个可能的问题都会导致异常，可以通过查看日志来确定。

问题 1 - 未找到列

第一个常见问题是通过修改 onCreate 方法使用的代码将列添加到表中。

• 例如自应用运行以来，您可能已更改表创建 SQL 以包含定义列 COLUNA_IMG。

onCreate 方法仅在数据库首次创建时运行一次。它不会在每次运行应用程序或每次创建 DatabaseHelper 类的实例时运行。

可以通过查看日志来确定此问题，在这种情况下，您可能会发现类似于以下内容的内容:-

2019-03-05 11:17:10.446 8872-8872/aaa.so54989458 E/SQLiteLog: (1) no such column: img
2019-03-05 11:17:10.447 8872-8872/aaa.so54989458 D/AndroidRuntime: Shutting down VM
2019-03-05 11:17:10.455 8872-8872/aaa.so54989458 E/AndroidRuntime: FATAL EXCEPTION: main
    Process: aaa.so54989458, PID: 8872
    android.database.sqlite.SQLiteException: no such column: img (code 1 SQLITE_ERROR): , while compiling: SELECT codigo, titulo, autor, img FROM livros WHERE titulo=? AND autor=?
        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 android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1332)
        at aaa.so54989458.DBHelper.selecionarLivro(DBHelper.java:70)
        at aaa.so54989458.MainActivity$2.onItemClick(MainActivity.java:78)
        at android.widget.AdapterView.performItemClick(AdapterView.java:318)
        at android.widget.AbsListView.performItemClick(AbsListView.java:1159)
        at android.widget.AbsListView$PerformClick.run(AbsListView.java:3136)
        at android.widget.AbsListView$3.run(AbsListView.java:4052)
        at android.os.Handler.handleCallback(Handler.java:873)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        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)

• 请注意，如果您使用的是 Android Studio，则会突出显示 aaa.so54989458.DBHelper.selecionarLivro(DBHelper.java:70) 行，并且通过单击该链接，您将转到代码中引发异常的代码。
  • (请注意，发生错误的方法是您的 selecionarLivro 方法，其他方面(例如类和包名称)将根据您的代码进行。

修复问题 1

解决上述问题的方法是执行以下操作之一:-

1. 删除/清除应用的数据(通过“设置”)。
2. 卸载应用

然后，假设代码正确，重新运行应用程序。

问题 2 - 图片太大。

图像不是存储在数据库中的理想选择，最好存储指示要使用的图像的内容(例如图像的路径)。图像本身由字节组成，通常是非常大量的字节。虽然 SQLite 可以容纳相当大的图像。

将数据提取到游标中的 android SDK 实现更具限制性。

游标实际上是一个缓冲区，保存整个数据的部分快照。底层快照(游标窗口)在 Android SDK 中限制为 2M，并且至少必须能够容纳单行。如果您的图像大于 2M，那么您肯定会失败。如果图像大于 0.5M，您很可能会遇到失败或问题。

即使图像已成功存储在数据库中，图像对于光标窗口来说太大的错误示例是:-

2019-03-05 11:33:46.878 9059-9059/aaa.so54989458 W/CursorWindow: Window is full: requested allocation 2097152 bytes, free space 1789213 bytes, window size 2097152 bytes
2019-03-05 11:33:46.886 9059-9059/aaa.so54989458 W/CursorWindow: Window is full: requested allocation 2097152 bytes, free space 1983933 bytes, window size 2097152 bytes
2019-03-05 11:33:46.886 9059-9059/aaa.so54989458 W/CursorWindow: Window is full: requested allocation 2097152 bytes, free space 2096653 bytes, window size 2097152 bytes
2019-03-05 11:33:46.887 9059-9059/aaa.so54989458 E/SQLiteQuery: exception: Row too big to fit into CursorWindow requiredPos=3, totalRows=4; query: SELECT * FROM livros
2019-03-05 11:33:46.887 9059-9059/aaa.so54989458 D/AndroidRuntime: Shutting down VM
2019-03-05 11:33:46.895 9059-9059/aaa.so54989458 E/AndroidRuntime: FATAL EXCEPTION: main
    Process: aaa.so54989458, PID: 9059
    android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=3, totalRows=4
        at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:859)
        at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:836)
        at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
        at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:157)
        at android.database.sqlite.SQLiteCursor.onMove(SQLiteCursor.java:128)
        at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:237)
        at android.widget.CursorAdapter.getItemId(CursorAdapter.java:258)
        at android.widget.AbsListView$RecycleBin.retrieveFromScrap(AbsListView.java:7105)
        at android.widget.AbsListView$RecycleBin.getScrapView(AbsListView.java:6816)
        at android.widget.AbsListView.obtainView(AbsListView.java:2365)
        at android.widget.ListView.measureHeightOfChildren(ListView.java:1408)
        at android.widget.ListView.onMeasure(ListView.java:1315)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)
        at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1535)
        at android.widget.LinearLayout.measureVertical(LinearLayout.java:825)
        at android.widget.LinearLayout.onMeasure(LinearLayout.java:704)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)
        at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)
        at android.support.v7.widget.ContentFrameLayout.onMeasure(ContentFrameLayout.java:143)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)
        at android.support.v7.widget.ActionBarOverlayLayout.onMeasure(ActionBarOverlayLayout.java:401)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)
        at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)
        at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1535)
        at android.widget.LinearLayout.measureVertical(LinearLayout.java:825)
        at android.widget.LinearLayout.onMeasure(LinearLayout.java:704)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)
        at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)
        at com.android.internal.policy.DecorView.onMeasure(DecorView.java:716)
        at android.view.View.measure(View.java:23169)
        at android.view.ViewRootImpl.performMeasure(ViewRootImpl.java:2718)
        at android.view.ViewRootImpl.measureHierarchy(ViewRootImpl.java:1572)
        at android.view.ViewRootImpl.performTraversals(ViewRootImpl.java:1855)
        at android.view.ViewRootImpl.doTraversal(ViewRootImpl.java:1460)
        at android.view.ViewRootImpl$TraversalRunnable.run(ViewRootImpl.java:7183)
        at android.view.Choreographer$CallbackRecord.run(Choreographer.java:949)
        at android.view.Choreographer.doCallbacks(Choreographer.java:761)
        at android.view.Choreographer.doFrame(Choreographer.java:696)
        at android.view.Choreographer$FrameDisplayEventReceiver.run(Choreographer.java:935)
        at android.os.Handler.handleCallback(Handler.java:873)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        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)

• 请注意，前三行适用于图像可以容纳的前几行(基本上，这些消息是说抱歉，因为 CursorWindow 未满，无法获取更多行。)

修复问题 2

除非您可以将图像的大小减小到可接受/可管理的大小，否则没有简单的修复方法。

推荐的方法是将图像存储在其他地方，然后将路径或其部分存储在数据库中。

• Here's一个示例，使用以下组合:在数据库中存储大于 100k 的图像路径，但在数据库中存储图像(如果 100k 或更少)(这可能会更有效)。