composite-id, one-to-one, formula, and an outer join

[Mr. Wampler]

Please pardon my nubity.

I have simple relationship.
company: company_id, [else]
address: address_id, type, [else]

company.company_id is an identity. address.address_id is NOT an identity. It is the natural key when paired with address.type. We have a one-to-one relationship here: when address_id = company_id and type = 'company'.

Now addresses can belong to other domain objects, such as Users or Invoices. The address_id gets the identity of those respective objects with its type differing. (In the case of User-to-Address we have a one-to-many.) Therefore my AddressVO is generic for use elsewhere.

It seemed I had the mapping for my company composite object correct at one point. I have a one-to-one and use a formula to hardcode the type. Here is my mapping:

  <class name="AddressVO" table="address">
    <composite-id>
      <key-property
        name="addressId"
        column="address_id"
        type="int"
        length="10"
      />
      <key-property
        name="type"
        column="type"
        type="java.lang.String"
        length="30"
      />
    </composite-id>
    ...
  </class>
    
  <class name="VendorVO" table="company">
      <id
        name="identityKey"
        column="company_id"
        type="int"
        length="10"
      >
      ...
      <one-to-one
          name="address"
          cascade="none"
          class="AddressVO" >
          <formula>company_id</formula>
          <formula>'company'</formula>
      </one-to-one>
  </class>

Unfortunately, I've found that if the address record is not there for the company, then the company record is never pulled on query. I would assume that the company object is found, but it would have a null address. I've traced this quibble back to an inner join in the SQL statement (simplified):

select 
    company.*, 
    'company' as formula, 
    address.* 
from 
    company [B]inner join[/B] address 
        on company.company_id= address.address_id and 
        'company'= address.type 
where lower(company.name) like ?

What I need is an outer join. I want that company regardless if it has an address.

I've tried just about every mapping property under the sun and I cannot get that inner to turn into an outer. Here's what has failed, there has been NO change to the resulting sql:

-outer-join="true"
-fetch="join"
-fetch="select" (even this still had an inner join in the SQL!)
-constrained="true|false"

I've tried it with a many-to-one mapping, again with:
-outer-join="true"
-fetch="join"
-fetch="select"
-not-null="true"
-not-found="ignore"
-unique="true"

I've also altered the hibernate config to include the property hibernate.max_fetch_depth at 1. I have only touched the VendorVO. Again, AddressVO is used elsewhere and I don't want to monkey with it.

I want to blame the formula/composite-id for confusing hibernate. We have another example in our system with a one-to-one and a many-to-one without formulas, with identities. In that case, it appears that the many-to-one becomes an inner and the one-to-ones becomes outers. The type of join is not explicitly stated in those mapping properties.

My questions come down to: What am I doing wrong? How does hibernate determine the join type? Why is hibernate completely ignoring my join mappings?

Additional notes:
-I'm on a large enterprise system. Much of the hibernate transaction calls are buried deep away from most developers.
-I am searching on VendorVO with aliased AddressVO fields.
-I don't want to make a special AddressVO object just for VendorVOs. Google seems to like this solution, but it seems redundant.

Thanks.

[Roshan Dawrani]

Hi,
I also had this exact problem. My one-to-one relationship between Customer and Address was optional but because the query generated by hibernate was using inner join between CUSTOMER and ADDRESS table, it was not returning the CUSTOMER records that did not have the corresponding record in the ADDRESS table. I tried many changes in the hibernate mapping configuration for this relationship but nothing helped.

The way I could solve the issue in my case was that when I create an alias for the association customer.address, I use the class org.hibernate.Criteria 's method "createAlias(String associationPath, String alias, int joinType)" and pass it explicitly the join type that I require. So I passed the join type flag org.hibernate.sql.JoinFragment.LEFT_OUTER_JOIN and it started using left outer join between customer and address tables in the query it generates and started returning customers without address records.

Hope you can apply this or some similar approach in your case.

Regards,
Roshan Dawrani

[Mark Spritzler]

"Mr. Wampler"

Please click on the My Profile link above and change your display name to meet the JavaRanch Naming Policy of using your real first and real last names.

Thanks for your understading.

Mark