Hibernate can't load Custom SQL collection

Question

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>

Answer 1

According to the documentation, you're supposed to return the value of the computed foreign key, nothing more:

formula (optional): an SQL expression that defines the value for a computed foreign key.

So I would expect a query like this:

select i.code
from Item i 
where (
  case length(code) 
    when 4 then i.code=SUBSTRING(code,1,2)
    else false
  end)

Disclaimer: not tested.