Hi,
When loading collections I need to filter some instances (to control orphans in an inheritance hierarchy). For doing so I have the following SQL code (which has a good performance in my MySQL 5.0.1 server)
Code:
SELECT *
FROM PDBElement
WHERE
idCode="1AGI" and pdbElementId IN
(
SELECT pdbElementId FROM
(
(SELECT P.pdbElementId FROM PDBElement P, Atom A
WHERE A.pdbElementId=P.pdbElementId AND idCode = "1AGI") UNION
(SELECT P.pdbElementId FROM PDBElement P, Residue R
WHERE R.pdbElementId=P.pdbElementId AND idCode = "1AGI")
) as q
)
1AGI is the id of the object where the collection is (so it should be a parameter in the where attribute of the mapping file). PDBElment is an abstract superclass and Atom and Residue are concrete subclasses.
These are the important parts of the mapping file:
Code:
<hibernate-mapping>
<class name="org.mmb.model.PDBEntry" table="entries">
<id name="idCode" type="string"/>
...
<set name="pdbElements" inverse="true" where="I_want_my_previous_query_here">
<key column="idCode"/>
<one-to-many class="org.mmb.model.PDBElement"/>
</set>
...
I've tried many different aproaches but it seems that Hibernate does not generate the SQL exactly how is written. It interpretes the SQL code and it seems not to like things like UNION or alias in subqueries.
Another proble is that I don't know how to specify parameters in the where attribute. I've tried this:
Code:
<set name="pdbElements" inverse="true" where="
pdbElementId IN (SELECT pdbElementId FROM((SELECT P.pdbElementId FROM PDBElement P, Atom A WHERE A.pdbElementId=P.pdbElementId AND idCode = ?) UNION (SELECT P.pdbElementId FROM PDBElement P, Residue R WHERE R.pdbElementId=P.pdbElementId AND idCode = ?)) as q)">
But then in the logs I receive a message that hibernate cannot bind these additional parameters...
I suppose I cannot parametrize the SQL code in the where attribute, but not sure...
Any ideas?
Thanks,
Login
FAQ
Search
