psqlException：error：语法错误位于或接近“.”带有排序查询的Spring可分页对象

Question

我有一个带有spring分页的本地查询

 @Query(value = "with RECURSIVE tbsons as(select * from customers where id=:companyId union all select customers.* from customers  join tbsons on customers.parent_id=tbsons.id) SELECT * FROM  tbsons where deleted = false order by ?#{#pageable}",
            countQuery = "with RECURSIVE tbsons as(select * from customers where id=:companyId union all select customers.* from customers  join tbsons on customers.parent_id=tbsons.id) SELECT COUNT (*) FROM  tbsons where deleted = false",
            nativeQuery = true)
        Page<Company> getCompaniesByparent(Pageable pageable, @Param("companyId") Long companyId);

我得到了例外

org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
      Position: 214
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)
        at org.hibernate.loader.Loader.doQuery(Loader.java:919)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
        at org.hibernate.loader.Loader.doList(Loader.java:2617)
        at org.hibernate.loader.Loader.doList(Loader.java:2600)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429)
        at org.hibernate.loader.Loader.list(Loader.java:2424)
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336)
        at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1967)
        at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322)
        at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125)
        at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
        at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)
        at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:195)
        at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:82)
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116)
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106)
        at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:482)
        at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
        at com.sun.proxy.$Proxy138.getCompaniesByparent(Unknown Source)
        at com.blupa.hb.restapi.company.CompanyController.listAll(CompanyController.java:65)
        at com.blupa.hb.restapi.company.CompanyController$$FastClassBySpringCGLIB$$80ce5f2b.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:69)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
        at com.blupa.hb.restapi.company.CompanyController$$EnhancerBySpringCGLIB$$bc1170e7.listAll(<generated>)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)

with RECURSIVE tbsons as(select * from customers where id=? union all select customers.* from customers  join tbsons on customers.parent_id=tbsons.id) SELECT * FROM  tbsons where deleted = false order by ?, join.name asc limit ?

Answer 1

我像这样更改了查询，它开始工作了。

@Query(value = "select * from customers cu where cu.deleted = false and cu.id in (with RECURSIVE tbsons as(select * from customers where id=:companyId union all select customers.* from customers  join tbsons on customers.parent_id=tbsons.id) SELECT id FROM  tbsons where deleted = false) order by ?#{#pageable}",
        countQuery = "select COUNT (*) from customers cu where cu.deleted = false and cu.id in(with RECURSIVE tbsons as(select * from customers where id=:companyId union all select customers.* from customers  join tbsons on customers.parent_id=tbsons.id) SELECT id FROM  tbsons where deleted = false)",
        nativeQuery = true)
    Page<Company> getCompaniesByparent(Pageable pageable, @Param("companyId") Long companyId);