java - 在 hql 中运行查询时出现语法错误

Question

我有一个 mysql 表，总结如下:

select * from transaction where workflow_id = 'A'\G
*************************** 1. row ***************************
    transfer_id: 2
    workflow_id: A
         amount: 552
         status: FAILED
instrument_type: Type-A
  creation_date: 2016-02-29 12:11:05
initiation_date: 2016-02-29 12:43:23
completion_date: 2016-02-29 12:43:23
*************************** 2. row ***************************
    transfer_id: 1
    workflow_id: A
         amount: 552
         status: SUCCESS
instrument_type: Type-B
  creation_date: 2016-03-01 10:25:22
initiation_date: 2016-03-01 10:25:23
completion_date: 2016-03-01 10:25:23
  last_modified: 2016-03-01 10:25:23

本次交易对应的模型如下:

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.Transient;

import com.limeroad.services.payments.xhr.CreateTransactionRequest;
import com.limeroad.services.payments.xhr.TransactionState;
import com.limeroad.commons.InstrumentType;
@Entity
@Table(name = "transaction")
public class Transaction implements Serializable {

  /**
   * 
   */
  private static final long serialVersionUID = 6353431043455994887L;

  public Transaction() {

  }


  @Id
  @Column(name = "transfer_id")
  private String transferId;

  @Column(name = "workflow_id")
  private String workflowId;

  @Column(name = "amount")
  private Double amount;

  @Column(name = "status")
  private String status;


  @Enumerated(EnumType.STRING)
  @Column(name = "instrument_type")
  private InstrumentType instrumentType;


  @Temporal(TemporalType.TIMESTAMP)
  @Column(name = "creation_date")
  private Date creationDate;

  @Temporal(TemporalType.TIMESTAMP)
  @Column(name = "initiation_date")
  private Date initiationDate;

  @Temporal(TemporalType.TIMESTAMP)
  @Column(name = "completion_date")
  private Date completionDate;

  .........
}

我目前正在尝试编写一个查询“给定工作流程ID，查找具有最大创建日期的事务”

我正在使用entityManager来编写hql查询。

对应的查询是:

(ArrayList<Transaction>) entityManager
        .createQuery(
            "select a from Transaction a, "
            + "( select workflowId, max(creationDate) as maxCreationDate from Transaction t where t.workflowId in :workflowIds group by t.workflowId) as b"
            + " where a.workflowId=b.workflowId and a.creationDate = b.maxCreationDate ")
        .setParameter("workflowIds", workflowIds).getResultList();

运行此查询时出现以下错误

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 71 [select a from com.limeroad.services.payments.dao.model.Transaction a, ( select workflowId, max(creationDate) as maxCreationDate from com.limeroad.services.payments.dao.model.Transaction t where t.workflowId in :workflowIds group by t.workflowId) as b where a.workflowId=b.workflowId and a.creationDate = b.maxCreationDate ]
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1683)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:331)
    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:606)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:293)

请帮忙

Answer 1

在 HQL 中，作为表进行子查询是不可能的。

HQL 子查询只能出现在 select 或 where 子句中。它不能作为表格放置。

请引用此文档https://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch16.html#queryhql-subqueries