CMR self-reference problem
ben2 Jan 19, 2003 7:54 PMI am having some problems doing a finder on the table defined below. I am using jboss3.0.5
Here is my EJB-QL
SELECT DISTINCT OBJECT(t) FROM Thread t WHERE t.topic.topicId = ?1 AND t.thread IS NULL
JBossCMP is generating the following SQL from this
SELECT DISTINCT t0_t.thread_id FROM thread t0_t, topic t1_t_topic WHERE (t1_t_topic.topic_id = ? AND t0_t.parent_thread_id IS NULL) AND (t0_t.topic_id=t1_t_topic.topic_id AND t0_t.parent_thread_id=t0_t.thread_id)
notice the very last equality statement(t0_t.parent_thread_id=t0_t.thread_id), this should not be here and I don't know why it is.
when it should be
SELECT DISTINCT t0_t.thread_id
FROM thread t0_t, topic t1_t_topic
WHERE
(t1_t_topic.topic_id = 60 AND t0_t.parent_thread_id IS NULL) AND
(t0_t.topic_id=t1_t_topic.topic_id )
Is this a bug or am I declaring something wrong?
Here are my defined methods
public abstract java.util.Collection getThreads();
public abstract void setThreads(java.util.Collection threads);
public abstract org.kelut.discourse.entity.ThreadLocal getThread();
public abstract void setThread(org.kelut.discourse.entity.ThreadLocal thread);
Here is my DB table definition
CREATE TABLE thread
(
thread_id integer NOT NULL,
parent_thread_id integer,-- REFERENCES thread thread_id)
topic_id integer,
subject varchar (4000),
body varchar (4000),
created timestamp,
created_by integer,
PRIMARY KEY (thread_id)
);
Here is ejb-jar.xml
<ejb-relation >
<ejb-relation-name>thread-cmp20-thread-cmp20</ejb-relation-name>
<ejb-relationship-role >
<ejb-relationship-role-name>thread-cmp20-2-has-thread-cmp20-1</ejb-relationship-role-name>
Many
<relationship-role-source >
<ejb-name>Thread</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>thread</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role >
<ejb-relationship-role-name>thread-cmp20-1-has-thread-cmp20-2</ejb-relationship-role-name>
One
<relationship-role-source >
<ejb-name>Thread</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>threads</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
Here is jbosscmp-jdbc.xml
<ejb-relation>
<ejb-relation-name>thread-cmp20-thread-cmp20</ejb-relation-name>
<foreign-key-mapping/>
<ejb-relationship-role>
<ejb-relationship-role-name>thread-cmp20-2-has-thread-cmp20-1</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields/>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>thread-cmp20-1-has-thread-cmp20-2</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>threadId</field-name>
<column-name>parent_thread_id</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
</ejb-relation>