安卓系统如何在安卓系统中查询庞大的数据库(游标大小被限制在1MB)?[英] Android how to query huge database in android (cursor size is limited to 1MB)

本文是小编为大家收集整理的关于安卓系统如何在安卓系统中查询庞大的数据库(游标大小被限制在1MB)?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正致力于在某个时间内开发一个必须查询的应用程序,一个带有超过4k行的数据库,并且每行都有90个字段(字符串).问题是,如果我select *从数据库中,我的光标变得非常大(超过4MB). Android中的光标限制为1MB.

如何解决这一点,或者最优雅的方法解决方案是什么? 可以在较小的块中拆分数据库并将其查询出来?

推荐答案

我找到了一种方法来处理这个,我想与需要它的所有人分享.

   int limit = 0;
   while (limit + 100 < numberOfRows) {
       //Compose the statement
       String statement = "SELECT * FROM Table ORDER someField LIMIT '"+ limit+"', 100";
       //Execute the query
       Cursor cursor = myDataBase.rawQuery(statement, null);
       while (cursor.moveToNext()) {
           Product product = new Product();
           product.setAllValuesFromCursor(cursor);
           productsArrayList.add(product);
      }
      cursor.close();
      limit += 100;
 }

 //Compose the statement
 String statement = "SELECT * FROM Table ORDER someField LIMIT '"+  (numberOfRows - limit)+"', 100";
 //Execute the query
 Cursor cursor = myDataBase.rawQuery(statement, null);

 while (cursor.moveToNext()) {
     Product product = new Product();
     product.setAllValuesFromCursor(cursor);
     productsArrayList.add(product);
 }
 cursor.close();

主要思想是拆分数据,因此您可以使用光标,因为它应该使用它.如果您有索引表,它在2秒下工作为5k行.

谢谢, arkde

其他推荐答案

您是否需要同时需要所有这些行?你能在零件中取代它们吗?这个问题已经询问了几次: Android sqlite和巨大的数据集

这里的一个建议:如果您有90个您需要修改的字段,请将它们分成10个不同的视图.在每个视图上都有一个左箭头和右箭头,因此您可以跨越屏幕水平遍历.因此,每个视图都将显示9个字段.或者一些这样的策略.基本上这些是除列名称之外的所有视图,所以您不必要修改太多代码.

其他推荐答案

通常,您永远不会选择*.对于开始,每行将具有唯一标识符,并且您的用户将只想仅选择某些行和列 - 即它们可以在Android屏幕上看到的内容.没有出现粗鲁这是一个非常基本的问题.您只返回要在手机上显示该屏幕的列和行 - 否则您将消耗不必要的电池寿命,传输从未成为DiaPlayed数据.标准方法是使用参数化存储过程.谷歌参数化存储过程并做一点读写 - 通过 - 您无法更新任何表未界,您将返回该表的唯一行标识符.

本文地址:https://www.itbaoku.cn/post/102344.html

问题描述

I'm working to develop an application that has to query at some time, a database with over 4k rows, and each row has 90 fields (Strings). The problem is that if I select * from database, my cursor gets really big (over 4MB). And the cursor in android is limited to 1MB.

How can I solve this, or what's the most elegant method to workaround this? It is possible to split database in smaller chunks and query them out?

推荐答案

I found a way to handle this and I want to share with all who need it.

   int limit = 0;
   while (limit + 100 < numberOfRows) {
       //Compose the statement
       String statement = "SELECT * FROM Table ORDER someField LIMIT '"+ limit+"', 100";
       //Execute the query
       Cursor cursor = myDataBase.rawQuery(statement, null);
       while (cursor.moveToNext()) {
           Product product = new Product();
           product.setAllValuesFromCursor(cursor);
           productsArrayList.add(product);
      }
      cursor.close();
      limit += 100;
 }

 //Compose the statement
 String statement = "SELECT * FROM Table ORDER someField LIMIT '"+  (numberOfRows - limit)+"', 100";
 //Execute the query
 Cursor cursor = myDataBase.rawQuery(statement, null);

 while (cursor.moveToNext()) {
     Product product = new Product();
     product.setAllValuesFromCursor(cursor);
     productsArrayList.add(product);
 }
 cursor.close();

The main idea is to split your data, so you can use the cursor as it should be used. It's working under 2 s for 5k rows if you have indexed table.

Thanks, Arkde

其他推荐答案

Do you need all these rows at the same time? Can you fetch them in parts? This question has been asked several times: Android SQLite and huge data sets

Here's one more suggestion: If you have 90 fields that you need to modify, split them into 10 different views. On each view have a left arrow and right arrow so you can horizontally traverse across screens. Hence each view will show 9 fields. Or some strategy like that. Essentially these are all the same views except for column names so you shouldn't have to modify much code.

其他推荐答案

Well as a rule you never do select *. For a start each row will have a unique identifier, and your user will want to select only certain rows and columns - ie what they can see on an android screen. Without appearing to be rude this is a pretty basic question. You only return the columns and rows you want to display for that screen on the phone - otherwise you consume unnecssary battery life transfering never to be diaplayed data. the standard approach is to used parameterised stored procedures. Google parameterised stored procedures and do a little reading - by the by - you cant update any table unlees you return the unique row identifier for that table.