I hope someone can help me. I'm in over my head...
I am experimenting with Castor JDO at the moment (0.9.5.2) and am having a problem where Castor appears to be generating invalid SQL against my HSQLDB 1.7.1 database.
I have a class 'Topic' which uses a 'parent' field to allow topics to refer to each other in a hierarchy. By trial and error I came up with the following mapping for the class:
<class name="Topic" identity="id" key-generator="IDENTITY">
<map-to table="topics"/><field name="id" type="java.lang.Integer">
<sql name="id" type="integer"/>
</field>
<field name="name" type="java.lang.String">
<sql name="name" type="varchar"/>
</field>
<field name="parent" type="Topic">
<sql name="parent"/>
</field>
<field name="subTopics" type="Topic" required="true" collection="arraylist">
<sql many-table="topics" many-key="parent"/>
</field>
</class>
for the table built with:
create table topics ( id int identity, name varchar(64) not null, parent int );
To get a list of top-level topics (ID=-1) I formulated an OQL query:
SELECT t FROM Topic t WHERE parent = -1
and the SQL being generated is:
SELECT topics.id,topics.name,topics.parent,topics_f2.id FROM topics LEFT OUTER JOIN topics_f2 ON (topics.id=topics_f2.parent) WHERE topics.parent = - 1
which is failing because no table 'topics_f2' exists in the database.
Using command line SQL I amended the query to:
SELECT topics.id,topics.name,topics.parent,f2.id FROM topics LEFT OUTER JOIN topics f2 ON (topics.id=f2.parent) WHERE topics.parent = - 1;
[by changing 'topics_f2' to 'topics f2' and using 'f2' as the table reference from there on.]
Using this modified query I get what look like valid results:
ID NAME PARENT ID 0 A -1 2 0 A -1 3 0 A -1 4 1 B -1 5 1 B -1 6
where topics with ID 2,3,4 have parent 0 and 5,6 have parent 1. I am assuming that Castor would then lazy-instantiate the corresponding child Topics and populate the sub-topics array on request.
I searched the archives and BugZilla before posting. I did find someone who seems to have logged the exact same problem (http://www.mail-archive.com/[EMAIL PROTECTED]/msg15923.html) couldn't see a solution linked from that.
I'd be really grateful if someone could advise me how to go forward with this problem.
Regards,
Matt
----------------------------------------------------------- If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
