set constraint for foreign key in many-to-one association

phongp · **Joined:** Wed Oct 15, 2008 12:57 pm **Posts:** 11

Hi,
I have a map in which I have a many-to-one association between 2 tables such as Person and Address(associated by their primary key). If I said session.get(Person.class,personId), then I dont want Hibernate to load Person's association(such as Address) when addressId in the Person table is 0. Is there any way to do that?
Thanks in advance.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Yes, specify an arbitrary SQL WHERE condition in the "where" parameter of
your collection. (6.2. Collection mappings)

phongp · **Joined:** Wed Oct 15, 2008 12:57 pm **Posts:** 11

Thanks for your reply.
Sorry for not being clear as the first post. I have a Contact.hbm.xml which defines the mapping for Contact object with the Contact table in the db. In the mapping. I have a many-to-one association for Contact and Customer since 1 customer may have many contacts. My goal is to avoid hibernate to load the customer when the customerId in the Contact is 0.

nordborg · **Posted:** Thu Oct 23, 2008 2:01 am

It would be a lot easier to help if you did post relevant parts of your mapping documents. You have two options:

1. Set not-found="ignore"

Code:

<many-to-one name="customer" column="customerId" not-found="ignore".... >

2. Use a formula instead of column:

Code:

<many-to-one name="customer" formula="if(customerId<>0, customerId, null)" .... >

phongp · **Joined:** Wed Oct 15, 2008 12:57 pm **Posts:** 11

Thanks for your reply. When I use your formula,it gives me the sql error near the key word if.However,this is not I want. What I want is when the method contact.performLoadById(int id) is invoked, it will load the info of the Contact. Besides,in the Contact,it has a Customer field(represented by its id),I just like to load only that particular Customer if its id is not 0.
My map is:

Code:

<hibernate-mapping>
  <class name="com.mcwsoftware.iuxserver.model.Contact" table="CONTACT">
      <id name="contactId" type="int" column="ContactId" >
      <generator class="native"/>
     </id>
<property name="firstName">
      <column name="FirstName"/>
     </property>
     <property name="lastName">
      <column name="LastName"/>
     </property>
<many-to-one  name="customer" column="CustomerId" fetch="join" foreign-key="FK_Contact_Customer" not-found="ignore" />
</hibernate-mapping>

<hibernate-mapping>
<class name="com.mcwsoftware.iuxserver.model.Customer" table="CUSTOMER">
<id name="customerId" type="int" column="CustomerId" >
<generator class="increment"/>
</id>

Code:

<property name="companyName">
        <column name="CompanyName"/>
     </property>
</hibernate-mapping>

Once again,thanks again for your reply.

nordborg · **Posted:** Thu Oct 23, 2008 5:38 am

Have you tried setting fetch="select" instead? When you have fetch="join" Hibernate tries to load everything with one SQL. I am not sure how this works out with formulas or not-found="ignore".

The formula example is for MySQL. The syntax is probably different for different databases. With PostgreSQL, for example, the corresponding function is nullif(customerId, 0).

phongp · **Joined:** Wed Oct 15, 2008 12:57 pm **Posts:** 11

Thanks for your reply.
I solved the problem by doing like this

Code:

<many-to-one  name="customer" column="CustomerId" fetch="join" foreign-key="FK_Contact_Customer" not-found="ignore" formula="case when CustomerId!=0 then CustomerId else -1 end" />

But now I have another problem. That is for insert. When I want to insert a new Contact in which there is a Customer with an existing id. Without formula, it works fine when I say

Code:

Contact contact = new Contact();
contact.setFirstName("fgfgkfg");
contact.setLastName("afldor");
contact.setCustomer(new Customer(3483));
contactService.performInsert(contact);

With the formula attribute in the many-to-one tag, the CustomerId cannot be inserted with the value 3843.

nordborg · **Posted:** Fri Oct 24, 2008 2:26 am

I think that is expected, a property with a formula is read-only.