大佬教程收集整理的这篇文章主要介绍了使用nativeQuery的Spring Boot Query注释在Postgresql中不起作用,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
好的,我花了很长时间尝试为我完成这项工作,但是我终于明白了。让未来的冒险家和社区也知道这一点。
对我有用的是强制转换到AH提出的间隔:
SELEct CURRENT_TIMESTAMP + ( 2 || ' days')::interval;
@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)
请注意转义::
变成\\:\\:
。在您要在sql编辑器中测试的纯sql中::
,但是@query
需要转义这些强制转换符号内的存储库代码(否则,您的Hibernate
/ JPA将不满意)。
开发环境。
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,PagingAndSorTingRepository<Node,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
@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
我认为在[生成查询]或[获取并分配给域对象]过程中出现了问题。
请帮忙〜
以上是大佬教程为你收集整理的使用nativeQuery的Spring Boot Query注释在Postgresql中不起作用全部内容,希望文章能够帮你解决使用nativeQuery的Spring Boot Query注释在Postgresql中不起作用所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。