Spring Data and Native Query with pagination
<p>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 <code>org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException</code> at startup. </p>

<h2><strong>UPDATE</strong>: 20180306 This issue is now fixed in <a href="https://jira.spring.io/browse/DATAJPA/fixforversion/16650/?selectedTab=com.atlassian.jira.plugins.jira-development-integration-plugin:release-report-tabpanel" rel="noreferrer">Spring 2.0.4</a> For those still interested or stuck with older versions check the related answers and comments for workarounds.</h2>

<p>According to <a href="http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query" rel="noreferrer">Example 50 at Using @Query from spring-data documentation</a> this is possible specifying the query itself and a countQuery, like this:</p>

<pre><code>public interface UserRepository extends JpaRepository&lt;User, Long&gt; {
  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page&lt;User&gt; findByLastname(String lastname, Pageable pageable);
}
</code></pre>

<p>Out of curiosity, In <code>NativeJpaQuery</code> class I can see that it contains the following code to check if it's a valid jpa query:</p>

<pre><code>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 &amp;&amp; !containsPageableOrSortInQueryExpression) {
       throw new InvalidJpaQueryMethodException("Cannot use native queries with dynamic sorting and/or pagination in method " + method);
   }
}
</code></pre>

<p>My query contains a <code>Pageable</code> parameter, so <code>hasPagingOrSortingParameter</code> is <code>true</code>, but it's also looking for a <code>#pageable</code> or <code>#sort</code> sequence inside the <code>queryString</code>, which I do not provide.</p>

<p>I've tried adding <code>#pageable</code> (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.</p>

<p>Funny thing is that, if I manually change <code>containsPageableOrSortInQueryExpression</code> from <code>false</code> to <code>true</code> while running, the query works fine so I don't know why it's checking for that string to be at my <code>queryString</code> and I don't know how to provide it.</p>

<p>Any help would be much appreciated.</p>

<p><em>Update 01/30/2018</em>
It seems that developers at spring-data project are working on a fix for this issue with a <a href="https://github.com/spring-projects/spring-data-jpa/pull/246" rel="noreferrer">PR by Jens Schauder</a></p>