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 Bobby Lawrence <ro...@jlab.org> on 2005/03/14 19:48:13 UTC
composite foreign key references
Using the PersistenceBroker API...
I have a class that has a 1:n relationship -> a project can have many
principal investigators.
The class descriptors look like this:
<class-descriptor class="org.jlab.mis.services.mics.client.generated.Project" table="projects">
<field-descriptor name="id" column="PROJECT_ID" jdbc-type="VARCHAR"/>
<field-descriptor name="label" column="REPO_CODE" jdbc-type="VARCHAR" primarykey="true" access="anonymous" />
<field-descriptor name="fiscalYear" column="FISCAL_YEAR" jdbc-type="VARCHAR" primarykey="true" />
<field-descriptor name="name" column="PROJECT_NAME" jdbc-type="VARCHAR" />
<collection-descriptor name="primaryInvestigators" element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator" auto-retrieve="false">
<inverse-foreignkey field-ref="projectId" />
<inverse-foreignkey field-ref="fiscalYear" />
</collection-descriptor>
</class-descriptor>
<class-descriptor class="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator" table="pis">
<field-descriptor name="id" column="PERSON_ID" jdbc-type="VARCHAR" primarykey="true"/>
<field-descriptor name="firstName" column="FIRSTNAME" jdbc-type="VARCHAR" />
<field-descriptor name="lastName" column="LASTNAME" jdbc-type="VARCHAR" />
<field-descriptor name="email" column="EMAIL" jdbc-type="VARCHAR" />
<field-descriptor name="fiscalYear" column="FISCAL_YEAR" jdbc-type="VARCHAR" access="anonymous" primarykey="true"/>
<field-descriptor name="projectId" column="PROJECT_ID" jdbc-type="VARCHAR" access="anonymous" />
</class-descriptor>
The problem is, whenever OJB tries to retrieve a collection of Primary
Investigators, it never uses the correct value for the composite primary
key.
The debugging output looks like this:
[org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG: executeQuery :
QueryByCriteria from class
org.jlab.mis.services.mics.client.generated.PrimaryInvestigator where
[projectId = 13179, fiscalYear = 13179]
As you can see, it is using the exact same value for the projectId and
fiscalYear attributes.
What am I doing wrong?
--
----------------------------
Bobby Lawrence
MIS Application Developer
Jefferson Lab (www.jlab.org)
Email: robertl@jlab.org
Office: (757) 269-5818
Pager: (757) 584-5818
----------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: composite foreign key references
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi bobby,
http://marc.theaimsgroup.com/?l=ojb-user&m=110504711807796&w=4
an example can be found in NoPkReferenceTest
jakob
Bobby Lawrence schrieb:
> Jakob -
> Where can I find info about this new feature?
>
> Jakob Braeuchi wrote:
>
>> hi bobby,
>>
>> your problem could be solved by the new relationship-definitions based
>> on non-pk-fields.
>> this feature is available in the cvs trunk for ojb 1.1
>>
>> Armin Waibel schrieb:
>>
>>> Hi,
>>>
>>> Project --1:n --> PrimaryInvestigator (PI)
>>> PK fields for Project:
>>> label
>>> fiscalYears
>>> why is 'id' not the PK in Project?
>>>
>>> In PI FK are:
>>> projectId
>>> fiscalYear
>>>
>>> projectId id not a PK in Project, you have to use a 'labelId'.
>>>
>>> As a side-note, be really careful when using anonymous fields for
>>> fields other than 1:1 FK.
>>>
>>> http://db.apache.org/ojb/docu/guides/advanced-technique.html#How+do+
>>>
>>> regards,
>>> Armin
>>>
>>>
>>> Bobby Lawrence wrote:
>>>
>>>> Using the PersistenceBroker API...
>>>> I have a class that has a 1:n relationship -> a project can have
>>>> many principal investigators.
>>>> The class descriptors look like this:
>>>>
>>>> <class-descriptor
>>>> class="org.jlab.mis.services.mics.client.generated.Project"
>>>> table="projects">
>>>> <field-descriptor name="id" column="PROJECT_ID"
>>>> jdbc-type="VARCHAR"/>
>>>> <field-descriptor name="label" column="REPO_CODE"
>>>> jdbc-type="VARCHAR" primarykey="true" access="anonymous" />
>>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>>> jdbc-type="VARCHAR" primarykey="true" />
>>>> <field-descriptor name="name" column="PROJECT_NAME"
>>>> jdbc-type="VARCHAR" />
>>>> <collection-descriptor name="primaryInvestigators"
>>>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>> auto-retrieve="false">
>>>> <inverse-foreignkey field-ref="projectId" />
>>>> <inverse-foreignkey field-ref="fiscalYear" />
>>>> </collection-descriptor>
>>>> </class-descriptor>
>>>>
>>>> <class-descriptor
>>>> class="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>> table="pis">
>>>> <field-descriptor name="id" column="PERSON_ID"
>>>> jdbc-type="VARCHAR" primarykey="true"/>
>>>> <field-descriptor name="firstName" column="FIRSTNAME"
>>>> jdbc-type="VARCHAR" />
>>>> <field-descriptor name="lastName" column="LASTNAME"
>>>> jdbc-type="VARCHAR" />
>>>> <field-descriptor name="email" column="EMAIL" jdbc-type="VARCHAR" />
>>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>>> jdbc-type="VARCHAR" access="anonymous" primarykey="true"/>
>>>> <field-descriptor name="projectId" column="PROJECT_ID"
>>>> jdbc-type="VARCHAR" access="anonymous" />
>>>> </class-descriptor>
>>>>
>>>> The problem is, whenever OJB tries to retrieve a collection of
>>>> Primary Investigators, it never uses the correct value for the
>>>> composite primary key.
>>>> The debugging output looks like this:
>>>>
>>>> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG:
>>>> executeQuery : QueryByCriteria from class
>>>> org.jlab.mis.services.mics.client.generated.PrimaryInvestigator
>>>> where [projectId = 13179, fiscalYear = 13179]
>>>>
>>>> As you can see, it is using the exact same value for the projectId
>>>> and fiscalYear attributes.
>>>> What am I doing wrong?
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-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
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: composite foreign key references
Posted by Bobby Lawrence <ro...@jlab.org>.
Jakob -
Where can I find info about this new feature?
Jakob Braeuchi wrote:
> hi bobby,
>
> your problem could be solved by the new relationship-definitions based
> on non-pk-fields.
> this feature is available in the cvs trunk for ojb 1.1
>
> Armin Waibel schrieb:
>
>> Hi,
>>
>> Project --1:n --> PrimaryInvestigator (PI)
>> PK fields for Project:
>> label
>> fiscalYears
>> why is 'id' not the PK in Project?
>>
>> In PI FK are:
>> projectId
>> fiscalYear
>>
>> projectId id not a PK in Project, you have to use a 'labelId'.
>>
>> As a side-note, be really careful when using anonymous fields for
>> fields other than 1:1 FK.
>>
>> http://db.apache.org/ojb/docu/guides/advanced-technique.html#How+do+
>>
>> regards,
>> Armin
>>
>>
>> Bobby Lawrence wrote:
>>
>>> Using the PersistenceBroker API...
>>> I have a class that has a 1:n relationship -> a project can have
>>> many principal investigators.
>>> The class descriptors look like this:
>>>
>>> <class-descriptor
>>> class="org.jlab.mis.services.mics.client.generated.Project"
>>> table="projects">
>>> <field-descriptor name="id" column="PROJECT_ID"
>>> jdbc-type="VARCHAR"/>
>>> <field-descriptor name="label" column="REPO_CODE"
>>> jdbc-type="VARCHAR" primarykey="true" access="anonymous" />
>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>> jdbc-type="VARCHAR" primarykey="true" />
>>> <field-descriptor name="name" column="PROJECT_NAME"
>>> jdbc-type="VARCHAR" />
>>> <collection-descriptor name="primaryInvestigators"
>>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>> auto-retrieve="false">
>>> <inverse-foreignkey field-ref="projectId" />
>>> <inverse-foreignkey field-ref="fiscalYear" />
>>> </collection-descriptor>
>>> </class-descriptor>
>>>
>>> <class-descriptor
>>> class="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>> table="pis">
>>> <field-descriptor name="id" column="PERSON_ID"
>>> jdbc-type="VARCHAR" primarykey="true"/>
>>> <field-descriptor name="firstName" column="FIRSTNAME"
>>> jdbc-type="VARCHAR" />
>>> <field-descriptor name="lastName" column="LASTNAME"
>>> jdbc-type="VARCHAR" />
>>> <field-descriptor name="email" column="EMAIL" jdbc-type="VARCHAR" />
>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>> jdbc-type="VARCHAR" access="anonymous" primarykey="true"/>
>>> <field-descriptor name="projectId" column="PROJECT_ID"
>>> jdbc-type="VARCHAR" access="anonymous" />
>>> </class-descriptor>
>>>
>>> The problem is, whenever OJB tries to retrieve a collection of
>>> Primary Investigators, it never uses the correct value for the
>>> composite primary key.
>>> The debugging output looks like this:
>>>
>>> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG:
>>> executeQuery : QueryByCriteria from class
>>> org.jlab.mis.services.mics.client.generated.PrimaryInvestigator
>>> where [projectId = 13179, fiscalYear = 13179]
>>>
>>> As you can see, it is using the exact same value for the projectId
>>> and fiscalYear attributes.
>>> What am I doing wrong?
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-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
>
--
----------------------------
Bobby Lawrence
MIS Application Developer
Jefferson Lab (www.jlab.org)
Email: robertl@jlab.org
Office: (757) 269-5818
Pager: (757) 584-5818
----------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: composite foreign key references
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi bobby,
your problem could be solved by the new relationship-definitions based
on non-pk-fields.
this feature is available in the cvs trunk for ojb 1.1
Armin Waibel schrieb:
> Hi,
>
> Project --1:n --> PrimaryInvestigator (PI)
> PK fields for Project:
> label
> fiscalYears
> why is 'id' not the PK in Project?
>
> In PI FK are:
> projectId
> fiscalYear
>
> projectId id not a PK in Project, you have to use a 'labelId'.
>
> As a side-note, be really careful when using anonymous fields for
> fields other than 1:1 FK.
>
> http://db.apache.org/ojb/docu/guides/advanced-technique.html#How+do+
>
> regards,
> Armin
>
>
> Bobby Lawrence wrote:
>
>> Using the PersistenceBroker API...
>> I have a class that has a 1:n relationship -> a project can have
>> many principal investigators.
>> The class descriptors look like this:
>>
>> <class-descriptor
>> class="org.jlab.mis.services.mics.client.generated.Project"
>> table="projects">
>> <field-descriptor name="id" column="PROJECT_ID" jdbc-type="VARCHAR"/>
>> <field-descriptor name="label" column="REPO_CODE"
>> jdbc-type="VARCHAR" primarykey="true" access="anonymous" />
>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>> jdbc-type="VARCHAR" primarykey="true" />
>> <field-descriptor name="name" column="PROJECT_NAME"
>> jdbc-type="VARCHAR" />
>> <collection-descriptor name="primaryInvestigators"
>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>> auto-retrieve="false">
>> <inverse-foreignkey field-ref="projectId" />
>> <inverse-foreignkey field-ref="fiscalYear" />
>> </collection-descriptor>
>> </class-descriptor>
>>
>> <class-descriptor
>> class="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>> table="pis">
>> <field-descriptor name="id" column="PERSON_ID" jdbc-type="VARCHAR"
>> primarykey="true"/>
>> <field-descriptor name="firstName" column="FIRSTNAME"
>> jdbc-type="VARCHAR" />
>> <field-descriptor name="lastName" column="LASTNAME"
>> jdbc-type="VARCHAR" />
>> <field-descriptor name="email" column="EMAIL" jdbc-type="VARCHAR" />
>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>> jdbc-type="VARCHAR" access="anonymous" primarykey="true"/>
>> <field-descriptor name="projectId" column="PROJECT_ID"
>> jdbc-type="VARCHAR" access="anonymous" />
>> </class-descriptor>
>>
>> The problem is, whenever OJB tries to retrieve a collection of
>> Primary Investigators, it never uses the correct value for the
>> composite primary key.
>> The debugging output looks like this:
>>
>> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG:
>> executeQuery : QueryByCriteria from class
>> org.jlab.mis.services.mics.client.generated.PrimaryInvestigator
>> where [projectId = 13179, fiscalYear = 13179]
>>
>> As you can see, it is using the exact same value for the projectId
>> and fiscalYear attributes.
>> What am I doing wrong?
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-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
Re: composite foreign key references
Posted by Bobby Lawrence <ro...@jlab.org>.
How exactly is that supposed to work?
I have a project and investigators. The project has id, lets say 13. I
want all the investigators for project #13.
I don't want to find the investigator w/ PK 13, so I can't use the PK
from the investigator object...I have to use a different field called
projectId (which is set access="anonymous" because I don't need to store
it in my object.)
Maybe I don't understand hat you mean...can you explain some more?
--Bobby
Jakob Braeuchi wrote:
> bobby,
>
> suppose your inverse-foreign key definitions in project point to the
> pk-fields of investigator,
> do you still have the problem that the same value is used for both
> attributes ? i do not ecpect so.
>
> ojb 1.0.x assumes that the inverse-foreign key points to the pk of the
> referenced class. a more flexible handling of inverse-fk will be
> available in 1.1
>
> jakob
>
> Bobby Lawrence schrieb:
>
>> OJB 1.0.1
>> --B
>>
>> Jakob Braeuchi wrote:
>>
>>> hi bobby,
>>>
>>> what version of ojb do you use ?
>>>
>>> jakob
>>>
>>> Bobby Lawrence schrieb:
>>>
>>>> Armin-
>>>> I can't use label as the key because to lookup a PI, I need the
>>>> projectId and not the label.
>>>> My problem is, OJB is using the value for projectId as the value
>>>> for BOTH foreign keys (projectId and fiscalYear). Its not using
>>>> the fiscalYear value in the query lookup.
>>>> --Bobby
>>>>
>>>> Armin Waibel wrote:
>>>>
>>>>> Bobby Lawrence wrote:
>>>>>
>>>>>> Armin - I have to.
>>>>>> I can a job table and a job class. The job table stores the
>>>>>> project label. That is why it is listed as a primary key.
>>>>>> I have to set it up this way.
>>>>>> Can you help me with my problem?
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> First, the PK declaration in OJB is independent from the PK
>>>>> declaration in your DB (but avoid constraint violations ;-)).
>>>>> OJB expects as FK fields in an object of a 1:n relation (PI) all
>>>>> PK fields of the main object (Project). In your case
>>>>> PI:
>>>>> fkFiscalYearId
>>>>> fkLabelId
>>>>>
>>>>> when Project has PK
>>>>> fiscalYear
>>>>> label
>>>>>
>>>>> <collection-descriptor name="primaryInvestigators"
>>>>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>>> auto-retrieve="false">
>>>>> <inverse-foreignkey field-ref="fkFiscalYearId" />
>>>>> <inverse-foreignkey field-ref="fkLabelId" />
>>>>> </collection-descriptor>
>>>>>
>>>>> HTH,
>>>>> Armin
>>>>>
>>>>>>
>>>>>>
>>>>>> Armin Waibel wrote:
>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> Project --1:n --> PrimaryInvestigator (PI)
>>>>>>> PK fields for Project:
>>>>>>> label
>>>>>>> fiscalYears
>>>>>>> why is 'id' not the PK in Project?
>>>>>>>
>>>>>>> In PI FK are:
>>>>>>> projectId
>>>>>>> fiscalYear
>>>>>>>
>>>>>>> projectId id not a PK in Project, you have to use a 'labelId'.
>>>>>>>
>>>>>>> As a side-note, be really careful when using anonymous fields
>>>>>>> for fields other than 1:1 FK.
>>>>>>>
>>>>>>> http://db.apache.org/ojb/docu/guides/advanced-technique.html#How+do+
>>>>>>>
>>>>>>>
>>>>>>> regards,
>>>>>>> Armin
>>>>>>>
>>>>>>>
>>>>>>> Bobby Lawrence wrote:
>>>>>>>
>>>>>>>> Using the PersistenceBroker API...
>>>>>>>> I have a class that has a 1:n relationship -> a project can
>>>>>>>> have many principal investigators.
>>>>>>>> The class descriptors look like this:
>>>>>>>>
>>>>>>>> <class-descriptor
>>>>>>>> class="org.jlab.mis.services.mics.client.generated.Project"
>>>>>>>> table="projects">
>>>>>>>> <field-descriptor name="id" column="PROJECT_ID"
>>>>>>>> jdbc-type="VARCHAR"/>
>>>>>>>> <field-descriptor name="label" column="REPO_CODE"
>>>>>>>> jdbc-type="VARCHAR" primarykey="true" access="anonymous" />
>>>>>>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>>>>>>> jdbc-type="VARCHAR" primarykey="true" />
>>>>>>>> <field-descriptor name="name" column="PROJECT_NAME"
>>>>>>>> jdbc-type="VARCHAR" />
>>>>>>>> <collection-descriptor name="primaryInvestigators"
>>>>>>>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>>>>>> auto-retrieve="false">
>>>>>>>> <inverse-foreignkey field-ref="projectId" />
>>>>>>>> <inverse-foreignkey field-ref="fiscalYear" />
>>>>>>>> </collection-descriptor>
>>>>>>>> </class-descriptor>
>>>>>>>>
>>>>>>>> <class-descriptor
>>>>>>>> class="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>>>>>> table="pis">
>>>>>>>> <field-descriptor name="id" column="PERSON_ID"
>>>>>>>> jdbc-type="VARCHAR" primarykey="true"/>
>>>>>>>> <field-descriptor name="firstName" column="FIRSTNAME"
>>>>>>>> jdbc-type="VARCHAR" />
>>>>>>>> <field-descriptor name="lastName" column="LASTNAME"
>>>>>>>> jdbc-type="VARCHAR" />
>>>>>>>> <field-descriptor name="email" column="EMAIL"
>>>>>>>> jdbc-type="VARCHAR" />
>>>>>>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>>>>>>> jdbc-type="VARCHAR" access="anonymous" primarykey="true"/>
>>>>>>>> <field-descriptor name="projectId" column="PROJECT_ID"
>>>>>>>> jdbc-type="VARCHAR" access="anonymous" />
>>>>>>>> </class-descriptor>
>>>>>>>>
>>>>>>>> The problem is, whenever OJB tries to retrieve a collection of
>>>>>>>> Primary Investigators, it never uses the correct value for the
>>>>>>>> composite primary key.
>>>>>>>> The debugging output looks like this:
>>>>>>>>
>>>>>>>> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG:
>>>>>>>> executeQuery : QueryByCriteria from class
>>>>>>>> org.jlab.mis.services.mics.client.generated.PrimaryInvestigator
>>>>>>>> where [projectId = 13179, fiscalYear = 13179]
>>>>>>>>
>>>>>>>> As you can see, it is using the exact same value for the
>>>>>>>> projectId and fiscalYear attributes.
>>>>>>>> What am I doing wrong?
>>>>>>>>
>>>>>>>
>>>>>>> ---------------------------------------------------------------------
>>>>>>>
>>>>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>>>>> For additional commands, e-mail: ojb-user-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
>>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-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
>
--
----------------------------
Bobby Lawrence
MIS Application Developer
Jefferson Lab (www.jlab.org)
Email: robertl@jlab.org
Office: (757) 269-5818
Pager: (757) 584-5818
----------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: composite foreign key references
Posted by Jakob Braeuchi <jb...@gmx.ch>.
bobby,
suppose your inverse-foreign key definitions in project point to the
pk-fields of investigator,
do you still have the problem that the same value is used for both
attributes ? i do not ecpect so.
ojb 1.0.x assumes that the inverse-foreign key points to the pk of the
referenced class. a more flexible handling of inverse-fk will be
available in 1.1
jakob
Bobby Lawrence schrieb:
> OJB 1.0.1
> --B
>
> Jakob Braeuchi wrote:
>
>> hi bobby,
>>
>> what version of ojb do you use ?
>>
>> jakob
>>
>> Bobby Lawrence schrieb:
>>
>>> Armin-
>>> I can't use label as the key because to lookup a PI, I need the
>>> projectId and not the label.
>>> My problem is, OJB is using the value for projectId as the value for
>>> BOTH foreign keys (projectId and fiscalYear). Its not using the
>>> fiscalYear value in the query lookup.
>>> --Bobby
>>>
>>> Armin Waibel wrote:
>>>
>>>> Bobby Lawrence wrote:
>>>>
>>>>> Armin - I have to.
>>>>> I can a job table and a job class. The job table stores the
>>>>> project label. That is why it is listed as a primary key.
>>>>> I have to set it up this way.
>>>>> Can you help me with my problem?
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> First, the PK declaration in OJB is independent from the PK
>>>> declaration in your DB (but avoid constraint violations ;-)).
>>>> OJB expects as FK fields in an object of a 1:n relation (PI) all PK
>>>> fields of the main object (Project). In your case
>>>> PI:
>>>> fkFiscalYearId
>>>> fkLabelId
>>>>
>>>> when Project has PK
>>>> fiscalYear
>>>> label
>>>>
>>>> <collection-descriptor name="primaryInvestigators"
>>>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>> auto-retrieve="false">
>>>> <inverse-foreignkey field-ref="fkFiscalYearId" />
>>>> <inverse-foreignkey field-ref="fkLabelId" />
>>>> </collection-descriptor>
>>>>
>>>> HTH,
>>>> Armin
>>>>
>>>>>
>>>>>
>>>>> Armin Waibel wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> Project --1:n --> PrimaryInvestigator (PI)
>>>>>> PK fields for Project:
>>>>>> label
>>>>>> fiscalYears
>>>>>> why is 'id' not the PK in Project?
>>>>>>
>>>>>> In PI FK are:
>>>>>> projectId
>>>>>> fiscalYear
>>>>>>
>>>>>> projectId id not a PK in Project, you have to use a 'labelId'.
>>>>>>
>>>>>> As a side-note, be really careful when using anonymous fields for
>>>>>> fields other than 1:1 FK.
>>>>>>
>>>>>> http://db.apache.org/ojb/docu/guides/advanced-technique.html#How+do+
>>>>>>
>>>>>> regards,
>>>>>> Armin
>>>>>>
>>>>>>
>>>>>> Bobby Lawrence wrote:
>>>>>>
>>>>>>> Using the PersistenceBroker API...
>>>>>>> I have a class that has a 1:n relationship -> a project can
>>>>>>> have many principal investigators.
>>>>>>> The class descriptors look like this:
>>>>>>>
>>>>>>> <class-descriptor
>>>>>>> class="org.jlab.mis.services.mics.client.generated.Project"
>>>>>>> table="projects">
>>>>>>> <field-descriptor name="id" column="PROJECT_ID"
>>>>>>> jdbc-type="VARCHAR"/>
>>>>>>> <field-descriptor name="label" column="REPO_CODE"
>>>>>>> jdbc-type="VARCHAR" primarykey="true" access="anonymous" />
>>>>>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>>>>>> jdbc-type="VARCHAR" primarykey="true" />
>>>>>>> <field-descriptor name="name" column="PROJECT_NAME"
>>>>>>> jdbc-type="VARCHAR" />
>>>>>>> <collection-descriptor name="primaryInvestigators"
>>>>>>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>>>>> auto-retrieve="false">
>>>>>>> <inverse-foreignkey field-ref="projectId" />
>>>>>>> <inverse-foreignkey field-ref="fiscalYear" />
>>>>>>> </collection-descriptor>
>>>>>>> </class-descriptor>
>>>>>>>
>>>>>>> <class-descriptor
>>>>>>> class="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>>>>> table="pis">
>>>>>>> <field-descriptor name="id" column="PERSON_ID"
>>>>>>> jdbc-type="VARCHAR" primarykey="true"/>
>>>>>>> <field-descriptor name="firstName" column="FIRSTNAME"
>>>>>>> jdbc-type="VARCHAR" />
>>>>>>> <field-descriptor name="lastName" column="LASTNAME"
>>>>>>> jdbc-type="VARCHAR" />
>>>>>>> <field-descriptor name="email" column="EMAIL"
>>>>>>> jdbc-type="VARCHAR" />
>>>>>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>>>>>> jdbc-type="VARCHAR" access="anonymous" primarykey="true"/>
>>>>>>> <field-descriptor name="projectId" column="PROJECT_ID"
>>>>>>> jdbc-type="VARCHAR" access="anonymous" />
>>>>>>> </class-descriptor>
>>>>>>>
>>>>>>> The problem is, whenever OJB tries to retrieve a collection of
>>>>>>> Primary Investigators, it never uses the correct value for the
>>>>>>> composite primary key.
>>>>>>> The debugging output looks like this:
>>>>>>>
>>>>>>> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG:
>>>>>>> executeQuery : QueryByCriteria from class
>>>>>>> org.jlab.mis.services.mics.client.generated.PrimaryInvestigator
>>>>>>> where [projectId = 13179, fiscalYear = 13179]
>>>>>>>
>>>>>>> As you can see, it is using the exact same value for the
>>>>>>> projectId and fiscalYear attributes.
>>>>>>> What am I doing wrong?
>>>>>>>
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>>>>
>>>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>>>> For additional commands, e-mail: ojb-user-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
>>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-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
Re: composite foreign key references
Posted by Bobby Lawrence <ro...@jlab.org>.
OJB 1.0.1
--B
Jakob Braeuchi wrote:
> hi bobby,
>
> what version of ojb do you use ?
>
> jakob
>
> Bobby Lawrence schrieb:
>
>> Armin-
>> I can't use label as the key because to lookup a PI, I need the
>> projectId and not the label.
>> My problem is, OJB is using the value for projectId as the value for
>> BOTH foreign keys (projectId and fiscalYear). Its not using the
>> fiscalYear value in the query lookup.
>> --Bobby
>>
>> Armin Waibel wrote:
>>
>>> Bobby Lawrence wrote:
>>>
>>>> Armin - I have to.
>>>> I can a job table and a job class. The job table stores the
>>>> project label. That is why it is listed as a primary key.
>>>> I have to set it up this way.
>>>> Can you help me with my problem?
>>>
>>>
>>>
>>>
>>> First, the PK declaration in OJB is independent from the PK
>>> declaration in your DB (but avoid constraint violations ;-)).
>>> OJB expects as FK fields in an object of a 1:n relation (PI) all PK
>>> fields of the main object (Project). In your case
>>> PI:
>>> fkFiscalYearId
>>> fkLabelId
>>>
>>> when Project has PK
>>> fiscalYear
>>> label
>>>
>>> <collection-descriptor name="primaryInvestigators"
>>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>> auto-retrieve="false">
>>> <inverse-foreignkey field-ref="fkFiscalYearId" />
>>> <inverse-foreignkey field-ref="fkLabelId" />
>>> </collection-descriptor>
>>>
>>> HTH,
>>> Armin
>>>
>>>>
>>>>
>>>> Armin Waibel wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> Project --1:n --> PrimaryInvestigator (PI)
>>>>> PK fields for Project:
>>>>> label
>>>>> fiscalYears
>>>>> why is 'id' not the PK in Project?
>>>>>
>>>>> In PI FK are:
>>>>> projectId
>>>>> fiscalYear
>>>>>
>>>>> projectId id not a PK in Project, you have to use a 'labelId'.
>>>>>
>>>>> As a side-note, be really careful when using anonymous fields for
>>>>> fields other than 1:1 FK.
>>>>>
>>>>> http://db.apache.org/ojb/docu/guides/advanced-technique.html#How+do+
>>>>>
>>>>> regards,
>>>>> Armin
>>>>>
>>>>>
>>>>> Bobby Lawrence wrote:
>>>>>
>>>>>> Using the PersistenceBroker API...
>>>>>> I have a class that has a 1:n relationship -> a project can have
>>>>>> many principal investigators.
>>>>>> The class descriptors look like this:
>>>>>>
>>>>>> <class-descriptor
>>>>>> class="org.jlab.mis.services.mics.client.generated.Project"
>>>>>> table="projects">
>>>>>> <field-descriptor name="id" column="PROJECT_ID"
>>>>>> jdbc-type="VARCHAR"/>
>>>>>> <field-descriptor name="label" column="REPO_CODE"
>>>>>> jdbc-type="VARCHAR" primarykey="true" access="anonymous" />
>>>>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>>>>> jdbc-type="VARCHAR" primarykey="true" />
>>>>>> <field-descriptor name="name" column="PROJECT_NAME"
>>>>>> jdbc-type="VARCHAR" />
>>>>>> <collection-descriptor name="primaryInvestigators"
>>>>>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>>>> auto-retrieve="false">
>>>>>> <inverse-foreignkey field-ref="projectId" />
>>>>>> <inverse-foreignkey field-ref="fiscalYear" />
>>>>>> </collection-descriptor>
>>>>>> </class-descriptor>
>>>>>>
>>>>>> <class-descriptor
>>>>>> class="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>>>> table="pis">
>>>>>> <field-descriptor name="id" column="PERSON_ID"
>>>>>> jdbc-type="VARCHAR" primarykey="true"/>
>>>>>> <field-descriptor name="firstName" column="FIRSTNAME"
>>>>>> jdbc-type="VARCHAR" />
>>>>>> <field-descriptor name="lastName" column="LASTNAME"
>>>>>> jdbc-type="VARCHAR" />
>>>>>> <field-descriptor name="email" column="EMAIL"
>>>>>> jdbc-type="VARCHAR" />
>>>>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>>>>> jdbc-type="VARCHAR" access="anonymous" primarykey="true"/>
>>>>>> <field-descriptor name="projectId" column="PROJECT_ID"
>>>>>> jdbc-type="VARCHAR" access="anonymous" />
>>>>>> </class-descriptor>
>>>>>>
>>>>>> The problem is, whenever OJB tries to retrieve a collection of
>>>>>> Primary Investigators, it never uses the correct value for the
>>>>>> composite primary key.
>>>>>> The debugging output looks like this:
>>>>>>
>>>>>> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG:
>>>>>> executeQuery : QueryByCriteria from class
>>>>>> org.jlab.mis.services.mics.client.generated.PrimaryInvestigator
>>>>>> where [projectId = 13179, fiscalYear = 13179]
>>>>>>
>>>>>> As you can see, it is using the exact same value for the
>>>>>> projectId and fiscalYear attributes.
>>>>>> What am I doing wrong?
>>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>>> For additional commands, e-mail: ojb-user-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
>>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
--
----------------------------
Bobby Lawrence
MIS Application Developer
Jefferson Lab (www.jlab.org)
Email: robertl@jlab.org
Office: (757) 269-5818
Pager: (757) 584-5818
----------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: composite foreign key references
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi bobby,
what version of ojb do you use ?
jakob
Bobby Lawrence schrieb:
> Armin-
> I can't use label as the key because to lookup a PI, I need the
> projectId and not the label.
> My problem is, OJB is using the value for projectId as the value for
> BOTH foreign keys (projectId and fiscalYear). Its not using the
> fiscalYear value in the query lookup.
> --Bobby
>
> Armin Waibel wrote:
>
>> Bobby Lawrence wrote:
>>
>>> Armin - I have to.
>>> I can a job table and a job class. The job table stores the project
>>> label. That is why it is listed as a primary key.
>>> I have to set it up this way.
>>> Can you help me with my problem?
>>
>>
>>
>> First, the PK declaration in OJB is independent from the PK
>> declaration in your DB (but avoid constraint violations ;-)).
>> OJB expects as FK fields in an object of a 1:n relation (PI) all PK
>> fields of the main object (Project). In your case
>> PI:
>> fkFiscalYearId
>> fkLabelId
>>
>> when Project has PK
>> fiscalYear
>> label
>>
>> <collection-descriptor name="primaryInvestigators"
>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>> auto-retrieve="false">
>> <inverse-foreignkey field-ref="fkFiscalYearId" />
>> <inverse-foreignkey field-ref="fkLabelId" />
>> </collection-descriptor>
>>
>> HTH,
>> Armin
>>
>>>
>>>
>>> Armin Waibel wrote:
>>>
>>>> Hi,
>>>>
>>>> Project --1:n --> PrimaryInvestigator (PI)
>>>> PK fields for Project:
>>>> label
>>>> fiscalYears
>>>> why is 'id' not the PK in Project?
>>>>
>>>> In PI FK are:
>>>> projectId
>>>> fiscalYear
>>>>
>>>> projectId id not a PK in Project, you have to use a 'labelId'.
>>>>
>>>> As a side-note, be really careful when using anonymous fields for
>>>> fields other than 1:1 FK.
>>>>
>>>> http://db.apache.org/ojb/docu/guides/advanced-technique.html#How+do+
>>>>
>>>> regards,
>>>> Armin
>>>>
>>>>
>>>> Bobby Lawrence wrote:
>>>>
>>>>> Using the PersistenceBroker API...
>>>>> I have a class that has a 1:n relationship -> a project can have
>>>>> many principal investigators.
>>>>> The class descriptors look like this:
>>>>>
>>>>> <class-descriptor
>>>>> class="org.jlab.mis.services.mics.client.generated.Project"
>>>>> table="projects">
>>>>> <field-descriptor name="id" column="PROJECT_ID"
>>>>> jdbc-type="VARCHAR"/>
>>>>> <field-descriptor name="label" column="REPO_CODE"
>>>>> jdbc-type="VARCHAR" primarykey="true" access="anonymous" />
>>>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>>>> jdbc-type="VARCHAR" primarykey="true" />
>>>>> <field-descriptor name="name" column="PROJECT_NAME"
>>>>> jdbc-type="VARCHAR" />
>>>>> <collection-descriptor name="primaryInvestigators"
>>>>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>>> auto-retrieve="false">
>>>>> <inverse-foreignkey field-ref="projectId" />
>>>>> <inverse-foreignkey field-ref="fiscalYear" />
>>>>> </collection-descriptor>
>>>>> </class-descriptor>
>>>>>
>>>>> <class-descriptor
>>>>> class="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>>> table="pis">
>>>>> <field-descriptor name="id" column="PERSON_ID"
>>>>> jdbc-type="VARCHAR" primarykey="true"/>
>>>>> <field-descriptor name="firstName" column="FIRSTNAME"
>>>>> jdbc-type="VARCHAR" />
>>>>> <field-descriptor name="lastName" column="LASTNAME"
>>>>> jdbc-type="VARCHAR" />
>>>>> <field-descriptor name="email" column="EMAIL"
>>>>> jdbc-type="VARCHAR" />
>>>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>>>> jdbc-type="VARCHAR" access="anonymous" primarykey="true"/>
>>>>> <field-descriptor name="projectId" column="PROJECT_ID"
>>>>> jdbc-type="VARCHAR" access="anonymous" />
>>>>> </class-descriptor>
>>>>>
>>>>> The problem is, whenever OJB tries to retrieve a collection of
>>>>> Primary Investigators, it never uses the correct value for the
>>>>> composite primary key.
>>>>> The debugging output looks like this:
>>>>>
>>>>> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG:
>>>>> executeQuery : QueryByCriteria from class
>>>>> org.jlab.mis.services.mics.client.generated.PrimaryInvestigator
>>>>> where [projectId = 13179, fiscalYear = 13179]
>>>>>
>>>>> As you can see, it is using the exact same value for the projectId
>>>>> and fiscalYear attributes.
>>>>> What am I doing wrong?
>>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-user-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
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: composite foreign key references
Posted by Bobby Lawrence <ro...@jlab.org>.
Armin-
I can't use label as the key because to lookup a PI, I need the
projectId and not the label.
My problem is, OJB is using the value for projectId as the value for
BOTH foreign keys (projectId and fiscalYear). Its not using the
fiscalYear value in the query lookup.
--Bobby
Armin Waibel wrote:
> Bobby Lawrence wrote:
>
>> Armin - I have to.
>> I can a job table and a job class. The job table stores the project
>> label. That is why it is listed as a primary key.
>> I have to set it up this way.
>> Can you help me with my problem?
>
>
> First, the PK declaration in OJB is independent from the PK
> declaration in your DB (but avoid constraint violations ;-)).
> OJB expects as FK fields in an object of a 1:n relation (PI) all PK
> fields of the main object (Project). In your case
> PI:
> fkFiscalYearId
> fkLabelId
>
> when Project has PK
> fiscalYear
> label
>
> <collection-descriptor name="primaryInvestigators"
> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
> auto-retrieve="false">
> <inverse-foreignkey field-ref="fkFiscalYearId" />
> <inverse-foreignkey field-ref="fkLabelId" />
> </collection-descriptor>
>
> HTH,
> Armin
>
>>
>>
>> Armin Waibel wrote:
>>
>>> Hi,
>>>
>>> Project --1:n --> PrimaryInvestigator (PI)
>>> PK fields for Project:
>>> label
>>> fiscalYears
>>> why is 'id' not the PK in Project?
>>>
>>> In PI FK are:
>>> projectId
>>> fiscalYear
>>>
>>> projectId id not a PK in Project, you have to use a 'labelId'.
>>>
>>> As a side-note, be really careful when using anonymous fields for
>>> fields other than 1:1 FK.
>>>
>>> http://db.apache.org/ojb/docu/guides/advanced-technique.html#How+do+
>>>
>>> regards,
>>> Armin
>>>
>>>
>>> Bobby Lawrence wrote:
>>>
>>>> Using the PersistenceBroker API...
>>>> I have a class that has a 1:n relationship -> a project can have
>>>> many principal investigators.
>>>> The class descriptors look like this:
>>>>
>>>> <class-descriptor
>>>> class="org.jlab.mis.services.mics.client.generated.Project"
>>>> table="projects">
>>>> <field-descriptor name="id" column="PROJECT_ID"
>>>> jdbc-type="VARCHAR"/>
>>>> <field-descriptor name="label" column="REPO_CODE"
>>>> jdbc-type="VARCHAR" primarykey="true" access="anonymous" />
>>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>>> jdbc-type="VARCHAR" primarykey="true" />
>>>> <field-descriptor name="name" column="PROJECT_NAME"
>>>> jdbc-type="VARCHAR" />
>>>> <collection-descriptor name="primaryInvestigators"
>>>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>> auto-retrieve="false">
>>>> <inverse-foreignkey field-ref="projectId" />
>>>> <inverse-foreignkey field-ref="fiscalYear" />
>>>> </collection-descriptor>
>>>> </class-descriptor>
>>>>
>>>> <class-descriptor
>>>> class="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>>> table="pis">
>>>> <field-descriptor name="id" column="PERSON_ID"
>>>> jdbc-type="VARCHAR" primarykey="true"/>
>>>> <field-descriptor name="firstName" column="FIRSTNAME"
>>>> jdbc-type="VARCHAR" />
>>>> <field-descriptor name="lastName" column="LASTNAME"
>>>> jdbc-type="VARCHAR" />
>>>> <field-descriptor name="email" column="EMAIL"
>>>> jdbc-type="VARCHAR" />
>>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>>> jdbc-type="VARCHAR" access="anonymous" primarykey="true"/>
>>>> <field-descriptor name="projectId" column="PROJECT_ID"
>>>> jdbc-type="VARCHAR" access="anonymous" />
>>>> </class-descriptor>
>>>>
>>>> The problem is, whenever OJB tries to retrieve a collection of
>>>> Primary Investigators, it never uses the correct value for the
>>>> composite primary key.
>>>> The debugging output looks like this:
>>>>
>>>> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG:
>>>> executeQuery : QueryByCriteria from class
>>>> org.jlab.mis.services.mics.client.generated.PrimaryInvestigator
>>>> where [projectId = 13179, fiscalYear = 13179]
>>>>
>>>> As you can see, it is using the exact same value for the projectId
>>>> and fiscalYear attributes.
>>>> What am I doing wrong?
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-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
>
--
----------------------------
Bobby Lawrence
MIS Application Developer
Jefferson Lab (www.jlab.org)
Email: robertl@jlab.org
Office: (757) 269-5818
Pager: (757) 584-5818
----------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: composite foreign key references
Posted by Armin Waibel <ar...@apache.org>.
Bobby Lawrence wrote:
> Armin - I have to.
> I can a job table and a job class. The job table stores the project
> label. That is why it is listed as a primary key.
> I have to set it up this way.
> Can you help me with my problem?
First, the PK declaration in OJB is independent from the PK declaration
in your DB (but avoid constraint violations ;-)).
OJB expects as FK fields in an object of a 1:n relation (PI) all PK
fields of the main object (Project). In your case
PI:
fkFiscalYearId
fkLabelId
when Project has PK
fiscalYear
label
<collection-descriptor name="primaryInvestigators"
element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
auto-retrieve="false">
<inverse-foreignkey field-ref="fkFiscalYearId" />
<inverse-foreignkey field-ref="fkLabelId" />
</collection-descriptor>
HTH,
Armin
>
>
> Armin Waibel wrote:
>
>> Hi,
>>
>> Project --1:n --> PrimaryInvestigator (PI)
>> PK fields for Project:
>> label
>> fiscalYears
>> why is 'id' not the PK in Project?
>>
>> In PI FK are:
>> projectId
>> fiscalYear
>>
>> projectId id not a PK in Project, you have to use a 'labelId'.
>>
>> As a side-note, be really careful when using anonymous fields for
>> fields other than 1:1 FK.
>>
>> http://db.apache.org/ojb/docu/guides/advanced-technique.html#How+do+
>>
>> regards,
>> Armin
>>
>>
>> Bobby Lawrence wrote:
>>
>>> Using the PersistenceBroker API...
>>> I have a class that has a 1:n relationship -> a project can have
>>> many principal investigators.
>>> The class descriptors look like this:
>>>
>>> <class-descriptor
>>> class="org.jlab.mis.services.mics.client.generated.Project"
>>> table="projects">
>>> <field-descriptor name="id" column="PROJECT_ID" jdbc-type="VARCHAR"/>
>>> <field-descriptor name="label" column="REPO_CODE"
>>> jdbc-type="VARCHAR" primarykey="true" access="anonymous" />
>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>> jdbc-type="VARCHAR" primarykey="true" />
>>> <field-descriptor name="name" column="PROJECT_NAME"
>>> jdbc-type="VARCHAR" />
>>> <collection-descriptor name="primaryInvestigators"
>>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>> auto-retrieve="false">
>>> <inverse-foreignkey field-ref="projectId" />
>>> <inverse-foreignkey field-ref="fiscalYear" />
>>> </collection-descriptor>
>>> </class-descriptor>
>>>
>>> <class-descriptor
>>> class="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>>> table="pis">
>>> <field-descriptor name="id" column="PERSON_ID" jdbc-type="VARCHAR"
>>> primarykey="true"/>
>>> <field-descriptor name="firstName" column="FIRSTNAME"
>>> jdbc-type="VARCHAR" />
>>> <field-descriptor name="lastName" column="LASTNAME"
>>> jdbc-type="VARCHAR" />
>>> <field-descriptor name="email" column="EMAIL" jdbc-type="VARCHAR" />
>>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>>> jdbc-type="VARCHAR" access="anonymous" primarykey="true"/>
>>> <field-descriptor name="projectId" column="PROJECT_ID"
>>> jdbc-type="VARCHAR" access="anonymous" />
>>> </class-descriptor>
>>>
>>> The problem is, whenever OJB tries to retrieve a collection of
>>> Primary Investigators, it never uses the correct value for the
>>> composite primary key.
>>> The debugging output looks like this:
>>>
>>> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG:
>>> executeQuery : QueryByCriteria from class
>>> org.jlab.mis.services.mics.client.generated.PrimaryInvestigator
>>> where [projectId = 13179, fiscalYear = 13179]
>>>
>>> As you can see, it is using the exact same value for the projectId
>>> and fiscalYear attributes.
>>> What am I doing wrong?
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-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
Re: composite foreign key references
Posted by Bobby Lawrence <ro...@jlab.org>.
Armin - I have to.
I can a job table and a job class. The job table stores the project
label. That is why it is listed as a primary key.
I have to set it up this way.
Can you help me with my problem?
Armin Waibel wrote:
> Hi,
>
> Project --1:n --> PrimaryInvestigator (PI)
> PK fields for Project:
> label
> fiscalYears
> why is 'id' not the PK in Project?
>
> In PI FK are:
> projectId
> fiscalYear
>
> projectId id not a PK in Project, you have to use a 'labelId'.
>
> As a side-note, be really careful when using anonymous fields for
> fields other than 1:1 FK.
>
> http://db.apache.org/ojb/docu/guides/advanced-technique.html#How+do+
>
> regards,
> Armin
>
>
> Bobby Lawrence wrote:
>
>> Using the PersistenceBroker API...
>> I have a class that has a 1:n relationship -> a project can have
>> many principal investigators.
>> The class descriptors look like this:
>>
>> <class-descriptor
>> class="org.jlab.mis.services.mics.client.generated.Project"
>> table="projects">
>> <field-descriptor name="id" column="PROJECT_ID" jdbc-type="VARCHAR"/>
>> <field-descriptor name="label" column="REPO_CODE"
>> jdbc-type="VARCHAR" primarykey="true" access="anonymous" />
>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>> jdbc-type="VARCHAR" primarykey="true" />
>> <field-descriptor name="name" column="PROJECT_NAME"
>> jdbc-type="VARCHAR" />
>> <collection-descriptor name="primaryInvestigators"
>> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>> auto-retrieve="false">
>> <inverse-foreignkey field-ref="projectId" />
>> <inverse-foreignkey field-ref="fiscalYear" />
>> </collection-descriptor>
>> </class-descriptor>
>>
>> <class-descriptor
>> class="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
>> table="pis">
>> <field-descriptor name="id" column="PERSON_ID" jdbc-type="VARCHAR"
>> primarykey="true"/>
>> <field-descriptor name="firstName" column="FIRSTNAME"
>> jdbc-type="VARCHAR" />
>> <field-descriptor name="lastName" column="LASTNAME"
>> jdbc-type="VARCHAR" />
>> <field-descriptor name="email" column="EMAIL" jdbc-type="VARCHAR" />
>> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
>> jdbc-type="VARCHAR" access="anonymous" primarykey="true"/>
>> <field-descriptor name="projectId" column="PROJECT_ID"
>> jdbc-type="VARCHAR" access="anonymous" />
>> </class-descriptor>
>>
>> The problem is, whenever OJB tries to retrieve a collection of
>> Primary Investigators, it never uses the correct value for the
>> composite primary key.
>> The debugging output looks like this:
>>
>> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG:
>> executeQuery : QueryByCriteria from class
>> org.jlab.mis.services.mics.client.generated.PrimaryInvestigator
>> where [projectId = 13179, fiscalYear = 13179]
>>
>> As you can see, it is using the exact same value for the projectId
>> and fiscalYear attributes.
>> What am I doing wrong?
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
--
----------------------------
Bobby Lawrence
MIS Application Developer
Jefferson Lab (www.jlab.org)
Email: robertl@jlab.org
Office: (757) 269-5818
Pager: (757) 584-5818
----------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: composite foreign key references
Posted by Armin Waibel <ar...@apache.org>.
Hi,
Project --1:n --> PrimaryInvestigator (PI)
PK fields for Project:
label
fiscalYears
why is 'id' not the PK in Project?
In PI FK are:
projectId
fiscalYear
projectId id not a PK in Project, you have to use a 'labelId'.
As a side-note, be really careful when using anonymous fields for fields
other than 1:1 FK.
http://db.apache.org/ojb/docu/guides/advanced-technique.html#How+do+
regards,
Armin
Bobby Lawrence wrote:
> Using the PersistenceBroker API...
> I have a class that has a 1:n relationship -> a project can have many
> principal investigators.
> The class descriptors look like this:
>
> <class-descriptor
> class="org.jlab.mis.services.mics.client.generated.Project"
> table="projects">
> <field-descriptor name="id" column="PROJECT_ID" jdbc-type="VARCHAR"/>
> <field-descriptor name="label" column="REPO_CODE" jdbc-type="VARCHAR"
> primarykey="true" access="anonymous" />
> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
> jdbc-type="VARCHAR" primarykey="true" />
> <field-descriptor name="name" column="PROJECT_NAME"
> jdbc-type="VARCHAR" />
> <collection-descriptor name="primaryInvestigators"
> element-class-ref="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
> auto-retrieve="false">
> <inverse-foreignkey field-ref="projectId" />
> <inverse-foreignkey field-ref="fiscalYear" />
> </collection-descriptor>
> </class-descriptor>
>
> <class-descriptor
> class="org.jlab.mis.services.mics.client.generated.PrimaryInvestigator"
> table="pis">
> <field-descriptor name="id" column="PERSON_ID" jdbc-type="VARCHAR"
> primarykey="true"/>
> <field-descriptor name="firstName" column="FIRSTNAME"
> jdbc-type="VARCHAR" />
> <field-descriptor name="lastName" column="LASTNAME"
> jdbc-type="VARCHAR" />
> <field-descriptor name="email" column="EMAIL" jdbc-type="VARCHAR" />
> <field-descriptor name="fiscalYear" column="FISCAL_YEAR"
> jdbc-type="VARCHAR" access="anonymous" primarykey="true"/>
> <field-descriptor name="projectId" column="PROJECT_ID"
> jdbc-type="VARCHAR" access="anonymous" />
> </class-descriptor>
>
> The problem is, whenever OJB tries to retrieve a collection of Primary
> Investigators, it never uses the correct value for the composite primary
> key.
> The debugging output looks like this:
>
> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] DEBUG: executeQuery :
> QueryByCriteria from class
> org.jlab.mis.services.mics.client.generated.PrimaryInvestigator where
> [projectId = 13179, fiscalYear = 13179]
>
> As you can see, it is using the exact same value for the projectId and
> fiscalYear attributes.
> What am I doing wrong?
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org