looks like my question wasn't clear enough ;) i know the join i have to do but i'm not sure about how to select the multiple keys when retrieving all A for several Cs:
A)
select * from A join B on A.A1 = B.A1 and A.A2 = B.A2 join C on C.C1 = B.C1 and C.C2 = B.C2 and C.C3 = B.C3 where (C.C1 = 1 and C2 = 2 and C.C3 = 3) or (C.C1 = 11 and C2 = 12 and C.C3 = 13) or (C.C1 = 21 and C2 = 22 and C.C3 = 23) or (C.C1 = 31 and C2 = 32 and C.C3 = 33)
(C.C1 = and C2 = and C.C3 = ) will be repeated for every C. this will result in a quite complex sql depending on the number of Cs.
an alternative would be to use IN-statement as in single-key :
B)
select * from A join B on A.A1 = B.A1 and A.A2 = B.A2 join C on C.C1 = B.C1 and C.C2 = B.C2 and C.C3 = B.C3 where C.C1 in ( 1, 11, 21, 31) and C.C2 in ( 2, 12, 22, 32) and C.C3 in ( 3, 13, 23, 33)
the query is simpler but may return more As than necessary because the IN-statements do not constrain the combination of keys.
i prefer alternative A) what do you think ?
jakob
Edson Carlos Ericksson Richter wrote:
Thinking less abstract, the field names of PK in A object are same field names in B table, as the field names of PK in C are the same field names in B.
So, if A has the fields A1, A2 (PKs), and C has fields C1, C2, C3 (PKs), then B has fields A1, A2, C1, C2, C3.
So, the select will be:
select * from A join B on A.A1 = B.A1 and A.A2 = B.A2 join C on C.C1 = B.C1 and C.C2 = B.C2 and C.C3 = B.C3
IMHO,
the names of the fields are not important, but their position. The "pointing-to-this-class" should be understood as 1st PK is pointing to first field, and 2nd PK to the 2nd field.
The first pointing-to-element should be mapped to 1st PK in the N element, the 2nd to the second PK, and the 3rd, to 3rd PK.
As far as I can see, the MtoN must know the CDS of the M element and the N element, so the mapping could be done.
Richter
Edson Carlos Ericksson Richter wrote:
The expected result is something like
A<->B<->C
Where A is M side has PK with 2 fields, C is N side has PK with 3 fields, B is indirection table, and has 5 fields.
select * from A join B on A.field1 = B.field1 and A.field2 = B.field2 join C on C.field1 = B.field3 and C.field2 = B.field4 and C.field3 = B.field5
I expect this help.
Edson Richter
Jakob Braeuchi wrote:
hi edson,
imo the whole handling of the multi-key criteria is a little bit weird. what should the sql look like in your case ?
jakob
Edson Carlos Ericksson Richter wrote:
Hum... There is some instability here. I don't know what combination of dynamic proxies/collection proxies is causing this bug. I've fixed using either proxy=true in the collections inside each bean...
Why? I really don't know. I'll need to compare each of the 12 collections
involved in this beans to know what's give the stack trace...
I'll need sometime to restart the app 132 times to know :(
Anyway, if someone could send me the rc5 implementation of the method, so I
can compare with actual, I'll appreciate.
Thanks in advantage.
Edson Richter
----- Original Message ----- From: Edson Carlos Ericksson Richter
To: 'OJB Users List'
Sent: Wednesday, March 24, 2004 2:46 PM
Subject:
MtoNCollectionPrefetcher.buildPrefetchCriteriaMultipleKeys(MtoNCollectionPre
fetcher.java:338)
This bug is driving me crazy... Someone could point me what changed from rc5
to 1.0 in this method (so I can at least fix locally)?
The problem is a M:N mapping using composite pk pointing the M bean, and 1
field pk pointing to the N bean.
The stack trace is:
java.lang.ArrayIndexOutOfBoundsException: 1
at
org.apache.ojb.broker.accesslayer.MtoNCollectionPrefetcher.buildPrefetchCrit
eriaMultipleKeys(MtoNCollectionPrefetcher.java:338)
at
org.apache.ojb.broker.accesslayer.MtoNCollectionPrefetcher.buildPrefetchCrit
eria(MtoNCollectionPrefetcher.java:259)
at
org.apache.ojb.broker.accesslayer.MtoNCollectionPrefetcher.buildPrefetchQuer
y(MtoNCollectionPrefetcher.java:115)
at
org.apache.ojb.broker.accesslayer.CollectionPrefetcher.buildPrefetchQueries(
CollectionPrefetcher.java:93)
at
org.apache.ojb.broker.accesslayer.MtoNCollectionPrefetcher.prefetchRelations
hip(MtoNCollectionPrefetcher.java:68)
at
org.apache.ojb.broker.core.QueryReferenceBroker.performRetrievalTasks(QueryR
eferenceBroker.java:277)
at
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
ferenceBroker.java:146)
at
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
ferenceBroker.java:203)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(Persis
tenceBrokerImpl.java:1046)
at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(
DelegatingPersistenceBroker.java:321)
at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(
DelegatingPersistenceBroker.java:321)
at
org.apache.ojb.broker.accesslayer.CollectionProxy.loadData(CollectionProxy.j
ava:109)
at
org.apache.ojb.broker.accesslayer.ListProxy.loadData(ListProxy.java:148)
at
org.apache.ojb.broker.accesslayer.CollectionProxy.getData(CollectionProxy.ja
va:362)
at
org.apache.ojb.broker.accesslayer.ListProxy.getListData(ListProxy.java:140)
at
org.apache.ojb.broker.accesslayer.ListProxy.get(ListProxy.java:71)
at
br.com.mgr.dialogs.ListBasedListModel.getElementAt(ListBasedListModel.java:6
3)
at
javax.swing.plaf.basic.BasicListUI.updateLayoutState(BasicListUI.java:1147)
at
javax.swing.plaf.basic.BasicListUI.maybeUpdateLayoutState(BasicListUI.java:1
098)
at
javax.swing.plaf.basic.BasicListUI.getPreferredSize(BasicListUI.java:281)
at javax.swing.JComponent.getPreferredSize(JComponent.java:1275)
at
javax.swing.JList.getPreferredScrollableViewportSize(JList.java:1913)
at
javax.swing.ViewportLayout.preferredLayoutSize(ViewportLayout.java:72)
at java.awt.Container.preferredSize(Container.java:1178)
at java.awt.Container.getPreferredSize(Container.java:1162)
at javax.swing.JComponent.getPreferredSize(JComponent.java:1277)
at
javax.swing.ScrollPaneLayout.preferredLayoutSize(ScrollPaneLayout.java:475)
at java.awt.Container.preferredSize(Container.java:1178)
at java.awt.Container.getPreferredSize(Container.java:1162)
at javax.swing.JComponent.getPreferredSize(JComponent.java:1277)
at java.awt.BorderLayout.preferredLayoutSize(BorderLayout.java:572)
at java.awt.Container.preferredSize(Container.java:1178)
at java.awt.Container.getPreferredSize(Container.java:1162)
at javax.swing.JComponent.getPreferredSize(JComponent.java:1277)
at
javax.swing.plaf.basic.BasicTabbedPaneUI$TabbedPaneLayout.calculateSize(Basi
cTabbedPaneUI.java:2001)
at
javax.swing.plaf.basic.BasicTabbedPaneUI$TabbedPaneLayout.preferredLayoutSiz
e(BasicTabbedPaneUI.java:1975)
at java.awt.Container.preferredSize(Container.java:1178)
at java.awt.Container.getPreferredSize(Container.java:1162)
at javax.swing.JComponent.getPreferredSize(JComponent.java:1277)
at java.awt.GridBagLayout.GetLayoutInfo(GridBagLayout.java:892)
at java.awt.GridBagLayout.getLayoutInfo(GridBagLayout.java:806)
at java.awt.GridBagLayout.ArrangeGrid(GridBagLayout.java:1374)
at java.awt.GridBagLayout.arrangeGrid(GridBagLayout.java:1337)
at java.awt.GridBagLayout.layoutContainer(GridBagLayout.java:706)
at java.awt.Container.layout(Container.java:1020)
at java.awt.Container.doLayout(Container.java:1010)
at java.awt.Container.validateTree(Container.java:1092)
at java.awt.Container.validateTree(Container.java:1099)
at java.awt.Container.validateTree(Container.java:1099)
at java.awt.Container.validateTree(Container.java:1099)
at java.awt.Container.validate(Container.java:1067)
at
javax.swing.RepaintManager.validateInvalidComponents(RepaintManager.java:353
)
at
javax.swing.SystemEventQueueUtilities$ComponentWorkRequest.run(SystemEventQu
eueUtilities.java:116)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:178)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:454)
at
java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.ja
va:201)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java
:151)
at
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145)
at
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
Thanks,
Edson Richter
--- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.637 / Virus Database: 408 - Release Date: 20/3/2004
--------------------------------------------------------------------- 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]