Hello,
I tried making joins by using multiple reference-descriptors. Here is an
explanation of the schema I have :
table A is related to tables B and C ; the join between tables A and B
depends on the value of column C : if this column contains "XXX", then A's D
column joins B on its "XXX" column , else if this column contains "YYY", A's
D column joins B on it "YYY" columns. So, to achieve this, I decided to add
two "reference-descriptor" : one linking A to B on the "XXX" column, and one
linking A to B on the "YYY" column
Here is the select that I would issue using JDBC :
select * from B B, A A1, A A2
where A1.D = B.XXX AND A1.C = 'XXX' AND A2.D = B.YYY AND A2.C = 'YYY';
The problem is that when I use P6Spy to see what SQL requests are sent to
the database, I see that :
1 - when I obtain an Iterator (getIteratorByQuery in PersistanceBroker),
only one request is issued, which suits me fine
2 - when I use the Iterator's getNext() method, I see that 2 extra requests
are sent to the database, and this for each getNext().
This second behavior is a major problem for us, as it dramatically slows
down performance, all the more so as I see I could be doing it issuing a
single SQL statement.
Here is my code :
criteria.addEqualTo("A1.C", "XXX");
criteria.addEqualTo("A2.C", "YYY");
Query query = QueryFactory.newQuery(ErrBO.class,criteria);
iterator = broker.getIteratorByQuery(query);
Here are my reference-descriptors (defined in B's class descriptor) :
<reference-descriptor name="A1" class-ref="mypackage.A1Class">
<foreignkey field-id-ref="1"/>
</reference-descriptor>
<reference-descriptor name="A2" class-ref="mypackage.A2Class">
<foreignkey field-id-ref="4"/>
</reference-descriptor>
Am I doing anything wrong ? Should I be defining reference-descriptor
another way ?
Thanks for answering.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
