Mapping to property that is neither pk nor unique

sanchitsriv · **Posted:** Wed Jul 02, 2008 5:37 am

I have got 2 tables jobs and person, which I need to map.
jobs
-----------
jobId (pk)
lastUpdateUser

<many-to-one name="person" class="Person" cascade="none" fetch="join" unique="false" property-ref="mailId">
<column unique="false" name="lastUpdateUser" length="16" not-null="true" />
</many-to-one>

person
---------------
id (pk)
mailId
flag1
flag2
flag3

Now i need to map lastUpdateUser in jobs to mailId in person, and the problem is neither of them is unique.
So what it says is that I will have a many-to-one relation from jobs to person, conditioning that i check the flag values.

I am querying using criteria api as follows
-------------------------------------

Code:

session.createCriteria(Jobs.class).createAlias("person", "lastUpdateUser", Criteria.LEFT_JOIN)
              .add(Restrictions.eq("lastUpdateUser.flag1", new Short((short) 1)))
              .add(Restrictions.eq("lastUpdateUser.flag2", new Short((short) 1)))
              .add(Restrictions.eq("lastUpdateUser.flag3", new Short((short) 1)))

When i execute this i see in logs that the generated sql is correct as i want it to be (matching lastUpdateUser in jobs to mailId in person). but after that hibernate executes another sql(as in logs)
------------------------------
select * from person where mailId=?

which end up giving hibernate multiple results since mailId is not unique and then hibernate throws this exception which crashes my app.
----------------------------------

Code:

org.hibernate.HibernateException: More than one row with the given identifier was found: turka, for class:

Is there any way to tell hibernate to not execute the second(which i think checks if referenced parameter - mailId is unique or not). or may be some other way to map a column to another column that is not unique.

Any hints wd be highly appreciated.

Thanks
Sanchit

nordborg · **Posted:** Wed Jul 02, 2008 6:47 am

If the mailId is not unique you need a <many-to-many> mapping, not a <many-to-one>. The second SQL happens because Hibernate is trying to load the Person that is linked with the Job through the <many-to-one> mapping.

sanchitsriv · **Posted:** Wed Jul 02, 2008 7:59 am

Thanks for the reply

I agree with your first statement about using mant-to-many mapping.
But then when my first query is executed, i can see in logs that i am getting the data from all the tables(both jobs and person), but after that hibernate again executes a query to get the data from person table even though data is obtained from first one...this is what i cant understand...

Sanchit