Re: ODMG - MULTIPLE TABLES - Retrieval

Sat, 11 Sep 2004 02:38:11 -0700 

Hi again,

not sure if this is possible, I'm not very familiar with the query stuff.

Now it's clear why you ask for "mapping a class to multiple tables". But this will be ONLY possible when PartA, ..B..C use the same composed PK (partNum, partSerial). So you have to change your composed PK or if you only do read operations and partNum/partSerial are unique don't tell OJB about the third PK (do not set flag primary-key in field-descriptor for 'system', 'type'...).

If you have the following java-object hierachy

PartA<--PartB<--PartC
(PartB extends PartA and introduce field 'type' and 'cost', ...)
and you use "mapping a class to multiple tables" feature, it should be possible (but I never tried this) when using a report query like this:

Criteria c = new Criteria();
ReportQueryByCriteria q = QueryFactory.newReportQuery(PartC.class, c);
q.setAttributes(new String[] { "partNum", "partSerial", "sum(qty)", "sum(cost)", "sum(size)"});
q.addGroupBy("partNum");
q.addGroupBy("partSerial");
Iterator iter = broker.getReportQueryIteratorByQuery(q);

(partNum,... are the field names of the persistent objects PartA,...)

But I think this will not fit your requirement.
By the way, you can always invoke plain SQL within OJB ;-)

regards,
Armin

kishore talagadadeevi wrote:

Thanks Armin for your input . Let me explain my question in a better way so I can be directed in right direction . I am attaching the sql also in this note...


1st Table :
--------------

TABLE_PARTA:
-------------------------
part_num(pk)
part_serial(pk)
system(pk)
qty

The primary key of this table consists of part_num,part_serial,system.
qty is a non-primary key column in the same table.

2ndTable :
--------------

TABLE_PARTB:
-------------------------
part_num(pk)
part_serial(pk)
type(pk)
cost

3rd table:
-----------------

The primary key of this table consists of part_num,part_serial,type.
cost is a non-primary key column in the same table.

TABLE_PARTC:
-------------------------
part_num(pk)
part_serial(pk)
dimension(pk)
size

The primary key of this table consists of part_num,part_serial,dimesion.
size is a non-primary key column in the same table.


The primary key of all these tables consist of three columns out of which two columns(part_num,part_serial ) are alike but not the third column.

I would like to retrieve qty from PartA table, cost from PartB table and size from PartC table . The following sql may explain it better what I am trying to attain....

select distinct \
      A.part_num, \
      A.part_serial, \
      sum(A.qty) as numerator, \
      sum(B.cost) as denominator, \
      sum(C.size) as dim \

from TABLE_PARTA A, TABLE_PARTB  B, \
    TABLE_PARTC C \

where \
     B.part_num = A.part_num \
     and B.part_serial= A.part_serial\
     and C.part_num = B.part_num \
     and C.part_serial= B.part_serial\


Thanks in advance ...


From: Armin Waibel <[EMAIL PROTECTED]>
Reply-To: "OJB Users List" <[EMAIL PROTECTED]>
To: OJB Users List <[EMAIL PROTECTED]>
Subject: Re: ODMG - MULTIPLE TABLES - Retrieval
Date: Fri, 10 Sep 2004 20:59:17 +0200

Hi,

kishore talagadadeevi wrote:

Hi,

I would like to know whether any known issues with ODMG api in handling multiple tables by mapping to a single class.


Mapping a single class to multiple tables was currently not supported (insert does not work, reading should work but I never test this) by ODMG (PB-api support this feature).

I need to just retrieve the data only . 


as said above, this could be work with odmg.

I have seen some documentation
explaining a mapping of a class to multile tables. But I want to know whether it is going to
work for my scenario or not before starting my coding ....

Requirement:
-----------------

I have three independent tables  .

All three tables have part_num and part_serial as part of composite primary key in addition to other columns.

Question: How do I retrieve qty,cost and size by joining all three tables on part_num and
part_serial columns using OJB ???

How do I define this scenario as a class descriptor in repository.xml file ???



Don't know if I understand your question, but nevertheless I will try answer something ;-)

You can use 'Extents' with a base interface.
http://db.apache.org/ojb/docu/guides/advanced-technique.html#Extents

interface Part
{
get/setNum
get/setSerial
{

PartA,...C implement interface Part

In repository file declare class-descriptor for PartA,...C (with PK attribute for num + serial) and
define these classes as extents of Part

<class-descriptor class="Part">
   <extent-class class-ref="PartA" />
   <extent-class class-ref="PartB" />
   <extent-class class-ref="PartC" />
</class-descriptor>

Criteria crit = new Criteria();
[crit.addEqualTo("fieldName", value);]
crit.addEqualTo("partNum", 12);
crit.addEqualTo("serial", 123);
Query q = QueryFactory.newQuery(Part.class, crit);
Collection result = broker.getCollectionByQuery(q);

Will return all Part classes (PartA, ...C) with part_num 12 and part_serial 123


regards,
Armin


TABLE_PARTA:
-------------------------

part_num(pk)
part_serial(pk)
system(pk)
qty


TABLE_PARTB
---------------------


part_num(pk)
part_serial(pk)
type(pk)
cost

TABLE_PARTC :
-----------------------
part_num(pk)
part_serial(pk)
dimension(pk)
size

Please throw some light on the above mentioned issue ...

Thanks in advance ...



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]




---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]




---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]





---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to