hi,friends
The Device has a recursive many-to-many relation as below:

<class name="com.omnet.test.Device" table="device">
<id name="devId">
<column name="dev_id" sql-type="int" not-null="true"/>
<generator class="native"/>
</id>
<property name="devName" type="string">
<column name="dev_name" sql-type="varchar(30)" not-null="true"/>
</property>
<bag name="parent" lazy="true" table="device_relation">
<key column="child_id"/>
<many-to-many column="parent_id" class="com.omnet.test.Device"/>
</bag>
</class>

then how to find the root device which has no parents ?

regards!

MySQL i use is version 4.0.4 which doesnot support sub-query :(
so the following query will not work:

select * from device d where d.dev_id not in (select child_id from device_relation);

then the HQL is more difficult to express it ?!


regards!

Maybe it is simple using HQL if i know how to determine whether or not the collection is empty :-)

thanks in advance !

select * from device d ,device_relation r where r.child_id <> d.dev_id;


In such a situation i really donot know how to express the above SQL using HQL :(

please shield a light on me,thanks again !

i have tried size(),count(),exists() but all need sub-query support :(

the following is some sql generated by hibernate:

select device0_.dev_id as x0_0_ from device device0_
where (count( (select parent1_.parent_id from device_relation parent1_ where device0_.dev_id=parent1_.child_id) )=0 )

select device0_.dev_id as x0_0_ from device device0_ where ((select count(*) from device_relation parent1_ where device0_.dev_id=parent1_.child_id)=0 )


all sql is right but mysql doesnot support. then what can i do to avoid to use sub-query ?


thanks !

Use a database with subquery support.

thanks for your answer ! however we can NOT change db now because we have done many job on mysql :(


regards!

I know it is not best but it works now in my current situation. I do twice query to resolve the sub-query problem as below:


private String get_root_device()
{
StringWriter result = new StringWriter();
Session sess = null;
try {
Marshaller marshaller = new Marshaller(result);
sess = sf.openSession();

Query q = sess.createQuery("select d.devId from com.omnet.test.Device d join d.parent p");
List tmp = q.list();

q = sess.createQuery("select d from com.omnet.test.Device d where not d.devId in (:tmp)");
q.setParameterList("tmp", tmp);
Iterator iter = q.iterate();
while ( iter.hasNext() ) {
marshaller.marshal( (com.omnet.test.Device)iter.next() );
}
}
catch(Exception e) {
e.printStackTrace();
}
finally {
if(sess != null) {
try {
sess.close();
}
catch (HibernateException e1) {
e1.printStackTrace();
}
}
}

String tmp = result.toString();
if( tmp.indexOf("?>") > 0)
tmp = tmp.substring( tmp.indexOf("?>") + 2) ;
return ( new StringBuffer() ).append("<?xml version='1.0'?>")
.append("<devices>")
.append( tmp )
.append("</devices>").toString();
}

Although it works i still wait for the right answer :-)