OK I think I follow this, however in my situation I have what might be a many
to many relationship without a go between table.
A REPORT table: reportId, desc, blah blah
A TREENODEREPORT table: reportId, treeId, nodeId (all three are the keys here)
I am given a treeId, NodeId and want to find all reports available. In the
case shown above I can have more then one row in the TREENODEREPORT table for
a given treeId and nodeId so the data might look like the following:
REPORT(1, "report1")
REPORT(2, "report2")
REPORT(3, "report3")
REPORT(4, "report4")
REPORT(5, "report5")
REPORT(6, "report6")
TREENODEREPORT(1, 1, 1)
TREENODEREPORT(2, 1, 1)
TREENODEREPORT(3, 1, 1)
TREENODEREPORT(4, 2, 1)
TREENODEREPORT(5, 2, 1)
TREENODEREPORT(6, 2, 1)
and I am provided the treeId, and nodeId of 1 and 1, I want to return a
collection of REPORT objects with reportId's 1,2 and 3.
The REPORT class has member fields of reportId, desc, blah blah
The TREENODEREPORT class has member fields of reportId, treeId, nodeId
Looking at the example you show I will need to add a Collection object to at
least the TREENODEREPORT class?? to write a query to get the object
collection I am interested in. Would I then add the collection descriptor
section to the TREENODEREPORT table as follows?
<class-descriptor
class="com.inventa.schema.report.impl.ReportImpl"
table="REPORT">
<field-descriptor id="1"
name="reportId"
column="REPORT_ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
nullable="false"
/>
<field-descriptor id="2"
name="name"
column="REPORT_NAME"
jdbc-type="vARCHAR"
nullable="false"
/>
<field-descriptor id="3"
name="reportURL"
column="REPORT_URL"
jdbc-type="vARCHAR"
conversion="com.inventa.schema.report.impl.ReportURLConverter"
nullable="false"
/>
<field-descriptor id="4"
name="desc"
column="REPORT_DESCRIPTION"
jdbc-type="vARCHAR"
nullable="true"
/>
</class-descriptor>
<class-descriptor
class="com.inventa.schema.report.impl.TreeNodeReportImpl"
table="TREE_NODE_REPORT">
<field-descriptor id="1"
name="reportId"
column="REPORT_ID"
jdbc-type="INTEGER"
primarykey="true"
nullable="false"
/>
<field-descriptor id="2"
name="treeId"
column="TREE_ID"
jdbc-type="INTEGER"
primarykey="true"
nullable="false"
/>
<field-descriptor id="3"
name="nodeId"
column="NODE_ID"
jdbc-type="INTEGER"
primarykey="true"
nullable="false"
/>
<collection-descriptor
name="treeNodeReports"
element-class-ref="com.inventa.schema.report.impl.ReportImpl"
auto-retrieve="false"
auto-update="false"
auto-delete="true">
<inverse-foreignkey field-id-ref="1"/>
</collection-descriptor>
</class-descriptor>
I am starting to feel like a dolt being so new to this... at the point I
really just want to write a piece of SQL to get the data I want... arrrghh.
Thanks for the help.
Joe
On Wednesday 18 September 2002 11:43 am, Charles Anthony wrote:
> Hi,
>
> OK; Joseph sent the examples direct to me - I'll reply to the list, so that
> others if they have the same questions can see my answers.
>
> Your repository.xml file doesn't contain any relationships (well, it
> defines one). Query by criteria needs the relationships defined to do the
> joins. Your object model,repository and database schema are a little
> confusing to me (as I don't know the business problem you're trying to
> solve), so let's discuss a simple example.
>
> Person<*---1>Address<*----1>Country
>
> i.e. a Person can have one Address, an Address can belong to many people.
> An Address can have one Country, and a Country can have many addresses.
> Here are three Tables : name, followed by columns.
> Person : personID, addressId, blah
> Address : blah, blah, countryId
> CountryId : countryId, countryName.
>
> Let us have three classes Person, Address and Country.
> Let Person have an address attribute of type "Address".
> Let Address have a people attribute of type "java.util.Collection"
> Let Address have a country attribute of type Country.
> Let Country have an addresses attribute of type "java.util.Collection"
> Let Country have a countryName attribute of type String.
>
> Let all the classes have attributes representing the appropriate "id" keys,
> and other attributes to represent other data.
>
> Hopefully, so far so clear. Let us have an appropriate repository.xml -
> with the appropriate collection-descriptor and reference-descriptor entries
> (see tutorial 3 for more details).
>
> Given all of the above, we can do a query to find all Persons that have an
> address in the countryName UK with the following query...
>
> Criteria crit = new Criteria();
>
> crit.addEqualTo("address.country.countryName", "UK");
> Query q = QueryFactory.newQuery(Person.class, crit);
> Collection results = broker.getCollectionByQuery(q);
>
> Essentionally, that query would be join between three tables, using the
> appropriate keys.
>
> Note that the fact that it is a join is hidden away from you; you are just
> searching on attribute names.
>
> So, in other words, you'll have to place more information in your
> repository.xml to get this sort of query to work, but once it's there, it's
> dead easy to use.
>
> HTH a little
>
> Cheers,
>
> Charles.
>
> >-----Original Message-----
>
> From: Charles Anthony [mailto:[EMAIL PROTECTED]]
>
> >Sent: 18 September 2002 15:55
> >To: 'OJB Users List'
> >Cc: '[EMAIL PROTECTED]'
> >Subject: RE: need a good join example... PLEASE HELP.
> >
> >
> >Hi;
> >
> >FYI The proxy in the documentation isn't at all relevant.
> >
> >Oh, and you haven't attached the files !
> >
> >Cheers,
> >
> >Charles.
> >
> >>-----Original Message-----
> >>From: Joseph Campbell [mailto:[EMAIL PROTECTED]]
> >>Sent: 18 September 2002 15:59
> >>To: OJB Users List
> >>Subject: Re: need a good join example... PLEASE HELP.
> >>
> >>
> >>Charles,
> >> It helps a little, but I note the use of proxy in the
> >>example you give. I am
> >>enclosing the xml descriptor for my tables, and the two java
> >>classes in
> >>question. In the hopes these will help.
> >>
> >>What I am looking for is the Collection of Base Class Report
> >>Objects for the
> >>Given treeId and nodeId as listed by query in one or more
> >>TreeNodeReport
> >>Objects.
> >>
> >>Thanks in advance,
> >> Joe
> >>
> >>PS: Sorry for being a newbie on this one...
> >>
> >>On Wednesday 18 September 2002 10:26 am, Charles Anthony wrote:
> >>> Hi Joseph,
> >>>
> >>> The design of your tables won't really help us to help you here.
> >>>
> >>> OJB is an Object-To-DB-mapping framework; your queries
> >>
> >>will/should (99% of
> >>
> >>> the time) be based on objects.
> >>>
> >>> There is a section on the query
> >>> document(http://jakarta.apache.org/ojb/query.html) that
> >>
> >>discusses joins.
> >>
> >>> Unfortunately, on the apache site the document hasn't been
> >>
> >>transformend
> >>
> >>> correctly. Here follows the XML source of the documentation.
> >>
> >>I hope this
> >>
> >>> helps a bit.
> >>>
> >>> Cheers,
> >>>
> >>> Charles.
> >>>
> >>> <subsection name="joins">
> >>> <p>
> >>> Joins resulting from path expressions ("relationship.attribute") in
> >>> criteria are automatically handled by OJB.
> >>> Path expressions are supported for all relationships 1:1,
> >
> >1:n and m:n
> >
> >>> (decomposed and non-decomposed)
> >>> and can be nested.
> >>> <br/><br/>
> >>> The following sample looks for all articles belonging to the
> >>
> >>product group
> >>
> >>> "Liquors".
> >>> Article and product group are linked by the relationship
> >>
> >>"productGroup" in
> >>
> >>> class Article:
> >>>
> >>> <source><![CDATA[
> >>> <!-- Definitions for org.apache.ojb.ojb.broker.Article -->
> >>> <class-descriptor
> >>> class="org.apache.ojb.broker.Article"
> >>> proxy="dynamic"
> >>> table="Artikel"
> >>>
> >>> ...
> >>> <reference-descriptor
> >>> name="productGroup"
> >>> class-ref="org.apache.ojb.broker.ProductGroup"
> >>>
> >>> <foreignkey field-id-ref="4"/>
> >>> </reference-descriptor>
> >>> </class-descriptor>
> >>>
> >>> <class-descriptor
> >>> class="org.apache.ojb.broker.ProductGroup"
> >>> proxy="org.apache.ojb.broker.ProductGroupProxy"
> >>> table="Kategorien"
> >>>
> >>> ...
> >>> <field-descriptor id="2"
> >>> name="groupName"
> >>> column="KategorieName"
> >>> jdbc-type="VARCHAR"
> >>> />
> >>> ...
> >>> </class-descriptor>
> >>> ]]></source>
> >>>
> >>> <br/>
> >>> The path expression includes the 1:1 relationship
> >>
> >>"productGroup" and the
> >>
> >>> attribute "groupName":
> >>>
> >>> <source><![CDATA[
> >>> Criteria crit = new Criteria();
> >>> crit.addEqualTo("productGroup.groupName", "Liquors");
> >>> Query q = QueryFactory.newQuery(Article.class, crit);
> >>>
> >>> Collection results = broker.getCollectionByQuery(q);
> >>> ]]></source>
> >>>
> >>> </p>
> >>> </subsection>
> >>>
> >>> >-----Original Message-----
> >>>
> >>> From: Joseph Campbell [mailto:[EMAIL PROTECTED]]
> >>>
> >>> >Sent: 18 September 2002 15:25
> >>> >To: [EMAIL PROTECTED]
> >>> >Subject: need a good join example... PLEASE HELP.
> >>> >
> >>> >
> >>> >This may have been answered elsewhere but I am unable to locate it:
> >>> >
> >>> >I am in need of an example for a join query. Here is the
> >>> >situation... two
> >>> >tables that look like this....
> >>> >
> >>> >CREATE TABLE one (
> >>> >keyid int not null,
> >>> >blah1 char(10),
> >>> >blah2 char(10),
> >>> >blah3 char(10)
> >>> >) PK = keyid;
> >>> >
> >>> >CREATE TABLE two (
> >>> >fkid int not null,
> >>> >key1 int not null,
> >>> >key2 int not null
> >>> >) PK = fkid, key1, key2, FK = fkid -> one(keyid)
> >>> >
> >>> >I am trying to get the collection of table one Objects for a
> >>> >given table
> >>> >two(key1, key2) combination. I think I have the XML
> >>> >descriptors set up right
> >>> >but am not sure. I have searched the website for an exmaple
> >>> >of java code
> >>> >with the XML descriptor that I can model after - but have as
> >>> >yet been unable
> >>> >to find one. Can anyone help with a small snip of what this
> >>> >looks like? Or
> >>> >maybe its in a page I just have not read yet and you can
> >>> >forward me to the
> >>> >URL?
> >>> >
> >>> >Thanks,
> >>> > Joe Campbell
> >>> >--
> >>> >You laugh at me because I am different,
> >>> >I laugh at you because you are the same.
> >>> >-----------------------------------------------------
> >>> >Joseph Campbell | EMAIL: [EMAIL PROTECTED]
> >>> >Staff Consultant | URL: www.inventa.com
> >>> >Inventa Technologies | PH: (856)914-5200
> >>> >
> >>> > | PGER: (888)454-0876
> >>> >
> >>> >-----------------------------------------------------
> >>> >
> >>> >
> >>> >--
> >>> >To unsubscribe, e-mail:
> >>>
> >>> <mailto:[EMAIL PROTECTED]>
> >>> For additional commands, e-mail:
> >
> ><mailto:[EMAIL PROTECTED]>
> >
> >> This email and any attachments are strictly confidential and
> >
> >are intended
> >
> >> solely for the addressee. If you are not the intended
> >
> >recipient you must
> >
> >> not disclose, forward, copy or take any action in reliance
> >
> >on this message
> >
> >> or its attachments. If you have received this email in error
> >
> >please notify
> >
> >> the sender as soon as possible and delete it from your
> >
> >computer systems.
> >
> >> Any views or opinions presented are solely those of the
> >
> >author and do not
> >
> >> necessarily reflect those of HPD Software Limited or its affiliates.
> >>
> >> At present the integrity of email across the internet cannot be
> >
> >guaranteed
> >
> >> and messages sent via this medium are potentially at risk.
> >
> >All liability
> >
> >> is excluded to the extent permitted by law for any claims
> >
> >arising as a re-
> >
> >> sult of the use of this medium to transmit information by or to
> >> HPD Software Limited or its affiliates.
> >
> >--
> >You laugh at me because I am different,
> >I laugh at you because you are the same.
> >-----------------------------------------------------
> >Joseph Campbell | EMAIL: [EMAIL PROTECTED]
> >Staff Consultant | URL: www.inventa.com
> >Inventa Technologies | PH: (856)914-5200
> >
> > | PGER: (888)454-0876
> >
> >-----------------------------------------------------
> >
> >
> >--
> >To unsubscribe, e-mail:
>
> <mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
>
>
> This email and any attachments are strictly confidential and are intended
> solely for the addressee. If you are not the intended recipient you must
> not disclose, forward, copy or take any action in reliance on this message
> or its attachments. If you have received this email in error please notify
> the sender as soon as possible and delete it from your computer systems.
> Any views or opinions presented are solely those of the author and do not
> necessarily reflect those of HPD Software Limited or its affiliates.
>
> At present the integrity of email across the internet cannot be guaranteed
> and messages sent via this medium are potentially at risk. All liability
> is excluded to the extent permitted by law for any claims arising as a re-
> sult of the use of this medium to transmit information by or to
> HPD Software Limited or its affiliates.
--
You laugh at me because I am different,
I laugh at you because you are the same.
-----------------------------------------------------
Joseph Campbell | EMAIL: [EMAIL PROTECTED]
Staff Consultant | URL: www.inventa.com
Inventa Technologies | PH: (856)914-5200
| PGER: (888)454-0876
-----------------------------------------------------
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
