Syntax Error in running a query in hql

Question

I have a mysql table summarized as under:

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

The corresponding model for this transaction is as under:

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;

  .........
}

I am currently trying to write a query for "given a workflowId, find the transaction having the maximum creation date"

I am using entityManager for writing the hql query.

The corresponding query is:

(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();

I get the following error when running this query

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)

Please help

score 0 · Answer 1 · answered Mar 01 '16 at 12:58

0

In HQL, Subquery as table is not possible.

HQL subqueries can occur only in the select or where clauses. It cannot be put as a table.

please refer this document https://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch16.html#queryhql-subqueries

answered Mar 01 '16 at 12:58

Thanga

7,811
3
19
38

Syntax Error in running a query in hql

1 Answers1