限制Spring数据JPA中的结果数 [英] Limiting the number of results in Spring data JPA

问题描述

我的数据库中有20多个表，我需要一种方法来从表中获取所有未删除的行.因此，我编写了以下界面:

I have 20+ tables in my DB and I need to have a method to fetch all non-deleted rows from the tables. Therefore, I wrote the following interface:

@NoRepositoryBean
public interface CommonRepositry<T, ID extends Serializable> extends CrudRepository<T, UUID>{
    List<T> findByIsDeleted(boolean isDeleted);
}

我所有的模型存储库都扩展了 CommonRepositry ，我可以访问所有未删除的行:

All my model repositories extend this CommonRepositry and I'm able to access all the non-deleted rows:

public interface ModelNameRepository extends CommonRepositry<ModelName, UUID> { 
// sevral spring JPA methods 

随着表大小的增加，我想对方法返回的结果数设置可调整的限制.这样做有两种方法，但我俩都遇到了问题:

As the tables are growing in size, I want to put an adjustable limit on the number of results that the method returns. There are two ways to do so and I'm having issues with both of them:

来自文档，这就是我应该做的事情:

From the docs, this is how I am supposed to do it:

List<User> findTop1000ByIsDeleted(boolean isDeleted);

它只返回前1000行.

It only returns me the top 1000 rows.

问题::我想将此 1000 外在化到属性文件中，以便在必须更改的情况下仅在单个位置进行操作.

PROBLEM: I want to externalise this 1000 to the properties file so that if I have to change, I do it only at a single place.

另一种方法是使用 @Query 批注并编写SQL查询:

Another way to do that would be to use @Query annotation and write the SQL query:

@Query(value = "SELECT * FROM table_name" + " WHERE is_deleted = :isDeleted LIMIT 1000;", nativeQuery = true)
List<T> findByIsDeleted(@Param("isDeleted")boolean isDeleted);

问题:如何获取table_name?我的模型类名称到表名称具有从CamelCase到camel_case的简单映射.但是如何从 T 获得模型类名称?

PROBLEM: how do I get the table_name? My model class names to table-names have simple CamelCase to camel_case mapping. But how do I get the model class name from T?

推荐答案

问题1:您可以向查询传递第二个参数，称为Pageable.

Problem 1: You can pass a second parameter to your query, called Pageable.

List< T>findByIsDeleted(boolean isDeleted，Pageable pageable);

此 Pageable 是一个接口，但是您可以为其使用 PageRequest 实现.创建 PageRequest 需要两件事，即页码和大小(即要获取多少行).当然，在您的情况下，页码为0，并且应该通过从属性文件中读取值来设置大小.

This Pageable is an interface, but you can use the PageRequest implementation for it. It requires two things to create a PageRequest, the page number and the size (meaning that how many rows you want to fetch). Of course, in your case the page number is 0 and the size should be set by reading the value from the properties file.

问题2:您可以将表名外部化为仅包含常量值的类.您也可以在实体类的 @Table 批注中以及此处使用这些值.

Problem 2: You can externalize the table name to a class which only consists of constant values. You can use these values in your @Table annotation on your entity class and here as well.

更多阅读: http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.special-parameters

这篇关于限制Spring数据JPA中的结果数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！