3

I was wondering if is possible to execute jpql query by a spring jpa repository and be able to use paging as sort feature as it can be donde with Example and Specification. I would like to do something like:

findAll(String jpql, Pageable pageable)

QueryByExampleExecutor interface i.e. declare:

findAll(Example<S> example, Pageable pageable);

JpaSpecificationExecutor interface declare:

Page<T> findAll(Specification<T> spec, Pageable pageable);
Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
Juan Rada
  • 3,513
  • 1
  • 26
  • 26

1 Answers1

0

As per Spring Data documentation it should be possible even with native queries. Have a look at example 51:

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

Sorting seems also possible as per documentation:

public interface UserRepository extends JpaRepository<User, Long> {

  @Query("select u from User u where u.lastname like ?1%")
  List<User> findByAndSort(String lastname, Sort sort);

  @Query("select u.id, LENGTH(u.firstname) as fn_len from User u where u.lastname like ?1%")
  List<Object[]> findByAsArrayAndSort(String lastname, Sort sort);
}
Alex Roig
  • 1,534
  • 12
  • 18
  • thanks for answer but what I was looking for is to create query dynamically and call repository in code, your alternatives required query already predefined – Juan Rada Jun 30 '17 at 18:46
  • Writing JPQL on the fly, can be buggy. JPQL is compiled at runtime but only at the classloading phase, so if you have an error your repository will not load. That's a good security measure. You can try to use Criteria Query instead: https://stackoverflow.com/questions/28874135/dynamic-spring-data-jpa-repository-query-with-arbitrary-and-clauses – Alex Roig Jul 03 '17 at 09:20