Jakob Braeuchi wrote:
hi all,
i adapted rogers pathes to the current version of ojb and committed them to the repository
thanks roger. the following text (also by roger) explains the reason for this patch:
Suppose you have two classes Issue and Keyword and there is a 1:N relation ship
between them. Now you want to retrieve Issues's by querying on Keword's.
Suppose want to retrieve all Issue with keywords 'JOIN' and 'ALIAS'. If these values
are stored in the attribute 'value' of Keyword, OJB generates a query that contains
" A1.value = 'JOIN' AND A1.value = 'ALIAS' " in the where-clause. Obviously,
this will not work, no hits will occur because A1.value can not have
more then 1 value at the time!!!!!
OJB should generate queries using joins and aliases for the keyword instances.
The given patch will do that for you.
Functionally it means that you have to be able to group Criteria and/or Selection
Criteria together in terms of that they will be using the same alias in their
respective where-clauses in the generated query.
New constructors to the selection criteria classes have been added so an
logical aliasname can be given to them. SelectionCriteria and Criteria using
the same aliasname will be using the same alias in the generated sql query.
The Criteria object has setter/getter methods to set the alias. If the setter
is used, the aliasname is propagated to all its SelectionCriteria, but not to
its nested Criteria, they may or may not have thier own aliasname. The use of
these aliases is optional.
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
Their are many,many more powerfull things you are able to do with the functionality
provided by this patch. These are not far-fetched, we use them in our applications today!
In fact, imho it is required functionality.
hth jakob
Janssen, Roger wrote:
and of course... this way it becomes an OR relation between the keywords...
i want the issues that have all three keywords... not just one of them,
thats why the multiple aliases are needed
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: Gelhar, Wallace J. [mailto:[EMAIL PROTECTED] Sent: 25 February 2003 18:01 To: OJB Users List Subject: RE: How to use multiple aliases for one class/table in query
Why would it not use an IN clause?
SELECT DISTINCT * FROM ISSUE A0 INNER JOIN KEYWORD A1 ON A0.ID=A1.OID WHERE A1.VALUE IN ('OJB', 'join', 'alias')
Wally
-----Original Message-----
From: Janssen, Roger [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 10:55 AM
To: OJB Users List (E-mail)
Subject: How to use multiple aliases for one class/table in query
Hi,
I posed this question earlier but i haven't seen any response yet. I'll try it again, a bit different this time.
Suppose i have an application registering issues, and in which i can assign keywords to issues. The keywords are stored in a different table (with a fk-relation to the related issue).
OJB could automatically load the related keywords when fethcing the issue.
So far, so good.
Now i would like to retrieve all issues that have the following keywords; 'OJB', 'join' and 'alias'.
The generated query should be something like this:
SELECT DISTINCT * FROM ISSUE A0 INNER JOIN KEYWORD A1 ON A0.ID=A1.OID INNER JOIN KEYWORD A2 ON A0.ID=A2.OID
INNER JOIN KEYWORD A3 ON A0.ID=A3.OID
INNER JOIN KEYWORD A4 ON A0.ID=A4.OID WHERE (
A1.VALUE= 'OJB' ) AND ( A2.NAME = 'join' ) AND (A3.VALUE = 'alias' )
Using the PB API, and the query/criteria classes, how can i get this query to be generated and executed?
I do not think this is some weird use-case? I know the OJB OQL does not support 'alias'-ing (according to documentation en some experiments i have done) so ODMG api is no option (i also don't want to use it). PB api supports 'alias'-ing but i can not seem to get OJB to generate multiple aliases in one query for the same objectclass/table.
What am i missing?
Roger Janssen <mailto:[EMAIL PROTECTED]>
mailto:[EMAIL PROTECTED] iBanx B.V <http://www.ibanx.nl/> http://www.ibanx.nl
************************************************************************ * 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]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
