Geek Answers Handbook

Android SQLite crashed after 170 rows

how to change this logic to work with more than 170 lines.

// Getting All test public List<Test> getAllTests(String str) { List<Test> testList = new ArrayList<Test>(); // Select All Query String selectQuery = "SELECT * FROM " + TABLE_TESTS; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (cursor.moveToFirst()) { do { //select rows by input string if(cursor.getString(1).equals(str)){ Test test = new Test(); test.setId(Integer.parseInt(cursor.getString(0))); test.setTest(cursor.getString(1)); test .setResult(Integer.parseInt(cursor.getString(2))); // Adding test to list testList.add(test); } } while (cursor.moveToNext()); } //close database db.close(); //return list data return testList; }

I want to select all rows by input line. This logic works fine with 150 lines, but after 160 runs slowly and fails on 170 lines

+5
source share
3 answers

How to change this logic to work with more than 170 lines?

 // Getting All test public List<Test> getAllTests(String str) { List<Test> testList = new ArrayList<Test>(); // Select All Query //String selectQuery = "SELECT * FROM " + TABLE_TESTS; String selectQuery = "SELECT id,result FROM " + TABLE_TESTS + " where name ='" + str + "'"; // Now you are saving memory of one column. SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (cursor.moveToFirst()) { Test test = new Test(); // moved outside loop to prevent creating new object every time. do { //select rows by input string //if(cursor.getString(1).equals(str)){ // No need for if Codition any more test.setId(Integer.parseInt(cursor.getString(0))); //test.setTest(cursor.getString(1)); test.setTest(str); test .setResult(Integer.parseInt(cursor.getString(2))); // Adding test to list testList.add(test); //} } while (cursor.moveToNext()); } //close database db.close(); //return list data return testList; }
+4
source

EDIT: WRONG METHOD

Use

 String selectQuery = "SELECT * FROM " + TABLE_TESTS + " WHERE " + your_id + " > " + String.valueOf(last_id) + " LIMIT 150";

as a query structure, and then track the last row id like this.

 int last_id; do { //select rows by input string if(cursor.getString(1).equals(str)){ Test test = new Test(); last_id = Integer.parseInt(cursor.getString(0)); test.setId(last_id); ... } } while (cursor.moveToNext());

every time the loop ends, just request your db again; the rows will be extracted from the next one you need, because the last_id variable changes dynamically according to your progress.

+2
source

You can try using the same code differently as follows

 // using sql query Differently (SQliteDatabase) db.query( "TABLE_TESTS" / table name /, new String[] { "id", "result" } / columns names /, "name = ?" / where or selection /, new String[] { str } / selectionArgs ie value to replace ? /, null / groupBy /, null / having /, null / orderBy / );

Another approach might be to use LIMIT and OFFSET to retrieve data in parts to improve performance.

 // using LIMIT AND OFFSET public List<Test> getAllTests(String str) { List<Test> testList = new ArrayList<Test>(); // Select All Query Integer count = 0; String countQuery = "SELECT count(id) FROM " + TABLE_TESTS; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(countQuery, null); if (cursor.moveToFirst()) { count= c.getCount(); } db.close(); int MAX_LENGTH = 150; if ( count > 0 ) { int total_length = ( count / MAX_LENGTH ) + 1; for ( int i=0; i<total_length; i++) { String selectQuery = "SELECT id,result FROM " + TABLE_TESTS + " LIMIT " + MAX_LENGTH + " OFFSET " + (i*MAX_LENGTH) ; db = this.getWritableDatabase(); cursor = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (cursor.moveToFirst()) { Test test = new Test(); // moved outside loop to prevent creating new object every time. do { //select rows by input string if(cursor.getString(1).equals(str)){ test.setId(Integer.parseInt(cursor.getString(0))); //test.setTest(cursor.getString(1)); test.setTest(str); test .setResult(Integer.parseInt(cursor.getString(2))); // Adding test to list testList.add(test); } } while (cursor.moveToNext()); } //close database db.close(); } } //return list data return testList; }
+1
source

More articles:


All Articles