Limit The Number Of Rows In A Room Database

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 a

Solution 1:

Here is sample solution:

Query is :

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

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.

Solution 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

Solution 3:

Assuming:

Your table is

createtable example_table (
      ts timestamp,
      uid number(19),
      some_other_field varchar(64)
    );

And you don't want to care about running some query manually.

Use database triggers:

createtrigger
      if notexists-- I don't actually know if you DB will support this line.-- Might want to remove it if it's not.
    example_table_limiter
    on example_table
    after insertbegindeletefrom example_table
      where ts in (
        select ts
        from example_table
        orderby ts 
        limit -1-- we don't want to limit how many rows we want to deleteoffset25-- but we want to offset query result so it leaves 25 rows in table
      );
    end;

"Offset without limit" syntax is inspired by this answer.

To enable your trigger in java:

Simple Android, where you can override SQLiteOpenHelper:

publicclassDataBaseSchemaHelperextendsSQLiteOpenHelper {
      @OverridepublicvoidonCreate(SQLiteDatabase db) {     
        db.execSQL(<trigger string from above>);
      }
    }

Android Room version:

publicclassMyDatabaseextendsRoomDatabase {
       @Overridepublicvoidinit(DatabaseConfiguration _config) {
         super.init(_config);
         getOpenHelper().getWritableDatabase().execSQL(<trigger string from above>);
       }
     }

Solution 4:

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")fungetAllData(): List<MyEntityClass>

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

Solution 5:

Follow this steps :

1> get count of rows of that table

your_count = SELECT count( * ) FROM table_name;

2> if count is >(greater than) 20 than to get oldest record

SELECT*FROM table_name
 ORDERBY entry_Date ASC
 LIMIT 1;

3> now delete these selected records

4> insert new datas

NOTE : if you are inserting multiple entries than put this in loop