java 限制房间数据库中的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46193356/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Limit the amount of rows in a room database
提问by x10sion
How can I limit the amount of rows in an Android Room Database by removing the oldest item in the row and inserting the newest one?
如何通过删除行中最旧的项目并插入最新的项目来限制 Android Room 数据库中的行数？
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.
编辑：我想将数据库表的最大行数限制为 20。如果达到该限制，我们将删除最旧的项目并通过将当前行数保持为 20 来插入新项目。
采纳答案by Kunu
I think you can insert the data into your table then remove all the rows except last 20 (limit)
我认为您可以将数据插入表中，然后删除除最后 20 行（限制）之外的所有行
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)
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
在这种情况下，id 是设置为自动递增的主键。如果按日期存储它们，也可以使用日期作为键
回答by Md. Sajedul Karim
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.
如果 loadAllUsersByPage(2,0)它将从表中返回前 2 行。
if loadAllUsersByPage(2,1)it will return 2nd and 3rd rows from table.
如果 loadAllUsersByPage(2,1)它将从表中返回第 2 行和第 3 行。
but if loadAllUsersByPage(-1,10)then it will serve first 10 rows from table.
但如果那样的 loadAllUsersByPage(-1,10)话，它将为表中的前 10 行提供服务。
回答by M. Prokhorov
Assuming:
假设：
Your table is
你的桌子是
create table 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:
使用数据库触发器：
create trigger
if not exists -- 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 insert
begin
delete
from example_table
where ts in (
select ts
from example_table
order by ts
limit -1 -- we don't want to limit how many rows we want to delete
offset 25 -- 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:
要在 Java 中启用触发器：
Simple Android, where you can override SQLiteOpenHelper:
简单的 Android，您可以在其中覆盖SQLiteOpenHelper：
public class DataBaseSchemaHelper extends SQLiteOpenHelper {
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(<trigger string from above>);
}
}
Android Room version:
安卓房间版本：
public MyDatabase extends RoomDatabase {
@Override
public void init(DatabaseConfiguration _config) {
super.init(_config);
getOpenHelper().getWritableDatabase().execSQL(<trigger string from above>);
}
}
回答by Amit Vaghela
Follow this steps :
请按照以下步骤操作：
1> get count of rows of that table
1> 获取该表的行数
your_count = SELECT count( * ) FROM table_name;
2> if count is >(greater than) 20 than to get oldest record
2> 如果计数 >（大于）20 则获取最旧的记录
SELECT *
FROM table_name
ORDER BY entry_Date ASC
LIMIT 1;
3> now delete these selected records
3> 现在删除这些选中的记录
4> insert new datas
4> 插入新数据
NOTE : if you are inserting multiple entries than put this in loop
注意：如果您要插入多个条目而不是将其放入循环中
