java - 创建 hibernate native 查询时出现查询语法异常

Question

用于创建查询的存储库方法如下面的代码所示。

@Override
public Long findTotalCountSentToLiveRequiredExecutionsFiltered(String searchString) {
    final StringBuilder queryString = new StringBuilder("SELECT count(fk_script) FROM ");
    queryString.append("ses_script s INNER JOIN ses_database d INNER JOIN "
            +"(SELECT * FROM ses_req_execution re WHERE re.sent_to_live = 1 "
            +"AND re.fk_database NOT IN "
            +"(SELECT e.fk_database FROM dbo.ses_execution e WHERE e.fk_script = re.fk_script)"
            +") AS a ON d.id = a.fk_database ON s.id = a.fk_script INNER JOIN ses_user u "
            +"ON s.fk_created_by_user = u.id "
            +"WHERE CONCAT (d.name, '', s.filename, '', u.username, '') LIKE :searchString");

    final Query query = this.em.createQuery(queryString.toString());
    query.setParameter("searchString","%" + searchString + "%");

    LOGGER.debug("Created query: "+queryString.toString());
    return (Long) query.getSingleResult();
}

这会在第 80 行触发查询语法异常，如下所示:

SEVERE: Servlet.service() for servlet [dispatcher] in context with path [/ses-web] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 80 [SELECT count(fk_script) FROM  ses_script s INNER JOIN ses_database d INNER JOIN (SELECT * FROM ses_req_execution re WHERE re.sent_to_live = 1 AND re.fk_database NOT IN (SELECT e.fk_database FROM dbo.ses_execution e WHERE e.fk_script = re.fk_script)) AS a ON d.id = a.fk_database ON s.id = a.fk_script INNER JOIN ses_user u ON s.fk_created_by_user = u.id WHERE CONCAT (d.name, '', s.filename, '', u.username, '') LIKE :searchString]; nested exception is java.lang.IllegalArgumentException:                 
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 80 [SELECT count(fk_script) FROM  ses_script s INNER JOIN ses_database d INNER JOIN (SELECT * FROM ses_req_execution re WHERE re.sent_to_live = 1 AND re.fk_database NOT IN (SELECT e.fk_database FROM dbo.ses_execution e WHERE e.fk_script = re.fk_script)) AS a ON d.id = a.fk_database ON s.id = a.fk_script INNER JOIN ses_user u ON s.fk_created_by_user = u.id WHERE CONCAT (d.name, '', s.filename, '', u.username, '') LIKE :searchString]] with root cause
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 80 [SELECT count(fk_script) FROM  ses_script s INNER JOIN ses_database d INNER JOIN (SELECT * FROM ses_req_execution re WHERE re.sent_to_live = 1 AND re.fk_database NOT IN (SELECT e.fk_database FROM dbo.ses_execution e WHERE e.fk_script = re.fk_script)) AS a ON d.id = a.fk_database ON s.id = a.fk_script INNER JOIN ses_user u ON s.fk_created_by_user = u.id WHERE CONCAT (d.name, '', s.filename, '', u.username, '') LIKE :searchString]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91)
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:304)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:203)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:126)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:88)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167)
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328)
at sun.reflect.GeneratedMethodAccessor55.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:342)
at com.sun.proxy.$Proxy54.createQuery(Unknown Source)
at sun.reflect.GeneratedMethodAccessor55.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:289)
at com.sun.proxy.$Proxy51.createQuery(Unknown Source)
at rs.naovis.repo.impl.RequiredExecutionRepositoryImpl.findTotalCountSentToLiveRequiredExecutionsFiltered(RequiredExecutionRepositoryImpl.java:132)
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)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:344)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:319)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:266)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
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.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:92)
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.$Proxy64.findTotalCountSentToLiveRequiredExecutionsFiltered(Unknown Source)
at rs.naovis.service.RequiredExecutionServiceImpl.findResultNumber(RequiredExecutionServiceImpl.java:320)
at rs.naovis.controller.ExecutionController.handleGetReqExecutionsByPageResultNumber(ExecutionController.java:575)
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)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:781)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:721)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:618)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:534)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1081)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:658)
at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1566)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1523)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)

问题是，我在 SQL Server Management Studio 中创建了相同的查询，该查询运行顺利(“%da%”只是此处的示例字符串):

SELECT count(fk_script) FROM ses_script s INNER JOIN ses_database d INNER JOIN (SELECT * FROM ses_req_execution re WHERE re.sent_to_live = 1 AND re.fk_database NOT IN (SELECT e.fk_database FROM dbo.ses_execution e WHERE e.fk_script = re.fk_script)) AS a ON d.id = a.fk_database ON s.id = a.fk_script INNER JOIN ses_user u ON s.fk_created_by_user = u.id WHERE CONCAT (d.name, '', s.filename, '', u.username, '') LIKE '%da%'

我一遍又一遍地检查语法，我不确定为什么在通过 hibernate 创建查询时“(”会触发异常。我也使用几乎相同的查询，唯一的区别是它没有“count”，不会引发异常。

有人看出问题出在哪里吗？

Answer 1

您必须调用 createNativeQuery 而不是 createQuery:

  final Query query = this.em.createNativeQuery(queryString.toString());

否则 Hibernate 会尝试将查询解析为 JPQL/HQL 查询。