Associations trouble

[Chris Halverson <cd...@halverson.org>]

I'm having some problems understanding how to implement the 1:1 and
1:n mappings in OJB (1.0rc1). I'm trying to use collections and
foreignkey mappings to represent my DB. For example, it appears that
the collection in my BillingControl class does not get propogated
(debug log shows size of 0), although I am able to do simple queries
w/ Critieria (for example, the authentication is done by looking up
account_number and customer_id in billing_control). I can also get
things such as all_so_header where customer_id = ?, but I would like
it to be automatic via the repository.

Any help would be greatly appreciated. As you can see, there is
actually a few different joins needed to get things.

The DDL is below:

create table billing_control (
  account_number varchar(20) NOT NULL,
  customer_id varchar(10) NOT NULL,
  primary key (account_number)
);

create table all_so_header (
  customer_id varchar(10) not null,
  order_id varchar(10) not null,
  ckt_order varchar(20),
  end_user_name varchar(80),
  product_id varchar(6),
  desired_due_date date,
  status varchar(10),
  primary key (customer_id, order_id),
  foreign key (customer_id) references billing_control(customer_id)
);

create table ckt_order (
  order_id varchar(10) not null,
  system_id varchar(10) not null,
  ckt_ser_service_code varchar(2),
  ckt_ser_service_modifier varchar(2),
  ckt_type varchar(1),
  issue integer,
  ckt_fac_location_a varchar(11),
  ckt_fac_location_z varchar(11),
  ckt_fac_facility_type varchar(6),
  ckt_fac_designator varchar(10),
  implementation_contact varchar(40),
  ckt_ser_serial_no varchar(10),
  primary key(order_id, system_id),
  foreign key(order_id) references all_so_header(order_id)
);

create table circuit_ckt_locations (
  system_id varchar(10) not null,
  location_a varchar(11),
  location_z varchar(11),
  primary key(system_id),
  foreign key(system_id) references ckt_order(system_id)
);

create table service_code_dict (
 service_code varchar(2) not null,
 service_modifier varchar(2),
 description varchar(60),
 primary key(service_code),
 foreign key(service_code) references ckt_order(ckt_ser_service_code),
 foreign key(service_modifier) references ckt_order(ckt_ser_service_modifier)
);

My repository_user.xml is:

<class-descriptor
 class="com.enventistelecom.custportal.businessobjects.BillingControl"
 table="billing_control">

  <field-descriptor id="1"
     name="accountNumber"
     column="account_number"
     jdbc-type="VARCHAR"
     primarykey="true"
  />

  <field-descriptor id="2"
     name="customerId"
     column="customer_id"
     jdbc-type="VARCHAR"
     primary-key="true"
  />

  <collection-descriptor
    name="allSoHeaders"
    element-class-ref="com.enventistelecom.custportal.businessobjects.AllSoHeader"
  >
    <inverse-foreignkey field-ref="customerId"/>
  </collection-descriptor>

</class-descriptor>

<class-descriptor
 class="com.enventistelecom.custportal.businessobjects.AllSoHeader"
 table="all_so_header">

  <field-descriptor
     name="orderId"
     column="order_id"
     jdbc-type="VARCHAR"
     primarykey="true"
  />

  <field-descriptor
     name="cktOrder"
     column="ckt_order"
     jdbc-type="VARCHAR"
  />

  <field-descriptor
     name="customerId"
     column="customer_id"
     jdbc-type="VARCHAR"
  />

  <field-descriptor
     name="endUserName"
     column="end_user_name"
     jdbc-type="VARCHAR"
  />

  <field-descriptor
     name="productId"
     column="product_id"
     jdbc-type="VARCHAR"
  />

  <field-descriptor
     name="desiredDueDate"
     column="desired_due_date"
     jdbc-type="DATE"
  />

  <field-descriptor
     name="status"
     column="status"
     jdbc-type="VARCHAR"
  />

  <reference-descriptor
     name="customerId"
     class-ref="com.enventistelecom.custportal.businessobjects.BillingControl">
     <foreignkey field-ref="customerId"/>
  </reference-descriptor>

  <collection-descriptor
    name="allOrders"
    auto-retrieve="true"
    element-class-ref="com.enventistelecom.custportal.businessobjects.CktOrder"
  >
    <inverse-foreignkey field-ref="orderId"/>
  </collection-descriptor>

</class-descriptor>

