无法从 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: img2019-03-05 11:17:10.447 8872-8872/aaa.so54989458 D/AndroidRuntime: Shutting down VM2019-03-05 11:17:10.455 8872-8872/aaa.so54989458 E/AndroidRuntime: FATAL EXCEPTION: main&nbsp; &nbsp; Process: aaa.so54989458, PID: 8872&nbsp; &nbsp; 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=?&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:903)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:514)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1408)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1255)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1126)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1332)&nbsp; &nbsp; &nbsp; &nbsp; at aaa.so54989458.DBHelper.selecionarLivro(DBHelper.java:70)&nbsp; &nbsp; &nbsp; &nbsp; at aaa.so54989458.MainActivity$2.onItemClick(MainActivity.java:78)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.AdapterView.performItemClick(AdapterView.java:318)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.AbsListView.performItemClick(AbsListView.java:1159)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.AbsListView$PerformClick.run(AbsListView.java:3136)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.AbsListView$3.run(AbsListView.java:4052)&nbsp; &nbsp; &nbsp; &nbsp; at android.os.Handler.handleCallback(Handler.java:873)&nbsp; &nbsp; &nbsp; &nbsp; at android.os.Handler.dispatchMessage(Handler.java:99)&nbsp; &nbsp; &nbsp; &nbsp; at android.os.Looper.loop(Looper.java:193)&nbsp; &nbsp; &nbsp; &nbsp; at android.app.ActivityThread.main(ActivityThread.java:6669)&nbsp; &nbsp; &nbsp; &nbsp; at java.lang.reflect.Method.invoke(Native Method)&nbsp; &nbsp; &nbsp; &nbsp; at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)&nbsp; &nbsp; &nbsp; &nbsp; at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)请注意，如果您使用的是 Android Studio，则它会突出显示aaa.so54989458.DBHelper.selecionarLivro(DBHelper.java:70) 处的行，并且通过单击链接，它会将您带到代码中引发异常的代码.（请注意，发生错误的方法是您的selecionarLivro方法，其他方面如类和包名称将根据您的代码）。修复问题 1上述问题的解决方法是执行以下操作之一：-删除/清除应用程序的数据（通过设置）。卸载应用程序然后，假设代码正确，重新运行 App。问题 2 - 图片太大。图像不是存储在数据库中的理想候选者，而是存储指示要使用的图像的内容（例如图像的路径）更好。图像本身由字节组成，通常是非常大的字节数。尽管 SQLite 可以容纳相当大的图像。将数据提取到光标中的 android SDK 实现更具限制性。Cursor 实际上是一个缓冲区，它保存整个数据的部分快照。底层快照（光标窗口）在 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 bytes2019-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 bytes2019-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 bytes2019-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 livros2019-03-05 11:33:46.887 9059-9059/aaa.so54989458 D/AndroidRuntime: Shutting down VM2019-03-05 11:33:46.895 9059-9059/aaa.so54989458 E/AndroidRuntime: FATAL EXCEPTION: main&nbsp; &nbsp; Process: aaa.so54989458, PID: 9059&nbsp; &nbsp; android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=3, totalRows=4&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:859)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:836)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:157)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.sqlite.SQLiteCursor.onMove(SQLiteCursor.java:128)&nbsp; &nbsp; &nbsp; &nbsp; at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:237)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.CursorAdapter.getItemId(CursorAdapter.java:258)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.AbsListView$RecycleBin.retrieveFromScrap(AbsListView.java:7105)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.AbsListView$RecycleBin.getScrapView(AbsListView.java:6816)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.AbsListView.obtainView(AbsListView.java:2365)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.ListView.measureHeightOfChildren(ListView.java:1408)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.ListView.onMeasure(ListView.java:1315)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.View.measure(View.java:23169)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1535)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.LinearLayout.measureVertical(LinearLayout.java:825)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.LinearLayout.onMeasure(LinearLayout.java:704)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.View.measure(View.java:23169)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)&nbsp; &nbsp; &nbsp; &nbsp; at android.support.v7.widget.ContentFrameLayout.onMeasure(ContentFrameLayout.java:143)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.View.measure(View.java:23169)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)&nbsp; &nbsp; &nbsp; &nbsp; at android.support.v7.widget.ActionBarOverlayLayout.onMeasure(ActionBarOverlayLayout.java:401)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.View.measure(View.java:23169)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.View.measure(View.java:23169)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1535)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.LinearLayout.measureVertical(LinearLayout.java:825)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.LinearLayout.onMeasure(LinearLayout.java:704)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.View.measure(View.java:23169)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6749)&nbsp; &nbsp; &nbsp; &nbsp; at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)&nbsp; &nbsp; &nbsp; &nbsp; at com.android.internal.policy.DecorView.onMeasure(DecorView.java:716)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.View.measure(View.java:23169)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.ViewRootImpl.performMeasure(ViewRootImpl.java:2718)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.ViewRootImpl.measureHierarchy(ViewRootImpl.java:1572)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.ViewRootImpl.performTraversals(ViewRootImpl.java:1855)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.ViewRootImpl.doTraversal(ViewRootImpl.java:1460)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.ViewRootImpl$TraversalRunnable.run(ViewRootImpl.java:7183)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.Choreographer$CallbackRecord.run(Choreographer.java:949)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.Choreographer.doCallbacks(Choreographer.java:761)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.Choreographer.doFrame(Choreographer.java:696)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.Choreographer$FrameDisplayEventReceiver.run(Choreographer.java:935)&nbsp; &nbsp; &nbsp; &nbsp; at android.os.Handler.handleCallback(Handler.java:873)&nbsp; &nbsp; &nbsp; &nbsp; at android.os.Handler.dispatchMessage(Handler.java:99)&nbsp; &nbsp; &nbsp; &nbsp; at android.os.Looper.loop(Looper.java:193)&nbsp; &nbsp; &nbsp; &nbsp; at android.app.ActivityThread.main(ActivityThread.java:6669)&nbsp; &nbsp; &nbsp; &nbsp; at java.lang.reflect.Method.invoke(Native Method)&nbsp; &nbsp; &nbsp; &nbsp; at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)&nbsp; &nbsp; &nbsp; &nbsp; at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)请注意，前三行是图像可以放入的前几行（基本上，消息是说抱歉，由于 CursorWindow 未满，无法获得更多行。）修复问题 2除非您可以将图像的大小减小到可接受/可管理的大小，否则没有简单的解决方法。推荐的方法是将图像存储在别处，然后将路径或其一部分存储在数据库中。这是一个示例，它使用将大于 100k 的图像的路径存储在数据库中但将图像存储在数据库中（如果 100k 或更少）的组合（这可能更有效）。