Limit the number of rows in a room database

Question

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.

Answer 1

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.

Answer 2

I think you can insert the data into your table then remove all the rows except last 20 (limit)

To delete you can use the following query

DELETE FROM tableName where id NOT IN (SELECT id from tableName ORDER BY id DESC LIMIT 20)

In this case, id is the primary key which is set to auto increment. You can use date as key as well if you are storing them by date