spring Data jpa坑爹Bug

一 起源

由于新公司项目是使用 spring data jpa ,因此就使用一下这个jpa. 在测试写原生SQL，启动项目（springboot项目），报了一个错了，下面张贴报错内容

Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'userService': Unsatisfied dependency expressed through field 'userRepository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userRepository': Invocation of init method failed; nested exception is org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting and/or pagination in method public abstract org.springframework.data.domain.Page cn.magicwindow.demo.dao.UserRepository.findByAppName(java.lang.String,org.springframework.data.domain.Pageable)
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:592) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:370) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1219) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:551) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:207) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1131) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1059) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:589) ~[spring-beans-4.3.4.RELEASE.jar:4.3.4.RELEASE]
	... 19 common frames omitted

一看这个就知道jpa 这个方法写的有问题。下面粘贴UserRepository方法代码：

@Query(value="SELECT * FROM USER u LEFT JOIN app a ON u.app_id = a.id WHERE a.`name` = ?1 "
           ,countQuery="SELECT count(*) FROM USER u LEFT JOIN app a ON u.app_id = a.id WHERE a.`name` = ?1", nativeQuery = true)
    Page<User>  findByAppName(String appName , Pageable pageable);

调用UserRepository方法的代码：

public Page<User> findByCondition(){

        Pageable pageable = new PageRequest(1,10);
        Page<User>  user =  userRepository.findByAppName("ELEX_APP",pageable);

        return user;
    }

二 解决方法

这个代码我也没有发现什么错误啊。然后通过各种查询找到了答案 需要sql加上

ORDER BY ?#{#pageable}

正确的代码

@Query(value="SELECT * FROM USER u LEFT JOIN app a ON u.app_id = a.id WHERE a.`name` = ?1 ORDER BY ?#{#pageable}"
           ,countQuery="SELECT count(*) FROM USER u LEFT JOIN app a ON u.app_id = a.id WHERE a.`name` = ?1", nativeQuery = true)
    Page<User>  findByAppName(String appName , Pageable pageable);

三 源代码解释

对于这个问题很无奈 所以找源码

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

从源码上一目了然 如果SQL中没有 #pageable 而 参数中有 Pageable就会报错。

是不是很坑爹 毕竟jpa案例就没有啊。https://docs.spring.io/spring-data/jpa/docs/1.10.2.RELEASE/reference/html/

案例截图：