SetMaxResults for Spring -Data-JPA annotation?

Question

I am trying to include Spring-Data-JPA in my project. One thing that confuses me is how do I achieve setMaxResults (n) using annotation?

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 maxResults. Can someone give me a hint?

(MySQL complains:

 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 the link: https://jira.springsource.org/browse/DATAJPA-147 , I tried, but failed. It seems now impossible? Why is such an important function not built into Spring-Data?

If I implement this function manually:

 public class UserRepositoryImpl implements UserRepository 

I have to implement a lot of predefined methods in CrudRepository , that would be horrible.

environment: spring-3.1, spring-data-jpa-1.0.3.RELEASE.jar, spring-data-commons-core-1.1.0.RELEASE.jar

Answer 1

As of Spring Data JPA 1.7.0 (Evans release).

You can use the recently introduced Top and First keywords that allow you to define query methods as follows:

 findTop10ByLastnameOrderByFirstnameAsc(String lastname); 

Spring Data will automatically limit the results to the number you specify (the default is 1 if not specified). Note that here the ordering of the results becomes relevant (either using the OrderBy clause, as shown in the example, or by passing the Sort parameter to the method). Read more about this in the Spring Data Evans new release features blog or documentation .

For previous versions

To extract only pieces of data, Spring Data uses the page numbering abstraction that comes with the Pageable interface on the requestor, as well as the Page abstraction on the results side. So you could start with

 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 really? Is it the first? How many?), Use Page as the return type:

 public interface UserRepository extends Repository<User, Long> { Page<User> findByUsername(String username, Pageable pageable); } 

Then the client code can do something like this:

 Pageable topTen = new PageRequest(0, 10); Page<User> result = repository.findByUsername("Matthews", topTen); Assert.assertThat(result.isFirstPage(), is(true)); 

It’s not that we started the projection of counting the actual query that will be executed if you use Page as the return type, since we need to find out how many elements are in total to calculate metadata. Also, make sure that you really have equipped PageRequest with PageRequest information to get consistent results. Otherwise, you can run the query twice and get other results, even if the data has not changed.

Answer 2

If you use Java 8 and Spring Data 1.7.0, you can use the default methods if you want to combine the @Query annotation with setting the maximum results:

 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)); } } 

Answer 3

There is a way that you can provide the equivalent of “setMaxResults (n) by annotation,” as shown below:

 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 the page is sent as a parameter. For example, to get the first 10 entries, you need to set the value of the page for this value:

 new PageRequest(0, 10) 

Answer 4

Use Spring Data Evans (1.7.0 RELEASE)

a new version of Spring Data JPA with a different list of modules, called Evans , has the function of using the Top20 and First keywords to limit the query result,

so you can write

 List<User> findTop20ByLastname(String lastname, Sort sort); 

or

 List<User> findTop20ByLastnameOrderByIdDesc(String lastname); 

or for one result

 List<User> findFirstByLastnameOrderByIdDesc(String lastname); 

Answer 5

The best choice for me is my own request:

 @Query(value="SELECT * FROM users WHERE other_obj = ?1 LIMIT 1", nativeQuery = true) User findByOhterObj(OtherObj otherObj); 

Answer 6

This is also possible with @QueryHints. The bellow example uses 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(); 

Answer 7

If your @Repository extends JpaRepository , you can use the example below.

 int limited = 100; Pageable pageable = new PageRequest(0,limited); Page<Transaction> transactionsPage = transactionRepository.findAll(specification, pageable); return transactionsPage.getContent(); 

getContent returns a List<Transaction> .