<class-descriptor
 class="com.enventistelecom.custportal.businessobjects.CktOrder"
 table="ckt_order">

  <field-descriptor id="1"
     name="orderId"
     column="order_id"
     jdbc-type="VARCHAR"
     primarykey="true"
  />

  <field-descriptor id="2"
     name="systemId"
     column="system_id"
     jdbc-type="VARCHAR"
     primarykey="true"
  />

  <field-descriptor id="3"
     name="cktSerServiceCode"
     column="ckt_ser_service_code"
     jdbc-type="VARCHAR"
  />

  <field-descriptor id="4"
     name="cktSerServiceModifier"
     column="ckt_ser_service_modifier"
     jdbc-type="VARCHAR"
  />

  <field-descriptor id="5"
     name="cktType"
     column="ckt_type"
     jdbc-type="VARCHAR"
  />
  <field-descriptor id="6"
     name="issue"
     column="issue"
     jdbc-type="INTEGER"
  />

  <field-descriptor id="7"
     name="cktFacLocationA"
     column="ckt_fac_location_A"
     jdbc-type="VARCHAR"
  />

  <field-descriptor id="8"
     name="cktFacLocationZ"
     column="ckt_fac_location_Z"
     jdbc-type="VARCHAR"
  />

  <field-descriptor id="9"
     name="cktFacFacilityType"
     column="ckt_fac_facility_type"
     jdbc-type="VARCHAR"
  />

  <field-descriptor id="10"
     name="cktFacDesignator"
     column="ckt_fac_designator"
     jdbc-type="VARCHAR"
  />

  <field-descriptor id="11"
     name="implementationContact"
     column="implementation_contact"
     jdbc-type="VARCHAR"
  />

  <field-descriptor id="12"
     name="cktSerSerialNo"
     column="ckt_ser_serial_no"
     jdbc-type="VARCHAR"
  />

  <reference-descriptor
     name="orderId"
     class-ref="com.enventistelecom.custportal.businessobjects.AllSoHeader">
     <foreignkey field-ref="orderId"/>
  </reference-descriptor>

</class-descriptor>

<class-descriptor
 class="com.enventistelecom.custportal.businessobjects.CircuitCktLocations"
 table="circuit_ckt_locations">

  <field-descriptor id="1"
     name="systemId"
     column="system_id"
     jdbc-type="VARCHAR"
     primarykey="true"
  />

  <field-descriptor id="2"
     name="locationA"
     column="location_a"
     jdbc-type="VARCHAR"
  />

  <field-descriptor id="3"
     name="locationZ"
     column="location_z"
     jdbc-type="VARCHAR"
  />

  <reference-descriptor
     name="systemId"
     class-ref="com.enventistelecom.custportal.businessobjects.CktOrder">
     <foreignkey field-ref="orderId"/>
  </reference-descriptor>

</class-descriptor>

<class-descriptor
 class="com.enventistelecom.custportal.businessobjects.ServiceCodeDict"
 table="service_code_dict">

  <field-descriptor id="1"
     name="serviceCode"
     column="service_code"
     jdbc-type="VARCHAR"
     primarykey="true"
  />

  <field-descriptor id="2"
     name="serviceModifier"
     column="service_modifier"
     jdbc-type="VARCHAR"
  />

  <field-descriptor id="3"
     name="description"
     column="description"
     jdbc-type="VARCHAR"
  />

  <reference-descriptor
     name="serviceCode"
     class-ref="com.enventistelecom.custportal.businessobjects.CktOrder">
     <foreignkey field-ref="cktSerServiceCode"/>
  </reference-descriptor>

  <reference-descriptor
     name="serviceModifier"
     class-ref="com.enventistelecom.custportal.businessobjects.CktOrder">
     <foreignkey field-ref="cktSerServiceModifier"/>
  </reference-descriptor>

</class-descriptor>

One query I'm trying to get is obtained via:

  select ckto.ckt_ser_service_code, ckto.ckt_ser_service_modifier,
  ckto.issue, ccl.location_a, ccl.location_z, scd.description,
  ckto.implementation_contact from all_so_header so left outer join
  ckt_order ckto on so.order_id = ckto.order_id left outer join
  circuit_ckt_locations ccl on ckto.system_id = ccl.system_id left outer
  join service_code_dict scd on ckto.ckt_ser_service_code =
  scd.service_code where so.customer_id = ? and ckto.ckt_type = ?;

Thank you for any help!!

cdh

-- 
Chris D. Halverson                         http://www.halverson.org/