Аннотации Spring Boot Query с nativeQuery не работают в Postgresql

Среда разработки.

OS: windows 8 IDE : Eclipse Luna with JAVA 8 Postgresql : 9.3.4 in OPENBSD 5.5 

Другое Все запросы JPA, такие как findFirst3ByTextOrderByTextAsc, работают нормально. Но не в NativeQuery, особенно с INTERVAL

вместилище

 @RepositoryRestResource(collectionResourceRel = "Nodes", path = "Nodes") public interface NodeRepository extends JpaRepository<Node, Integer>, CrudRepository<Node, Integer>, PagingAndSortingRepository<Node, Integer>, Repository<Node, Integer> { //1st INTERVAL is native keyword in POSTGRESQL @Query(value = "SELECT n.* from nodes n WHERE n.node_id = 10510 AND n.last_good_ping > CURRENT_DATE - INTERVAL '1 day' ", nativeQuery = true) List<Node> getLast24HoursByNodeId(); //2nd @Query(value = "SELECT n.* from nodes n WHERE n.node_id = 10510 AND n.last_good_ping > CURRENT_DATE - INTERVAL :interval ", nativeQuery = true) List<Node> getByNodeIdAndInterval(@Param("interval") String interval); //3rd @Query(value = "SELECT n.* from nodes n WHERE n.node_id = 10510 AND n.last_good_ping > CURRENT_DATE - INTERVAL ?1", nativeQuery = true) List<Node> getByNodeIdAndInterval(String interval); } 

1-й запрос работает в поиске, но не во втором, третьем запросе

КОНТРОЛЬНАЯ РАБОТА

 ... //1st @Test public void getLast24HoursByNodeId(){ List<Node> nodes = repo.getLast24HoursByNodeId(); log.debug(nodes.size()); Assert.assertTrue(nodes.size() > 0); } //2st @Test public void getByInterval(){ List<Node> nodes = repo.getByNodeIdAndInterval("1 day".toString()); log.debug(nodes.size()); Assert.assertTrue(nodes.size() > 0); } ... 

Отказоустойчивость

 org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:231) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:214) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417) at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122) 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:207) at com.sun.proxy.$Proxy83.getByNodeIdAndInterval(Unknown Source) at ocdc.johnc.repository.NodeRepositoryTest.getByInterval(NodeRepositoryTest.java:83) 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:483) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:73) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:217) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:68) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192) Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91) at org.hibernate.loader.Loader.getResultSet(Loader.java:2066) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839) at org.hibernate.loader.Loader.doQuery(Loader.java:910) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355) at org.hibernate.loader.Loader.doList(Loader.java:2554) at org.hibernate.loader.Loader.doList(Loader.java:2540) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370) at org.hibernate.loader.Loader.list(Loader.java:2365) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353) at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1873) at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311) at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141) at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573) at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449) at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:110) at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:98) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:89) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:421) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:381) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.repository.core.support.RepositoryFactorySupport$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java:512) 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:281) 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) ... 37 more Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 103 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:304) 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:483) at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:453) at com.sun.proxy.$Proxy99.executeQuery(Unknown Source) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) ... 66 more 

Я предполагаю, что что-то пошло не так в процессе генерации запроса или [выборки и назначения объекта домена].

Пожалуйста, помогите ~

    Хорошо, я трачу очень долгое время, пытаясь сделать эту работу для меня, НО Я НАКОНЕЦ ИМЕЮ ЭТО. Пусть и будущие авантюристы и сообщество знают об этом.

    То, что сработало для меня, было отброшено до интервала, представленного AH здесь :

     select current_timestamp + ( 2 || ' days')::interval; 

    Тогда ваше решение было бы чем-то вроде этого (когда анализируется 2-й запрос):

     @Query(value = "SELECT n.* from nodes n WHERE n.node_id = 10510 AND n.last_good_ping > CURRENT_DATE - ( :interval )\\:\\:interval ", nativeQuery = true) 

    Или для третьего запроса (именно так я сделал это в моем случае)

     @Query(value = "SELECT n.* from nodes n WHERE n.node_id = 10510 AND n.last_good_ping > CURRENT_DATE - ( ?1 )\\:\\:interval", nativeQuery = true) 

    Обратите внимание, что escaping :: становится \\:\\: В вашем чистом SQL, который вы проверите в своем редакторе SQL, используйте :: , но в вашем коде репозитория в @Query эти знаки кастинга должны быть экранированы (иначе ваш Hibernate / JPA будет недоволен).