You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Cornillon Cecile <ce...@yahoo.fr> on 2005/07/20 16:52:11 UTC
Joins on several tables (at least 3)
Hello,
I am just starting with OJB, and I haven't found the answer to the following question in neither the archives neither the tutorials... (there was a pretty similar question back in February 2005 (
"Using join-queries to build up (complex) object trees") but I have not found an answer in the archives ....)
Basically what I am trying to achieve is the following :
SELECT Domain.IDDOM
FROM Context, Fonction, Domain
WHERE Context.IDCTXT=266353
AND Context.IDFONCTION=Fonction.IDFONCTION
AND Fonction.IDDOM=Domain.IDDOM
with the following 3 tables :
Domain :
idDom (primary key)
codeDom
libDom
....
Fonction :
idFonction (primary key)
codeFonction
libFonction
idDom (foreign key to table Domain)
...
Context :
idCtxt (primary key)
idFonction (foreign key to table Fonction)
...
One domain can be linked to several fonctions (1:n)
One fonction can be linked to several contexts (1:n)
I want to get the domain associated to one specific context, via the foreign keys.
My Java method is the following :
PersistenceBroker broker = null;
Criteria crit = new Criteria();
crit.addEqualTo("listeFonctions.listeContextes.idCtxt", new Integer(idCtxt));
QueryByCriteria query = new QueryByCriteria(Domain.class, crit);
log.debug("query.toString() = " + query.toString());
broker = PersistenceBrokerFactory.defaultPersistenceBroker();
Collection domaines = broker.getCollectionByQuery(query);
I get
java.lang.ArrayIndexOutOfBoundsException: 1
at org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$Join.appendJoinEqualities(SqlQueryStatement.java:2055)
Am I using the right request ? ("listeFonctions.listeContextes.idCtxt" ?)
For information, my repository is the following :
<!-- TABLE FONCTION -->
<class-descriptor class="com.inetpsa.infotec.sht.dtb.Fonction" table="FONCTION">
<field-descriptor id="1" name="idFonction" column="idFonction" jdbc-type="INTEGER" nullable="false" primarykey="true" />
<field-descriptor id="2" name="ordreFonction" column="ordreFonction" jdbc-type="INTEGER" />
<field-descriptor id="3" name="codeFonction" column="codeFonction" jdbc-type="VARCHAR" />
<field-descriptor id="4" name="libFonction" column="libFonction" jdbc-type="VARCHAR" />
<field-descriptor id="5" name="thFonction" column="thFonction" jdbc-type="VARCHAR" />
<field-descriptor id="6" name="idDom" column="idDom" jdbc-type="INTEGER" nullable="false" indexed="true" primarykey="true" />
<collection-descriptor
name="listeContextes"
element-class-ref="com.inetpsa.infotec.sht.dtb.Context"
proxy="false"
auto-retrieve="true"
auto-delete="false"
auto-update="false"
>
<inverse-foreignkey field-ref="idFonction"/>
</collection-descriptor>
</class-descriptor>
<!-- TABLE DOMAIN -->
<class-descriptor class="com.inetpsa.infotec.sht.dtb.Domain" table="DOMAIN">
<field-descriptor id="1" name="idDom" column="idDom" jdbc-type="INTEGER" nullable="false" primarykey="true" />
<field-descriptor id="2" name="codeDom" column="codeDom" jdbc-type="VARCHAR" />
<field-descriptor id="3" name="libDom" column="libDom" jdbc-type="VARCHAR" />
<field-descriptor id="4" name="thDom" column="thDom" jdbc-type="VARCHAR" />
<collection-descriptor
name="listeFonctions"
element-class-ref="com.inetpsa.infotec.sht.dtb.Fonction"
proxy="false"
auto-retrieve="true"
auto-delete="false"
auto-update="false"
>
<inverse-foreignkey field-ref="idDom"/>
</collection-descriptor>
</class-descriptor>
<!-- TABLE CONTEXT -->
<class-descriptor class="com.inetpsa.infotec.sht.dtb.Context" table="CONTEXT" >
<field-descriptor id="1" name="idCtxt" column="idCtxt" jdbc-type="INTEGER" nullable="false" primarykey="true" />
<field-descriptor id="2" name="idFonction" column="idFonction" jdbc-type="INTEGER" nullable="false" indexed="true" primarykey="true" />
<field-descriptor id="3" name="idDAM" column="idDAM" jdbc-type="INTEGER" nullable="false" indexed="true" primarykey="true" />
<field-descriptor id="4" name="idGrpDescs" column="IdGrpDescs" jdbc-type="INTEGER" nullable="false" indexed="true" primarykey="true" />
</class-descriptor>
Thanks a lot for your time and help ...
Cécile
---------------------------------
Appel audio GRATUIT partout dans le monde avec le nouveau Yahoo! Messenger
Téléchargez le ici !
RE: AW: Joins on several tables (at least 3)
Posted by Cornillon Cecile <ce...@yahoo.fr>.
Thank you all for your kindness and answers to my 2 last questions !
Christine, that was exactly the problem...
And thank you Tom for the link to p6Spy...
Cécile
Christine Gerstenmayer <ch...@chello.at> a écrit :
Hello Cecile,
I think, your coding is right.
But I got this exception at another problem: I didn't define a primary key
at one of my related tables.
When I regard your repository I see two primary keys in your table FONCTION.
Maybe this is the problem similar to my problem.
Sorry, but I don't know how to solve this, if these two primary keys are
required. But maybe someone of the others knows that.
Best regards,
Christine
-----Ursprüngliche Nachricht-----
Von: Cornillon Cecile [mailto:cecilecornillon@yahoo.fr]
Gesendet: Mittwoch, 20. Juli 2005 16:52
An: OJB users List
Betreff: Joins on several tables (at least 3)
Hello,
I am just starting with OJB, and I haven't found the answer to the following
question in neither the archives neither the tutorials... (there was a
pretty similar question back in February 2005 (
"Using join-queries to build up (complex) object trees") but I have not
found an answer in the archives ....)
Basically what I am trying to achieve is the following :
SELECT Domain.IDDOM
FROM Context, Fonction, Domain
WHERE Context.IDCTXT=266353
AND Context.IDFONCTION=Fonction.IDFONCTION
AND Fonction.IDDOM=Domain.IDDOM
with the following 3 tables :
Domain :
idDom (primary key)
codeDom
libDom
....
Fonction :
idFonction (primary key)
codeFonction
libFonction
idDom (foreign key to table Domain)
...
Context :
idCtxt (primary key)
idFonction (foreign key to table Fonction)
...
One domain can be linked to several fonctions (1:n)
One fonction can be linked to several contexts (1:n)
I want to get the domain associated to one specific context, via the foreign
keys.
My Java method is the following :
PersistenceBroker broker = null;
Criteria crit = new Criteria();
crit.addEqualTo("listeFonctions.listeContextes.idCtxt", new
Integer(idCtxt));
QueryByCriteria query = new QueryByCriteria(Domain.class, crit);
log.debug("query.toString() = " + query.toString());
broker = PersistenceBrokerFactory.defaultPersistenceBroker();
Collection domaines = broker.getCollectionByQuery(query);
I get
java.lang.ArrayIndexOutOfBoundsException: 1
at
org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$Join.appendJoinEqual
ities(SqlQueryStatement.java:2055)
Am I using the right request ? ("listeFonctions.listeContextes.idCtxt" ?)
For information, my repository is the following :
table="FONCTION">
jdbc-type="INTEGER" nullable="false" primarykey="true" />
jdbc-type="INTEGER" />
jdbc-type="VARCHAR" />
jdbc-type="VARCHAR" />
jdbc-type="VARCHAR" />
jdbc-type="INTEGER" nullable="false" indexed="true" primarykey="true" />
name="listeContextes"
element-class-ref="com.inetpsa.infotec.sht.dtb.Context"
proxy="false"
auto-retrieve="true"
auto-delete="false"
auto-update="false"
>
jdbc-type="INTEGER" nullable="false" primarykey="true" />
jdbc-type="VARCHAR" />
jdbc-type="VARCHAR" />
/>
name="listeFonctions"
element-class-ref="com.inetpsa.infotec.sht.dtb.Fonction"
proxy="false"
auto-retrieve="true"
auto-delete="false"
auto-update="false"
>
table="CONTEXT" >
jdbc-type="INTEGER" nullable="false" primarykey="true" />
jdbc-type="INTEGER" nullable="false" indexed="true"
primarykey="true" />
jdbc-type="INTEGER" nullable="false" indexed="true"
primarykey="true" />
jdbc-type="INTEGER" nullable="false" indexed="true"
primarykey="true" />
Thanks a lot for your time and help ...
Cécile
---------------------------------
Appel audio GRATUIT partout dans le monde avec le nouveau Yahoo! Messenger
Téléchargez le ici !
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------
Appel audio GRATUIT partout dans le monde avec le nouveau Yahoo! Messenger
Téléchargez le ici !
AW: Joins on several tables (at least 3)
Posted by Christine Gerstenmayer <ch...@chello.at>.
Hello Cecile,
I think, your coding is right.
But I got this exception at another problem: I didn't define a primary key
at one of my related tables.
When I regard your repository I see two primary keys in your table FONCTION.
Maybe this is the problem similar to my problem.
Sorry, but I don't know how to solve this, if these two primary keys are
required. But maybe someone of the others knows that.
Best regards,
Christine
-----Ursprüngliche Nachricht-----
Von: Cornillon Cecile [mailto:cecilecornillon@yahoo.fr]
Gesendet: Mittwoch, 20. Juli 2005 16:52
An: OJB users List
Betreff: Joins on several tables (at least 3)
Hello,
I am just starting with OJB, and I haven't found the answer to the following
question in neither the archives neither the tutorials... (there was a
pretty similar question back in February 2005 (
"Using join-queries to build up (complex) object trees") but I have not
found an answer in the archives ....)
Basically what I am trying to achieve is the following :
SELECT Domain.IDDOM
FROM Context, Fonction, Domain
WHERE Context.IDCTXT=266353
AND Context.IDFONCTION=Fonction.IDFONCTION
AND Fonction.IDDOM=Domain.IDDOM
with the following 3 tables :
Domain :
idDom (primary key)
codeDom
libDom
....
Fonction :
idFonction (primary key)
codeFonction
libFonction
idDom (foreign key to table Domain)
...
Context :
idCtxt (primary key)
idFonction (foreign key to table Fonction)
...
One domain can be linked to several fonctions (1:n)
One fonction can be linked to several contexts (1:n)
I want to get the domain associated to one specific context, via the foreign
keys.
My Java method is the following :
PersistenceBroker broker = null;
Criteria crit = new Criteria();
crit.addEqualTo("listeFonctions.listeContextes.idCtxt", new
Integer(idCtxt));
QueryByCriteria query = new QueryByCriteria(Domain.class, crit);
log.debug("query.toString() = " + query.toString());
broker = PersistenceBrokerFactory.defaultPersistenceBroker();
Collection domaines = broker.getCollectionByQuery(query);
I get
java.lang.ArrayIndexOutOfBoundsException: 1
at
org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement$Join.appendJoinEqual
ities(SqlQueryStatement.java:2055)
Am I using the right request ? ("listeFonctions.listeContextes.idCtxt" ?)
For information, my repository is the following :
<!-- TABLE FONCTION -->
<class-descriptor class="com.inetpsa.infotec.sht.dtb.Fonction"
table="FONCTION">
<field-descriptor id="1" name="idFonction" column="idFonction"
jdbc-type="INTEGER" nullable="false" primarykey="true" />
<field-descriptor id="2" name="ordreFonction" column="ordreFonction"
jdbc-type="INTEGER" />
<field-descriptor id="3" name="codeFonction" column="codeFonction"
jdbc-type="VARCHAR" />
<field-descriptor id="4" name="libFonction" column="libFonction"
jdbc-type="VARCHAR" />
<field-descriptor id="5" name="thFonction" column="thFonction"
jdbc-type="VARCHAR" />
<field-descriptor id="6" name="idDom" column="idDom"
jdbc-type="INTEGER" nullable="false" indexed="true" primarykey="true" />
<collection-descriptor
name="listeContextes"
element-class-ref="com.inetpsa.infotec.sht.dtb.Context"
proxy="false"
auto-retrieve="true"
auto-delete="false"
auto-update="false"
>
<inverse-foreignkey field-ref="idFonction"/>
</collection-descriptor>
</class-descriptor>
<!-- TABLE DOMAIN -->
<class-descriptor class="com.inetpsa.infotec.sht.dtb.Domain" table="DOMAIN">
<field-descriptor id="1" name="idDom" column="idDom"
jdbc-type="INTEGER" nullable="false" primarykey="true" />
<field-descriptor id="2" name="codeDom" column="codeDom"
jdbc-type="VARCHAR" />
<field-descriptor id="3" name="libDom" column="libDom"
jdbc-type="VARCHAR" />
<field-descriptor id="4" name="thDom" column="thDom" jdbc-type="VARCHAR"
/>
<collection-descriptor
name="listeFonctions"
element-class-ref="com.inetpsa.infotec.sht.dtb.Fonction"
proxy="false"
auto-retrieve="true"
auto-delete="false"
auto-update="false"
>
<inverse-foreignkey field-ref="idDom"/>
</collection-descriptor>
</class-descriptor>
<!-- TABLE CONTEXT -->
<class-descriptor class="com.inetpsa.infotec.sht.dtb.Context"
table="CONTEXT" >
<field-descriptor id="1" name="idCtxt" column="idCtxt"
jdbc-type="INTEGER" nullable="false" primarykey="true" />
<field-descriptor id="2" name="idFonction" column="idFonction"
jdbc-type="INTEGER" nullable="false" indexed="true"
primarykey="true" />
<field-descriptor id="3" name="idDAM" column="idDAM"
jdbc-type="INTEGER" nullable="false" indexed="true"
primarykey="true" />
<field-descriptor id="4" name="idGrpDescs" column="IdGrpDescs"
jdbc-type="INTEGER" nullable="false" indexed="true"
primarykey="true" />
</class-descriptor>
Thanks a lot for your time and help ...
Cécile
---------------------------------
Appel audio GRATUIT partout dans le monde avec le nouveau Yahoo! Messenger
Téléchargez le ici !
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org