I'm relatively new to Hibernate but have been working with SQL for over a dozen years. I'm using DB2 running on the iSeries and using Hibernate 3.1.2.
I have a query where I'm joining two different objects (tables) where the documentId of one is the most recent revision of a set of revisions for that document. I've come up with two alternative sql statements that will work equally as well and are quick to return results.
There are two tricky parts two either query option. The first is that I need to use the Criteria API because the query needs to support searching on a user-supplied combination of fields. The fields they choose for the search may vary. The second tricky part is that I need to be able to select just the max revision in the select clause instead of also returning a field I'm grouping by. In most cases, the grouped by column is required in the select but in this case on DB2 it is not.
The first version of the sql statement uses a subselect:
Code:
select
document_id,
compound_policy_term_data,
item_type,
revision_date as create_date,
description,
page_count
from ieaddta.waf_revised_documents
where document_id
in (select max(revision)
from ieaddta.waf_document_revisions
where compound_policy_term_data like '0001134440%'
group by original);
The second version uses an inner join on another query leveraging an alias to complete the join. I'm not even sure if this is possible using Hibernate or how it would be done:
Code:
select
document_id,
compound_policy_term_data,
item_type,
revision_date as create_date,
description,
page_count
from ieaddta.waf_revised_documents wrd
inner join (select max(revision) as max_revision_id, original
from ieaddta.waf_document_revisions
where compound_policy_term_data like '0001134440%'
group by original) max_document_revisions on wrd.document_id = max_document_revisions.max_revision_id;
My Hibernate code to create the criteria for the subselect looks like the following:
Code:
// search criteria is a proprietary object akin to the Criteria object in
// Hibernate
searchCriteria.setBusinessClass(RevisionMappingBean.class);
DetachedCriteria criteria = getCriteria(searchCriteria,
excludedSearchProperties);
ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.max("revisionDocumentId"), "maxRevisionDocumentId");
projectionList.add(Projections.groupProperty("originalDocumentId"), "originalDocumentId");
criteria.setProjection(projectionList);
// the statement is then executed at this point and yields both the max field and the originalDocumentId field
So given all of this, is there a way to get the subselect to just return the max field using Criteria objects to generate the SQL? If necessary, can I somehow influence the SQL and still use Hibernate to generate the SQL (at least the where clause)? Should I modify the code to wrap the subselect with another select that just returns the max and if so, then what's the cleanest way to do this through the Criteria API?
Any other ideas are welcome.
Thanks in advance,
Mark
Login
FAQ
Search