OK. Here is a high level look at my objects. I have a PurcahseOrderMetaData object that contains a SortedSet of PurchaseOrderAction objects. These actions represent an action taken by the user (viewed it, created an invoice for it, etc). Here is snippet of the classes and their mappings with all the extraneous properties removed:
Code:
/**
* @hibernate.class
* table="purchase_order"
*/
public class PurchaseOrderMetaData implements Serializable {
private Integer purchaseOrderId;
private Vendor vendor;
private SortedSet actions;
public Status getStatus() {
return getMostRecentAction().getStatus();
}
public PurchaseOrderAction getMostRecentAction() {
return (PurchaseOrderAction) getActions().first();
}
/**
* @hibernate.id
* column="purchase_order_id"
* generator-class="assigned"
*/
public Integer getPurchaseOrderId() {
return purchaseOrderId;
}
public void setPurchaseOrderId(Integer purchaseOrderId) {
this.purchaseOrderId = purchaseOrderId;
}
/**
* @hibernate.many-to-one
* column="vendor_id"
*/
public Vendor getVendor() {
return vendor;
}
public void setVendor(Vendor vendor) {
this.vendor = vendor;
}
/**
* @hibernate.set
* table="purchase_order_action"
* lazy="false"
* cascade="all"
* sort="natural"
*
* @hibernate.collection-key
* column="purchase_order_id"
*
* @hibernate.composite-element
* class="PurchaseOrderAction"
*/
public SortedSet getActions() {
return actions;
}
public void setActions(SortedSet actions) {
this.actions = actions;
}
}
Code:
public class PurchaseOrderAction implements Serializable, Comparable {
private Status status;
public boolean equals(Object obj) {
return this.status.equals( ((PurchaseOrderAction) obj).status);
}
public int hashCode() {
return status.hashCode();
}
public int compareTo(Object obj) {
return this.status.compareTo( ((PurchaseOrderAction) obj).status);
}
/**
* @hibernate.property
* column="po_status_id"
* type="StatusUserType"
*/
public Status getStatus() {
return status;
}
public void setStatus(Status status) {
this.status = status;
}
}
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class
name="PurchaseOrderMetaData"
table="purchase_order"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="purchaseOrderId"
column="purchase_order_id"
type="java.lang.Integer"
>
<generator class="assigned">
</generator>
</id>
<many-to-one
name="vendor"
class="Vendor"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="vendor_id"
/>
<set
name="actions"
table="purchase_order_action"
lazy="false"
inverse="false"
cascade="all"
sort="natural"
>
<key
column="purchase_order_id"
/>
<composite-element
class="PurchaseOrderAction"
>
<property
name="status"
type="StatusUserType"
update="true"
insert="true"
column="po_status_id"
/>
</composite-element>
</set>
<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-PurchaseOrderMetaData.xml
containing the additional properties and place it in your merge dir.
-->
</class>
</hibernate-mapping>
What I am trying to do is create a query that finds all POs for a given status. This is determined by finding POs whose action with the "greatest" status (a status is represented by an integer in the DB - successive statuses are greater than the previous) equals the given status. So, I think I need a subselect against the action table with a max clause. At least, this is how it is being done currently with SQL (I am trying to convert this to Hibernate).
My currect query looks like this:
Code:
public static List findUnverifiedPurchaseOrders(Status status) throws SQLException {
Object[] values = new Object[] { status.getId() };
Type[] types= new Type[] { Hibernate.INTEGER, Hibernate.INTEGER };
String query =
"select purchaseOrder from PurchaseOrderMetaData purchaseOrder " +
"join purchaseOrder.actions action " +
"where action.status = ? ";
return HibernateUtils.find(query, values, types);
}
This pulls back all POs that have any action with the given status. I need to pull back POs whose action with the "greatest" status is equal to the given status. I have looked at several HQL examples in the docs and forum, but have not found a solution. I hope I provided enough info. Thanks for the help.
Ryan