Hibernate can't load Custom SQL collection
- by Geln Yang
Hi,
There is a table Item like,
code,name
01,parent1
02,parent2
0101,child11
0102,child12
0201,child21
0202,child22
Create a java object and hbm xml to map the table.The Item.parent is a Item whose code is equal to the first two characters of its code :
class Item{
String code;
String name;
Item parent;
List<Item> children;
.... setter/getter....
}
<hibernate-mapping>
<class name="Item" table="Item">
<id name="code" length="4" type="string">
<generator class="assigned" />
</id>
<property name="name" column="name" length="50" not-null="true" />
<many-to-one name="parent" class="Item" not-found="ignore">
<formula>
<![CDATA[
(select i.code,r.name from Item i where (case length(code) when 4 then i.code=SUBSTRING(code,1,2) else false end))
]]>
</formula>
</many-to-one>
<bag name="children"></bag>
</class>
</hibernate-mapping>
I try to use formula to define the many-to-one relationship,but it doesn't work!Is there something wrong?Or is there other method?
Thanks!
ps,I use mysql database.
add 2010/05/23
Pascal's answer is right,but the "false" value must be replaced with other expression,like "1=2".Because the "false" value would be considered to be a column of the table.
select i.code
from Item i
where (
case length(code)
when 4 then i.code=SUBSTRING(code,1,2)
else 1=2
end)
And I have another question about the children "bag" mapping.There isn't formula configuration option for "bag",but we can use "loader" to load a sql-query.I configure the "bag" as following.But it get a list whose size is 0.What's wrong with it?
<class>
... ...
<bag name="children">
<key />
<one-to-many class="Item"></one-to-many>
<loader query-ref="getChildren"></loader>
</bag>
</class>
<sql-query name="getChildren">
<load-collection alias="r" role="Item.children" />
<![CDATA[(select {r.*} from Item r join Item o where
o.code=:code and
(
case length(o.code)
when 2 then (length(r.code)=4 and SUBSTRING(r.code,1,2)=o.code)
else 1=2
end ))]]>
</sql-query>
