Limit the number of rows in a room database

user_4578531 0
Viewed 30101

How can I limit the number of rows in an Android room database by removing the oldest item in the row and inserting the newest one?

I am guessing its a standard query when adding an item to the database?

EDIT: I want to limit a database table to have a max row count of say 20. If that limit is reached, we remove the oldest item and insert the new one by keeping the current row count to 20.

android-room android java
5 Answers

Here is sample solution:

Query is :

@Query("SELECT * FROM user LIMIT :limit OFFSET :offset")
    User[] loadAllUsersByPage(int limit,int offset);

Here, it will give a list of user based on limit and offset.

if loadAllUsersByPage(2,0) it will return first 2 rows from table.

if loadAllUsersByPage(2,1) it will return 2nd and 3rd rows from table.

but if loadAllUsersByPage(-1,10) then it will serve first 10 rows from table.

user_30739450

You can limit columns/rows by doing this: this query will return the new data and remove old data when its reach its limit.

Explanation:

  1. First query is select all data order by descending
  2. Second query is remove data from columns/rows id > 20

If you want your table only have 20 row then set the OFFSET to 20, the LIMIT is represent how many rows inserted & deleted at once.

In my example I remove 1 row (the oldest/last row) when the user inputs 1 new data


  @Query("SELECT * FROM my_table ORDER BY timeStamp DESC")
  fun getAllData(): List<MyEntityClass>

  @Query("DELETE FROM my_table WHERE id IN (SELECT id FROM my_table ORDER BY timeStamp DESC LIMIT 1 OFFSET 20)")
  fun removeOldData()
user_156927000
Related