You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by ni...@mpsa.com on 2006/01/04 11:02:37 UTC
bad sql on n-m relation to subclassed object
Hi,
It seems when i use a n-m relation to a subclassed object, the sql produced
by ojb is not good.
The interesting part of our mapping :
(note that the problem is because VersionJeuLimites inherits from Version
not because VersionPdS inherits also from Version.)
<class-descriptor class="com.inetpsa.suq.referentiel.pds.model.Version"
table="SUQQTVER">
<field-descriptor name="id"
column="VER_ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
access="readonly"/>
<field-descriptor column="VER_NUMERO" jdbc-type="INTEGER"
name="version"/>
</class-descriptor>
<class-descriptor
class="com.inetpsa.suq.referentiel.pds.model.limite.VersionJeuLimites"
table="SUQQTVJL">
<field-descriptor name="id"
column="VJL_VER_IDREF"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"/>
<reference-descriptor name="super"
class-ref="com.inetpsa.suq.referentiel.pds.model.Version"
auto-retrieve="true"
auto-update="true"
auto-delete="true"
>
<foreignkey field-ref="id"/>
</reference-descriptor>
<field-descriptor column="VJL_VAL_NOMINAL" jdbc-type="REAL"
name="valeurNominale"/>
<field-descriptor column="VJL_VAL_SURV_SUP" jdbc-type="REAL"
name="limiteSurveillanceSup"/>
<field-descriptor column="VJL_VAL_SURV_INF" jdbc-type="REAL"
name="limiteSurveillanceInf"/>
<field-descriptor column="VJL_VAL_TOL_SUP" jdbc-type="REAL"
name="limiteSup"/>
<field-descriptor column="VJL_VAL_TOL_INF" jdbc-type="REAL"
name="limiteInf"/>
</class-descriptor>
<class-descriptor class="com.inetpsa.suq.referentiel.pds.model.VersionPdS"
table="SUQQTVPS">
<field-descriptor name="id"
column="VPS_VER_IDREF"
jdbc-type="INTEGER"
primarykey="true"/>
<reference-descriptor name="super"
class-ref="com.inetpsa.suq.referentiel.pds.model.Version"
auto-retrieve="true"
auto-update="true"
auto-delete="true"
>
<foreignkey field-ref="id"/>
</reference-descriptor>
<collection-descriptor
name="versionsJeuLimite"
collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
element-class-ref="com.inetpsa.suq.referentiel.pds.model.limite.VersionJeuLimites"
auto-retrieve="true"
auto-update="false"
auto-delete="false"
indirection-table="SUQQTL9P"
>
<fk-pointing-to-this-class column="L9P_VPS_VER_IDREF"/>
<fk-pointing-to-element-class column="L9P_VJL_VER_IDREF"/>
</collection-descriptor>
</class-descriptor>
When i load an instance of VersionPdS, ojb tries to load the collection of
VersionJeuLimites.
The bad sql produced is :
SELECT
A0.VJL_VER_IDREF,A0.VJL_JLM_IDREF,A0.VJL_VAL_NOMINAL,A0.VJL_VAL_SURV_SUP,A0.VJL_VAL_SURV_INF,A0.VJL_VAL_TOL_SUP,A0.VJL_VAL_TOL_INF
FROM SUQQTVJL A0,
SUQQTL9P INNER JOIN SUQQTVER A1,SUQQTL9P ON A0.VJL_VER_IDREF=A1.VER_ID
WHERE (SUQQTL9P.L9P_VPS_VER_IDREF = ?) AND A0.VJL_VER_IDREF =
SUQQTL9P.L9P_VJL_VER_IDREF;
I suppose the good sql to produce would be :
SELECT
A0.VJL_VER_IDREF,A0.VJL_JLM_IDREF,A0.VJL_VAL_NOMINAL,A0.VJL_VAL_SURV_SUP,A0.VJL_VAL_SURV_INF,A0.VJL_VAL_TOL_SUP,A0.VJL_VAL_TOL_INF
FROM SUQQTVJL A0 INNER JOIN SUQQTVER A1 ON A0.VJL_VER_IDREF=A1.VER_ID,
SUQQTL9P
WHERE (SUQQTL9P.L9P_VPS_VER_IDREF = ?) AND A0.VJL_VER_IDREF =
SUQQTL9P.L9P_VJL_VER_IDREF;
So, is it an ojb bug ? What do you recommend ?
Nicolas DELAHAYE, Dimitri GOUX
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: bad sql on n-m relation to subclassed object
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi nicolas,
ojb just release 1.0.4. could you please try this as well ?
jakob
DELAHAYE Nicolas schrieb:
>
> Sorry for multi posting,
>
> Otherwise, the version we use is 1.03
>
>
>
> Jakob Braeuchi wrote:
>
>> hi nicolas,
>>
>> i recommend not to cross-post, and to post the same message only once ;)
>> further i recommend to tell us the version of ojb you're using.
>>
>> thank
>> jakob
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: bad sql on n-m relation to subclassed object
Posted by DELAHAYE Nicolas <de...@laposte.net>.
Sorry for multi posting,
Otherwise, the version we use is 1.03
Jakob Braeuchi wrote:
> hi nicolas,
>
> i recommend not to cross-post, and to post the same message only once ;)
> further i recommend to tell us the version of ojb you're using.
>
> thank
> jakob
--
Nicolas DELAHAYE
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: bad sql on n-m relation to subclassed object
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi nicolas,
i recommend not to cross-post, and to post the same message only once ;)
further i recommend to tell us the version of ojb you're using.
thank
jakob
nicolas.delahaye1@mpsa.com schrieb:
>
>
>
> Hi,
>
> It seems when i use a n-m relation to a subclassed object, the sql produced
> by ojb is not good.
>
> The interesting part of our mapping :
> (note that the problem is because VersionJeuLimites inherits from Version
> not because VersionPdS inherits also from Version.)
> <class-descriptor class="com.inetpsa.suq.referentiel.pds.model.Version"
> table="SUQQTVER">
> <field-descriptor name="id"
> column="VER_ID"
> jdbc-type="INTEGER"
> primarykey="true"
> autoincrement="true"
> access="readonly"/>
> <field-descriptor column="VER_NUMERO" jdbc-type="INTEGER"
> name="version"/>
> </class-descriptor>
>
> <class-descriptor
> class="com.inetpsa.suq.referentiel.pds.model.limite.VersionJeuLimites"
> table="SUQQTVJL">
> <field-descriptor name="id"
> column="VJL_VER_IDREF"
> jdbc-type="INTEGER"
> primarykey="true"
> autoincrement="true"/>
> <reference-descriptor name="super"
> class-ref="com.inetpsa.suq.referentiel.pds.model.Version"
> auto-retrieve="true"
> auto-update="true"
> auto-delete="true"
> >
> <foreignkey field-ref="id"/>
> </reference-descriptor>
> <field-descriptor column="VJL_VAL_NOMINAL" jdbc-type="REAL"
> name="valeurNominale"/>
> <field-descriptor column="VJL_VAL_SURV_SUP" jdbc-type="REAL"
> name="limiteSurveillanceSup"/>
> <field-descriptor column="VJL_VAL_SURV_INF" jdbc-type="REAL"
> name="limiteSurveillanceInf"/>
> <field-descriptor column="VJL_VAL_TOL_SUP" jdbc-type="REAL"
> name="limiteSup"/>
> <field-descriptor column="VJL_VAL_TOL_INF" jdbc-type="REAL"
> name="limiteInf"/>
>
> </class-descriptor>
>
> <class-descriptor class="com.inetpsa.suq.referentiel.pds.model.VersionPdS"
> table="SUQQTVPS">
> <field-descriptor name="id"
> column="VPS_VER_IDREF"
> jdbc-type="INTEGER"
> primarykey="true"/>
>
> <reference-descriptor name="super"
> class-ref="com.inetpsa.suq.referentiel.pds.model.Version"
> auto-retrieve="true"
> auto-update="true"
> auto-delete="true"
> >
> <foreignkey field-ref="id"/>
> </reference-descriptor>
> <collection-descriptor
> name="versionsJeuLimite"
>
> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
>
> element-class-ref="com.inetpsa.suq.referentiel.pds.model.limite.VersionJeuLimites"
> auto-retrieve="true"
> auto-update="false"
> auto-delete="false"
> indirection-table="SUQQTL9P"
> >
> <fk-pointing-to-this-class column="L9P_VPS_VER_IDREF"/>
> <fk-pointing-to-element-class column="L9P_VJL_VER_IDREF"/>
> </collection-descriptor>
> </class-descriptor>
>
> When i load an instance of VersionPdS, ojb tries to load the collection of
> VersionJeuLimites.
> The bad sql produced is :
> SELECT
> A0.VJL_VER_IDREF,A0.VJL_JLM_IDREF,A0.VJL_VAL_NOMINAL,A0.VJL_VAL_SURV_SUP,A0.VJL_VAL_SURV_INF,A0.VJL_VAL_TOL_SUP,A0.VJL_VAL_TOL_INF
> FROM SUQQTVJL A0,
> SUQQTL9P INNER JOIN SUQQTVER A1,SUQQTL9P ON A0.VJL_VER_IDREF=A1.VER_ID
> WHERE (SUQQTL9P.L9P_VPS_VER_IDREF = ?) AND A0.VJL_VER_IDREF =
> SUQQTL9P.L9P_VJL_VER_IDREF;
>
> I suppose the good sql to produce would be :
> SELECT
> A0.VJL_VER_IDREF,A0.VJL_JLM_IDREF,A0.VJL_VAL_NOMINAL,A0.VJL_VAL_SURV_SUP,A0.VJL_VAL_SURV_INF,A0.VJL_VAL_TOL_SUP,A0.VJL_VAL_TOL_INF
> FROM SUQQTVJL A0 INNER JOIN SUQQTVER A1 ON A0.VJL_VER_IDREF=A1.VER_ID,
> SUQQTL9P
> WHERE (SUQQTL9P.L9P_VPS_VER_IDREF = ?) AND A0.VJL_VER_IDREF =
> SUQQTL9P.L9P_VJL_VER_IDREF;
>
> So, is it an ojb bug ? What do you recommend ?
>
> Nicolas DELAHAYE, Dimitri GOUX
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: bad sql on n-m relation to subclassed object
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi nicolas,
i recommend not to cross-post, and to post the same message only once ;)
further i recommend to tell us the version of ojb you're using.
thank
jakob
nicolas.delahaye1@mpsa.com schrieb:
>
>
>
> Hi,
>
> It seems when i use a n-m relation to a subclassed object, the sql produced
> by ojb is not good.
>
> The interesting part of our mapping :
> (note that the problem is because VersionJeuLimites inherits from Version
> not because VersionPdS inherits also from Version.)
> <class-descriptor class="com.inetpsa.suq.referentiel.pds.model.Version"
> table="SUQQTVER">
> <field-descriptor name="id"
> column="VER_ID"
> jdbc-type="INTEGER"
> primarykey="true"
> autoincrement="true"
> access="readonly"/>
> <field-descriptor column="VER_NUMERO" jdbc-type="INTEGER"
> name="version"/>
> </class-descriptor>
>
> <class-descriptor
> class="com.inetpsa.suq.referentiel.pds.model.limite.VersionJeuLimites"
> table="SUQQTVJL">
> <field-descriptor name="id"
> column="VJL_VER_IDREF"
> jdbc-type="INTEGER"
> primarykey="true"
> autoincrement="true"/>
> <reference-descriptor name="super"
> class-ref="com.inetpsa.suq.referentiel.pds.model.Version"
> auto-retrieve="true"
> auto-update="true"
> auto-delete="true"
> >
> <foreignkey field-ref="id"/>
> </reference-descriptor>
> <field-descriptor column="VJL_VAL_NOMINAL" jdbc-type="REAL"
> name="valeurNominale"/>
> <field-descriptor column="VJL_VAL_SURV_SUP" jdbc-type="REAL"
> name="limiteSurveillanceSup"/>
> <field-descriptor column="VJL_VAL_SURV_INF" jdbc-type="REAL"
> name="limiteSurveillanceInf"/>
> <field-descriptor column="VJL_VAL_TOL_SUP" jdbc-type="REAL"
> name="limiteSup"/>
> <field-descriptor column="VJL_VAL_TOL_INF" jdbc-type="REAL"
> name="limiteInf"/>
>
> </class-descriptor>
>
> <class-descriptor class="com.inetpsa.suq.referentiel.pds.model.VersionPdS"
> table="SUQQTVPS">
> <field-descriptor name="id"
> column="VPS_VER_IDREF"
> jdbc-type="INTEGER"
> primarykey="true"/>
>
> <reference-descriptor name="super"
> class-ref="com.inetpsa.suq.referentiel.pds.model.Version"
> auto-retrieve="true"
> auto-update="true"
> auto-delete="true"
> >
> <foreignkey field-ref="id"/>
> </reference-descriptor>
> <collection-descriptor
> name="versionsJeuLimite"
>
> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
>
> element-class-ref="com.inetpsa.suq.referentiel.pds.model.limite.VersionJeuLimites"
> auto-retrieve="true"
> auto-update="false"
> auto-delete="false"
> indirection-table="SUQQTL9P"
> >
> <fk-pointing-to-this-class column="L9P_VPS_VER_IDREF"/>
> <fk-pointing-to-element-class column="L9P_VJL_VER_IDREF"/>
> </collection-descriptor>
> </class-descriptor>
>
> When i load an instance of VersionPdS, ojb tries to load the collection of
> VersionJeuLimites.
> The bad sql produced is :
> SELECT
> A0.VJL_VER_IDREF,A0.VJL_JLM_IDREF,A0.VJL_VAL_NOMINAL,A0.VJL_VAL_SURV_SUP,A0.VJL_VAL_SURV_INF,A0.VJL_VAL_TOL_SUP,A0.VJL_VAL_TOL_INF
> FROM SUQQTVJL A0,
> SUQQTL9P INNER JOIN SUQQTVER A1,SUQQTL9P ON A0.VJL_VER_IDREF=A1.VER_ID
> WHERE (SUQQTL9P.L9P_VPS_VER_IDREF = ?) AND A0.VJL_VER_IDREF =
> SUQQTL9P.L9P_VJL_VER_IDREF;
>
> I suppose the good sql to produce would be :
> SELECT
> A0.VJL_VER_IDREF,A0.VJL_JLM_IDREF,A0.VJL_VAL_NOMINAL,A0.VJL_VAL_SURV_SUP,A0.VJL_VAL_SURV_INF,A0.VJL_VAL_TOL_SUP,A0.VJL_VAL_TOL_INF
> FROM SUQQTVJL A0 INNER JOIN SUQQTVER A1 ON A0.VJL_VER_IDREF=A1.VER_ID,
> SUQQTL9P
> WHERE (SUQQTL9P.L9P_VPS_VER_IDREF = ?) AND A0.VJL_VER_IDREF =
> SUQQTL9P.L9P_VJL_VER_IDREF;
>
> So, is it an ojb bug ? What do you recommend ?
>
> Nicolas DELAHAYE, Dimitri GOUX
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org