I have a list that represents the state transitions in a document workflow. I want to add a method to my DAO that will retrieve all documents that have a particular state, but can't work out the HQL to do this. My environment is Java 5, Oracle 9i, Hibernate 3.2. Here are the relevant snippets of annotated Java:
Code:
@Entity
@Table(name="aml_institution")
public class Institution
implements Serializable {
...
@Id
@Column
private int id;
...
@Embedded
private RiskAssessment riskAssessment;
...
}
@Embeddable
public class RiskAssessment {
...
@CollectionOfElements
@JoinTable(name="aml_transition",
joinColumns = @JoinColumn(name="institution_id"))
@IndexColumn(name="ndx", base=0)
private List<Transition> transitions;
...
}
@Embeddable
public class Transition {
/** state resulting from this transition */
@Enumerated(EnumType.STRING)
@Column(name="state", length=20, nullable=false)
private State resultingState;
@ManyToOne
@JoinColumn(name="user_id", nullable=false)
private User who;
/** time the action was performed */
@Column(name="time_of", nullable=false)
private Date when;
/** any comment entered by the user */
@Column(name="comments", length=500)
private String comment;
...
}
I first thought a query with a
group by clause would do the trick, and tried the following in the Hibernate Console:
Code:
select i, max(tran.when)
from Institution i
join i.riskAssessment.transitions tran
where tran.resultingState = 'PENDING'
group by i
but Hibernate complains, throwing an
SQLGrammarException: could not execute query. A variation of that query
Code:
select i.id, max(tran.when)
from Institution i
join i.riskAssessment.transitions tran
where tran.resultingState = 'PENDING'
group by i.id
does execute and the resulting ids are correct.
An alternative might be to use a subquery to retrieve the transition with the maximum timestamp for each Institution, but I'm not sure how to do this. Any help would be greatly appreciated.
Login
FAQ
Search
