我正在使用Jesey在Java（JAX-RS）中实现Restful Web Service。我在Tomcat v7.0上运行它，我使用Hibernate将数据映射到数据库（MySQL）。我有一个查询来获取可交付成果的列表：
deliverables = (List<Deliverable>) session.createQuery("Select deliverable from Task as t where t.project.id= :id And t.user.username = :name") .setLong("id", projectId).setString("name", username).list();
它给了我一百个可交付成果，但我想在Task表中拥有前三名。感谢您的帮助。如何修改查询？
请注意，这三个可交付成果不应重复。
与@FGreg答案存在冲突。当我使用这些查询时，一切正常：
deliverables = (List<Deliverable>) session.createQuery( "from Deliverable as d where d.project.id= :id").setLong("id", projectId).list(); long task_id; for(Deliverable d: deliverables) { task_id = (long) session.createQuery("Select Max(id) from Task as t where t.deliverable.id = :id and t.user.username = :name") .setLong("id", d.getId()).setString("name", username).uniqueResult(); d.setTask_id(task_id); } sortDeliverable(deliverables); // sort by Task_id
我认为在@FGreg提供的查询中，没有考虑max（id），例如，task_id = 31，643两次使用了交付项。它可能认为Task_id = 31，这是错误的。有任何修复的帮助吗？
我尝试了@FGreg查询，没有区别。然后它可以正常工作，除了重复问题。
我尝试了这个：
deliverables = (List<Deliverable>) session.createQuery("from Deliverable as d inner join Task as t where t.id = (select max(t1.id) from Task as t1 where t1.deliverable.id = d.id) and d.project.id= :id and t.user.username = :name order by t.id desc") .setMaxResults(3) .setLong("id", projectId) .setString("name", username) .list();
我在堆栈跟踪中得到了New Error：
Jul 13, 2012 5:03:10 PM org.hibernate.hql.internal.ast.ErrorCounter reportError ERROR: Path expected for join! Jul 13, 2012 5:03:10 PM org.hibernate.hql.internal.ast.ErrorCounter reportError ERROR: Path expected for join! Path expected for join! at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:371) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3477) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3263) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3141) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:694) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:550) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:287) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:235) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:248) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:101) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:119) at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:214) at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:192) at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1537) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.hibernate.context.internal.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:350) at $Proxy65.createQuery(Unknown Source) at se.softwerk.timelog.controller.DeliverableManager.deliverableList2(DeliverableManager.java:185) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205) at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75) at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:288) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1469) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1400) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1349) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1339) at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:537) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:708) at javax.servlet.http.HttpServlet.service(HttpServlet.java:722) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:298) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Jul 13, 2012 5:03:10 PM org.hibernate.hql.internal.ast.ErrorCounter reportError ERROR: Invalid path: 't.id' Jul 13, 2012 5:03:10 PM org.hibernate.hql.internal.ast.ErrorCounter reportError ERROR: Invalid path: 't.id' Invalid path: 't.id' at org.hibernate.hql.internal.ast.util.LiteralProcessor.lookupConstant(LiteralProcessor.java:131) at org.hibernate.hql.internal.ast.tree.DotNode.resolve(DotNode.java:219) at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:118) at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:114) at org.hibernate.hql.internal.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:883) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1246) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4252) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3730) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1923) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1848) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1848) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:782) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:583) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:287) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:235) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:248) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:101) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:119) at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:214) at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:192) at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1537) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.hibernate.context.internal.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:350) at $Proxy65.createQuery(Unknown Source) at se.softwerk.timelog.controller.DeliverableManager.deliverableList2(DeliverableManager.java:185) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source)
模型类：
@Entity public class Task { @Id @GeneratedValue private long id; @ManyToOne(optional = false) private User user; @ManyToOne(optional = false) private Project project; @ManyToOne(optional = false) private Deliverable deliverable; }
可交付成果：
@Entity public class Deliverable { @Id @GeneratedValue private long id; @Column(name = "short", nullable = false) private String key; @Column(nullable = false) private String name; @OneToMany(targetEntity = Task.class, mappedBy = "deliverable", fetch = FetchType.LAZY, cascade = CascadeType.ALL) @JsonIgnore private Collection<Task> tasks; @ManyToOne(optional = false, fetch = FetchType.EAGER) @JoinColumn(name = "project") private Project project; @ManyToOne(fetch = FetchType.EAGER) @JoinColumn(name = "parent") private Deliverable parent; @OneToMany(targetEntity = Deliverable.class, mappedBy = "parent", fetch = FetchType.LAZY, cascade = CascadeType.ALL) @JsonIgnore private Collection<Deliverable> children; }
我认为您是Deliverable从TaskID的顺序中查询前3位。您可以尝试这样的事情：
Deliverable
Task
编辑： 确定，我将再对此刺一击。这应该给你的前3 Deliverable小号下令Task.id采取只Deliverable关联到max(Task.id)
Task.id
max(Task.id)
deliverables = (List<Deliverable>) session.createQuery( "from Deliverable as d, Task as t where t.deliverable.id = d.id and t.id = (select max(t1.id) from Task t1 where t1.deliverable.id = d.id) and d.project.id= :id and t.user.username = :name order by t.id desc") .setMaxResults(3) .setLong("id", projectId) .setString("name", username) .list();
