本文介紹了去除子查詢?cè)贘PA標(biāo)準(zhǔn)中產(chǎn)生的冗余連接的處理方法，對(duì)大家解決問(wèn)題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧！
問(wèn)題描述
我只需要使用JPA標(biāo)準(zhǔn)執(zhí)行以下MySQL查詢(根據(jù)給定的國(guó)家名稱(在country中)從state_table獲取一個(gè)州的列表)。
SELECT state_id,
state_name,
country_id
FROM state_table
WHERE country_id IN(SELECT country_id
FROM country
WHERE country_name = ?)
我編寫(xiě)了以下JPA標(biāo)準(zhǔn)查詢。
CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<StateTable>criteriaQuery=criteriaBuilder.createQuery(StateTable.class);
Root<StateTable> root = criteriaQuery.from(entityManager.getMetamodel().entity(StateTable.class));
Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
Root<Country> subRoot = subquery.from(Country.class);
subquery.select(subRoot.get(Country_.countryId));
ParameterExpression<String>parameterExpression=criteriaBuilder.parameter(String.class);
subquery.where(criteriaBuilder.equal(subRoot.get(Country_.countryName), parameterExpression));
criteriaQuery.where(criteriaBuilder.in(root.get(StateTable_.country).get(Country_.countryId)).value(subquery));
List<StateTable> list = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, "India").getResultList();
此條件查詢不必要地在生成的SQL查詢中生成冗余聯(lián)接，如下所示。
SELECT t1.state_id,
t1.state_name,
t1.country_id
FROM projectdb.country t0,
projectdb.state_table t1
WHERE (t0.country_id IN (SELECT t2.country_id
FROM projectdb.country t2
WHERE (t2.country_name = ? ))
AND (t0.country_id = t1.country_id ))
可以注意到，存在冗余聯(lián)接AND (t0.country_id = t1.country_id ))。
這是由root.get(StateTable_.countryId).get(Country_.countryId)引起的，root.get(StateTable_.countryId).get(Country_.countryId)意味著在上面給出的條件查詢中的以下行中有一個(gè)內(nèi)部聯(lián)接。在這種情況下，不應(yīng)出現(xiàn)不必要的情況。
criteriaQuery.where(criteriaBuilder.in(root.get(StateTable_.countryId).get(Country_.countryId)).value(subquery));
如何刪除此冗余聯(lián)接？
我使用的是NikpseLink 2.3.2提供的JPA 2.0。
在這種情況下，與其使用子查詢，不如在這兩個(gè)表之間使用聯(lián)接來(lái)處理，但編寫(xiě)這樣的子查詢而不使用冗余聯(lián)接應(yīng)該是可能的，因?yàn)檫@是一件非?；镜氖虑?。
編輯1：
等效的EXISTS()查詢可以按如下方式正常工作。
CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<StateTable>criteriaQuery=criteriaBuilder.createQuery(StateTable.class);
Root<StateTable> root = criteriaQuery.from(entityManager.getMetamodel().entity(StateTable.class));
criteriaQuery.select(root);
Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
Root<Country> subRoot = subquery.from(Country.class);
subquery.select(subRoot.get(Country_.countryId));
ParameterExpression<String>parameterExpression=criteriaBuilder.parameter(String.class);
subquery.where(criteriaBuilder.equal(root.get(StateTable_.country), subRoot), criteriaBuilder.equal(subRoot.get(Country_.countryName), parameterExpression));
criteriaQuery.where(criteriaBuilder.exists(subquery));
List<StateTable> list = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, "India").getResultList();
導(dǎo)致生成以下正確的SQL查詢，該查詢將被委托給MySQL。
SELECT t0.state_id,
t0.state_name,
t0.country_id
FROM projectdb.state_table t0
WHERE EXISTS (SELECT t1.country_id
FROM projectdb.country t1
WHERE ((t0.country_id = t1.country_id)
AND (t1.country_name = ?)))
找不到生成等效IN()查詢的方法。
編輯2：
以下JPQL，
SELECT s
FROM StateTable s
WHERE s.country IN(SELECT c
FROM Country c
WHERE c.countryname = :countryName)
生成正確的IN()子查詢。
SELECT t0.state_id,
t0.state_name,
t0.country_id
FROM projectdb.state_table t0
WHERE t0.country_id IN (SELECT t1.country_id
FROM projectdb.country t1
WHERE (t1.country_name = ?))
由此，我可以設(shè)想以下條件查詢。
CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<StateTable>criteriaQuery=criteriaBuilder.createQuery(StateTable.class);
Root<StateTable> root = criteriaQuery.from(entityManager.getMetamodel().entity(StateTable.class));
criteriaQuery.select(root);
Subquery<Country> subquery = criteriaQuery.subquery(Country.class);
Root<Country> subRoot = subquery.from(Country.class);
subquery.select(subRoot);
ParameterExpression<String>parameterExpression=criteriaBuilder.parameter(String.class);
subquery.where(criteriaBuilder.equal(subRoot.get(Country_.countryName), parameterExpression));
criteriaQuery.where(criteriaBuilder.in(root.get(StateTable_.country)).value(subquery));
List<StateTable> list = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, "India").getResultList();
但它在運(yùn)行時(shí)失敗，出現(xiàn)以下異常。
Exception [EclipseLink-6048] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.QueryException
Exception Description: Illegal use of getField() [projectdb.country.country_id] in expression.
Query: ReadAllQuery(referenceClass=StateTable )
at org.eclipse.persistence.exceptions.QueryException.illegalUseOfGetField(QueryException.java:563)
at org.eclipse.persistence.expressions.Expression.getField(Expression.java:1739)
at org.eclipse.persistence.internal.expressions.FunctionExpression.normalize(FunctionExpression.java:476)
at org.eclipse.persistence.internal.expressions.SQLSelectStatement.normalize(SQLSelectStatement.java:1402)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.buildNormalSelectStatement(ExpressionQueryMechanism.java:549)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.prepareSelectAllRows(ExpressionQueryMechanism.java:1708)
at org.eclipse.persistence.queries.ReadAllQuery.prepareSelectAllRows(ReadAllQuery.java:785)
at org.eclipse.persistence.queries.ReadAllQuery.prepare(ReadAllQuery.java:716)
at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:661)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:888)
at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:613)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:867)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1114)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:402)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1202)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2894)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1797)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1779)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1744)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:468)
at client.beans.ProductDetailsBean.test(ProductDetailsBean.java:139)
at client.beans.ProductDetailsBean.getProductList(ProductDetailsBean.java:119)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1081)
at org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:1153)
at com.sun.ejb.containers.BaseContainer.invokeBeanMethod(BaseContainer.java:4695)
at com.sun.ejb.EjbInvocation.invokeBeanMethod(EjbInvocation.java:630)
at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:822)
at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:582)
at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:46)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:883)
at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:822)
at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:582)
at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.doCall(SystemInterceptorProxy.java:163)
at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.aroundInvoke(SystemInterceptorProxy.java:140)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:883)
at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:822)
at com.sun.ejb.containers.interceptors.InterceptorManager.intercept(InterceptorManager.java:369)
at com.sun.ejb.containers.BaseContainer.__intercept(BaseContainer.java:4667)
at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:4655)
at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:212)
at com.sun.ejb.containers.EJBLocalObjectInvocationHandlerDelegate.invoke(EJBLocalObjectInvocationHandlerDelegate.java:88)
at $Proxy482.getProductList(Unknown Source)
at client.bean.ProductDetailsManagedBean.load(ProductDetailsManagedBean.java:59)
at org.primefaces.component.datagrid.DataGrid.loadLazyData(DataGrid.java:144)
at org.primefaces.component.datagrid.DataGridRenderer.encodeMarkup(DataGridRenderer.java:54)
at org.primefaces.component.datagrid.DataGridRenderer.encodeEnd(DataGridRenderer.java:47)
at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:919)
at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:74)
at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:57)
at org.primefaces.component.panel.PanelRenderer.encodeContent(PanelRenderer.java:204)
at org.primefaces.component.panel.PanelRenderer.encodeMarkup(PanelRenderer.java:121)
at org.primefaces.component.panel.PanelRenderer.encodeEnd(PanelRenderer.java:58)
at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:919)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1864)
at javax.faces.render.Renderer.encodeChildren(Renderer.java:176)
at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:889)
at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:70)
at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:57)
at org.primefaces.component.layout.LayoutUnitRenderer.encodeEnd(LayoutUnitRenderer.java:51)
at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:919)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1864)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1860)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1860)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1860)
at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:461)
at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:133)
at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:337)
at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:337)
at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:120)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:219)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:647)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:344)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:70)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:316)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188)
at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191)
at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168)
at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189)
at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114)
at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838)
at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544)
at java.lang.Thread.run(Thread.java:722)
我看不到此異常背后的任何原因。
編輯3：
在最新版本中相同的條件查詢–出現(xiàn)異常而失敗的那個(gè)，在Hibernate(4.2.7最終版)提供的JPA上運(yùn)行正常，根本不需要修改，在不同的項(xiàng)目中具有完全相同的表關(guān)系。
它生成以下正確的SQL查詢。
SELECT
statetable0_.state_id as state_id1_24_,
statetable0_.country_id as country_3_24_,
statetable0_.state_name as state_na2_24_
FROM
social_networking.state_table statetable0_
WHERE
statetable0_.country_id IN (
SELECT
country1_.country_id
FROM
social_networking.country country1_
WHERE
country1_.country_name=?
)
因此，在NikpseLink的幕后應(yīng)該會(huì)發(fā)生一些不可靠的事情。
我是否應(yīng)該安全地假設(shè)這是Criteria API中的NikpseLink中的疏忽？
編輯4：
如上所述使用EXISTS()也會(huì)產(chǎn)生冗余連接，如果查詢語(yǔ)句顛倒過(guò)來(lái)，
SELECT *
FROM country c
WHERE EXISTS (SELECT s.country_id
FROM state_table s
WHERE s.country_id = c.country_id
AND s.state_name = 'desired_state_name')
Hibernate提供的JPA(我當(dāng)前使用的是4.2.7最終版)生成查詢語(yǔ)句，這是我們通常在沒(méi)有這種冗余聯(lián)接的情況下所期望的。
這是一個(gè)非常基本的查詢，很難相信這是NikpseLink中的疏忽。在我看來(lái)，我一定是做錯(cuò)了什么，一定是遺漏了一些顯而易見(jiàn)的、非?；镜臇|西。請(qǐng)澄清。
我最近已將NikpseLink升級(jí)到其最新版本2.5.1，并安裝了JPA 2.1。
長(zhǎng)話短說(shuō)：引用root.get("property1").get("propery2InAnotherRelatedEntity")這樣的實(shí)體中的嵌套屬性總是會(huì)導(dǎo)致生成多余的聯(lián)接，這通常不應(yīng)該發(fā)生。
請(qǐng)?jiān)徫?，如果我做了這么多修改，但我不得不…：)
JPA
示例中的冗余聯(lián)接是由不正確的推薦答案標(biāo)準(zhǔn)查詢構(gòu)造產(chǎn)生的，而不是由其中的子查詢產(chǎn)生的。所以這個(gè)問(wèn)題的題目真的沒(méi)有多大意義。
要消除冗余聯(lián)接，您只需正確構(gòu)建條件。事實(shí)上，編輯2中的條件查詢是問(wèn)題開(kāi)頭的原始SQL查詢到JPA條件語(yǔ)言的正確轉(zhuǎn)換，不應(yīng)該產(chǎn)生任何多余的聯(lián)接。您使用Hibernate進(jìn)行的實(shí)驗(yàn)證實(shí)了這一點(diǎn)。而你得到的QueryException絕對(duì)是一個(gè)NikpseLink的錯(cuò)誤。我建議您將其報(bào)告給NikpseLink：https://wiki.eclipse.org/EclipseLink/Bugs的開(kāi)發(fā)人員。
但實(shí)際上，這里根本不需要子查詢，如果使用正確，連接通常不是壞事，所以我建議用這個(gè)完全等價(jià)但更簡(jiǎn)潔的查詢來(lái)替換SQL查詢：
SELECT state_id,
state_name,
s.country_id
FROM state_table s, country c
WHERE s.country_id = c.country_id AND country_name = ?
它到條件查詢的翻譯也簡(jiǎn)單明了得多(肯定不會(huì)混淆NikpseLink：))：
CriteriaBuilder criteriaBuilder =
entityManager.getCriteriaBuilder();
CriteriaQuery<StateTable> criteriaQuery =
criteriaBuilder.createQuery(StateTable.class);
Root<StateTable> root = criteriaQuery.from(StateTable.class);
ParameterExpression<String> parameterExpression =
criteriaBuilder.parameter(String.class);
criteriaQuery.where(criteriaBuilder.equal(
root.get(StateTable_.country).get(Country_.countryName),
parameterExpression));
List<StateTable> list = entityManager.createQuery(criteriaQuery)
.setParameter(parameterExpression, "India").getResultList();
并且在MySQL中的性能不會(huì)比原始版本差。
這篇關(guān)于去除子查詢?cè)贘PA標(biāo)準(zhǔn)中產(chǎn)生的冗余連接的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，
