Android How To Query Huge Database In Android (cursor Size Is Limited To 1mb)

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 dat

Solution 1:

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

intlimit=0;
   while (limit + 100 < numberOfRows) {
       //Compose the statementStringstatement="SELECT * FROM Table ORDER someField LIMIT '"+ limit+"', 100";
       //Execute the queryCursorcursor= myDataBase.rawQuery(statement, null);
       while (cursor.moveToNext()) {
           Productproduct=newProduct();
           product.setAllValuesFromCursor(cursor);
           productsArrayList.add(product);
      }
      cursor.close();
      limit += 100;
 }

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

 while (cursor.moveToNext()) {
     Productproduct=newProduct();
     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

Solution 2:

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.

Solution 3:

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.