如何在JPA 2.0中使用where子句编写MAX查询？ [英] How do I write a MAX query with a where clause in JPA 2.0?

问题描述

我正在使用JPA 2.0。 Hibernate 4.1.0.Final和Java 6.如何从以下psuedo-SQL编写JPA查询？

I'm using JPA 2.0. Hibernate 4.1.0.Final, and Java 6. How do I write a JPA query from the following psuedo-SQL?

select max(e.dateProcessed) from Event e where e.org = myOrg

我的域对象看起来像如下：

And my domain object looks like the following:

@GenericGenerator(name = "uuid-strategy", strategy = "org.mainco.subco.core.util.subcoUUIDGenerator")
@Entity
@Table(name = "sb__event",
    uniqueConstraints = { @UniqueConstraint(columnNames={"EVENT_ID"}) }
)
public class Event
{

    @Id
    @Column(name = "ID")
    @GeneratedValue(generator = "uuid-strategy")
    private String id;

    @ManyToOne(fetch = FetchType.LAZY, cascade = {CascadeType.REMOVE})
    @JoinColumn(name = "ORGANIZATION_ID", nullable = false, updatable = true)
    private Organization org;

    @Column(name = "DATE_PROCESSED")
    @NotNull
    private java.util.Date dateProcessed;

我知道CriteriaBuilder.greatest是参与的，但我无法弄清楚如何编写查询。这将返回与组织相匹配的所有事件对象，但这是我已经得到的。

I know that CriteriaBuilder.greatest is involved, but I just can't figure out how to write the query. This will return all the event objects that match the organization, but that's as far as I've gotten.

final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
final CriteriaQuery<Event> criteria = builder.createQuery(Event.class);
final Root<Event> event = criteria.from(Event.class);
criteria.select(event);
criteria.where(builder.equal(Event.get("org"), org));
results.addAll(m_entityManager.createQuery(criteria).getResultList());

推荐答案

有两种方法，一种使用JPQL，另一种使用条件查询。

JPQL简单：

There are two ways, one using JPQL, the other using criteria queries.
JPQL is simply:

em.createQuery("select max(e.dateProcessed) from Event e where e.org = :myOrg")
  .setParameter("myOrg", myOrg)
  .getSingleResult();

在使用您可能拥有的标准时：

while using criteria you might have:

CriteriaBuilder qb = em.getCriteriaBuilder();
CriteriaQuery<Number> cq = qb.createQuery(Number.class);
Root<Event> root = cq.from(Event.class);
cq.select(qb.max(root.get("dateProcessed")));
cq.where(qb.equal(Event.get("org"), qb.parameter(MyOrgType.class, "myOrg")));
em.createQuery(cq).setParameter("myOrg", myOrg).getSingleResult();

这篇关于如何在JPA 2.0中使用where子句编写MAX查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！