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.