Menu
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~
710
Spring Boot JPA - Native Query Spring Boot JPA - Native Query, Some time case arises, where we need a custom native query to fulfil one test case. We can use @Query annotation to specify a query within a repository.
To use PostgreSQL in the database, we need to first install it on the same or another server. We have using the JDBC template to connect the PostgreSQL database using spring boot. What is spring boot postgresql? Using spring boot, we can easily connect to the database server. Unfortunately, PostgreSQL is running by default on the port as 5432.
Previous Page Next Page Some time case arises, where we need a custom native query to fulfil one test case. We can use @Query annotation to specify a query within a repository. Following is an example. In this example, we are using native query, and set an attribute nativeQuery=truein Query annotation to mark the query as native.
Both native queries and JPQL queries in the @Query annotation can accept annotated method parameters, which further classify into: Check out our hands-on, practical guide to learning Git, with best-practices, industry-accepted standards, and included cheat sheet.
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).
84
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
