Hi,
in the 1.0rc1 version, some new thingy is that you are able to define
aliases with you're criteria. checkout the criteria object!
with this functionality you can solve you're problem.
Below is an excerpt of the new documentation (or soon to become it):
For the examples below, suppose you have the following classes
(pseudo-code):
class Container
int id
Collection allAbstractAttributes
class AbstractAttribute
int id
inf ref_id
String name
String value
Collection allAbstractAttributes
OJB maps these classes to seperate tables where it maps
allAbstractAttributes using a collectiondescriptor to AbstractAttribute
using ref_id as inverse foreignkey on Container for the collection
descriptor.
For demo purposes : AbstractAttribute also has a collection of abstract
attributes.
Example 1:
Criteria crit1 = new Criteria();
crit1.setAlias("company");
crit1.addEqualTo("allAbstractAttributes.name", new
String("companyName"));
crit1.addEqualTo("allAbstractAttributes.value", new String("iBanx"));
Criteria crit2 = new Criteria();
crit2.addEqualTo("allAbstractAttributes.name", new
String("contactPerson"));
crit2.addLike("allAbstractAttributes.value", new String("janssen"));
Criteria crit3 = new Criteria();
crit3.addEqualTo("allAbstractAttributes.name", new String("size"));
crit3.addGreaterThan("allAbstractAttributes.value", new Integer(500));
crit1.addAndCriteria(crit2);
crit1.addAndCriteria(crit3);
crit1.addOrderBy("company.value");
q = QueryFactory.newQuery(Container.class, crit1);
the generated query will be:
SELECT DISTINCT A0.ID, A1.VALUE
FROM CONTAINER A0 INNER JOIN ABSTRACT_ATTRIBUTE A1
ON A0.ID=A1.REF_ID
INNER JOIN ABSTRACT_ATTRIBUTE A2
ON A0.ID=A2.REF_ID
INNER JOIN ABSTRACT_ATTRIBUTE A3
ON A0.ID=A3.REF_ID
WHERE (( A0.NAME = 'companyName' ) AND (A0.VALUE = 'iBanx' )) AND
(( A1.NAME = 'contactPerson' ) AND (A1.VALUE LIKE '%janssen%' ))
AND
(( A2.NAME = 'size' ) AND (A2.VALUE = '500' ))
ORDER BY 2
Example 2 (report query):
Criteria crit1 = new Criteria();
crit1.setAlias("ALIAS1");
crit1.addEqualTo("allAbstractAttributes.allAbstractAttributes.name", new
String("xxxx"));
crit1.addEqualTo("allAbstractAttributes.allAbstractAttributes.value",
new String("hello"));
Criteria crit2 = new Criteria();
crit2.setAlias("ALIAS2");
crit2.addEqualTo("allAbstractAttributes.name", new String("yyyy"));
crit2.addLike("allAbstractAttributes.value", new String(""));
crit1.addAndCriteria(crit2);
q = QueryFactory.newReportQuery(Container.class, crit1);
String[] cols = { id, "ALIAS2.name", "ALIAS2.name", "ALIAS1.name",
"ALIAS1.name" };
q.setColumns(cls);
the generated query will be:
SELECT DISTINCT A0.ID, A1.NAME, A1.VALUE, A2.NAME, A2.VALUE
FROM CONTAINER A0 INNER JOIN ABSTRACT_ATTRIBUTE A1
ON A0.ID=A1.REF_ID
INNER JOIN ABSTRACT_ATTRIBUTE A2
ON A1.ID=A2.REF_ID
WHERE (( A2.NAME = 'xxxx' ) AND (A2.VALUE = 'hello' )) AND
(( A1.NAME = 'yyyy' ) AND (A2.VALUE LIKE '%%' )) AND
ORDER BY 2
Roger Janssen mailto:[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>
iBanx B.V http://www.ibanx.nl <http://www.ibanx.nl/>
Kon. Wilhelminaplein 13 tel +31-20-5727900
P.O. Box 69289 fax +31-20-5727901
1060 CH Amsterdam mobile +31-6-505 267 13
The Netherlands
-----Original Message-----
From: Hennebelle [mailto:[EMAIL PROTECTED]
Sent: 07 March 2003 13:38
To: [EMAIL PROTECTED]
Subject: Problem with conditionnal joins
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]
*************************************************************************
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed.You should not copy, disclose or distribute this communication
without the authority of iBanx bv. iBanx bv is neither liable for
the proper and complete transmission of the information has been maintained
nor that the communication is free of viruses, interceptions or interference.
If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
