SQL Spring Data JPA Java - 从查询中获取最后 10 条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24068884/
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
Spring Data JPA Java - get Last 10 records from query
提问by headlikearock
Is there a way to retrieve the last X number of results from a query?
有没有办法从查询中检索最后 X 个结果？
For example - If want the first ten results, I see that example here works: setMaxResults for Spring-Data-JPA annotation?
例如 - 如果想要前十个结果，我看到这里的例子有效：setMaxResults for Spring-Data-JPA annotation?
public interface UserRepository extends Repository<User, Long> {
List<User> findByUsername(String username, Pageable pageable);
}
//and then I could call it like this
Pageable topTen = new PageRequest(0, 10);
List<User> result = repository.findByUsername("Matthews", topTen);
But how do I get the LAST ten records?
但是我如何获得最后十个记录？
The only way I could think of doing it would be to flip the order in the query (findByUsernameDesc, assuming original results were ascending) and then iterate through the list backwards so I can process it in the order I wanted (ascending).
我能想到的唯一方法是翻转查询中的顺序（findByUsernameDesc，假设原始结果是升序），然后向后遍历列表，以便我可以按照我想要的顺序（升序）处理它。
That seems like an ugly way to do it. Is there a way to have the query give me the last X results in the order I want?
这似乎是一种丑陋的方法。有没有办法让查询按照我想要的顺序给我最后的 X 个结果？
采纳答案by Vlad Mihalcea
The question is how efficient it would be such on option, especially against large data sets.
问题是它在选项上的效率有多高，尤其是针对大型数据集。
I would go for a descending index, which I could query using the maxResult support, as you already figured it out.
我会选择一个降序索引，我可以使用 maxResult 支持进行查询，正如您已经知道的那样。
This is no way a hack. If you were to match 100M results only to get the last X ones, this method would yield the best results.
这绝不是一个黑客。如果只匹配 100M 个结果以获得最后 X 个结果，则此方法将产生最佳结果。
回答by Rafael Rocha
Spring Data JPA 1.7 has introduced 'top' and 'first' as keywords in derived queries so now we can do like:
Spring Data JPA 1.7 在派生查询中引入了 'top' 和 'first' 作为关键字，所以现在我们可以这样做：
public interface UserRepository extends Repository<User, Long> {
List<User> findFirst10ByUsername(String username);
}
Check it out - Spring Data Release Train Evans Goes GA
回答by leandrobh
PageRequest could be extremely useful for it. There are many options for to construct the PageRequest.
PageRequest 可能对它非常有用。有许多选项可用于构造 PageRequest。
So, an option possible is:
所以，一个可能的选择是：
Pageable topTen = new PageRequest(0, 10, Direction.ASC, "username");
List<User> result = repository.findByUsername("Matthews", topTen);
I also use without parameters (conditions about object).
我也使用不带参数（关于对象的条件）。
@Query(value="select p from Person p")
public List<Person> findWithPageable(Pageable pageable);
And call:
并调用：
repository.findWithPageable(new PageRequest(0, 10, Direction.DESC, "id"));
回答by freemanpolys
You can do this to get the 10 last records filter by the Username:
您可以这样做以按用户名获取最后 10 条记录过滤器：
List<User> findFirst10ByUsernameOrderByIdDesc(String username);
回答by bradleyfitz
The only other way I can think of doing it (other than by ordering first), would be to get the total number of records and then use set max results and set first results.
我能想到的唯一另一种方法（除了先排序）是获取记录总数，然后使用 set max results 和 set first results。
I don't know the spring-data-jpa, but it should have a set firstresult on the query as well.
我不知道 spring-data-jpa，但它也应该在查询中设置一个 firstresult。
// this is a hibernate query... but should be simple
Integer count = (Integer) session.createQuery("select count(*) from ....").uniqueResult();
//Get the last ten
Pageable topTen = new PageRequest(count - 10, count);
List<User> result = repository.findByUsername("Matthews", topTen);
