CODEWITHSUNDEEP
javaspringjpaspring-dataspring-data-jpa
smallufo
smallufo

Reputation: 11796

setMaxResults for Spring-Data-JPA annotation?

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

for example, my code:

public interface UserRepository extends CrudRepository<User , Long>
{
  @Query(value="From User u where u.otherObj = ?1 ")
  public User findByOtherObj(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 ?

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

If I implement this feature manually:

public class UserRepositoryImpl implements UserRepository

I have to implement tons of predefined methods in CrudRepository, this would be terrible.

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

Upvotes: 163

Views: 224241

Answers (10)

hurelhuyag
hurelhuyag

Reputation: 1753

I can't believe after 12 years. Sping-Data-JPA still has no actual solution. I ended up writing a custom Repository implementation.

public class CustomJpaRepository <Entity, Id> extends JpaRepositoryImplementation<Entity, Id> {
  Optional<Entity> findFirst(String query, Map<String, Object> parameters, String entityGraph);
}

public class CustomExtendedJpaRepository<E, I> extends SimpleJpaRepository<E, I> implements ExtendedJpaRepository<E, I>{

    private final EntityManager entityManager;
    private final Class<E> domainClass;

    public CustomExtendedJpaRepository(JpaEntityInformation<E, I> entityInformation, EntityManager em) {
        super(entityInformation, em);
        this.entityManager = em;
        this.domainClass = entityInformation.getJavaType();
    }

    public CustomExtendedJpaRepository(Class<E> domainClass, EntityManager em) {
        super(domainClass, em);
        this.entityManager = em;
        this.domainClass = domainClass;
    }
    
    @Override
    public Optional<E> findFirst(String query, Map<String, Object> parameters, String entityGraph) {
        var tq = entityManager.createQuery(query, domainClass);
        for (var p : parameters.entrySet()) {
            tq.setParameter(p.getKey(), p.getValue());
        }
        if (entityGraph != null) {
            tq.setHint(AvailableHints.HINT_SPEC_FETCH_GRAPH, entityManager.getEntityGraph(entityGraph));
        }
        tq.setMaxResults(1);
        return Optional.ofNullable(tq.getSingleResult());
    }
}

To enable custom implementation

@EnableJpaRepositories(
        basePackages = "your.company.persistence",
        repositoryBaseClass = CustomExtendedJpaRepository.class
)

Upvotes: 0

Liutas
Liutas

Reputation: 5783

Use

Pageable pageable = PageRequest.of(0,1);
Page<Transaction> transactionsPage = transactionRepository.findAll(specification, pageable);
return transactionsPage.getContent();

Upvotes: -2

Abbas Hosseini
Abbas Hosseini

Reputation: 747

new PageRequest(0,10) doesn't work in newer Spring versions (I am using 2.2.1.RELEASE). Basically, the constructor got an additional parameter as Sort type. Moreover, the constructor is protected so you have to either use one of its child classes or call its of static method:

PageRequest.of(0, 10, Sort.sort(User.class).by(User::getFirstName).ascending()))

You can also omit the use of Sort parameter and implicitly user the default sort (sort by pk, etc.):

PageRequest.of(0, 10)

Your function declaration should be something like this:

List<User> findByUsername(String username, Pageable pageable)

and the function will be:

userRepository.findByUsername("Abbas", PageRequest.of(0,10, Sort.sort(User.class).by(User::getLastName).ascending());

Upvotes: 10

Oliver Drotbohm
Oliver Drotbohm

Reputation: 83051

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 this:

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.

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

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:

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.

Upvotes: 211

Luis Camargo
Luis Camargo

Reputation: 91

If your class @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 return a List<Transaction>.

Upvotes: 5

Grigory Kislin
Grigory Kislin

Reputation: 17980

Best choice for me is native query:

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

Upvotes: 15

Wim Deblauwe
Wim Deblauwe

Reputation: 26848

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:

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

}

Upvotes: 120

kraken
kraken

Reputation: 493

It's also posible using @QueryHints. Example bellow 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();

Upvotes: 6

ncaralicea
ncaralicea

Reputation: 923

There is a way you can provide the equivalent of "a setMaxResults(n) by annotation" like in the following:

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:

new PageRequest(0, 10)

Upvotes: 31

azerafati
azerafati

Reputation: 18583

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,

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

Upvotes: 28

Related Questions