androidsqldatabasesublistsugarorm

Android Sugar ORM select sublist from database

I am using Sugar ORM, which is actually a library for SQL database operation. My database has over 4000 records, and I would like to retrieve 500 records from it, say the 501th to 1000th record.

The extraction of 500 records are as follows:

db_record_listing = Records.findWithQuery(Records.class, "Select * FROM Records ORDER BY recordId ASC WHERE qref_canton BETWEEN '" + s_start + "' AND '" + s_end + "'");

However, the error logcat is as follows:

Logcat:

11-25 23:29:35.639: E/AndroidRuntime(28801): Caused by: android.database.sqlite.SQLiteException: near "WHERE": syntax error (code 1): , while compiling: Select * FROM Records ORDER BY recordId ASC WHERE qref_canton BETWEEN '1' AND '500'
11-25 23:29:35.639: E/AndroidRuntime(28801):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
11-25 23:29:35.639: E/AndroidRuntime(28801):    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
11-25 23:29:35.639: E/AndroidRuntime(28801):    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
11-25 23:29:35.639: E/AndroidRuntime(28801):    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
11-25 23:29:35.639: E/AndroidRuntime(28801):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
11-25 23:29:35.639: E/AndroidRuntime(28801):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
11-25 23:29:35.639: E/AndroidRuntime(28801):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
11-25 23:29:35.639: E/AndroidRuntime(28801):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
11-25 23:29:35.639: E/AndroidRuntime(28801):    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)

Question:

  1. What is the problem for the above? I found that if I remove the ORDER BY the code can be run properly (despite it is very slow).
  2. Will the system still run through all the records and only pick those with recordId within the range and thereby very slow? If yes, would there be a quicker way where system would only retrieve those 500 records and no more?
Solution 
  • Select * FROM Records WHERE qref_canton BETWEEN '1' AND '500' ORDER BY recordId ASC