In a web project, using latest spring-data (1.10.2) with a MySQL 5.6 database, I'm trying to use a native query with pagination but I'm experiencing an org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException
at startup.
According to Example 50 at Using @Query from spring-data documentation this is possible specifying the query itself and a countQuery, like this:
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);
}
Out of curiosity, In NativeJpaQuery
class I can see that it contains the following code to check if it's a valid jpa query:
public NativeJpaQuery(JpaQueryMethod method, EntityManager em, String queryString, EvaluationContextProvider evaluationContextProvider, SpelExpressionParser parser) {
super(method, em, queryString, evaluationContextProvider, parser);
JpaParameters parameters = method.getParameters();
boolean hasPagingOrSortingParameter = parameters.hasPageableParameter() || parameters.hasSortParameter();
boolean containsPageableOrSortInQueryExpression = queryString.contains("#pageable") || queryString.contains("#sort");
if(hasPagingOrSortingParameter && !containsPageableOrSortInQueryExpression) {
throw new InvalidJpaQueryMethodException("Cannot use native queries with dynamic sorting and/or pagination in method " + method);
}
}
My query contains a Pageable
parameter, so hasPagingOrSortingParameter
is true
, but it's also looking for a #pageable
or #sort
sequence inside the queryString
, which I do not provide.
I've tried adding #pageable
(it's a comment) at the end of my query, which makes validation to pass but then, it fails at execution saying that the query expects one additional parameter: 3 instead of 2.
Funny thing is that, if I manually change containsPageableOrSortInQueryExpression
from false
to true
while running, the query works fine so I don't know why it's checking for that string to be at my queryString
and I don't know how to provide it.
Any help would be much appreciated.
Update 01/30/2018 It seems that developers at spring-data project are working on a fix for this issue with a PR by Jens Schauder
My apologies in advance, this is pretty much summing up the original question and the comment from Janar, however...
I run into the same problem: I found the Example 50 of Spring Data as the solution for my need of having a native query with pagination but Spring was complaining on startup that I could not use pagination with native queries.
I just wanted to report that I managed to run successfully the native query I needed, using pagination, with the following code:
@Query(value="SELECT a.* "
+ "FROM author a left outer join mappable_natural_person p on a.id = p.provenance_id "
+ "WHERE p.update_time is null OR (p.provenance_name='biblio_db' and a.update_time>p.update_time)"
+ "ORDER BY a.id \n#pageable\n",
/*countQuery="SELECT count(a.*) "
+ "FROM author a left outer join mappable_natural_person p on a.id = p.provenance_id "
+ "WHERE p.update_time is null OR (p.provenance_name='biblio_db' and a.update_time>p.update_time) \n#pageable\n",*/
nativeQuery=true)
public List<Author> findAuthorsUpdatedAndNew(Pageable pageable);
The countQuery (that is commented out in the code block) is needed to use Page<Author>
as the return type of the query, the newlines around the "#pageable" comment are needed to avoid the runtime error on the number of expected parameters (workaround of the workaround). I hope this bug will be fixed soon...