java - 将 mysql 查询转换为 JPQL 查询

Question

我如何在 JPQL 中声明以下 mysql 查询

select * from Price WHERE `valueDate` = (SELECT MAX(`valueDate`) FROM Price) and fundId = 2930

我尝试过以下内容:

"select a from Price a where a.valueDate = select MAX(a.valueDate) and a.fund.id = :" +Price.QUERY_PARAM_FUND_ID

但是这种方法会出错:

Caused by: <openjpa-2.3.0-r422266:1540826 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: "Encountered "MAX" at character 50, but expected: ["AND", "GROUP", "HAVING", "OR", "ORDER", <EOF>]." while parsing JPQL "select a from Price a where a.valueDate = select MAX(b.valueDate) from Price b and a.fund.id = :fundId"

Answer 1

我认为如果您在子查询之前和之后添加括号，就会起作用。

“从价格 a 中选择一个，其中 a.valueDate =(从价格中选择 MAX(a.valueDate))和 a.fund.id = :” +Price.QUERY_PARAM_FUND_ID

另外让我说一些与 JPA 规范的 hibernate 实现相关的事情。如果您有名为“Price”的 Hibernate 模型，那么您将通过该模型进行查询。 HQL 会是这样的

try {
    final StringBuilder qry = new StringBuilder();
    qry.append(" SELECT")
            .append(" FROM Price p")
            .append(" WHERE p.valueDate = (SELECT MAX(pr.valueDate) FROM Price pr)")
            .append(" AND p.fundId = :fundId");
    return getJpaTemplate().execute(new JpaCallback() {
        public Object doInJpa(EntityManager em)
                throws PersistenceException {
            Query q = em.createQuery(qry.toString());
            q.setParameter("fundId", fundId);
            return q.getResultList();
        }
    });
} catch (RuntimeException re) {}