我有两个具有@ManyToMany关系的实体Travel和Traveler。在JSF页面中，我将所有旅行者的列表放在了primefaces数据表中。我需要显示和更新与所选旅行者相关的旅行列表。因此，我这样进行：
JSF页面：
<p:dataTable id="travels" var="travel" value="#{travelersMBean.selectedTraveler.travels}"
selection="#{travelersMBean.selectedTravel}" selectionMode="single" rowKey="#{travel.id}">
...
</p:datatable>
Traveler实体：@ManyToMany(cascade = CascadeType.PERSIST)
@JoinTable(name = "TRAVELS_TRAVELLERS",
joinColumns =
@JoinColumn(name = "traveller_id"),
inverseJoinColumns =
@JoinColumn(name = "travel_id"))
private List<Travel> travels;
Travel实体：@ManyToMany
private List<Traveller> travellers;
这是堆栈跟踪：
Avertissement: Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown
column 't0.traveller_id' in 'where clause'
Error Code: 1054
Call: SELECT t1.id, t1.arrivalDateToThailand, t1.enabled, t1.lengthToStay,
t1.placeToStayCity, t1.placeToStayPostCode, t1.placeToStayStreet,
t1.returnDateFromThailand, t1.system, t1.version, t1.VISITPURPOSE FROM
TRAVELS_TRAVELLERS t0, TRAVELS t1 WHERE ((t0.traveller_id = ?) AND (t1.id =
t0.travel_id))
bind => [1 parameter bound]
Query: ReadAllQuery(name="file:/home/rym/SVN/trunk/VisasMarketing/target/VisasMarketing-
1.0-SNAPSHOT/WEB-INF/classes/_visasMarketingPU" referenceClass=Travel sql="SELECT t1.id,
t1.arrivalDateToThailand, t1.enabled, t1.lengthToStay, t1.placeToStayCity,
t1.placeToStayPostCode, t1.placeToStayStreet, t1.returnDateFromThailand, t1.system,
t1.version, t1.VISITPURPOSE FROM TRAVELS_TRAVELLERS t0, TRAVELS t1 WHERE
((t0.traveller_id = ?) AND (t1.id = t0.travel_id))")
at org.eclipse.persistence.exceptions.DatabaseException.
sqlException(DatabaseException.java:333)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.
basicExecuteCall(DatabaseAccessor.java:644)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.
executeCall(DatabaseAccessor.java:535)
at org.eclipse.persistence.internal.sessions.AbstractSession.
basicExecuteCall(AbstractSession.java:1717)
at org.eclipse.persistence.sessions.server.ServerSession.
executeCall(ServerSession.java:566)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.
executeCall(DatasourceCallQueryMechanism.java:207)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.
executeCall(DatasourceCallQueryMechanism.java:193)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.
executeSelectCall(DatasourceCallQueryMechanism.java:264)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.
selectAllRows(DatasourceCallQueryMechanism.java:646)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.
selectAllRowsFromTable(ExpressionQueryMechanism.java:2611)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.
selectAllRows(ExpressionQueryMechanism.java:2570)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery
(ReadAllQuery.java:420)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.
executeDatabaseQuery(ObjectLevelReadQuery.java:1081)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute
(ObjectLevelReadQuery.java:1040)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:392)
at org.eclipse.persistence.internal.sessions.AbstractSession.
internalExecuteQuery(AbstractSession.java:2831)
at org.eclipse.persistence.internal.sessions.AbstractSession.
executeQuery(AbstractSession.java:1516)
at org.eclipse.persistence.internal.sessions.AbstractSession.
executeQuery(AbstractSession.java:1498)
at org.eclipse.persistence.internal.indirection.QueryBasedValueHolder.
instantiate(QueryBasedValueHolder.java:98)
at org.eclipse.persistence.internal.indirection.QueryBasedValueHolder.
instantiate(QueryBasedValueHolder.java:88)
at org.eclipse.persistence.internal.indirection.DatabaseValueHolder.
getValue(DatabaseValueHolder.java:88)
at org.eclipse.persistence.internal.indirection.UnitOfWorkValueHolder.
instantiateImpl(UnitOfWorkValueHolder.java:161)
at org.eclipse.persistence.internal.indirection.UnitOfWorkValueHolder.
instantiate(UnitOfWorkValueHolder.java:222)
at org.eclipse.persistence.internal.indirection.DatabaseValueHolder.
getValue(DatabaseValueHolder.java:88)
at org.eclipse.persistence.indirection.IndirectList.
buildDelegate(IndirectList.java:244)
at org.eclipse.persistence.indirection.IndirectList.
getDelegate(IndirectList.java:414)
at org.eclipse.persistence.indirection.IndirectList.size(IndirectList.java:752)
at javax.faces.model.ListDataModel.isRowAvailable(ListDataModel.java:110)
at javax.faces.model.ListDataModel.setRowIndex(ListDataModel.java:185)
at javax.faces.model.ListDataModel.setWrappedData(ListDataModel.java:220)
at javax.faces.model.ListDataModel.<init>(ListDataModel.java:79)
at org.primefaces.component.api.UIData.getDataModel(UIData.java:623)
at org.primefaces.component.api.UIData.setRowModel(UIData.java:422)
at org.primefaces.component.api.UIData.setRowIndex(UIData.java:414)
at org.primefaces.component.api.UIData.visitTree(UIData.java:669)
at javax.faces.component.UIComponent.visitTree(UIComponent.java:1601)
at javax.faces.component.UIForm.visitTree(UIForm.java:344)
at javax.faces.component.UIComponent.visitTree(UIComponent.java:1601)
at javax.faces.component.UIComponent.visitTree(UIComponent.java:1601)
at javax.faces.component.UIComponent.visitTree(UIComponent.java:1601)
at com.sun.faces.context.PartialViewContextImpl.processComponents
(PartialViewContextImpl.java:376)
at com.sun.faces.context.PartialViewContextImpl.processPartial
(PartialViewContextImpl.java:297)
at org.primefaces.context.PrimePartialViewContext.processPartial
(PrimePartialViewContext.java:57)
at javax.faces.component.UIViewRoot.encodeChildren(UIViewRoot.java:981)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1757)
at com.sun.faces.application.view.FaceletViewHandlingStrategy.
renderView(FaceletViewHandlingStrategy.java:391)
at com.sun.faces.application.view.MultiViewHandler.renderView
(MultiViewHandler.java:131)
at javax.faces.application.ViewHandlerWrapper.renderView
(ViewHandlerWrapper.java:288)
at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:121)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:594)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1550)
at org.apache.catalina.core.StandardWrapperValve.invoke
(StandardWrapperValve.java:281)
at org.apache.catalina.core.StandardContextValve.invoke
(StandardContextValve.java:175)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
at com.sun.enterprise.v3.services.impl.ContainerMapper$
AdapterCallable.call(ContainerMapper.java:317)
at com.sun.enterprise.v3.services.impl.ContainerMapper.
service(ContainerMapper.java:195)
at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:860)
at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:757)
at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1056)
at com.sun.grizzly.http.DefaultProtocolFilter.execute
(DefaultProtocolFilter.java:229)
at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter
(DefaultProtocolChain.java:137)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork
(AbstractThreadPool.java:532)
at com.sun.grizzly.util.AbstractThreadPool$Worker.
run(AbstractThreadPool.java:513)
at java.lang.Thread.run(Thread.java:744)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
Unknown column 't0.traveller_id' in 'where clause'
at sun.reflect.GeneratedConstructorAccessor405.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance
(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2324)
at com.mysql.jdbc.jdbc2.optional.PreparedStatementWrapper.executeQuery
(PreparedStatementWrapper.java:846)
at com.sun.gjc.spi.jdbc40.PreparedStatementWrapper40.executeQuery
(PreparedStatementWrapper40.java:642)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.
executeSelect(DatabaseAccessor.java:931)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.
basicExecuteCall(DatabaseAccessor.java:607)
... 75 more
最佳答案
根据产生的问题，命名查询应如下所示：
在实体层中：（与之相同的类）
@NamedQuery (
name ="selecting_travels",
query="SELECT t1.* FROM TRAVELS_TRAVELLERS t0, TRAVELS t1, TRAVELLERS t2 WHERE t0.id_travel = t1.id AND t0.id_traveller = t2.id AND t2.id = :selectedTraveller "
)
假设表
TRAVELLERS的标识也是id。在表示层中：替换
:selectedTraveler像这样：public List<Travel> searchByTraveller(int identifiant) {
Query query = persistance.createNamedQuery("selecting_travels");
query.setParameter("selectedTraveler", identifiant);
return query.getResultList();
}