This is my first ever post to a newsgroup, so I hope I get it right.
I've got the basics of OJB down, but now I am attempting to do some fairly
nested joins and OJB is not producting the SQL
statements I know I need (i.e. I've written them manually before).
This is about what I need:
SELECT DISTINCT IMENU.SYSTEM_NUMBER, IMENU.LAST_UPDATED,
IMENU.LAST_UPDATED_BY, IMENU.NAME, IMENU.DISABLED
FROM (((ASSOC_IGROUP_IUSER INNER JOIN IGROUP ON
ASSOC_IGROUP_IUSER.GROUP_SYSNO = IGROUP.SYSTEM_NUMBER) INNER JOIN
ASSOC_IGROUP_IOBJECT ON IGROUP.SYSTEM_NUMBER =
ASSOC_IGROUP_IOBJECT.GROUP_SYSNO) INNER JOIN IOBJECT ON
ASSOC_IGROUP_IOBJECT.OBJECT_SYSNO = IOBJECT.SYSTEM_NUMBER) INNER JOIN
(ASSOC_IOBJECT_IMENU INNER JOIN IMENU ON
ASSOC_IOBJECT_IMENU.MENU_SYSNO = IMENU.SYSTEM_NUMBER) ON
IOBJECT.SYSTEM_NUMBER = ASSOC_IOBJECT_IMENU.OBJECT_SYSNO
WHERE ASSOC_IGROUP_IUSER.USER_SYSNO='24';
Yeah I know, pretty messed up, but I am a newbie. OJB is giving me:
SELECT DISTINCT
A0.DISABLED,A0.NAME,A0.SYSTEM_NUMBER,A0.LAST_UPDATED_BY,A0.LAST_UPDATED
FROM IMCOP.IMENU A0,ASSOC_IOBJECT_IMENU A1,IMCOP.IOBJECT
A2,ASSOC_IGROUP_IOBJECT A3,IMCOP.IGROUP A4,IMCOP.ASSOC_IGROUP_IUSER
A5
WHERE A4.SYSTEM_NUMBER=A5.USER_SYSNO AND A3.GROUP_SYSNO=A4.SYSTEM_NUMBER AND
A2.SYSTEM_NUMBER=A3.OBJECT_SYSNO AND
A1.OBJECT_SYSNO=A2.SYSTEM_NUMBER AND A0.SYSTEM_NUMBER=A1.MENU_SYSNO AND
(A5.USER_SYSNO = '24' );
To produce this I'm using this code:
public static String getUsersMenus(User user) throws Exception {
PersistenceBroker broker = null;
broker = PersistenceBrokerFactory.defaultPersistenceBroker();
Criteria criteria = new Criteria();
criteria.addEqualTo("systemNumber.memberOfMenus.memberObjects.memberOfGroups
.memberUsers.userSystemNumber",user.getSystemNumb
er());
Query query = QueryFactory.newQuery(Menu.class, criteria, true);
Collection leafMenus = broker.getCollectionByQuery(query);
...
}
This is my repository_user.xml (omitting non-important fields):
<!-- Definitions for User object-->
<class-descriptor
class="mil.army.ima.ser.imcop.user.User"
table="IMCOP.IUSER">
<field-descriptor
name="systemNumber"
column="SYSTEM_NUMBER"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
indexed="true" />
...
<collection-descriptor
name="memberOfGroups"
element-class-ref="mil.army.ima.ser.imcop.associations.UserRole"
proxy="true">
<inverse-foreignkey field-ref="groupSystemNumber" />
</collection-descriptor>
<collection-descriptor
name="favoriteObjects"
element-class-ref="mil.army.ima.ser.imcop.object.Object"
indirection-table="IFAVORITES"
proxy="true">
<fk-pointing-to-this-class column="USER_SYSNO" />
<fk-pointing-to-element-class column="OBJECT_SYSNO" />
</collection-descriptor>
</class-descriptor>
<!-- Definitions for associations UserRole object
This is a table for Manual Decomp -->
<class-descriptor
class="mil.army.ima.ser.imcop.associations.UserRole"
table="IMCOP.ASSOC_IGROUP_IUSER">
<field-descriptor
name="userSystemNumber"
column="USER_SYSNO"
jdbc-type="INTEGER"
primarykey="true" />
<field-descriptor
name="groupSystemNumber"
column="GROUP_SYSNO"
jdbc-type="INTEGER"
primarykey="true" />
<field-descriptor
name="administrator"
column="ADMINISTRATOR"
jdbc-type="BIT" />
<reference-descriptor
name="user"
class-ref="mil.army.ima.ser.imcop.user.User"
proxy="true">
<foreignkey field-ref="userSystemNumber" />
</reference-descriptor>
<reference-descriptor
name="group"
class-ref="mil.army.ima.ser.imcop.group.Group"
proxy="true">
<foreignkey field-ref="groupSystemNumber" />
</reference-descriptor>
</class-descriptor>
<!-- Definitions for Group object-->
<class-descriptor
class="mil.army.ima.ser.imcop.group.Group"
table="IMCOP.IGROUP">
<field-descriptor
name="systemNumber"
column="SYSTEM_NUMBER"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
indexed="true" />
...
<collection-descriptor
name="memberUsers"
element-class-ref="mil.army.ima.ser.imcop.associations.UserRole"
proxy="true">
<inverse-foreignkey field-ref="userSystemNumber" />
</collection-descriptor>
<collection-descriptor
name="memberObjects"
element-class-ref="mil.army.ima.ser.imcop.object.Object"
indirection-table="ASSOC_IGROUP_IOBJECT"
proxy="true">
<fk-pointing-to-this-class column="GROUP_SYSNO" />
<fk-pointing-to-element-class column="OBJECT_SYSNO" />
</collection-descriptor>
</class-descriptor>
<!-- Definitions for Object object-->
<class-descriptor
class="mil.army.ima.ser.imcop.object.Object"
table="IMCOP.IOBJECT">
<field-descriptor
name="systemNumber"
column="SYSTEM_NUMBER"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
indexed="true" />
jdbc-type="VARCHAR" />
...
<collection-descriptor
name="memberOfGroups"
element-class-ref="mil.army.ima.ser.imcop.group.Group"
indirection-table="ASSOC_IGROUP_IOBJECT"
proxy="true">
<fk-pointing-to-this-class column="OBJECT_SYSNO" />
<fk-pointing-to-element-class column="GROUP_SYSNO" />
</collection-descriptor>
<collection-descriptor
name="memberOfMenus"
element-class-ref="mil.army.ima.ser.imcop.menu.Menu"
indirection-table="ASSOC_IOBJECT_IMENU"
proxy="true">
<fk-pointing-to-this-class column="OBJECT_SYSNO" />
<fk-pointing-to-element-class column="MENU_SYSNO" />
</collection-descriptor>
...
</class-descriptor>
<!-- Definitions for Menu object-->
<class-descriptor
class="mil.army.ima.ser.imcop.menu.Menu"
table="IMCOP.IMENU">
<field-descriptor
name="systemNumber"
column="SYSTEM_NUMBER"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
indexed="true" />
...
<collection-descriptor
name="memberObjects"
element-class-ref="mil.army.ima.ser.imcop.object.Object"
indirection-table="ASSOC_IOBJECT_IMENU"
proxy="true">
<fk-pointing-to-this-class column="MENU_SYSNO" />
<fk-pointing-to-element-class column="OBJECT_SYSNO" />
</collection-descriptor>
<collection-descriptor
name="children"
element-class-ref="mil.army.ima.ser.imcop.associations.MenuOrder"
proxy="true">
<inverse-foreignkey field-ref="child" />
</collection-descriptor>
<collection-descriptor
name="parents"
element-class-ref="mil.army.ima.ser.imcop.associations.MenuOrder"
proxy="true">
<inverse-foreignkey field-ref="parent" />
</collection-descriptor>
</class-descriptor>
In this there are a lot of M:N joins, and maybe I'm not going about this the
right way, but for time being I need to get this
to work.
Thanks for a great product.
Mike
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
