Problem :
I am hitting a api in my application which send's date in pages(page size = 20).So currently i am trying to save data in my local db using ROOM which is a basic insert operation.And when the application is not connected to network i want the app to get data from db in form of pages.
Solution(what i have tried):
Inside DAO layer i using
@Query("SELECT * FROM ( SELECT ROW_NUMBER () OVER ( ORDER BY rating ) RowNum,* FROM movie ) t WHERE t.RowNum > 0 AND t.RowNum <= 20")
suspend fun getPagedMovie(): List<MovieEntity>
i am getting two compilation error:
< compound operator >, FROM, GROUP, LIMIT, ORDER, WHERE or comma expected,
&
cannot relove t.RowNum
So, as to solve this i tried use @RawQuery
val query = SimpleSQLiteQuery(
"SELECT * FROM ( SELECT ROW_NUMBER () OVER ( ORDER BY rating ) RowNum,* FROM movie ) t WHERE t.RowNum > 0 AND t.RowNum <= 20"
)
return movieDao.getPagedMovie(query).map { it.toGeneralMovie() }
&
@RawQuery
suspend fun getPagedMovie(query: SupportSQLiteQuery): List<MovieEntity>
In above case i am getting below in error log:
2020-04-18 16:23:33.014 21854-21854/? E/m.andor.watchi: Unknown bits set in runtime_flags: 0x8000 2020-04-18 16:23:34.158 21854-21891/com.andor.watchit E/[Gralloc-ERROR]: sanitize_formats:482 Format [id:0x4] which doesn't support afbc should not have bpp defined 2020-04-18 16:23:34.415 21854-21979/com.andor.watchit E/SQLiteLog: (1) near "(": syntax error 2020-04-18 16:23:34.456 21854-21978/com.andor.watchit E/AndroidRuntime: FATAL EXCEPTION: DefaultDispatcher-worker-3 Process: com.andor.watchit, PID: 21854 android.database.sqlite.SQLiteException: near "(": syntax error (code 1 SQLITE_ERROR[1]): , while compiling: SELECT * FROM ( SELECT ROW_NUMBER () OVER ( ORDER BY rating ) RowNum,* FROM movie ) t WHERE t.RowNum > 0 AND t.RowNum <= 20 at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1372) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:811) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:62) at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37) at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1959) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1934) at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.query(FrameworkSQLiteDatabase.java:161) at androidx.room.RoomDatabase.query(RoomDatabase.java:328) at androidx.room.util.DBUtil.query(DBUtil.java:83) at com.andor.watchit.core.framework.db.MovieDao_Impl$8.call(MovieDao_Impl.java:237) at com.andor.watchit.core.framework.db.MovieDao_Impl$8.call(MovieDao_Impl.java:234) at androidx.room.CoroutinesRoom$Companion$execute$2.invokeSuspend(CoroutinesRoom.kt:54) at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33) at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:56) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641) at java.lang.Thread.run(Thread.java:919)
I would like to know a solution to implement pagination using ROOM ?
P.S. : my query is just to fetch 1st 20 element's right now i.e 1st page i can make it dynamic query later on and on DB Browser it work.On side note I don't want to load the whole result and then make pages.
i found the solution to above problem by using LIMIT and OFFSET
@Query("SELECT * FROM movie ORDER BY rating DESC LIMIT :pageSize OFFSET (:pageNumber-1)*:pageSize")
suspend fun getPagedMovie(pageNumber: Int, pageSize: Int): List<MovieEntity>
Wish this help's other people also.
And thanks to @dev-masih also for giving a reference in comment's.