sql - Spring Boot Query annotation with nativeQuery doesn't work in Postgresql

Question

Development environment.

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

Other All JPA query like findFirst3ByTextOrderByTextAsc are working fine. But not in NativeQuery, especially with INTERVAL

Repository

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

1st query is working find in Test, But not in 2nd,3rd Query

TEST

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

Failure Trace

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

I presume something goes wrong in [generating Query] or [fetching and assigning to Domain Object] process.

Please help~

Answer 1

Ok, I spend really long time trying to make this work for me, BUT I FINALLY GOT IT. Let the future adventurers and community know it as well.

What worked for me was casting to interval presented by A.H. here:

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

Your solution would be then something like that (when 2nd query is analyzed):

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

Or for 3rd query (which is exactly how I made it work in my case)

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

Please notice escaping :: becomes \:\:. In your pure SQL which you would test in your SQL editor use ::, but in your repository code within @Query these casting signs needs to be escaped (otherwise your Hibernate/JPA will be displeased).