I am trying to incorporate Spring-Data-JPA to my project. One thing confuses me is how to achieve setMaxResults(n) by annotation ?
我正在尝试将Spring-Data-JPA合并到我的项目中。让我困惑的一件事是如何通过注释实现setMaxResults(n)?
for example , my code:
例如,我的代码:
public interface UserRepository extends CrudRepository<User , Long>
{
@Query(value="From User u where u.otherObj = ?1 ")
public User findByOhterObj(OtherObj otherObj);
}
I only need to return one (and only one)
User from otherObj , but I cannot find a way to annotate the maxResults ... Can somebody give me a hint ?
我只需要从otherObj返回一个(并且只有一个)用户,但我找不到一种方法来注释maxResults ...有人可以给我一个提示吗?
(mysql complains :
(mysql抱怨:
com.mysql.jdbc.JDBC4PreparedStatement@5add5415: select user0_.id as id100_, user0_.created as created100_ from User user0_ where user0_.id=2 limit ** NOT SPECIFIED **
WARN util.JDBCExceptionReporter - SQL Error: 0, SQLState: 07001
ERROR util.JDBCExceptionReporter - No value specified for parameter 2
)
)
I found a link : https://jira.springsource.org/browse/DATAJPA-147 , I tried but failed. It seems not possible now ? Why such a important feature not built in Spring-Data ?
我找到了一个链接:https://jira.springsource.org/browse/DATAJPA-147,我试过但失败了。现在似乎不可能?为什么Spring-Data中没有内置这么重要的功能?
If I implement this feature manually :
如果我手动实现此功能:
public class UserRepositoryImpl implements UserRepository
I have to implement tons of predefined methods in CrudRepository
, this will be terrible.
我必须在CrudRepository中实现大量的预定义方法,这将是非常糟糕的。
environments : spring-3.1 , spring-data-jpa-1.0.3.RELEASE.jar , spring-data-commons-core-1.1.0.RELEASE.jar
环境:spring-3.1,spring-data-jpa-1.0.3.RELEASE.jar,spring-data-commons-core-1.1.0.RELEASE.jar
7 个解决方案
#1
140
As of Spring Data JPA 1.7.0 (Evans release train).
… you can use the newly introduced Top
and First
keywords that allow you to define query methods like these:
...您可以使用新引入的Top和First关键字,这些关键字允许您定义如下的查询方法:
findTop10ByLastnameOrderByFirstnameAsc(String lastname);
Spring Data will automatically limit the results to the number you defined (defaulting to 1 if omitted). Note, that the ordering of the results becomes relevant here (either through an OrderBy
clause as seen in the example or by handing a Sort
parameter into the method). Read more on that in the blog post covering new features of the Spring Data Evans release train or in the documentation.
Spring Data会自动将结果限制为您定义的数字(如果省略则默认为1)。注意,结果的排序在这里变得相关(通过示例中看到的OrderBy子句或通过将Sort参数传递给方法)。在博客文章中阅读更多内容,内容涵盖Spring Data Evans发布系列的新功能或文档。
For previous versions
To retrieve only slices of data Spring Data uses the pagination abstraction which comes with a Pageable
interface on the requesting side as well as a Page
abstraction on the result side of things. So you could start with
为了只检索数据片段,Spring Data使用分页抽象,它在请求端带有Pageable接口,在结果端有一个Page抽象。所以你可以先开始吧
public interface UserRepository extends Repository<User, Long> {
List<User> findByUsername(String username, Pageable pageable);
}
and use it like this:
并像这样使用它:
Pageable topTen = new PageRequest(0, 10);
List<User> result = repository.findByUsername("Matthews", topTen);
If you need to know the context of the result (which page is it actually? is it the first one? how many are there in total?) use Page
as return type:
如果你需要知道结果的上下文(它实际上是哪一页?它是第一个?总共有多少?)使用Page作为返回类型:
public interface UserRepository extends Repository<User, Long> {
Page<User> findByUsername(String username, Pageable pageable);
}
The client code can then do something like this:
然后客户端代码可以执行以下操作:
Pageable topTen = new PageRequest(0, 10);
Page<User> result = repository.findByUsername("Matthews", topTen);
Assert.assertThat(result.isFirstPage(), is(true));
Not that we will trigger a count projection of the actual query to be executed in case you use Page
as return type as we need to find out how many elements there are in total to calculate the metadata. Beyond that be sure you actually equip the PageRequest
with sorting information to get stable results. Otherwise you might trigger the query twice and get different results even without the data having changed underneath.
并不是我们将触发要执行的实际查询的计数投影,以防您使用Page作为返回类型,因为我们需要找出总计有多少元素来计算元数据。除此之外,请确保您实际上为PageRequest配备了排序信息以获得稳定的结果。否则,您可能会触发查询两次并获得不同的结果,即使数据没有在下面更改。
#2
63
If you are using Java 8 and Spring Data 1.7.0, you can use default methods if you want to combine a @Query
annotation with setting maximum results:
如果您使用的是Java 8和Spring Data 1.7.0,则可以使用默认方法,如果要将@Query注释与设置最大结果组合使用:
public interface UserRepository extends PagingAndSortingRepository<User,Long> {
@Query("from User u where ...")
List<User> findAllUsersWhereFoo(@Param("foo") Foo foo, Pageable pageable);
default List<User> findTop10UsersWhereFoo(Foo foo) {
return findAllUsersWhereFoo(foo, new PageRequest(0,10));
}
}
#3
23
There is a way you can provide the equivalent of "a setMaxResults(n) by annotation" like in the following:
有一种方法可以提供相当于“a by注释的setMaxResults(n)”,如下所示:
public interface ISomething extends JpaRepository<XYZ, Long>
{
@Query("FROM XYZ a WHERE a.eventDateTime < :before ORDER BY a.eventDateTime DESC")
List<XYZ> findXYZRecords(@Param("before") Date before, Pageable pageable);
}
This should do the trick, when a pageable is sent as parameter. For instance to fetch the first 10 records you need to set pageable to this value:
当一个pageable作为参数发送时,这应该可以解决问题。例如,要获取前10个记录,您需要将可分页设置为此值:
new PageRequest(0, 10)
#4
18
Use Spring Data Evans (1.7.0 RELEASE)
the new release of Spring Data JPA with another list of modules together called Evans has the feature of using keywords Top20
and First
to limit the query result,
新版本的Spring Data JPA和另一个名为Evans的模块列表具有使用关键字Top20和First来限制查询结果的功能,
so you could now write
所以你现在可以写了
List<User> findTop20ByLastname(String lastname, Sort sort);
or
要么
List<User> findTop20ByLastnameOrderByIdDesc(String lastname);
or for a single result
或者单个结果
List<User> findFirstByLastnameOrderByIdDesc(String lastname);
#5
7
Best choice for me is native query:
对我来说最好的选择是原生查询:
@Query(value="SELECT * FROM users WHERE other_obj = ?1 LIMIT 1", nativeQuery = true)
User findByOhterObj(OtherObj otherObj);
#6
5
It's also posible using @QueryHints. Example bellow uses org.eclipse.persistence.config.QueryHints#JDBC_MAX_ROWS
它也可以使用@QueryHints。示例bellow使用org.eclipse.persistence.config.QueryHints#JDBC_MAX_ROWS
@Query("SELECT u FROM User u WHERE .....")
@QueryHints(@QueryHint(name = JDBC_MAX_ROWS, value = "1"))
Voter findUser();
#7
2
If your class @Repository
extends JpaRepository
you can use the example below.
如果你的类@Repository扩展了JpaRepository,你可以使用下面的例子。
int limited = 100;
Pageable pageable = new PageRequest(0,limited);
Page<Transaction> transactionsPage = transactionRepository.findAll(specification, pageable);
return transactionsPage.getContent();
getContent return a List<Transaction>
.
getContent返回List
#1
140
As of Spring Data JPA 1.7.0 (Evans release train).
… you can use the newly introduced Top
and First
keywords that allow you to define query methods like these:
...您可以使用新引入的Top和First关键字,这些关键字允许您定义如下的查询方法:
findTop10ByLastnameOrderByFirstnameAsc(String lastname);
Spring Data will automatically limit the results to the number you defined (defaulting to 1 if omitted). Note, that the ordering of the results becomes relevant here (either through an OrderBy
clause as seen in the example or by handing a Sort
parameter into the method). Read more on that in the blog post covering new features of the Spring Data Evans release train or in the documentation.
Spring Data会自动将结果限制为您定义的数字(如果省略则默认为1)。注意,结果的排序在这里变得相关(通过示例中看到的OrderBy子句或通过将Sort参数传递给方法)。在博客文章中阅读更多内容,内容涵盖Spring Data Evans发布系列的新功能或文档。
For previous versions
To retrieve only slices of data Spring Data uses the pagination abstraction which comes with a Pageable
interface on the requesting side as well as a Page
abstraction on the result side of things. So you could start with
为了只检索数据片段,Spring Data使用分页抽象,它在请求端带有Pageable接口,在结果端有一个Page抽象。所以你可以先开始吧
public interface UserRepository extends Repository<User, Long> {
List<User> findByUsername(String username, Pageable pageable);
}
and use it like this:
并像这样使用它:
Pageable topTen = new PageRequest(0, 10);
List<User> result = repository.findByUsername("Matthews", topTen);
If you need to know the context of the result (which page is it actually? is it the first one? how many are there in total?) use Page
as return type:
如果你需要知道结果的上下文(它实际上是哪一页?它是第一个?总共有多少?)使用Page作为返回类型:
public interface UserRepository extends Repository<User, Long> {
Page<User> findByUsername(String username, Pageable pageable);
}
The client code can then do something like this:
然后客户端代码可以执行以下操作:
Pageable topTen = new PageRequest(0, 10);
Page<User> result = repository.findByUsername("Matthews", topTen);
Assert.assertThat(result.isFirstPage(), is(true));
Not that we will trigger a count projection of the actual query to be executed in case you use Page
as return type as we need to find out how many elements there are in total to calculate the metadata. Beyond that be sure you actually equip the PageRequest
with sorting information to get stable results. Otherwise you might trigger the query twice and get different results even without the data having changed underneath.
并不是我们将触发要执行的实际查询的计数投影,以防您使用Page作为返回类型,因为我们需要找出总计有多少元素来计算元数据。除此之外,请确保您实际上为PageRequest配备了排序信息以获得稳定的结果。否则,您可能会触发查询两次并获得不同的结果,即使数据没有在下面更改。
#2
63
If you are using Java 8 and Spring Data 1.7.0, you can use default methods if you want to combine a @Query
annotation with setting maximum results:
如果您使用的是Java 8和Spring Data 1.7.0,则可以使用默认方法,如果要将@Query注释与设置最大结果组合使用:
public interface UserRepository extends PagingAndSortingRepository<User,Long> {
@Query("from User u where ...")
List<User> findAllUsersWhereFoo(@Param("foo") Foo foo, Pageable pageable);
default List<User> findTop10UsersWhereFoo(Foo foo) {
return findAllUsersWhereFoo(foo, new PageRequest(0,10));
}
}
#3
23
There is a way you can provide the equivalent of "a setMaxResults(n) by annotation" like in the following:
有一种方法可以提供相当于“a by注释的setMaxResults(n)”,如下所示:
public interface ISomething extends JpaRepository<XYZ, Long>
{
@Query("FROM XYZ a WHERE a.eventDateTime < :before ORDER BY a.eventDateTime DESC")
List<XYZ> findXYZRecords(@Param("before") Date before, Pageable pageable);
}
This should do the trick, when a pageable is sent as parameter. For instance to fetch the first 10 records you need to set pageable to this value:
当一个pageable作为参数发送时,这应该可以解决问题。例如,要获取前10个记录,您需要将可分页设置为此值:
new PageRequest(0, 10)
#4
18
Use Spring Data Evans (1.7.0 RELEASE)
the new release of Spring Data JPA with another list of modules together called Evans has the feature of using keywords Top20
and First
to limit the query result,
新版本的Spring Data JPA和另一个名为Evans的模块列表具有使用关键字Top20和First来限制查询结果的功能,
so you could now write
所以你现在可以写了
List<User> findTop20ByLastname(String lastname, Sort sort);
or
要么
List<User> findTop20ByLastnameOrderByIdDesc(String lastname);
or for a single result
或者单个结果
List<User> findFirstByLastnameOrderByIdDesc(String lastname);
#5
7
Best choice for me is native query:
对我来说最好的选择是原生查询:
@Query(value="SELECT * FROM users WHERE other_obj = ?1 LIMIT 1", nativeQuery = true)
User findByOhterObj(OtherObj otherObj);
#6
5
It's also posible using @QueryHints. Example bellow uses org.eclipse.persistence.config.QueryHints#JDBC_MAX_ROWS
它也可以使用@QueryHints。示例bellow使用org.eclipse.persistence.config.QueryHints#JDBC_MAX_ROWS
@Query("SELECT u FROM User u WHERE .....")
@QueryHints(@QueryHint(name = JDBC_MAX_ROWS, value = "1"))
Voter findUser();
#7
2
If your class @Repository
extends JpaRepository
you can use the example below.
如果你的类@Repository扩展了JpaRepository,你可以使用下面的例子。
int limited = 100;
Pageable pageable = new PageRequest(0,limited);
Page<Transaction> transactionsPage = transactionRepository.findAll(specification, pageable);
return transactionsPage.getContent();
getContent return a List<Transaction>
.
getContent返回List